BITMAP KEY ITERATION

Description

Iterates through bitmaps. Used in star transactions

Versions

This operation is implemented in the following versions

Example

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'