The BITMAP COMPACTION operation compacts a bitmap index.
The BITMAP CONSTRUCTION operation converts column values to a bitmap.
Execution Plan image:
Row operation.
A bitmap index contains a set of bits that indicate which row contains a certain value. For example, if the first row and the second row in the COMPANY both have a state value of 'MD', then there is a bitmap for the value 'MD'. The bitmap has the first and the second bits set to 1. If the first two rows are the only companies with a state value of 'MD', there will be no more 1 bits in the 'MD' bitmap. When Oracle needs to access the actual rows, the relative location of the row is not sufficient information to provide direct location in the table. For accessing the rows in the table, Oracle needs to convert the relative location stored in the bitmap index to ROWID; only then can Oracle access the table. The process of converting a bitmap value to a ROWID occurs via a BITMAP CONVERSION operation.
Assume that both City and State columns each has a single-column bitmap index.
select Name, City, State
from COMPANY
where City = 'Roanoke'
and State = 'VA';
The query shown in the preceding listing selects rows from the COMPANY table for all companies in Roanoke, VA.
TABLE ACCESS (BY INDEX ROWID) OF 'COMPANY'
BITMAP CONVERSION (TO ROWIDS)
BITMAP AND
BITMAP INDEX (SINGLE VALUE) OF 'COMPANY$STATE'
BITMAP INDEX (SINGLE VALUE) OF 'COMPANY$CITY'
The plan shows that the bitmap index on the City column is used to find a bitmap for all the rows in the COMPANY table that satisfy the where clause condition (where City='Roanoke'). Another bitmap is created from the index on the State column. The two bitmaps are then united to one bitmap using a BITMAP AND operation. The resulting bitmap is converted to a list of ROWIDs via the BITMAP CONVERSION operation. The resulting list of ROWIDs is used as the basis for an access of the COMPANY table.
Assume that both City and State columns each has a single-column bitmap index.
select COUNT(*)
from COMPANY
where City = 'Roanoke'
and State = 'VA';
The query shown in the preceding listing selects the count of rows from the COMPANY table for all companies in Roanoke, VA.
SORT (AGGREGATE)
BITMAP CONVERSION (COUNT)
BITMAP AND
BITMAP INDEX (SINGLE VALUE) OF 'COMPANY$CITY'
BITMAP INDEX (SINGLE VALUE) OF 'COMPANY$STATE'
The plan shows that the bitmap index on the City column is used to find a bitmap for all the rows in the COMPANY table that satisfy the where clause condition (where City='Roanoke'). Another bitmap is created from the index on the State column. The two bitmaps are then united to one bitmap using a BITMAP AND operation. The resulting bitmap is converted to a count. Based on the counts returned, a sort aggregate operation sums the values and returns the output to the user.
Execution Plan image:
Row operation.
Bitmap index operations are performed when accessing a bitmap index. The following options are available:
SINGLE VALUE
Accesses a single value in the index and returns a bitmap for all of the matching rows.
Example: Where State = 'MD'
FULL SCAN
A complete scan of the index to find any matching values.
Example: Where State not in ('HI', 'AL')
RANGE SCAN
Accesses a range of values in the index and returns multiple bitmaps. These bitmaps are then merged into one bitmap.
Example: Where City like 'New*'
Assume that both City and State columns each has a single-column bitmap index.
select Name, City, State
from COMPANY
where City = 'Roanoke'
and State = 'VA';
This query selects the count of rows from the COMPANY for all companies in Roanoke, VA.
TABLE ACCESS (BY INDEX ROWID) OF 'COMPANY'
BITMAP CONVERSION (TO ROWIDS)
BITMAP AND
BITMAP INDEX (SINGLE VALUE) OF 'COMPANY$CITY'
BITMAP INDEX (SINGLE VALUE) OF 'COMPANY$STATE'
The plan shows that the bitmap index on the City column is used to find a bitmap for all the rows in the COMPANY table that satisfy the WHERE clause condition (where City=’Roanoke’). Another bitmap is created from the index on the State column. The two bitmaps are then united to one bitmap using a BITMAP AND operation. The resulting bitmap is converted to a count. Based on the counts returned, a sort aggregate operation sums the values and returns the output to the user.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback 使用条款 隐私 Cookie Preference Center