BITMAP JOIN INDEX UPDATE STATEMENT

Description

Update bitmap join index

This operation is performed as a recursive autonomous transaction to update a bitmap join index when DML is performed on either of the base tables

This operation can only be viewed using V$SQL_PLAN

Versions

This operation was introduced in Oracle 9.0.1

This operation is implemented in the following versions

9.0.1
9.2.0

Not yet reproduced in Oracle 10.1 or Oracle 10.2

Example

This example was developed using Oracle 9.2.0.1 on Windows 2000

This operation cannot be detected using AUTOTRACE or EXPLAIN PLAN or SQL trace

This example requires the following table and index definitions 

    -- Create dimension table
    CREATE TABLE t1 (c1 NUMBER PRIMARY KEY,c2 NUMBER);

    INSERT INTO t1 VALUES (1,100);
    COMMIT;

    -- Create fact table
    CREATE TABLE t2 (c1 NUMBER,c2 NUMBER);

    -- Create bitmap index
    CREATE BITMAP INDEX i1
    ON t2 (t1.c1)
    FROM t1,t2
    WHERE t1.c1 = t2.c1;

The statement 

    INSERT INTO t2 VALUES (1,1000);

generates the following recursive execution statement (SQL Trace) 

    UPD_JOININDEX "US01"."BJI2I1" AS
    SELECT T32551."C1", T32553.L$ROWID 
    FROM "US01"."BJI1" T32551, SYS.L$12 T32553 
    WHERE T32553."C1" = T32551."C1";

Note that this statement cannot be executed using SQL*Plus 

0      BITMAP JOIN INDEX UPDATE STATEMENT
1        BITMAP JOIN INDEX UPDATE
2    1     NESTED LOOPS
3    2       TABLE ACCESS (FULL) OF L$12
4    2       INDEX (UNIQUE SCAN) OF SYS_C001843