Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

bitmap join index tips

Oracle Tips by Burleson Consulting

July 12, 2013

 

Oracle9i added the bitmap join index to its mind-boggling array of table join methods. This new table access method requires that you create an index that performs the join at index creation time and that creates a bitmap index of the keys used in the join. But unlike most relational database indexes, the indexed columns don't reside in the table. Oracle has revolutionized index creation by allowing a WHERE clause to be included in the index creation syntax. This feature revolutionizes the way relational tables are accessed via SQL.

The bitmap join index is extremely useful for table joins that involve low-cardinality columns (e.g., columns with less than 300 distinct values). However, bitmap join indexes aren't useful in all cases. You shouldn't use them for OLTP databases because of the high overhead associated with updating bitmap indexes. Let?s take a closer look at how this type of index works.

How bitmap join indexes work

To illustrate bitmap join indexes, I'll use a simple example, a many-to-many relationship where we have parts and suppliers with an inventory table serving as the junction for the many-to-many relationship. Each part has many suppliers and each supplier provides many parts (Figure A).
 

 
Figure A
A many-to-many Oracle table relationship



For this example, I'll assume the database has 300 types of parts and the suppliers provide parts in all 50 states. So there are 50 distinct values in the State column and only 300 distinct values in the Part_type column.

Note in Figure A that we create an index on the Inventory using columns contained in the Supplier and Part tables. The idea behind a bitmap join index is to pre-join the low cardinality columns, making the overall join faster.

It is well known that bitmap indexes can improve the performance of Oracle9i queries where the predicates involve the low cardinality columns, but this technique has never been employed in cases where the low cardinality columns reside in a foreign table.

To create a bitmap join index, issue the following Oracle DDL: (Note the inclusion of the FROM and WHERE clauses inside the CREATE INDEX syntax.)

create bitmap index
   part_suppliers_state
on
   inventory( parts.part_type, supplier.state)
from
   inventory i,
   parts p,
   supplier s
where
   i.part_id=p.part_id
and
   i.supplier_id=s.supplier_id;


Bitmap join indexes in action

To see how bitmap join indexes work, look at this example of a SQL query. Let's suppose you want a list of all suppliers of pistons in North Carolina. To get that list, you would use this query:

select
   supplier_name
from
   parts
natural join
   inventory
natural join
   suppliers
where
   part_type = 'piston' and state='nc';


Prior to Oracle9i, this SQL query would be serviced by a nested loop join or hash join of all three tables. With a bitmap join index, the index has pre-joined the tables, and the query can quickly retrieve a row ID list of matching table rows in all three tables.

Note that this bitmap join index specified the join criteria for the three tables and created a bitmap index on the junction table (Inventory) with the Part_type and State keys (Figure A).

Testing shows that bitmap join indexes can run a query more than eight times faster than traditional indexing methods. However, this speed improvement is dependent upon many factors, and the bitmap join is not a panacea. Some restrictions on using the bitmap join index include:

  • The indexed columns must be of low cardinality?usually with less than 300 distinct values.
  • The query must not have any references in the WHERE clause to data columns that are not contained in the index.
  • The overhead when updating bitmap join indexes is substantial. For practical use, bitmap join indexes are dropped and rebuilt each evening about the daily batch load jobs. This means that bitmap join indexes are useful only for Oracle data warehouses that remain read-only during the processing day.


Remember: Bitmap join indexes can tremendously speed up specific data warehouse queries but at the expense of pre-joining the tables at bitmap index creation time. You must also be concerned about high-volume updates. Bitmap indexes are notoriously slow to change when the table data changes, and this can severely slow down INSERT and UPDATE DML against the target tables.

Exclusions for bitmap join indexes

There are also restrictions on when the SQL optimizer is allowed to invoke a bitmap join index. For queries that have additional criteria in the WHERE clause that doesn't appear in the bitmap join index, Oracle9i will be unable to use this index to service the query. For example, the following query will not use the bitmap join index:

select
   supplier_name
from
   parts
natural join
   inventory
natural join
   suppliers
where
   part_type = 'piston'
and
   state = 'nc' and part_color = 'yellow';


Using bitmap join indexes requires planning

Oracle9i has introduced extremely sophisticated execution plan features that can dramatically improve query performance, but these features cannot be used automatically. The Oracle9i professional's challenge is to understand these new indexing features, analyze the trade-offs of additional indexing, and judge when the new features can be used to speed queries.

 
 
 
Get Complete Oracle Tuning Details 

The landmark book "Oracle Tuning: The Definitive Reference Third Edition" has been updated with over 800 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle performance and you can buy it for 40% off directly from the publisher.
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.


 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster