Oracle Concepts - Administering Oracle
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
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
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
* 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:
heading 'Table|Name' format a20
heading 'Table|Freelists' format 99
heading 'Index|Name' format a20
heading 'Index|Freelists' format 99
t.table_name = i.table_name
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
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.