A couple of good index questions from a novice

Hi,

This blog comes from the e-mail bag. [:)] One of my followers asks two useful questions that a novice to relational databases or an end user/power user is likely to have.  The questions relate to ‘how does it work’, a fun series on discovery that I enjoy.

The questions

Question 1: How does our application understand that there is an index in a table?

The people who designed your application determine the best uses for indexes and on which columns of data there is indexing.  Design experience comes into play here.  Also, the design specifies how the data will be accessed, how much data will be accessed, and how often the data will be accessed.

I’ve always felt that analysts would benefit from the indexing part of my SQL performance tuning class by gaining a better understanding of how indexes work and getting some best practices for them.

Indexes provide a method to quickly locate certain requested data items within a table.  Indexes can be unique in that each key value only has one index entry and points to one row in the associated table; or alternatively indexes can store repeating values that will help locate groups of related data quickly in the associated table.

In the Oracle world, when processing any SQL statement, there is a series of steps followed that not only checks for proper SQL syntax but also retrieves any indexing information (mostly on columns in the WHERE clause of your SQL), along with statistics.

Read through my blogs; I have published lots of information here on how these statistics are used.  But to make the process simple…

1. Oracle knows that there are available indexes
2. Oracle makes educated guesses that by using an index, there would be considerably fewer physical I/O operations to retrieve the data than ifnot using the index.

The fewer physical I/O operations needed to return data to your query, the faster it will execute!

So simply put, Oracle knows if there are indexes available and makes a decision, based on your WHERE clause data, whether or not performance might be significantly better using the index.

Question 2:  How does using an index retrieve data faster than a table without index?

Indexes quickly point to where the data is…by what is being requested in a WHERE clause and if there is an index on that column.  There are many factors that go into whether Oracle will decide to use the index or not…but Oracle does know about indexes when processing any SQL request for processing.

Indexes contain the key fields (your column data) and pointers to where that data is in the table.  Oracle can then just jump to the specific data blocks (with just a few I/O operations) that contain the information you are requesting…rather than doing a full scan (reading ALL the table data blocks).  Proper use of indexes can make your SQL run significantly faster because only the data blocks that contain the info you are looking for are read…not all of the tables.

So…the larger your table (row count), the more indexes can be of value to you.

Hope this helps. 

Let me know what you need to know.  I’ll answer the question to the best of my knowledge **AND** I’ll use this blog (not your name) to share the information with others who might have the same question.

Dan Hotka

Author/Instructor/Oracle Expert

www.DanHotka.com

Dan@DanHotka.com