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:

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:

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.