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 


 

 

 


 

 

 

 
 

Oracle Concepts - Administering Oracle Indexes

Oracle Tips by Burleson Consulting

Administering Oracle Indexes

Tables can get quite big. I've had databases with tables that were hundreds of gigabytes in size, which is very big indeed! Big tables typically have a large number of rows in them. Imagine how long it might take to search through 500 million rows of a 200 gigabyte table for one employee record. It could take a very long time.

One way we make accessing table faster is to create an index on that table. You are probably very familiar with indexes and there is an index at the end of this book. If you want to find out about Oracle tables, you go to the end of the book, look in the index, and it tells you which page to read for information on Oracle indexes.  Without this index, finding a topic would be more difficult.  Now imagine if we asked you to find every instance of the word 'the' in the book.  How long would it take you?

By default Oracle uses indexes called B*Tree indexes. These indexes work very much the same way as the index in the back of this book. You build an index based on one or more columns in the table. Those column values are stored in the index. Say we create an index on the EMPLOYEE_ID column. Our index would have 500 million EMPLOYEE_ID values. Also in that index, with each EMPLOYEE_ID, is an address that tells Oracle exactly where that EMPLOYEE_ID is located in the table. This address is called the ROWID. The ROWID is like your address at home, it identifies one and only one row in a table. Hence, armed with the column value, and the ROWID, Oracle can quickly find the rows that have the value we are interested in.

For example, say we wanted EMPLOYEE_ID 5555. Part of our index might look like this:

Column Value
ROWID
5551
AAAL+ZAAEAAAAMOAGa
5552
AAAL+ZAAEAAAAMOAGb
5553
AAAL+ZAAEAAAAMOAGc
5554
AAAL+ZAAEAAAAMOAGd
5555
AAAL+ZAAEAAAAMOAGe
5556
AAAL+ZAAEAAAAMOAGf
5556
AAAL+ZAAEAAAAMOAG

Oracle indexes are built so Oracle can very quickly find the column value entries you are looking for. In our case, Oracle will then very quickly find the index entry for 5555, and read the associated ROWID. Based on the ROWID, it knows exactly where the row is in the table, and it will go read it.

Did you notice that the column values are sorted in the index? This is good because it makes looking for specific values or a range of values very fast. For example, if we want to find all column values between 5553 and 5556, we know that we start reading the index at 5553, and that we don't need to read any more of the index after I've read 5556. That makes for very quick lookups of a range of values. Queries like this can take advantage of these types of range scans:

SELECT empid, sal FROM emp WHERE empid BETWEEN 5553 AND 5556;

Also, since the column values are sorted, Oracle may not need to perform a sort operation. For example a query like this:

SELECT empid, sal FROM emp WHERE empid BETWEEN 5553 AND 5556 ORDER BY empid;

Will require a sort if there is no index, but since we have an index on the EMPID column, in many cases Oracle will not need to do a sort as long as it uses that index to get the data we need.  Oracle sometimes creates indexes for you. When you define a primary key constraint (discussed earlier in this chapter) Oracle will create an index for you. When you define a unique constraint, Oracle will also create an index for you.

Recall that Oracle uses B*Tree indexes by default. A picture of the index makes it look like an upside down tree. At this point, it's not important that you know how a B*Tree index really works. All you need to know is that they are very fast and efficient for many kinds of queries. If your queries are going to look at only a certain amount of table data, indexes can improve the speed of your queries several fold. Here is a graphic of a BTREE Index. 

In the graphic of the B*Tree index Oracle starts from the top box called the root node, and works it's past the intermediate branch nodes down to the bottom boxes, called the leaf nodes, to find your data. The root node points to the correct branch node to go to based on the data value you are looking at. Each branch node points to the correct leaf node based on the data value you are looking for.

For example in the B*Tree graphic, we are looking for a value of 65. The root node points us to the left branch node, because it contains all values up to 122. This branch node then points us to the left most leaf node, because the left most lead node contains all values up to 65.

We find the record for 65, along with the ROWID in the left most leaf node. Oracle will then take that ROWID and read the correct row in the table. Usually this is a very fast and efficient way to get to your data, however as you can see this can be a very expensive process, it took us 4 IO's to get just one record out of the table (3 index IO's and 1 table IO). Hence, indexes are not always the best way to get at table data. You will really need to make a study of Oracle Performance Tuning to understand when indexes are good and when they are bad. Since this book is about being a DBA and managing your database, we will leave that discussion for another day.

Oracle can use other kinds of indexes, such as index organized tables, function based indexes and indexed clusters. These are slightly more advanced topics and best saved for another book. In this book, we will stick with B*Tree indexes. Also, Oracle offers advanced functionality with indexes such as partitioning and key compression. In this book, we are just trying to get you through the basics first. Once you are through the basics and comfortable with them, then you can move onto these index options later.

Oracle index dictionary views

As a DBA we need to manage our indexes. This includes knowing who owns the index, what tablespace the index is in and what columns the index is made of. The following data dictionary views are used for these purposes:

* dba_indexes, all_indexes, user_indexes

* dba_ind_columns, all_ind_columns, user_ind_columns

There is an almost infinite number of ways that you can query details from the dba_indexes view.  For example, here is a simple query to show its tables and freelists:

col c1 heading 'Table|Name'      format a20
col c2 heading 'Table|Freelists' format 99
col c3 heading 'Index|Name'      format a20
col c4 heading 'Index|Freelists' format 99
 
 
select distinct
   t.table_name  c1,
   t.freelists   c2,
   index_name    c3,
   i.freelists   c4
from
   dba_tables    t,
   dba_indexes   i
where
   t.table_name = i.table_name
and
   i.index_name = 'IDX_EVENTCASE_STATUS_OVERAGE'

 

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It's only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
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 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.