• Welcome
• Projects
• Presentations
• Research

TEMP TABLE GENERATION

Description

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

Versions

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

Example

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'