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 was introduced in Oracle 9.0.1
This operation is implemented in the following versions
|
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