Describe SQL Hidden Access Paths

SQL was the first database access language to hide the actual access path to the data tables. Prior to SQL, the database programmer would explicitly specify which indexes were to be used to access a data structure and what linked-lists were to be traversed to achieve the desired result. A declarative language, however, like SQL, is one where the access path to the data is not required. One simply declares, as in the example below:

SELECT
     Stuff
FROM
     tables
WHERE
    Boolean_conditions;

• Execution Plan is created using Declarative Language:
  The database will figure the best path to the data. The following series of images illustrate how an execution plan is created using a declarative language.

SQL (Structured Query Language) is traditionally a declarative language used for querying and manipulating relational databases. In its standard form, SQL allows you to specify what data you want but not how to process it. Oracle Corporation extended SQL by introducing procedural features through PL/SQL (Procedural Language/SQL), effectively transforming SQL into a procedural language within the Oracle database environment. This enhancement allows developers to write complex scripts that include variables, control structures, and error handling mechanisms.
Key Extensions by Oracle:

1. PL/SQL (Procedural Language/SQL): Variables and Data Types: PL/SQL allows the declaration of variables, constants, and complex data types, enabling storage and manipulation of intermediate data.
   

   DECLARE
   	v_employee_id NUMBER;
   	v_employee_name VARCHAR2(100);
   BEGIN
   	-- Code logic here
   END;
   

   
   Control Structures:
   • Conditional Statements: Use `IF...THEN...ELSE` to execute code based on conditions.
     

     IF v_salary > 5000 THEN
      v_bonus := v_salary * 0.10;
     ELSE
      v_bonus := v_salary * 0.05;
     END IF;
     

   • Loops: Implement iterative operations using `LOOP`, `WHILE`, and `FOR` loops.
     

     FOR i IN 1..10 LOOP
      DBMS_OUTPUT.PUT_LINE('Iteration ' || i);
     END LOOP;
     

   • Exception Handling: Manage runtime errors gracefully using `EXCEPTION` blocks.
     

      EXCEPTION
        WHEN NO_DATA_FOUND THEN
     	 DBMS_OUTPUT.PUT_LINE('No records found.');
        WHEN OTHERS THEN
     	 DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
      END;
      

2. Stored Procedures and Functions:
   • Modularity: Encapsulate complex operations into reusable procedures and functions.
     

      CREATE OR REPLACE PROCEDURE update_salary(p_employee_id NUMBER, p_increment NUMBER) IS
      BEGIN
        UPDATE employees SET salary = salary + p_increment WHERE employee_id = p_employee_id;
      END;
      

   • Improved Performance: Reduce network traffic by executing logic on the server side.
3. Packages: Grouping Related Elements: Packages allow grouping of related procedures, functions, variables, and cursors.
   

   CREATE OR REPLACE PACKAGE employee_pkg IS
   	PROCEDURE hire_employee(...);
   	PROCEDURE fire_employee(...);
   END employee_pkg;
   

4. Triggers: Automated Execution: Automatically execute procedural code in response to specific database events like `INSERT`, `UPDATE`, or `DELETE`.
   

    CREATE OR REPLACE TRIGGER update_employee_audit
    AFTER UPDATE ON employees
    FOR EACH ROW
    BEGIN
      INSERT INTO employee_audit VALUES (:OLD.employee_id, :NEW.salary, SYSDATE);
    END;
    

5. Cursors: Row-by-Row Processing: Fetch and manipulate query results one row at a time.
   

    DECLARE
      CURSOR emp_cursor IS SELECT employee_id, salary FROM employees;
    BEGIN
      FOR emp_record IN emp_cursor LOOP
   	 -- Process each record
      END LOOP;
    END;
    

6. Dynamic SQL: Flexible Query Execution: Execute SQL statements constructed at runtime using `EXECUTE IMMEDIATE`.
   

   EXECUTE IMMEDIATE 'UPDATE employees SET salary = salary * 1.1 
   WHERE department_id = :dept_id' USING v_dept_id;
   

Impact on SQL as a Procedural Language:

• Enhanced Capabilities: Oracle's extensions allow for complex business logic to be implemented directly within the database.
• Performance Optimization: By processing data on the server side, applications reduce network latency and improve efficiency.
• Robust Error Handling: PL/SQL provides mechanisms to catch and handle exceptions, ensuring smoother execution.
• Modularity and Reusability: Procedures, functions, and packages promote code reuse and better organization.
• Security: By embedding logic within the database, sensitive operations can be secured and audited more effectively.

Conclusion Oracle transformed SQL into a procedural language by introducing PL/SQL, which integrates procedural programming constructs with SQL's declarative nature. This hybrid language enables developers to write complex scripts involving variables, control flow statements, exception handling, and modular programming constructs like procedures and packages. The result is a powerful tool that combines the strengths of SQL for data manipulation with the flexibility of procedural programming, allowing for the development of robust, efficient, and maintainable database applications.
SQL used to be nothing more than a data access method, a way to get rows from the database into an application program, but Oracle's extensions have transformed SQL into a procedural language. SQL as a declarative language has many ways to create and execute any query, with identical results but with a huge variation in performance. There are several areas of SQL programming best practices:

1. Security: The application developer wants to avoid SQL injections
2. Performance: Using low-impact techniques like analytic functions, rewriting subqueries whenever possible.
3. Standards: Writing standard SQL is a challenge.

The premise behind SQL was simple. The person executing the SQL command should only specify the data that is required, but should not have to know the exact access path to the data. To achieve this result, SQL optimizers were constructed to determine the access path to the data. An SQL optimizer is a software program that reads the SQL and examines the data dictionary to figure out the fastest way to service the query. It is important to remember that SQL is not 100% declarative. We still need to tell the optimizer the names of the tables that contain the data that we want to see, and we must manually specify the join criteria when multiple tables are involved.

• New database access paradigm Prior to the introduction of SQL, there was only one correct way to write a database query. However, SQL provided added flexibility by allowing numerous ways to specify a query. Given that each variant of the query returns identical results, the only criteria for correctness is the speed in which the query is serviced. To illustrate, consider the following identical queries to display the honor roll list for a school.

This simple example should illustrate the tremendous importance of properly tuned SQL on the overall performance of your database. If we can determine the access path to data, we can determine if SQL is optimized for Oracle. To accomplish this, we must use the EXPLAIN PLAN utility. So let us get started by examining the access path to Oracle tables.