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


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

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'

 


The above text is an excerpt from:

Oracle SQL Tuning & CBO Internals
ISBN 0-9745993-3-6

by Kimberly Floss


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.