To be quite frank, when it comes to Oracle Indexes and Joins I am as good as with Oracle Analytical functions. Much fly above my head & every time I have to go back to my notes to “learn” for the tasks in hand!

Recently, I took some interests in understanding the “index usage” once after reading about V$OBJECT_USAGE & realized to my shock that more than 50% of my indexes were never used! I wanted to know why & I kept reading for days without finding much that felt like a true answer.

Hence I made a decision to understand how the indexes work by example. Our Oracle EBS environment has more than half dozen custom applications integrated and few of them are with millions of rows, sufficing “large” table requirements to test the effectiveness of indexing. From the layman perspectives, please note, I am not an Indexing expert, I can’t explain why your Indexes are not being used “even after following everything step by step”. For me, what I did work, giving me an understanding about how should I plan my next Indexes. So let us see how I came to my understandings

We’ve bio-metric devices that are used for attendances purposes. These devices offload the data to a Microsoft SQL database instance and using transactional SQL, we register them with our Oracle database. The technical part of it. The table that stores the fingerprints has 2.3 Million rows as on date and I used the same table to understand how the indexing works.

There was one Index on this table (Yes, I created it), that I dropped before experimenting as the Index was never used! The logic behind the query is:

I should get the first punch in time for the employee, identified by type “0” and last punch out for the employee, identified by type “1”, the machine name on which the employee has registered in and out punches. Each employee might use the bio-metric devices at different locations for door accesses or other purposes like a proof of visiting another office. Without the Index on this table, let us see how Oracle plans the execution.

This table has just few columns and sought data is usually the punch time against the employee.

Regardless, the cost for the execution didn’t look appealing. So, I created an Index that has all the four columns referred in the main and inline queries.

This time the cost looked far better, however, I could see that the base table still being used when there were no additional columns from the base table referred.

Here comes the thumb rule for indexes (I think). Indexes are not used unless a condition is used against one of the indexed columns! Let us see, whether this makes any sense.

I created a view against the above query to be more certain.

After creating the view, I did a simple select * against the view and the execution plan brought me the same results discussed above. Wherever the predicates were used, query used the existing Index and for the rest, did the base table scan.

So I went ahead against my “understanding” and added a condition to Select * from query and did another Explain plan.

This time, the cardinality, ie the total number of rows fetched came down to just a four digit number, base table was not referred and the cost was dirt cheap compared to the earlier situations.

Let’s summarize everything now.

1. Indexes are mostly effective about large tables
2. Oracle will use indexes only when one of the columns used in the index is used against a predicate. Said, I created a view against our dear Scott.Emp table and “Select * from emp;” used that index. I don’t know why and I don’t care!
3. Add up IS NOT NULL against all your index columns in your query to make sure that your Index is used instead of base table.

Now, I needed to understand further. Hence, I went back to the HR sample schema and chose the table “Employees” this time for my continued experiments. As I said Scott.Emp, the results were the same.

HR.EMPLOYEES tables have many Indexes defined.

For the first query as seen with the image below, I didn’t include a predicate. Regardless, Oracle used the Index for the column

Then I tried another query with multiple columns and without predicate. Oracle used the Index this time as well.

Apparently, this gives me an idea like, for larger tables the Indexes are opted when predicates are available against indexed columns and for tables like HR.EMPLOYEES which has only 107 rows, if there is an index exist against the queried column exist, it is used by Oracle.

Cheers friends, it was fun learning something, once again my own way. Hope this helps few others out there who were breaking their heads to understand this horrible thing. Merry Christmas and a very Happy New Year to everyone out there.