Home » RDBMS Server » Performance Tuning » combination index issue (10.2.0.1)
combination index issue [message #557834] Sat, 16 June 2012 02:50 Go to next message
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 #557838 is a reply to message #557834] Sat, 16 June 2012 03:18 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Because CBO cannot do index range scan for "c2=1". It has to decide between index full scan, index fast full scan or index skip scan.

[Updated on: Sat, 16 June 2012 03:18]

Report message to a moderator

Re: combination index issue [message #557846 is a reply to message #557834] Sat, 16 June 2012 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 67950
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read the Database Concepts?
Without this you will not understand how Oracle works.
So do it.

Regards
Michel
Re: combination index issue [message #557986 is a reply to message #557846] Mon, 18 June 2012 08:01 Go to previous messageGo to next message
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
Re: combination index issue [message #558019 is a reply to message #557986] Mon, 18 June 2012 22:36 Go to previous message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks,
Ross!
Previous Topic: Passing record to a variable - Performance Issue
Next Topic: PL/SQL Block (LOOP) Comparison
Goto Forum:
  


Current Time: Sun Sep 26 01:32:19 CDT 2021