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 


 

 

 


 

 

 
  Oracle Tips by Burleson

B-Tree Index

By default, the Oracle creates a b_tree index.  In a b-tree, you walk the branches until you get to the node that has the data you want to use.  In the classic b-tree structure, there are branches from the top that lead to leaf nodes that contain the data.  If I wanted to find the rowid for the number 28 in the b-tree defined in Figure 5.3, I would start at the top or header block.

Is my number greater or less that 50?  Well 28 is less than 50, so I move to the branch marked 25.  Is 28 greater or less that 25?  Since 28 is greater than 25, I move to the leaf node marked 26-49.  I scan this node for the rowid of the number 28.  The key to the b-tree in Figure 5.3 is that I can find any number from one to 100 by reading no more than three nodes. 

The Oracle database implements the b-tree index in a little different manner.  An Oracle b-tree starts with only two nodes, one header and one leaf.  The header contains a pointer to the leaf block and the values stored in the leaf block.  As the index grows leaf bocks are added to the index (Figure 5.4).

To find a specific row, we look at the header to find the range of values in each leaf and then go directly to the leaf node that contains the value we are looking for.  In the index in Figure 5.4, any row can be found by reading two nodes.  Since the header contains only pointers to leaf blocks, a single header node can support a very large number (hundreds) of leaf nodes. 

If the header block fills, then a new header block is established, and the former header node becomes a branch node.  This is called a three level b-tree (Figure 5.5).

In Figure 5.5, you can find any value in any leaf node by reading no more than three blocks.  I can also create a multicolumn index, also called a concatenated or complex index.

SQL> create index sales_keys
  2  on sales (book_key, store_key, order_number); 

Index created.

Here, we created an index called sales_keys on three columns of the sales table.  A multicolumn index can be used by the database but only from the first or lead column.  Our sales_keys index can be used in the following query.

select
  order_number,
  quantity
from
  sales
where
   book_key = 'B103';
 

Note that the lead column of the index is the book_key, so the database can use the index in the query above.  I can also use the sales_keys index in the queries below.

select
  order_number,
  quantity
from
  sales
where
   book_key = 'B103'
and
   store_key = 'S105'
and
   order_number = 'O168';
 

However, the database cannot use that index in the following query because the WHERE clause does not contain the index lead column.

select
  order_number,
  quantity
from
  sales
where
   store_key = 'S105'
and
   order_number = 'O168';
 

Also, note that in the query below, the database can answer the query from the index and so will not access the table at all.

 select
  order_number
from
  sales
where
   store_key = 'S105'
and
   book_key = 'B108'; 

As you can see, b-tree indexes are very powerful.  You must remember that a multicolumn index cannot skip over columns, so the lead index column must be in the WHERE clause filters.  Oracle has used b-tree indexes for many years, and they are appropriate from most of your indexing needs.  However, the Oracle database provides specialized indexes that can provide additional capabilities; the bit-mapped index and the function-based index.

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.
��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
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 -  2014

All rights reserved by Burleson

Oracle is the registered trademark of Oracle Corporation.