Creating optimal indexes to improve query performance Part-1

 Hello Guys,

So far we have understood that ACCESS is better than FILTER (please read this post of mine where I have explained it in detail). In this post, we will see which are the scenarios that turn INDEX “access” into “filter” and how we can create optimal indexes for our queries so that we can take benefit of “access” method.

Please note, we are talking about normal indexes i.e. B-Tree indexes. Let’s begin:

Scenarios which will turn access into filter:

Creating a dummy table:

SQL> create table amol_dba_objects as select * from dba_objects; 
Table created. 

SQL> select count(*) from amol_dba_objects; 
COUNT(*) 
---------- 
91673

Let’s take 1 query for example purpose:
select *
from amol_dba_objects
where owner=’HR’
and object_type=’TABLE’
and status<>’INVALID’
and TEMPORARY=’Y’
and created > sysdate-2;

Before actually starting to tune this query, I would like to point at below 3 points which we must keep in mind for EVERY query we are going to tune:
a. Familiarize with the query: try to understand the logic behind the query, what this query is trying to return etc. This will help us to re-write the query if developer has done some mistake and by re-writing it we can fix the problem (sometimes it may not possible for us to re-write the query but it’s worth to understand it’s logic).
b. Neatness counts in this game: Please see below formatted version of the query. No it’s not formatted using any tool. I have added one harmless condition in WHERE clause (I have explained it’s reason below), I have clubbed all equal to and non-equal conditions in logical groups, this will help me in tuning especially creating indexes , trust me 
c. Understand your data: If we are not understanding characteristics of our data and trying to tune queries, we are loosing very big advantage. If we know our data then it may happen that rather than creating normal index, we will create unique index which is better than normal index.

Formatted query and it’s explain plan:

Few things about about query and it’s explain plan:
a. condition 1=1 is of no use, this condition is added just for formatting purpose, when I added 1=1 in front of WHERE clause, we can see AND operator was put in-front of remaining conditions of WHERE clause, it has added readability of the query. Whenever optimizer come across such conditions, optimizer ignores then (or removes them before generating explain plan), we can see that in predicate information section, we can’t find condition 1=1.
b. I have logically separated equal to conditions and non-equal to conditions. This has added readability of query.
c. This query has 3 equal conditions (i.e. condition containing ‘=’ operator) and 2 non-equi conditions (i.e. >, <>). It’s very useful information, in a moment we will see why.
d. In predicate information section, we can see FILTER operation (As we have not created any index yet).

Now, we are seeing FILTER in predicate information section and we don’t have any index on our table, we will create 1 index on our table for this query but with a twist 

Case 1: only 1 column with non-equal to condition is allowed in index.
Equal to predicates (conditions in WHERE clause with only ‘=’ operator or with IN operator. Yes, Oracle will translate IN operator into series of ‘=’ operator combined with OR operator. Please see this post for the proof.) support access but only 1 non-equal to (>,<,>=,<=,! etc.) condition is allowed for ACCESS, remaining non-equal to conditions will be used for FILTER. Let’s understand it with example:

 
SQL> create index amol.AMOL_DBA_OBJECTS_i1
2 on amol.amol_dba_objects(TEMPORARY,owner,object_type,created,status);
Index created.

 

Now, in explain plan screenshot, we can see that STATUS column was used in FILTER predicate. Why so? because as per index rules, only one column of index which is referred in non equal to condition of query can be used for access purpose and after that column, remaining all columns of index will be used for filter.
In our query, temporary,owner,object_type columns are used with equal to condition so they will be directly used for ACCESS. But in our query, created,status columns are used with non-equal to condition, so only 1 column among them can be used for ACCESS purpose and remaining column will be used for FILTER purpose. As CREATED column is before STATUS column in column sequence of index, CREATED column was picked up for ACCESS and STATUS was picked up for FILTER.
Just for fun, please note how optimizer has modified our query by adding conditions:
“CREATED”>SYSDATE@!-2 AND “CREATED” IS NOT NULL

Case 2: When query is not using all columns of the index

PFB screenshot, please note that I have changed conditions in WHERE clause.

Now, we are only referring 3 columns in our query but in index we have 5 columns.
We are using columns TEMPORARY, OWNER in query which are first 2 columns in index so those columns will be used for ACCESS purpose but column STATUS is at no. 5 in index so it will be used for FILTER purpose as we have skipped 2 column of index which are  positioned before STATUS column. (Even if we skip 1 column from INDEX in our query, all remaining columns of that index will be used for FILTER purpose).

Case 3: Adding unwanted columns in index
If we add any column in index and in query, we are referring column which are there after that column in index, all those “after” columns will be used for FILTER. In below example, I have added column OBJECT_ID at 2nd place in index because of that we can see, OWNER, STATUS column are getting used in FILTER. Please note that, if we are seeing 1 column in ACCESS as well as in FILTER section, consider it as a FILTER only.
(If we think closely, this case is as same as that of case 2).

I hope guys, so far you have understood which are the conditions that turn ACCESS into FILTER.

In next post, we will understand, how to create optimal indexes using these basics, see you there.