Check Indexes in WHERE conditions

I have taken “HZ_LOCATIONS” table from Oracle ERP to explain where the indexes are been used or not if we have the index on column COUNTRY and STATE.
So now we have index called HZ_LOCATIONS_N5 ON HZ_LOCATIONS(COUNTRY, STATE), but we will see where indexes are been used based on the where condition and how we need to keep a watch on that when we develop any code or simply write an SQL statement:

Leading index column will use index but not the non leading index , so in our case the COUNTRY will use the index but not the STATE column .

For example for non leading index :

SQL>explain plan for
select * from hz_locations where state = 'TX' ;

PLAN_TABLE_OUTPUT

Plan hash value: 3092045126

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1372 | 352K| 3850 (0)| 01:11:32 |
| 1 | TABLE ACCESS BY INDEX ROWID| HZ_LOCATIONS | 1372 | 352K| 3850 (0)| 01:11:32 |
|* 2 | INDEX SKIP SCAN | HZ_LOCATIONS_N5 | 1372 | | 2738 (0)| 00:50:53 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - access("STATE"='TX')
 filter("STATE"='TX')

Here we can see that the Index is not been used , and skipped as column ‘STATE’ is non leading index.

Example for leading index :

Let’s check what will be the plan if we put country after WHERE condition:

SQL> explain plan for
select * from hz_locations where country = 'US';

PLAN_TABLE_OUTPUT

Plan hash value: 509890072

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49843 | 12M| 34738 (1)| 10:45:24 |
|* 1 | TABLE ACCESS FULL| HZ_LOCATIONS | 49843 | 12M| 34738 (1)| 10:45:24 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - filter("COUNTRY"='US')

So now we can see that the index is used when we use ‘COUNTRY’ column after WHERE condition.

But if we use both ‘COUNTRY’ and ‘STATE’ then the index will be used.
For example :

explain plan for
select * from hz_locations where state = 'TX' and country = 'US' ;

PLAN_TABLE_OUTPUT

Plan hash value: 3242166240

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1372 | 352K| 1123 (0)| 00:20:52 |
| 1 | TABLE ACCESS BY INDEX ROWID| HZ_LOCATIONS | 1372 | 352K| 1123 (0)| 00:20:52 |
|* 2 | INDEX RANGE SCAN | HZ_LOCATIONS_N5 | 1372 | | 11 (0)| 00:00:13 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - access("COUNTRY"='US' AND "STATE"='TX')

From the above explain plan we can see that the index is used in our query when we added country and state arguments after WHERE condition.

In few conditions our index are not used or we can say by using such condition we indirectly disable the use of the index like using ‘ NOT, != and <>’:
For example if we use conditions like :

SQL> SELECT *
 FROM hz_locations
 WHERE state NOT IN ('NJ', 'WV', 'SD', 'RJ', 'MN');

OR

SQL> SELECT *
 FROM hz_locations
 WHERE state != 'NJ';

For the above two SQL statements the index will not be used.
After this we might be having the questions in our mind about the NULL value reference like will index be used or not ?
If we refer NULL values after WHERE condition then INDEX will not be used :

SQL>SELECT *
 FROM hz_locations
 WHERE state IS NULL;

OR

SQL>SELECT *
 FROM hz_locations
 WHERE state IS NOT NULL;

In the above SQL statements index will not be used as NULL values are been referred after WHERE condition.

Expressions that we use in our SQL statements will never use any indexes for example if we use substr on ‘STATE’ column or if we try to concatenate (“||”) COUNTRY and STATE column.

Advertisements

One thought on “Check Indexes in WHERE conditions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s