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
This operation was introduced in Oracle 9.0.1
This operation is implemented in the following versions
|
Not yet reproduced in Oracle 10.1 or Oracle 10.2
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