Transcript Bitmap Index Internals
Bitmap Index Internals
Julian Dyke Independent Consultant
Web Version
1 © 2005 Julian Dyke
juliandyke.com
Agenda
Introduction
Bitmap Row Sources
Internal Structures
Bitmap Index DML
Conclusion
2 © 2005 Julian Dyke
juliandyke.com
3 © 2005 Julian Dyke
Introduction
juliandyke.com
Bitmap Indexes
Introduced in Oracle 7.3
Originally intended for columns with
Low cardinality (few distinct values)
Little or no DML activity
Often used in DSS environments
Useful for ad-hoc queries where predicates are not known in advance 4 © 2005 Julian Dyke
juliandyke.com
Bitmap Index Properties
Bitmap indexes
Must be non-unique
Include NULL values (B*Tree indexes do not)
Maximum 30 columns (B*Tree indexes max 32 columns)
Can be locally partitioned (8.1.5 and above)
Cannot be globally partitioned
Can be function-based indexes (built-in or user-defined)
Can be IOT secondary indexes (9.2 and above)
Can be created on global temporary tables
Cannot be compressed
Cannot be reversed 5 © 2005 Julian Dyke
juliandyke.com
Bitmap Indexes
For example ( CREATE TABLE property property_code NUMBER, bedrooms NUMBER, receptions NUMBER, garages NUMBER ); CREATE BITMAP INDEX index1 ON property (bedrooms); CREATE BITMAP INDEX index2 ON property (receptions); CREATE BITMAP INDEX index3 ON property (garages); SELECT property_code FROM property WHERE bedrooms = 4 AND receptions = 3 AND garages = 2 6 © 2005 Julian Dyke
juliandyke.com
Logical Operations
Bitmaps can be combined using the logical operations AND , OR , NOT .
A 0 0 1 1 B 0 1 0 1 A AND B 0 0 0 1 A OR B 0 1 1 1 NOT A 1 1 0 0
Oracle also implements a MINUS operation internally
A MINUS B is equivalent to A AND NOT B 7 © 2005 Julian Dyke
juliandyke.com
Multicolumn Bitmap Indexes
Bitmap indexes can be defined for more than one column CREATE BITMAP INDEX i1 ON t1 (c1,c2);
Can be used for queries such as SELECT * FROM t1 WHERE c1 = 0; SELECT * FROM t1 WHERE c1 = 0 AND c2 = 0;
However for queries such as SELECT * FROM t1 WHERE c2 = 0;
there is apparently no equivalent to INDEX (SKIP SCAN) Consider creating two indexes and allowing Oracle to perform join dynamically CREATE BITMAP INDEX i1 ON t1 (c1); CREATE BITMAP INDEX i2 ON t1 (c2); 8 © 2005 Julian Dyke
juliandyke.com
9 © 2005 Julian Dyke
Bitmap Row Sources
juliandyke.com
Bitmap Row Sources
Operation BITMAP INDEX (SINGLE VALUE) BITMAP INDEX (RANGE SCAN) BITMAP INDEX (FULL SCAN) BITMAP INDEX (FAST FULL SCAN) BITMAP AND BITMAP OR BITMAP MINUS BITMAP MERGE BITMAP KEY ITERATION BITMAP CONVERSION TO ROWIDS BITMAP CONVERSION COUNT BITMAP CONVERSION FROM ROWIDS BITMAP CONSTRUCTION BITMAP COMPACTION 10 © 2005 Julian Dyke Version 7.3.2
7.3.2
7.3.2
9.0.1
7.3.2
7.3.2
7.3.2
7.3.2
7.3.2
7.3.2
7.3.2
7.3.2
7.3.2
7.3.2
juliandyke.com
Bitmap Index Operation
Introduced in 7.3
Options are
BITMAP INDEX (SINGLE VALUE)
Builds a single bitmap for a given value
BITMAP INDEX (RANGE SCAN)
Builds a bitmap containing each value in the range
BITMAP INDEX (FULL SCAN)
Builds a bitmap containing each value in the index
BITMAP INDEX (FAST FULL SCAN)
Oracle 9.0.1 and above
Equivalent to INDEX (FAST FULL SCAN) 11 © 2005 Julian Dyke
juliandyke.com
Bitmap Conversion Operation
Introduced in Oracle 7.3
Options are
BITMAP CONVERSION (TO ROWIDS)
Converts a bitmap into a set of ROWIDs
BITMAP CONVERSION (COUNT)
Returns the number of set bits in a bitmap
Used to implement COUNT() aggregates
BITMAP CONVERSION (FROM ROWIDS)
Converts a set of ROWIDs into a bitmap 12 © 2005 Julian Dyke
juliandyke.com
Bitmap And Operation
Introduced in Oracle 7.3 Performs logical AND operation between two bitmaps CREATE TABLE t1 (c1 NUMBER, c2 NUMBER); CREATE BITMAP INDEX i1 ON t1 (c1); CREATE BITMAP INDEX i2 ON t1 (c2); SELECT /*+ INDEX_COMBINE (t1 i1 i2) */ c1,c2 FROM t1 WHERE c1 = 0 AND c2 = 0; 0 1 0 2 1 3 2 4 2 SELECT STATEMENT Optimizer=CHOOSE BITMAP CONVERSION (TO ROWIDS) BITMAP AND BITMAP INDEX (SINGLE VALUE) OF 'I1‘ BITMAP INDEX (SINGLE VALUE) OF 'I2‘ 13 © 2005 Julian Dyke
juliandyke.com
Bitmap Or Operation
Introduced in Oracle 7.3 Performs logical OR operation between two bitmaps CREATE TABLE t1 (c1 NUMBER, c2 NUMBER); CREATE BITMAP INDEX i1 ON t1 (c1); CREATE BITMAP INDEX i2 ON t1 (c2); SELECT /*+ INDEX_COMBINE (t1 i1 i2) */ c1,c2 FROM t1 WHERE c1 = 0 OR c2 = 0; 0 1 0 2 1 3 2 4 3 5 3 SELECT STATEMENT Optimizer=CHOOSE TABLE ACCESS (BY INDEX ROWID) OF 'T1‘ BITMAP CONVERSION (TO ROWIDS) BITMAP OR BITMAP INDEX (SINGLE VALUE) OF 'I1‘ BITMAP INDEX (SINGLE VALUE) OF 'I2' 14 © 2005 Julian Dyke
juliandyke.com
Bitmap Minus Operation
Introduced in Oracle 7.3 Performs logical MINUS operation between two bitmaps CREATE TABLE t1 (c1 NUMBER, c2 NUMBER); CREATE BITMAP INDEX i1 ON t1 (c1); CREATE BITMAP INDEX i2 ON t1 (c2); SELECT /*+ INDEX_COMBINE (t1 i1 i2) */ c1,c2 FROM t1 WHERE c1 = 0 AND NOT c2 = 0; 15 0 1 0 2 1 3 2 4 3 5 4 6 4 7 3 SELECT STATEMENT Optimizer=CHOOSE TABLE ACCESS (BY INDEX ROWID) OF 'T1‘ BITMAP CONVERSION (TO ROWIDS) BITMAP MINUS BITMAP MINUS BITMAP INDEX (SINGLE VALUE) OF 'I1‘ BITMAP INDEX (SINGLE VALUE) OF 'I2‘ BITMAP INDEX (SINGLE VALUE) OF 'I2' © 2005 Julian Dyke
juliandyke.com
Bitmap Merge Operation
Introduced in Oracle 7.3 Merges two or more bitmaps together CREATE TABLE t1 (c1 NUMBER, c2 NUMBER); CREATE BITMAP INDEX i1 ON t1 (c1); CREATE BITMAP INDEX i2 ON t1 (c2); SELECT /*+ INDEX_COMBINE (t1 i1 i2) */ c1,c2 FROM t1 WHERE c1 > 0 AND c2 = 0; 0 1 0 2 1 3 2 4 3 5 3 6 5 SELECT STATEMENT Optimizer=CHOOSE TABLE ACCESS (BY INDEX ROWID) OF 'T1' BITMAP CONVERSION (TO ROWIDS) BITMAP AND BITMAP INDEX (SINGLE VALUE) OF 'I2‘ BITMAP MERGE BITMAP INDEX (RANGE SCAN) OF 'I1' 16 © 2005 Julian Dyke
juliandyke.com
Bitmap Construction / Compaction
Introduced in Oracle 7.3 Used by index creation/rebuild operations Appear in EXPLAIN PLAN Do not appear in AUTOTRACE or V$SQL_PLAN CREATE TABLE t1 (c1 NUMBER); EXPLAIN PLAN FOR CREATE BITMAP INDEX i1 ON t1 (c01) 0 1 0 2 1 3 2 4 3 5 4 CREATE INDEX STATEMENT Optimizer=CHOOSE INDEX BUILD (NON UNIQUE) OF 'I1‘ BITMAP COMPACTION SORT (CREATE INDEX) BITMAP CONSTRUCTION TABLE ACCESS (FULL) OF 'T1' 17 © 2005 Julian Dyke
juliandyke.com
Bitmap Key Iteration
Introduced in Oracle 7.3
Used to implement star transformations Star transformations join three or more tables together -- Create fact table CREATE TABLE fact (factkey NUMBER,dim1key NUMBER,dim2key NUMBER); -- Create bitmap indexes on fact columns CREATE BITMAP INDEX index1 ON fact (dim1key); CREATE BITMAP INDEX index2 ON fact (dim2key); -- Set the number of rows BEGIN DBMS_STATS.SET_TABLE_STATS(USER,’FACT’,numrows=>100000); END; -- Create dimension tables CREATE TABLE dim1 (dim1key NUMBER,dim1desc NUMBER); CREATE TABLE dim2 (dim2key NUMBER,dim2desc NUMBER); 18 © 2005 Julian Dyke
juliandyke.com
Bitmap Key Iteration
Star transformations require the following parameter ALTER SESSION SET star_transformation_enabled = TRUE;
The statement SELECT /*+ STAR_TRANSFORMATION */ fact.factkey, dim1.dim1key, dim2.dim2key
FROM fact, dim1, dim2 WHERE dim1.dim1key = fact.dim1key
AND dim2.dim2key = fact.dim2key
AND dim1.dim1desc = 100 AND dim2.dim2desc = 200; 19 © 2005 Julian Dyke
juliandyke.com
Bitmap Key Iteration
In Oracle 9.0.1 and above, the following plan is generated 0 1 2 3 4 5 6 7 8 6 7 9 8 10 9 11 10 12 10 13 8 0 1 2 2 4 1 14 13 15 14 16 14 SELECT STATEMENT Optimizer=CHOOSE HASH JOIN MERGE JOIN (CARTESIAN) TABLE ACCESS (FULL) OF ‘DIM1' BUFFER (SORT) TABLE ACCESS (FULL) OF ‘DIM2' TABLE ACCESS (BY INDEX ROWID) OF ‘FACT‘ BITMAP CONVERSION (TO ROWIDS) BITMAP AND BITMAP MERGE BITMAP KEY ITERATION TABLE ACCESS (FULL) OF ‘DIM1‘ BITMAP INDEX (RANGE SCAN) OF 'INDEX1‘ BITMAP MERGE BITMAP KEY ITERATION TABLE ACCESS (FULL) OF ‘DIM2‘ BITMAP INDEX (RANGE SCAN) OF 'INDEX2' 20 © 2005 Julian Dyke
juliandyke.com
21
B*Tree Bitmap Plans
In Oracle 7.3 and above it is possible to convert B*tree indexes into bitmaps Enabled using _B_TREE_BITMAP_PLANS parameter Default value FALSE (8.1.7 and below), TRUE (9.0.1 and above)
For example (8192 byte block size) CREATE TABLE t1 (c1 NUMBER, c2 NUMBER); BEGIN FOR f IN 0..9999 LOOP INSERT INTO t1 VALUES (MOD (f,100), MOD (f,200)); END LOOP; END; COMMIT; CREATE INDEX i1 ON t1 (c1); -- Not bitmap index CREATE INDEX i2 ON t1 (c2); -- Not bitmap index ANALYZE TABLE t1 COMPUTE STATISTICS; © 2005 Julian Dyke
juliandyke.com
B*Tree Bitmap Plans
In Oracle 9.2 the statement SELECT c1, c2 FROM t1 WHERE c1 = 0 AND c2 = 0;
generates the plan 0 1 0 2 1 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=5) BITMAP CONVERSION (TO ROWIDS) BITMAP AND 3 2 4 3 5 2 6 5 BITMAP CONVERSION (FROM ROWIDS) INDEX (RANGE SCAN) OF 'I2' (NON-UNIQUE) (Cost=1) BITMAP CONVERSION (FROM ROWIDS) INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) (Cost=1) 22 © 2005 Julian Dyke
juliandyke.com
23 © 2005 Julian Dyke
Internal Structure
juliandyke.com
Leaf Block Format
Each value in the index is represented by one or more bitmaps Each bitmap represents a range of ROWIDs Each leaf row entry typically contains four columns Column 1 2 3 4 Description Column Value Start ROWID End ROWID Bitmap
A multi-column index will have additional key columns Branch block format and characteristics are identical to B*Tree indexes 24 © 2005 Julian Dyke
juliandyke.com
Bitmaps and Leaf Blocks
For example, consider a bitmap index on column BEDROOMS Key Start ROWID End ROWID Bitmap 2 211/0 212/3 10000010 3 211/0 212/3 00110101 4 211/0 212/3 01001000 PROPERTY_CODE BEDROOMS RECEPTIONS GARAGES 2 4 3 3 Block 211 25 © 2005 Julian Dyke 4 3 2 3 Block 212
juliandyke.com
Bitmap Block Dump
Partial block dump for the previous slide row#0[8013] flag: -----, lock: 0 col 0; len 2; (2): c1 03 col 1; len 6; (6): 01 00 01 82 00 00 col 2; len 6; (6): 01 00 01 83 00 07 col 3; len 3; (3): 00 c2 44 row#1[7988] flag: -----, lock: 0 col 0; len 2; (2): c1 04 col 1; len 6; (6): 01 00 01 82 00 00 col 2; len 6; (6): 01 00 01 83 00 07 col 3; len 5; (5): c8 0c f8 56 0a row#2[7965] flag: -----, lock: 0 col 0; len 2; (2): c1 05 col 1; len 6; (6): 01 00 01 82 00 00 col 2; len 6; (6): 01 00 01 83 00 07 col 3; len 3; (3): 01 c0 44 26 © 2005 Julian Dyke Key Start ROWID End ROWID Bitmap
juliandyke.com
B*Tree Block Dump
27 Key row#0[8024] flag: -----, lock: 0 col 0; len 2; (2): c1 03 col 1; len 6; (6): 01 00 01 82 00 00 row#1[8012] flag: -----, lock: 0 col 0; len 2; (2): c1 03 col 1; len 6; (6): 01 00 01 83 00 02 row#2[8000] flag: -----, lock: 0 col 0; len 2; (2): c1 04 col 1; len 6; (6): 01 00 01 82 00 02 row#3[7988] flag: -----, lock: 0 col 0; len 2; (2): c1 04 col 1; len 6; (6): 01 00 01 82 00 03 ROWID row#4[7976] flag: -----, lock: 0 col 0; len 2; (2): c1 04 col 1; len 6; (6): 01 00 01 83 00 01 row#5[7964] flag: -----, lock: 0 col 0; len 2; (2): c1 04 col 1; len 6; (6): 01 00 01 83 00 03 row#6[7952] flag: -----, lock: 0 col 0; len 2; (2): c1 05 col 1; len 6; (6): 01 00 01 82 00 01 row#7[7940] flag: -----, lock: 0 col 0; len 2; (2): c1 05 col 1; len 6; (6): 01 00 01 83 00 00 © 2005 Julian Dyke
juliandyke.com
Bitmap Pieces
Every bitmap piece has a start ROWID and an end ROWID
Start ROWID is rounded to the nearest byte boundary below End ROWID is rounded to the nearest byte boundary above
Each indexed column value may have one or more bitmap pieces
A bitmap piece
covers a contiguous set of rows in one or more extents
may cross extent boundaries
may split within a block 28 © 2005 Julian Dyke
juliandyke.com
Bitmap Pieces
For example the following are all valid bitmap pieces Start 211/0 Start 211/0 End 211/15 Start 532/0 Start 211/0 Start 211/0 End 532/7 End 533/15 End 533/15 End 532/15 Start 533/0 End 533/15 Start 532/8 End 533/15 Start 211/8 Start 532/8 End 533/7 End 532/15 29 Block 211 Extent 1 © 2005 Julian Dyke Block 532 Extent 2 Block 533
juliandyke.com
Compression Algorithm
Bitmaps consists of zeros and ones Zeros are compressed; ones are not compressed Bitmaps can be subdivided into groups of bytes First byte in each group determines length of group First Byte < 192 >= 192 Description Single-byte group Multi-byte group 30 © 2005 Julian Dyke
juliandyke.com
31
Single-Byte Groups
Byte represents the number of zero bits followed by a one bit Maximum of 191 zero bits Byte 00 10 11 17 18 19 01 02 03 08 0F Bitmap 10000000 01000000 00100000 00010000 00000000 10000000 00000000 00000001 00000000 00000000 10000000 00000000 00000000 01000000 00000000 00000000 00000001 00000000 00000000 00000000 10000000 00000000 00000000 00000000 01000000
Range of byte values is 0x00 to 0xBF © 2005 Julian Dyke
juliandyke.com
Multi-Byte Groups
Multi-byte groups allow more than 192 bits to be skipped First byte is a control byte 1 1
First two bits indicate this is a control byte (always 11)
Next three bits indicate number of zero bytes to skip If all three bits are set then number overflows to second byte If top bit of second byte is set then number of zero bytes overflows to third byte 32
Last three bits indicate number of bytes following control block (minimum 1, maximum 8) © 2005 Julian Dyke
juliandyke.com
33
Multi-Byte Groups
Examples Hex C8 D0 D8 E0 E8 F0 F8 00 F8 01 F8 02 F8 7F F8 80 01 F8 81 01 © 2005 Julian Dyke Code Binary 11 001 000 11 010 000 11 011 000 11 100 000 11 101 000 11 110 000 11 111 000 00000000 11 111 000 00000001 11 111 000 00000010 11 111 000 01111111 11 111 000 10000000 00000001 11 111 000 10000001 00000001 Number of Zero Bytes Number of Zero Bits 0 8 133 134 135 1 2 3 4 5 6 7 0 8 16 24 32 40 48 56 64 1064 1072 1080
juliandyke.com
Multi-Byte Groups
Last three bits indicate number of bytes following control block (minimum 1, maximum 8) Hex Code Binary C8 C9 CA CB CC CD CE CF 11001 11001 11001 11001 11001 11001 000 001 010 11001 011 11001 100 101 110 111 Number of bytes 6 7 8 1 2 3 4 5 Example C8 FF C9 FF FF CA FF FF FF CB FF FF FF FF CC FF FF FF FF FF CD FF FF FF FF FF FF CE FF FF FF FF FF FF FF CF FF FF FF FF FF FF FF FF 34 © 2005 Julian Dyke
juliandyke.com
35
H åkan Factor
For each table describes maximum number of rows that each block can theoretically hold Derived from column definition Affected by
Number of columns
Column datatypes and lengths
NOT NULL constraints Stored in lower 11 (at least) bits of SYS.TAB$.SPARE1
Minimum value is 11 bytes per row to allow row to be migrated Block Size 2048 4096 8192 16384 Maximum Rows per Block 178 364 736 1481 © 2005 Julian Dyke
juliandyke.com
H åkan Factor
H åkan Factor can be adjusted using ALTER TABLE table_name MINIMIZE RECORDS_PER_BLOCK;
This command
Scans entire table
Counts number of rows in each block Sets H åkan Factor in SPARE1 to maximum row count
Sets bit 0x8000 in SPARE1 to indicate value has been set Subsequently modified blocks will be limited to the new H åkan Factor Command is reversed using 36 © 2005 Julian Dyke ALTER TABLE table_name NOMINIMIZE RECORDS_PER_BLOCK;
juliandyke.com
H åkan Factor
Hakan Factor is used when compressing bitmaps Each bitmap represents an array Blocks Maximum rows per block
Minimising records per block reduces size of this array Blocks Maximum rows per block
Reduces memory required to access/manipulate bitmap May reduce disk space required to hold bitmap 37 © 2005 Julian Dyke
juliandyke.com
Nulls
B*Tree indexes do not include NULL values Bitmap indexes do include a leaf row for NULL values For example, if I1 is a bitmap index, the statement SELECT COUNT(*) FROM t1 WHERE c1 IS NULL;
generates the plan 0 1 0 2 1 3 2 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=5) SORT (AGGREGATE) BITMAP CONVERSION (COUNT) BITMAP INDEX (SINGLE VALUE) OF ‘I1'
If I1 is a B*Tree index the same statement generates the plan 0 1 0 2 1 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=5) SORT (AGGREGATE) TABLE ACCESS (FULL) OF ‘T1’ 38 © 2005 Julian Dyke
juliandyke.com
Distinct Keys
This example shows how the number of distinct keys affects number of leaf blocks for 100000 row table 8192 byte block size Distinct Keys 1 2 5 10 100 1000 10000 50000 100000 B*Tree 237 237 237 237 237 237 237 237 237 Bitmap 3 5 13 25 50 48 87 210 363 B*Tree Bitmap 400 350 300 250 200 150 100 50 0 1 4 20 10 0 50 0 25 00 20 00 0 10 00 00 Distinct Keys 39 © 2005 Julian Dyke
juliandyke.com
40
Clustering
Size of bitmap indexes is affected by the clustering of the data For example 100000 row table Column1 and Column2 contain 100 distinct values Column1 (Distributed) Column2 (Clustered) Height Blocks Leaf Rows Leaf Blocks Branch Rows Branch Blocks © 2005 Julian Dyke Column1 (Distributed) 2 24 100 20 19 1 Column2 (Clustered) 2 8 100 3 2 1
juliandyke.com
41 © 2005 Julian Dyke
Bitmap Index DML
juliandyke.com
Inserts
In Oracle 9.2 and below, insertion is very inefficient When a row is inserted
if leaf row already exists for ROWID then bitmap is updated
otherwise a new leaf row is inserted for the eight bits around the new row
In Oracle 10g, insertion is more efficient
rows are inserted into existing leaf rows where possible
if necessary start ROWID or end ROWID is adjusted 42 © 2005 Julian Dyke
juliandyke.com
Inserts
Insertion behaviour in Oracle 9.2 and below Bitmap Index Leaf Rows Key 2 Start 573/0 End 574/7 Key 2 Start 574/8 End 574/15 Key Start End 2 575/0 575/7 Key Start End 2 575/8 575/15 Block 573 Table Rows 43 © 2005 Julian Dyke Block 574 Key = 2 Key <> 2 Block 575
juliandyke.com
Inserts
Insertion behaviour in Oracle 10g Bitmap Index Leaf Rows Key 2 Start 573/0 End Block 573 Table Rows 44 © 2005 Julian Dyke Block 574 Key = 2 Key <> 2 Block 575
juliandyke.com
Updates
As with B*Tree indexes updates consist of a delete followed by an insert
delete old value from bitmap piece
add new value from bitmap piece
At least two bitmap pieces are affected by every update
Also applies to bitmap indexes containing NULL values
When bitmaps are updated on a block
Must have sufficient space on block for old and new rows
Otherwise block is split
Can lead to long branch block rows 45 © 2005 Julian Dyke
juliandyke.com
Locking
When a bitmap index is updated, a row lock is taken for the index leaf entry
The index leaf entry contains a bitmap which may cover many rows across a number of blocks
No other transaction can update an indexed column in any row covered by that bitmap piece until the original transaction commits or rolls back 46 © 2005 Julian Dyke
juliandyke.com
Locking
Bitmap Index Leaf Rows Lock 2 Key Start End 0 2 211/0 214/3 CREATE BITMAP INDEX i1 ON t1 (c2); Lock Key Start End 0 3 213/0 214/3 c1 c2 c3 77 78 80 25 5 Block 211 10 Table Rows 83 2 5 47 © 2005 Julian Dyke 86 10 Block 212 89 15 90 2 5 92 25 94 2 10 Block 213 97 3 15 UPDATE t1 SET c2 = 4 WHERE c1 = 88; 98 3 20 99 3 15 100 104 107 3 20 5 2 10 Block 214 Updated Locked
juliandyke.com
Leaf Block Splits
When bitmaps indexes are updated
Old row is retained until transaction is committed
New row is written to free space in block
If no free space in existing block, new block is used
Leaf rows stored in ascending order
Key, start and end ROWID s are unchanged therefore leaf row ordered by bitmap
If last bitmap in block is updated then block is split
Branch blocks contain unique leading edge for columns
If change is at end of bitmap, entire bitmap may be copied up into branch block 48 © 2005 Julian Dyke
juliandyke.com
Leaf Block Splits
Example of leaf block split Oracle 9.2 – 8192 byte block size 8 distinct values – approximately 3000 rows each 1 3 5 7 Branch Block 1 START END 100000 010000 001000 000100 000010 2 START END 010000 001000 000100 000010 000001 3 START END 001000 000100 000010 000001 100000 4 START END 000010 000001 100000 010000 001000 5 START END 100000 010000 001000 000100 000010 6 START END 010000 001000 000100 000010 000001 Initial state. Branch rows initial contain index key values only 49 © 2005 Julian Dyke 7 START END 001000 000100 000010 000001 100000 8 START END 000100 000010 000001 100000 010000 Leaf Blocks
juliandyke.com
Leaf Block Splits
UPDATE t1 SET c2 = 2 /* was 4 */ WHERE c1 = 23988; 1 2 START END 100000 010000 001000 000100 000010 3 4 START END 010000 001000 000100 000010 000001 5 7 Updated Deleted Branch Block 1 START END 100000 010000 001000 000100 000010 2 START END 010000 001000 000100 000010 000001 2 START END 100000 010000 001000 000100 000010 3 START END 001000 000100 000010 000001 100000 4 START END 000010 000001 100000 010000 001000 4 START END 100000 010000 001000 000100 000010 5 START END 100000 010000 001000 000100 000010 6 START END 010000 001000 000100 000010 000001 7 START END 001000 000100 000010 000001 100000 8 START END 000100 000010 000001 100000 010000 Leaf Blocks 50 Deleted row is retained until end of transaction. Leaf block splits between updated and deleted rows. Branch row must contain bitmap © 2005 Julian Dyke
juliandyke.com
Leaf Block Splits
UPDATE t1 SET c2 = 6 /* was 8 */ WHERE c1 = 23992; 1 2 START END 100000 010000 001000 000100 000010 3 4 START END 010000 001000 000100 000010 000001 1 5 5 6 START END 100000 010000 001000 000100 000010 7 8 START END 010000 001000 000100 000010 000001 Updated Deleted 1 START END 100000 010000 001000 000100 000010 2 START END 010000 001000 000100 000010 000001 2 START END 100000 010000 001000 000100 000010 3 START END 001000 000100 000010 000001 100000 4 START END 000010 000001 100000 010000 001000 4 START END 100000 010000 001000 000100 000010 5 START END 100000 010000 001000 000100 000010 6 START END 010000 001000 000100 000010 000001 6 START END 100000 010000 001000 000100 000010 7 START END 001000 000100 000010 000001 100000 8 START END 000100 000010 000001 100000 010000 8 START END 000010 000001 100000 010000 001000 Insufficient space for new branch rows in branch block 51 Branch block splits. New root block created. Index height increases © 2005 Julian Dyke
juliandyke.com
Conclusions
Columns with relatively high cardinality may be suitable for bitmap indexes
Clustering of data significantly affects bitmap index size
DML is very inefficient in Oracle 9i and below; in Oracle 10g it is much more efficient
Be aware of implications of locking when indexes are updated by multiple sessions
In Oracle 10g bitmap indexes are viable for volatile tables if all updates are made by one session 52 © 2005 Julian Dyke
juliandyke.com
References
Jonathan Lewis
Understanding Bitmap Indexes ( http://www.dbazine.com
) Optimising Oracle – Performance by Design 2001-2003
Steve Adams
Miracle Master Class 2003
Julian Dyke ( http://www.juliandyke.com
) 53 © 2005 Julian Dyke
juliandyke.com
Thank you for your interest For more information and to provide feedback please contact me My e-mail address is: [email protected]
My website address is: www.juliandyke.com
54 © 2005 Julian Dyke