Iterates through bitmaps. Used in star transactions
This operation is implemented in the following versions
|
This operation cannot be generated using empty tables. The fact table must contain some rows. In 8.1.5 and above, this can be simulated using DBMS_STATS.SET_TABLE_STATS
This example will only work in 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
ALTER SESSION SET star_transformation_enabled = TRUE; -- Create the fact table CREATE TABLE t1 (c1 NUMBER,c2 NUMBER,c3 NUMBER); -- Create bitmap indexes on the fact table CREATE BITMAP INDEX i1 ON t1 (c1); CREATE BITMAP INDEX i2 ON t1 (c2); -- Set the number of rows in the fact table EXECUTE DBMS_STATS.SET_TABLE_STATS (USER,'T1',numrows=>100000); -- Create the dimension tables CREATE TABLE t2 (c1 NUMBER,c2 NUMBER); CREATE TABLE t3 (c1 NUMBER,c2 NUMBER);
The statement
SELECT /*+ STAR_TRANSFORMATION */ t1.c01,t2.c02, t3.c02 FROM t1,t2,t3 WHERE t2.c01 = t1.c02 AND t3.c01 = t1.c03 AND t2.c02 = 0 AND t3.c02 = 0;
generates the following execution plan
In Oracle 8.1.7
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 HASH JOIN 2 1 MERGE JOIN (CARTESIAN) 3 2 TABLE ACCESS (FULL) OF 'T2' 4 2 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF 'T3' 6 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' 7 6 BITMAP CONVERSION (TO ROWIDS) 8 7 BITMAP AND 9 8 BITMAP MERGE 10 9 BITMAP KEY ITERATION 11 10 TABLE ACCESS (FULL) OF 'T2' 12 10 BITMAP INDEX (RANGE SCAN) OF 'I1' 13 8 BITMAP MERGE 14 13 BITMAP KEY ITERATION 15 14 TABLE ACCESS (FULL) OF 'T3' 16 14 BITMAP INDEX (RANGE SCAN) OF 'I2'
In Oracle 9.0.1 and 9.2.0
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 HASH JOIN 2 1 MERGE JOIN (CARTESIAN) 3 2 TABLE ACCESS (FULL) OF 'T2' 4 2 BUFFER (SORT) 5 4 TABLE ACCESS (FULL) OF 'T3' 6 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' 7 6 BITMAP CONVERSION (TO ROWIDS) 8 7 BITMAP AND 9 8 BITMAP MERGE 10 9 BITMAP KEY ITERATION 11 10 TABLE ACCESS (FULL) OF 'T2' 12 10 BITMAP INDEX (RANGE SCAN) OF 'I1' 13 8 BITMAP MERGE 14 13 BITMAP KEY ITERATION 15 14 TABLE ACCESS (FULL) OF 'T3' 16 14 BITMAP INDEX (RANGE SCAN) OF 'I2'
Not yet tested in Oracle 10.1.0
In Oracle 10.2.0
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 HASH JOIN 2 1 HASH JOIN 3 2 TABLE ACCESS (FULL) OF 'T2' 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'T1' 5 4 BITMAP CONVERSION (TO ROWIDS) 6 5 BITMAP AND 7 6 BITMAP MERGE 8 7 BITMAP KEY ITERATION 9 8 TABLE ACCESS (FULL) OF 'T2' 10 8 BITMAP INDEX (RANGE SCAN) OF 'I1' 11 6 BITMAP MERGE 12 11 BITMAP KEY ITERATION 13 12 TABLE ACCESS (FULL) OF 'T3' 14 12 BITMAP INDEX (RANGE SCAN) OF 'I2' 15 1 TABLE ACCESS (FULL) OF 'T3'