Column Order in Composite Index

=> Create a composite index cust_income_state_idx

SQL> CREATE INDEX sh.cust_income_state_idx
  2  ON sh.cust(cust_income_level,cust_state_province)
  3  NOLOGGING
  4  COMPUTE STATISTICS
  5  /

Index SH.CUST_INCOME_STATE_IDX created.


SQL>

=> Check the execution plan for the below statement that is using the composite index cust_income_state_idx, notice that the cost is 74 

SQL> EXPLAIN PLAN FOR SELECT cust_first_name, cust_last_name,
  2  cust_income_level, cust_credit_limit
  3  FROM sh.cust
  4  WHERE cust_income_level like 'F%'
  5  AND cust_state_province in ('NJ', 'NY')
  6  AND cust_gender = 'M'
  7  AND rownum < 31
  8  /

Explained.

SQL> SELECT * FROM TABLE (dbms_xplan.display);

Plan hash value: 2872430508
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                       |    30 |  1590 |    74   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                       |                       |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CUST                  |    30 |  1590 |    74   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | CUST_INCOME_STATE_IDX |   133 |       |    28   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<31)
   2 - filter("CUST_GENDER"='M')
   3 - access("CUST_INCOME_LEVEL" LIKE 'F%')
       filter(("CUST_STATE_PROVINCE"='NJ' OR "CUST_STATE_PROVINCE"='NY') AND "CUST_INCOME_LEVEL" LIKE 
              'F%')
			  

=> Now drop the index and create a new index cust_state_income_idx by changing the column order 

SQL> drop index CUST_INCOME_STATE_IDX;

Index CUST_INCOME_STATE_IDX dropped.

SQL> CREATE INDEX sh.cust_state_income_idx
  2  ON sh.cust (cust_state_province, cust_income_level)
  3  NOLOGGING
  4  COMPUTE STATISTICS
  5  /

Index SH.CUST_STATE_INCOME_IDX created.

=> Check the execution plan for the same statement that is using the new index. Notice that the cost has reduced to 49. 

SQL> EXPLAIN PLAN FOR SELECT cust_first_name, cust_last_name,
  2  cust_income_level, cust_credit_limit
  3  FROM sh.cust
  4  WHERE cust_income_level like 'F%'
  5  AND cust_state_province in ('NJ', 'NY')
  6  AND cust_gender = 'M'
  7  AND rownum < 31
  8  /

Explained.


SQL> SELECT * FROM TABLE (dbms_xplan.display);

Plan hash value: 1371959703
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                       |    30 |  1590 |    49   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                        |                       |       |       |            |          |
|   2 |   INLIST ITERATOR                     |                       |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| CUST                  |    30 |  1590 |    49   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | CUST_STATE_INCOME_IDX |   133 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<31)
   3 - filter("CUST_GENDER"='M')
   4 - access(("CUST_STATE_PROVINCE"='NJ' OR "CUST_STATE_PROVINCE"='NY') AND "CUST_INCOME_LEVEL" LIKE 
              'F%')
       filter("CUST_INCOME_LEVEL" LIKE 'F%')
	   

=> Column order within index effects execution plan due to various factors like access paths, data distribution etc, so it is good to verify the cost with different column orders before creating a composite index.