Creating optimal indexes to improve query performance Part-2

 Hi Guys,

In last post we saw which are the conditions that turn ACCESS into FILTER and lower down performance of our index. If you have not read that post then I will suggest to read it first. Also in case you have doubt about ACCESS and FILTER predicates then please read this post.

So let me take one example query and together we will tune it.
select *
from amol.AMOL_DBA_OBJECTS where 1=1
—
and status=’VALID’
and owner=’HR’
and TEMPORARY=’N’
and OBJECT_TYPE in (‘INDEX’,’TABLE’)
—
and CREATED > sysdate-5000
and ORACLE_MAINTAINED<>’Y’
and object_id between 92562 and 92751;

Explain plan for above query is:

Now, as we don’t have any index on our table, it’s obvious that we will be seeing FILTER operation. So why not create one index?
As we are aware all equal to conditions in above query will support ACCESS and all non equal to predicates will allow FILTER except the first non equal to column as per index sequence. (Please refer part 1 of this post in case of any doubt).

As per our expectation, CBO is picking up newly created index and it has significantly reduced cost of the query from 430 to 5. Our query is tuned right????
No, newly created index is not an optimal index. Index with 7 columns in it is certainly not an optimal one. So let’s try to optimize it by reducing few columns.

First, we will focus on columns which are used with non equal to predicate in index. We will take count of number of rows returned by those columns.

Here, I have first taken row count of table. Then I have taken row count of all non-equal to predicates. Now, column with lowest row count (among all non equal to columns) must be put ahead of other columns with non-equal to predicate. As column with lowest row count is returning fewer rows and only 1 column with non-equal to predicate can be used for ACCESS, it’s beneficial to bring that column ahead. Then arrange all remaining columns in ascending order as per their row counts.
Let’s create new index and analyze it:

Now, we can see our 2nd index is getting picked up by CBO, meaning we are going at right direction and this index is more optimal than first one. Good isn’t it!!!!
But wait, still our index is 7 columns wide 

Let’s do something for that.
I have 1 magic script with name gencardinalitycheckcode.sql which you can download from downloads section or from github, thanks to Mr. Kevin Meade.
Process to use this script is:
Step 1: Run the script with below syntax:
@gencardinalitycheckcode.sql table_owner table_name column1,column2,columnN column_count
In short, column list should be given in comma separated value (csv) format
Step 2: After executing script with above syntax you will get 1 big sql query which you should run in sqlplus (highlighted in blue) Please note, to generate that big SQL i’m using sqldeveloper and then I will run that big sql sqlplus.

Step 3: After executing output of the script we will get below output.

Now, let’s analyze the output:
1. First highlighted row “AMOL AMOL_DBA_OBJECTS 91673″ is the total no. of rows in the table which is 91673 in this case.
2. Second row ” 1 987 CREATED” suggests CREATED column has 987 distinct rows in it. 1 in this row is column count. Same way, 3rd row “1 91673 OBJECT_ID” suggests column OBJECT_ID has 91673 distinct rows in it.
Does row count of column OBJECT_ID gave us any hint?
Distinct no. of rows in column OBJECT_ID are equal to total no. of rows in the table.
After doing some analysis (at functional/application level) I found that I can in fact use this column as a unique or primary constraint. This is very helpful information. Remember, I told you guys that if you want to be good at performance tuning, you must understand your data.
Now, let me create a primary constraint on this table using column OBJECT_ID.

As we can see, CBO is not picking up primary key index (whenever we create unique or primary constraint, Oracle automatically creates an index for the same).
As our index is not getting picked up, let’s add 1 more column in it. If we see output of gencardinalitycheckcode.sql we can see OWNER column has 33 distinct rows, as we want to keep index light, we must add column with lower distinct count in index, in 1 by 1 fashion.

Let’s do the same:

Cheers!!!! Now, our newly created index is getting picked up. This index is certainly optimal one as we have 2 columns as compare to 7 columns and it has reduced cost of query too. Please see below screenshot for comparison of our indexes:

Certainly, our primary key index is best and we can drop remaining 2 indexes