Testing Different Access Paths : Concatenated Index

 => Create a concatenated index on the CUSTOMERS table, and in the order cust_gender, cust_credit_limit and cust_postal_code 

SQL> CREATE INDEX cust_gender_limit_code_idx
  2  ON customers(cust_gender,cust_credit_limit,cust_postal_code)
  3  NOLOGGING COMPUTE STATISTICS;

Index created.

SQL>

=> Run the below query that uses all the columns in the concatenated index in the where clause. The optimizer uses your concatenated index and the resulting cost is less. 

SQL> set lines 1000
SQL> set autotrace traceonly explain statistics
SQL> SELECT /*+ INDEX(c) */ c.*
  2  FROM   customers c
  3  WHERE  cust_gender   = 'M'
  4  AND    cust_postal_code = 40804
  5  AND   cust_credit_limit = 10000
  6  /

6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3961226828
------------------------------------------------------------------------------------------------------------------
| Id  | Operation     | Name  | Rows  | Bytes | Cost (%CPU)| Time  |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |  |     4 |   756 |    19   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS   |     4 |   756 |    19   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN     | CUST_GENDER_LIMIT_CODE_IDX |     4 |  |    14   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CUST_GENDER"='M' AND "CUST_CREDIT_LIMIT"=10000)
       filter(TO_NUMBER("CUST_POSTAL_CODE")=40804)

Statistics
----------------------------------------------------------
 39  recursive calls
  8  db block gets
 92  consistent gets
 14  physical reads
       1524  redo size
       3617  bytes sent via SQL*Net to client
498  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  6  rows processed

SQL>

=> Remove one of the column from where clause and observe the execution plan. The query is almost the same as in the previous step, but the predicate on cust_postal_code is removed. The optimizer can still use the concatenated index, but the resulting cost is much higher because neither cust_credit_limit nor cust_gender are very selective. Number of rows returned are 4101 unlike in previous query it is 6

SQL> SELECT /*+ INDEX(c) */ c.*
  2  FROM   customers c
  3  WHERE  cust_gender   = 'M'
  4  AND   cust_credit_limit = 10000
  5  /

4101 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3961226828
------------------------------------------------------------------------------------------------------------------
| Id  | Operation     | Name  | Rows  | Bytes | Cost (%CPU)| Time  |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |  |  3975 |   733K|  3961   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS   |  3975 |   733K|  3961   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN     | CUST_GENDER_LIMIT_CODE_IDX |  3975 |  |    14   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CUST_GENDER"='M' AND "CUST_CREDIT_LIMIT"=10000)

Statistics
----------------------------------------------------------
  3  recursive calls
  4  db block gets
       4387  consistent gets
  0  physical reads
636  redo size
     846040  bytes sent via SQL*Net to client
       3469  bytes received via SQL*Net from client
275  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
       4101  rows processed
SQL>

=> Now replace the cust_credit_limit with cust_postal_code, which has better selectivity (75 rows). The index is still used, and the resulting cost is better than previous one 

SQL> SELECT /*+ INDEX(c) */ c.*
  2  FROM   customers c
  3  WHERE cust_gender = 'M'
  4  AND   cust_postal_code = 40804
  5  /

75 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3961226828
------------------------------------------------------------------------------------------------------------------
| Id  | Operation     | Name  | Rows  | Bytes | Cost (%CPU)| Time  |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |  |    40 |  7560 |   156   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS   |    40 |  7560 |   156   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN     | CUST_GENDER_LIMIT_CODE_IDX |    40 |  |   116   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CUST_GENDER"='M')
       filter(TO_NUMBER("CUST_POSTAL_CODE")=40804)

Statistics
----------------------------------------------------------
  3  recursive calls
  5  db block gets
198  consistent gets
101  physical reads
884  redo size
      17681  bytes sent via SQL*Net to client
727  bytes received via SQL*Net from client
  6  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
 75  rows processed
SQL>

=> Now remove the leading column of the concatenated index from the where clause of the query. However, the optimizer is still able to use the index by doing an index skip scan.

SQL> SELECT /*+ INDEX(c) */ c.*
  2  FROM   customers c
  3  WHERE  cust_postal_code = 40804
  4   AND   cust_credit_limit = 10000
  5  /

15 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 136390073
------------------------------------------------------------------------------------------------------------------
| Id  | Operation     | Name  | Rows  | Bytes | Cost (%CPU)| Time  |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |  |     6 |  1134 |    99   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS   |     6 |  1134 |    99   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN     | CUST_GENDER_LIMIT_CODE_IDX |     6 |  |    92   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CUST_CREDIT_LIMIT"=10000)
       filter("CUST_CREDIT_LIMIT"=10000 AND TO_NUMBER("CUST_POSTAL_CODE")=40804)

Statistics
----------------------------------------------------------
  3  recursive calls
  5  db block gets
 43  consistent gets
  7  physical reads
944  redo size
       5325  bytes sent via SQL*Net to client
472  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
 15  rows processed
SQL>