combination index issue [message #557834] |
Sat, 16 June 2012 02:50 |
andy huang
Messages: 498 Registered: July 2011
|
Senior Member |
|
|
Dear all,
I created a combination index on a table,the sql plan is differenct between where c1 and where c2?
one is the index range scan and the other is index fast full scan,why?
how to store the index key values in a combination index?
QL> create table tb_index_test(c1 number,c2 number);
Table created.
SQL> create index idx_tb_index_test on tb_index_test(c1,c2);
Index created.
SQL> insert into tb_index_test values(null,1);
1 row created.
SQL> insert into tb_index_test values(2,null);
1 row created.
SQL> commit;
Commit complete.
SQL>explain plan for select * from tb_index_test where c2=1;
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3534558272
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)|
00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_TB_INDEX_TEST | 1 | 26 | 2 (0)|
00:00:01 |
--------------------------------------------------------------------------------
SQL>explain plan for select * from tb_index_test where c1=2;
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1564323412
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:0
0:01 |
|* 1 | INDEX RANGE SCAN| IDX_TB_INDEX_TEST | 1 | 26 | 2 (0)| 00:0
0:01 |
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|
|
|
|
|
Re: combination index issue [message #557986 is a reply to message #557846] |
Mon, 18 June 2012 08:01 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
If you want to scan on C1, then you need an index with C1 as the first column
If you want to scan on C2, then you need an index with C2 as the first column
You need 2 indexes
The only exception to this rule is SKIP SCANs, but you should leave those to the experts.
Here's an article that explains how indexes work. It might help.
Ross Leishman
|
|
|
|