Executes a recursive SQL statement
Note that this operation does NOT appear in V$SQL_PLAN
This operation was introduced in Oracle 9.0.1
This operation is implemented in the following versions
|
This example was developed using Oracle 9.2.0.1 on Windows 2000
This example requires the following table definition
CREATE TABLE t1 (c1 NUMBER);
The table does not need to be analysed
Consider the statement
SELECT c1,c2,COUNT(*) FROM t1 GROUP BY GROUPING SETS (c1,c2);
In Oracle 9.2 this statement generates the following execution plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 4 RECURSIVE EXECUTION OF 'SYS_LE_4_0' 2 4 RECURSIVE EXECUTION OF 'SYS_LE_4_1' 3 4 RECURSIVE EXECUTION OF 'SYS_LE_4_2' 4 0 TEMP TABLE TRANSFORMATION 5 4 VIEW 6 5 TABLE ACCESS (FULL) OF 'SYS_TEMP_1_0_FD9D6603'
In Oracle 10.2 this statement generates the following execution plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TEMP TABLE TRANSFORMATION 2 1 LOAD AS SELECT 3 2 TABLE ACCESS (FULL) OF 'T1' 4 1 LOAD AS SELECT 5 4 HASH GROUP BY 6 5 TABLE ACCESS (FULL) OF 'SYS_TEMP_OFD9D6602_6718F7' 7 1 LOAD AS SELECT 8 7 HASH GROUP BY 9 8 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6602_6718F7' 10 1 VIEW 11 10 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6602_6718F7'