 |
|
Oracle Tips by Burleson |
Indexes
So, you go to the library to find a book to read.
It is summer, and you have time on your hands and are not sure what
you want. You probably browse the shelves looking for something that
might interest you. When you’re not sure what you want, this is a
pretty easy access method, browsing the shelves. But a database never
browses for a row. It is always looking for a particular row or rows.
So, let’s go back to the library and look for a
particular book. In this instance, you would not go and browse the
shelves; you would head for the card catalog. It could take you hours
to find a book by searching the shelves, while it will take only
minutes to look up the book and go directly to the shelf where the
book is located. This is because all of the books in the library are
numbered using the Dewey Decimal system. Sometimes, you are not sure
which book you want but know the general subject. You can look up the
subject in the card catalog and then go to the shelves and look at a
range of books.
Oracle uses basically the same system, except that
the card catalog is an index and the book number becomes the rowid.
When a query asks for a specific row of data, an index allows the
database to look up the rowid of that specific row and directly
retrieve it. Otherwise, the database must start at the beginning of
the table and check each row to find the data it wants.
select *
from author
where author_key = 'A104';
In the example above, I am looking for a row in
the AUTHOR table where the
author_key is A104. If I have an index on the author key, the
database will use that index to quickly find the correct row.
AUTHOR_KEY
AUTHOR_LAST_NAME AUTHOR_FIRST_NAME
----------- ---------------------------------------- -----AUTHOR_PHONEAUTHOR_STREET AUTHOR_CITY AU
------------ ---------------------------------------- ----AUTHO
AUTHOR_CONTRACT_NBR
----- -------------------
A104 jeckle pierre
543-333-9241 3671 old fort st north hollywood CA
91607 6602
Execution
Plan
---------------------------------------------------------- 0
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1
Card=1 Bytes=73)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'AUTHOR'
(TABLE) (Cost=1
Card=1 Bytes=73)
2 1 INDEX (UNIQUE SCAN) OF 'AUTHOR_PK' (INDEX (UNIQUE)) (Cos
t=0 Card=1)
In the example above, I pulled the execution plan
from the database. Notice that the database used the author_pk
index (which is on the author key) to find the rowed, and then
performed a table access by rowid to retrieve the row (read
from inside to outside). It basically looked up the book title (author_key)
in the card catalog (index) and went directly to the shelve (table)
and retrieved the book (row).
So, an index is a pairing of a column (or columns)
value and the rowid for that row. In our example with the PUBS
database, there is not much difference between using an index and
scanning the entire table. That is because we only have ten or so
rows of data in our table. If we had hundreds of thousands of rows,
the index would show a significant speed improvement over scanning the
entire table. If there were only ten books in the library, you would
also skip the card catalog. An index can also provide a range if
values for the database to retrieve from the table. Let’s look at an
index and how the database creates it.
SQL> create
index sales_book
2 on sales (book_key);
Index
created.
In the example above, we created an index called
sales_book on the book key of the SALES table. We chose this
index because many of our queries use the book_key in the WHERE
clause filters. Since the query is filtering by book key, it can use
the index and collect the rowids of rows that pass the filters.
Indexes also speed UPDATEs and DELETEs. Remember,
to DELETE or UPDATE
a row, the database must first
find the row. The index helps the database quickly find the row.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
