Oracle SQL Tuning and CBO Internal Bitmap Indexes

Call (800) 766-1884 for Oracle support & training

Oracle Tips by Burleson

Oracle SQL Tuning and CBO Internal

Chapter 2: Bitmap Indexes

Another Oracle 8i feature requiring the cost-based optimizer is bitmap indexes. This is primarily a data-warehousing feature for very large tables. If a column contains only a few values, the table contains millions of rows, and the column is referenced frequently in WHERE clauses, then consider creating a bitmap index. The following command indicates that the SEX column contains only two distinct values: F for female and M for male. The table has one million rows. The SELECTIVITY of the SEX column is very, very low and is an ideal candidate for a bitmap index. The selectivity of the SEX column is 2/1,000,000 or 0.000002. You would never create a btree index on the SEX column. Btree indexes are best suited for columns with high selectivity. Primary key columns, for example, have a selectivity of one, and that is as good as it gets.

SQL> COL SEX FORMAT A3

SQL> SELECT SEX,

2 COUNT(*)

3 FROM STATS

4* GROUP BY SEX;

SEX COUNT(*)

--- ----------

F 500000

M 500000

Create a non-unique btree index on the SNAME column of the STATS table.

SQL> CREATE INDEX I_STATS_SNAME

2* ON STATS(SNAME);

Index created.

The following query always results in a full table scan because the SEX column has no indexes.

SQL> SET AUTOTRACE TRACEONLY EXPLAIN

SQL> SELECT *

2 FROM STATS

3 WHERE SNAME = 'NAME IS 222' OR SEX = 'F';

Execution Plan

-----------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

(Cost=520 Card=500001 Bytes=9500019)

1 0 TABLE ACCESS (FULL) OF 'STATS'

(Cost=520 Card=500001 Bytes=9500019)

Now create a bitmap index on the SEX column of the STATS table. Activate TIMING to observe the actual amount of elapsed time to create the bitmap index; 12 seconds is very, very fast.

SQL> SET TIMING ON

SQL> CREATE BITMAP INDEX BMI_STATS_SEX

2 ON STATS(SEX);

Index created.

Elapsed: 00:00:12.08

A query against the data dictionary view user_indexesdisplays information about the btree and bitmap indexes on the STATS table.

SQL> SELECTINDEX_NAME,

2 INDEX_TYPE,

3 UNIQUENESS

4 FROM USER_INDEXES

5* WHERE TABLE_NAME = 'STATS';

INDEX_NAME INDEX_TYPE UNIQUENES

------------- ----------- ---------

BMI_STATS_SEX BITMAP NONUNIQUE

I_STATS_SNAME NORMAL NONUNIQUE

Analyze the stats table so queries using stats will use the cost-based optimizer. You can also use the ALTER SESSION command or a HINT to force Oracle to use the cost-based optimizer. Note the elapsed time of more than eight minutes to analyze the one million row table with two indexes.

SQL> ANALYZE TABLE STATS COMPUTE STATISTICS;

Table analyzed.

Elapsed: 00:08:517.04

Using AUTOTRACE with the EXPLAIN option, notice that Oracle prefers a full table scan to using both indexes with an estimated cost of 520.

SQL> SET AUTOTRACE TRACEONLY EXPLAIN

SQL> SELECT *

2 FROM STATS

3* WHERE SNAME = 'NAME IS 222' OR SEX = 'F';

Execution Plan

-----------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

(Cost=520 Card=500001 Bytes=9500019)

1 0 TABLE ACCESS (FULL) OF 'STATS'

(Cost=520 Card=500001 Bytes=9500019)

Using the ALTER SESSION command, tell Oracle to use the cost-based optimizer and select the access path that returns the first row as quickly as possible.

SQL> ALTER SESSION SET OPTIMIZER_MODE =

FIRST_ROWS;

Session altered.

Executing the command again using the EXPLAIN option of AUTOTRACE indicates that Oracle uses both indexes. The index on the SNAME column is converted to bitmaps, and a BITMAP OR is used for both indexes. The result is converted back to rowids, and the rowids are used to retrieve the rows. The estimated cost to perform this query is 2166.

SQL> SELECT *

2 FROM STATS

3* WHERE SNAME = 'NAME IS 222' OR SEX = 'F';

Execution Plan

-----------------------------------------

0 SELECT STATEMENT Optimizer=FIRST_ROWS

(Cost=2166 Card=500001 Bytes=9500019)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'STATS'

(Cost=2166 Card=500001 Bytes=9500019)

2 1 BITMAP CONVERSION (TO ROWIDS)

3 2 BITMAP OR

4 3 BITMAP CONVERSION (FROM ROWIDS)

5 4 INDEX (RANGE SCAN) OF 'I_STATS_SNAME' (NONUNIQUE)

(Cost=4)

6 3 BITMAP INDEX (SINGLE VALUE) OF 'BMI_STATS_SEX'

Change the optimizer mode for your session back to CHOOSE, which is the default. Execute the query again, using the index_combinehint to force the use of the bitmap index on the SEX column. Notice the same results as using the optimizer mode of first_rows .

SQL> ALTER SESSION SET OPTIMIZER_MODE = CHOOSE;
Session altered.SQL> SELECT /*+INDEX_COMBINE(STATS) */ *
2   FROM STATS
3* WHERE SNAME = 'NAME IS 222' OR SEX = 'F';Execution Plan
-----------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE
     (Cost=2166 Card=500001 Bytes=9500019)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'STATS'
     (Cost=2166 Card=500001 Bytes=9500019)
2 1   BITMAP CONVERSION (TO ROWIDS)
3 2     BITMAP OR
4 3       BITMAP CONVERSION (FROM ROWIDS)
5 4         INDEX (RANGE SCAN) OF 'I_STATS_SNAME'
            (NONUNIQUE)
            (Cost=4)
6 3       BITMAP INDEX (SINGLE VALUE) OF 'BMI_STATS_SEX'