Generate a temporary table. Used in star transformations joining fact and dimension tables with very large numbers of rows
Note that this operation does NOT appear in V$SQL_PLAN
This operation was probably introduced in Oracle 8.1.5
This operation is implemented in the following versions
|
This operation has not yet been observed in Oracle 10.2
This operation cannot be generated using empty tables. The fact table and the dimension tables must contain some rows. In Oracle 8.1.5 and above this can be simulated using DBMS_STATS.SET_TABLE_STATS.
This example will only work in Oracle 8.1.5 and above because of the modification of the statistics.
This example was developed using Oracle 9.2.0.1 on Windows 2000
This example requires the following table and index definitions
CREATE TABLE t1 (c1 NUMBER,c2 NUMBER,c3 NUMBER); CREATE BITMAP INDEX i1 ON t1 (c2); CREATE BITMAP INDEX i2 ON t1 (c3); -- Set the number of rows for t1 EXECUTE DBMS_STATS.SET_TABLE_STATS - (USER,'T1',numrows=>10000000); CREATE TABLE t2 (c1 NUMBER,c2 NUMBER); -- Set the number of rows for t2 EXECUTE DBMS_STATS.SET_TABLE_STATS - (USER,'T2',numrows=>10000); CREATE TABLE t3 (c1 NUMBER,c2 NUMBER); -- Set the number of rows for t3 EXECUTE DBMS_STATS.SET_TABLE_STATS - (USER,'T3',numrows=>10000);
The statement
SELECT /*+ STAR_TRANSFORMATION */ t1.c1, t2.c2, t3.c2 FROM t1,t2,t3 WHERE t2.c1 = t1.c2 AND t3.c1 = t1.c3 AND t2.c2 = 0 AND t3.c2 = 0;
generates the following execution plan (in 8.1.7 and 9.0.1)
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TEMP TABLE GENERATION 2 0 TEMP TABLE GENERATION 3 0 TEMP TABLE GENERATION 4 0 TEMP TABLE GENERATION 5 4 HASH JOIN 6 5 TABLE ACCESS (FULL) OF 'ORA_TEMP_1_609' 7 5 HASH JOIN 8 7 TABLE ACCESS (FULL) OF 'ORA_TEMP_1_608' 9 7 TABLE ACCESS (BY INDEX ROWID) OF 'T1' 10 9 BITMAP CONVERSION (TO ROWIDS) 11 10 BITMAP AND 12 11 BITMAP MERGE 13 12 BITMAP KEY ITERATION 14 13 TABLE ACCESS (FULL) OF 'ORA_TEMP_1_609' 15 13 BITMAP INDEX (RANGE SCAN) OF 'I1' 16 11 BITMAP MERGE 17 16 BITMAP KEY ITERATION 18 17 TABLE ACCESS (FULL) OF 'ORA_TEMP_1_608' 19 17 BITMAP INDEX (RANGE SCAN) OF 'I2'