Databases Reference
In-Depth Information
Index created.
SQL>
SQL> select b,c,a from test_tab where b='pc-5895' and c='pc-2893' and a=564;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3182375932
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0) |00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 20 | 2 (0) |00:00:01 |
|* 2 | INDEX RANGE SCAN | SINGLE_IDX1 | 1 | | 1 (0) |00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"='pc-5895' AND "C"='pc-2893')
2 - access("A"=564)
SQL>
The optimizer uses an index scan, but it also has to scan the table rows since all the required
columns are not part of your single column index. This means more I/O and more time to complete the
query in most cases. You now drop the index on the single column and create a composite index using
all three columns this time.
SQL> drop index single_idx1;
Index dropped.
SQL> create index comp_idx1 on test_tab(a,b,c)
SQL> /
Index created.
SQL> select b,c,a from test_tab where b='pc-5895' and c='pc-2893' and a=564;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1685463053
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| COMP_IDX1 | 1 | 20 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"=564 AND "B"='pc-5895' AND "C"='pc-2893')
SQL>
