Parameter: always_star_transformation

Description

always favor use of star transformation

Unsupported Versions

This unsupported parameter was introduced in Oracle 8.0.4

Syntax

_ALWAYS_STAR_TRANSFORMATION = FALSE | TRUE

Example

This example was developed on an Oracle 9.2 database using a 2048 byte block size.

This example cannot be generated using empty tables. The fact table 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.

The 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
    EXECUTE DBMS_STATS.SET_TABLE_STATS (USER,'T1',numrows=>100000);

    CREATE TABLE t2 (c1 NUMBER,c2 NUMBER);
    CREATE TABLE t3 (c1 NUMBER,c2 NUMBER);

This example also requires the parameter STAR_TRANSFORMATION_ENABLED to be set to TRUE

    ALTER SESSION SET star_transformation_enabled = TRUE;

With the default value for _ALWAYS_STAR_TRANSFORMATION

    ALTER SESSION SET "_always_star_transformation" = FALSE;

the statement

    SELECT 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 execution plan

    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 (FULL) OF 'T1'

With _ALWAYS_STAR_TRANSFORMATION set to HASH

    ALTER SESSION SET "_always_star_transformation" = HASH;

the same statement generates the following execution plan

    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 (FULL) 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'