Oracle table and index access paths

In this post we will see oracle table and index access paths and their impact on query performance.

But before that please make sure you have read my this post, in which I have explained:
1. How to read explain plan
2. Why access is better than filer when it comes to indexes

So with this knowledge let’s start exploring indexes with an example.

There’s one newbie writer (but very wise Physicists) who has written a very nice book on Physicists and their important theories. It’s a big fat book but with very informative contents. Book’s contents are like this from page 1 to last page (say 1500 pages).
Chapter 1: Newton
              –> First Law of motion
              –> Second Law of motion
              –> Third Law of motion
              –> Gravity and Apple
Chapter 2:  Archimedes
              –> Hydrostatics
              –>The center of gravity
Chapter 3: Richard Feynman
              –> Manhattan Project
Chapter 4: Sheldon Cooper
              –> Fun with Flags
              –>Bazinga!!!!
and info few more 20-30 Physicists……

Now, as this book has very impressive contents, teachers have started to ask their students to refer it. On one such fine day, Miss Pasternak asked her student Jake Harper to refer it. See what she has asked and how our poor Jake got in trouble.

Day 1 she asked him to list out names of Physicists who’s information is there in this book.
Day 2 she asked him to list about Richard Feynman’s research mentioned in this book.
Day 3 she asked him to read entire book as you will be having 3 week’s holidays coming.

Now folks, remember, author has only written a book with lovely contents i.e. from page 1 to last page he has given information about physicists, as he is new in book business, he has not given any index for his contents but his mistake is proving very costly for Jake, why????
On day 1, as Jake just need to list names of scientist but he will need to read entire book.
On day 2, Jake just need to read about 1 physicists but he will need to read entire book until he finds his scientist of interest (which he may find on page 1 or may be on last page or may be that scientist is not at all listed in the book).

So to save himself, Jake went to the only wise man he has ever meet, uncle Charlie!!!!
Uncle Charlie gave him a suggestion to create an index containing name of physicists and page number from which his information is beginning, something like this:

Name                                            Page no.
Newton                                               1
Archimedes                                      100
Richard Feynman                            233
Sheldon Cooper                               555
and so on.

Now see, how it has made Jake’s life easy.
On Day 1, Jake will need to read only index!!!!! Only 3 pages of index (I’m assuming index is 3 page long), great!!!!
On Day 2, Jake will need to read exactly 325 pages (3 pages of index + 322 pages of book), which is still better than reading entire book….
On Day 3, that index is of no use as he need to read entire book, so he will skip the index and read 1500 pages.

In conclusion, with index in place Jake will read in total 3+325+1500=1828 pages but previously, without index he was about to read 4500 pages!!!! and don’t forget how much time he is saving with index.

Now, back to real life of databases, let’s replace book with database table and book index with table index.
Case 1: Only index fulfills our data need, in “where” clause we are querying owner column and in select clause also we have same column, so CBO thinks I can get required data only by visiting the index, there’s no need for me to visit tables (in explain plan we can’t see table access operation). So we as a user get performance boost as we are skipping lookup of heavy object (i.e. table), benefit of saving IO, network traffic and scanning entire table (as index with “access” gives us exact rowid of table)

Case 2: we need to visit index + table, please notice in below screenshot that I have kept “where” clause as same as previous query but I have added 2 columns in “select” clause. So, while generating explain plan CBO come to know that in order to fulfill data need , he will need to visit index and table because index doesn’t contain all required columns of the query.

In such cases, we may think to add remaining query columns in index so that only by visiting an index our data need will get satisfy, this type of technique/index is known as coverage index. It’s ok to add few columns in index to bypass table access but say, if we have 10 columns in table and we are creating index of 9-10 columns, we are making that index as big as table so we are killing advantage of index scanning by doing so (Just imagine, book of 100 pages and index of 90 pages. This index is as wider as book, making our document 190 pages long and having redundant information too, so better to skip creating such index and save space/pages).

Case 3: we need to read entire table as:
a. we have no where clause (which is our example in below screen shot)
b. query is written in such a way that i can’t take advantage on indexes e.g.  NOT condition, LIKE ‘%….%’ condition etc.
c. appropriate indexes are not created
d. indexes are in invalid state
e. due to lack of statistics, CBO thinks that index skipping is a good option
f. some type of bug in DB and many other conditions.

So guys, I hope we are clear about below points:
a. what is only index visit
b. what is index + table visit
c. what is table visit and when it can occur

In this post, I have addressed how to create optimal indexes, which mistakes/situations turn our optimal index into poor performing (or useless if I’m not too harsh) one. So see you there!!!!

P.S.
Sheldon Cooper is from sitcom Big Bang Theory Charlier Harper, Jake Harper and Miss Pasternak are from sitcom Two and Half Men