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

 
 Home
 E-mail Us
 Oracle Articles
New 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 


 

 

 


 

 

 

Alternatives to Oracle Fine Grained Access Control

Bitmap join indexes

 

Donald K. Burleson

Oracle has introduced a new method to create speed join queries against very large data warehouse tables.  This new method is called the bitmap join index, and this new table access method required the creation of an index that performs the join at index creation time and creates a bitmap index of the keys that are used in the join.

For our example, we will use a many-to-many relationship where we have parts and suppliers.  Each part has many suppliers and each supplier provides many parts

In this example, the database has 200 types of parts and the suppliers provide parts in all 50 states. The idea behind a bitmap join index is to pre-join the low cardinality columns together, thereby making the overall join faster:

To create a bitmap join index we issue the following SQL.  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 = p.part_id;

While b-tree indexes are used in the standard junction records, we can improve the performance of Oracle queries where the predicates involve the low cardinality columns.  For example, look at the query below where we want a list of all suppliers of pistons in North Carolina:

select

   supplier_name

from

   parts

natural join

   inventory

natural join

   suppliers

where

   part_type = 'piston'

and

   state = 'nc'

;

Prior to Oracle, this query would require a nested loop join or hash join of all three tables.  In Oracle, we can pre-join these tables based on the low cardinality columns.

For queries that have additional criteria in the WHERE clause that does not appear in the bitmap join index, Oracle will be unable to use this index to service the query. 

While Oracle markets this new feature with great fanfare, the bitmap join index is only useful for table joins that involve low-cardinality columns (e.g. columns with less than 300 distinct values).  Bitmap join indexes are also not useful for OLTP databases because of the high overhead associated with updating bitmap indexes.

Oracle claims that this indexing method results in more than 8x improvement in table joins in cases where all of the query data resides inside the index.  However, this claim is dependent upon many factors, and the bitmap join is not a panacea.  In many cases the traditional hash join or nested loop join may out-perform a bitmap join.  Some limitations of the bitmap join index join 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 re-built each evening about the daily batch load jobs.  Hence bitmap join indexes are only useful for Oracle data warehouses that remain read-only during the processing day.

In sum, bitmap join indexes will tremendously speed-up specific data warehouse queries, but at the expense of pre-joining the tables at bitmap index creation time.

If you like Oracle tuning, you might enjoy my latest book "Oracle Tuning: The Definitive Reference" by Rampant TechPress.  It's only $41.95 (I don't think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2003_1_Oracle_sga.htm

 

”call

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.