Cursors

         Whenever a SQL statement is executed, Oracle automatically allocates a memory area (known as context area) in Oracle database PGA i.e. Process Global Area. This allocated memory space is the query work area which holds the query related information.This is know as cursors

OPEN stage

•PGA memory allocation for cursor processing (OPEN Cursor)
•Parsing of SELECT statement (Parse SQL)
•Variable binding (Bind SQL)
•SELECT Query execution (Execute Query)
•Move the record pointer to the first record

FETCH stage

The record, to which the record pointer points, is pulled from the result set. The record pointer moves only in the forward direction. The FETCH phase lives until the last record is reached.

CLOSE stage

After the last record of the result set is reached, cursor is closed, and allocated memory is flushed off and released back to SGA. Even if an open cursor is not closed, oracle automatically closes it after the execution of its parent block


Cursor FOR loops

Cursor FOR loops improvise upon the performance and code interactivity by their implicit actions

Parameterized Cursors

Parameterized cursors enables programmer to pass parameter to the cursors

Dynamic Cursor FOR Loops

FOR I IN (SELECT EMPLOYEE_NAME, JOB_ID,SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID=10)

FOR UPDATE OF clause is used to lock a set of rows in a session. This concept can be used in explicit cursors to impose exclusive row level lock on all the rows contained by the cursor query result set. These rows will remain locked until the session issues ROLLBACK or COMMIT.

Oracle provides WHERE CURRENT OF clause to update or delete the rows which are locked by the FOR UPDATE OF cursor in the session

UNDERSTANDING EXPLAIN PLAN

    Explain Plan a small overview

            To interpret an execution plan and correctly evaluate your SQL optimization options, you need to first understand the differences between the available database operations. The following topics describe each database access operation identified by the name given to it by the Oracle EXPLAIN PLAN command, along with its characteristics.

For each type of operation, an example is provided. In some cases, the example for an operation will use operations described later in the topic (such as MERGE JOIN, which always uses a SORT JOIN operation).

The operations are classified as row operations or set operations. The following list describes the differences between row and set operations. 

Row Operations	Set Operations
Executed on one row at a time	Executed on a result set of rows
Executed at the FETCH stage, if there is no set operation involved	Executed at the EXECUTE stage when the cursor is opened
Ability to view the first result before the last row is fetched	Inability to view the first result until all rows are fetched and processed
Example: A full-table scan	Example: A full-table scan with a GROUP BY

Aggregation Operations

The following Aggregation operations are available.

COUNT

Counts the rows in the result set to satisfy the COUNT() function

COUNT is executed when the RowNum pseudo-column is used without specifying a maximum value for RowNum. COUNT receives rows from its child operations and increments the RowNum counter. If a limiting counter is used on the RowNum pseudo-column, then the COUNT STOPKEY operation is used instead of COUNT. 
.

Example

.

select Name, City, State, RowNum from COMPANY where City > ‘Roanoke’order by Zip;

.

The query shown in the preceding listing selects rows from the COMPANY. Each row will have the original row number returned.

Execution Plan

SORT ORDER BY
COUNT
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$CITY

 

Interpreting the Execution Plan
.

The Execution Plan shows that the index on the City column is used to find ROWIDs in the COMPANY table that satisfy the WHERE clause condition (where City > ‘Roanoke’). The ROWIDs from the City index scan are used to query the COMPANY table for the Name and State column values. For each row returned, the counter is incremented. Because of the use of the index, the rows that are returned will be the "lowest" city names that are greater than the value ‘Roanoke’. The rows will be returned from the COMPANY$CITY index in ascending order of the City column’s value. The RowNum pseudo-column will then be calculated and put into the row. The SORT ORDER BY operation will order the rows by Zip, as requested in the ORDER BY clause.  The RowNum values are assigned before the ordering takes place.

.

STOPKEY

Counts the numbers of rows returned by a result set and stop processing when a certain number of rows are reached. This is usually the result of a WHERE clause which specifies a maximum ROWNUM (for example, WHERE ROWNUM <=10).

­­­Example 

.

 

Select Name, City, State from COMPANY where City > ‘Roanoke’ and Rownum <= 100;
.

Execution Plan

COUNT STOPKEY
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$CITY

SORT	Performs a sort.
ORDER BY	Sorts a result set to satisfy an ORDER BY clause.
AGGREGATE		Occurs when a group function is used on data, which is already grouped.

SORT AGGREGATE is used to sort and aggregate result sets whenever a grouping function appears in a SQL statement without a GROUP BY clause. Grouping functions include MAX, MIN, COUNT, SUM, and AVG.

Example 

.

select SUM(Total) from SALES;
.

SORT AGGREGATE
TABLE ACCESS FULL SALES
.

The Execution Plan shows that after the SALES table is scanned (via the TABLE ACCESS FULL operation), the records are passed to the SORT AGGREGATE operation. SORT AGGREGATE sums the Total values and returns the output to the user.

JOIN	Sorts the rows in preparation for a merge join.

SORT JOIN sorts a set of records that is to be used in a MERGE JOIN operation.

Example

select C.Name  from COMPANY c, SALES s where C.Company_ID+0 = S.Company_ID+0
and S.Period_ID =3 and S.Sales_Total>1000;

Execution Plan

MERGE JOIN
SORT JOIN
TABLE ACCESS FULL SALES
SORT JOIN
TABLE ACCESS FULL COMPANY

Interpreting the Execution Plan

The Execution Plan shows that the COMPANY table and SALES table will be accessed using TABLE ACCESS FULL operations. Before the records from those tables are passed to the MERGE JOIN operation, they will first be processed by SORT JOIN operations that sort the records. The SORT JOIN output is used as input to the MERGE JOIN operation.

Benefit of Cursor For loop for 10g

Which will run much slower than the other two, and why?

a.
DECLARE
CURSOR employees_cur IS SELECT * FROM employees;
BEGIN
FOR employee_rec IN employees_cur LOOP
do_stuff (employee_rec);
END LOOP;
END;

b.
DECLARE
CURSOR employees_cur IS SELECT * FROM employees;
l_employee employees%ROWTYPE;
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur INTO l_employee;
EXIT WHEN employees_cur%NOTFOUND;
do_stuff (l_employee);
END LOOP;
CLOSE employees_cur;
END;

c.
DECLARE
CURSOR employees_cur IS SELECT * FROM employees;
TYPE employees_aat IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
l_employees employees_aat;
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur
BULK COLLECT INTO l_employees LIMIT 100;
EXIT WHEN l_employees.COUNT () = 0;
FOR indx IN 1 .. l_employees.COUNT
LOOP
do_stuff (l_employees (indx));
END LOOP;
END LOOP;
CLOSE employees_cur;
END;

(b) Is the slowest. That's because on Oracle 10g and higher, the PL/SQL optimizer will automatically rewrite cursor FOR loops so that they are executed in the same way as the BULK COLLECT query.