BITMAP JOIN INDEX UPDATE

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

Versions

This operation was introduced in Oracle 9.0.1

This operation is implemented in the following versions

Example

This example was developed using Oracle 9.2.0.1 on Windows 2000

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

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