RE: Index skip scan
- From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
- To: "Gogala, Mladen" <MGogala@xxxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 20 Sep 2005 14:49:16 -0400
It is very simple to generate a plan without a hint:
create table test as select * from dba_indexes;
create index test_idx1 on test(uniqueness, compression, index_name); --
the first two columns are not very selective
exec dbms_stats.gather_table_stats('<OwnerName','TEST',cascade=>true);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost
|
------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 1 | 44 |
5 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 44 |
5 |
| 2 | INDEX SKIP SCAN | TEST_IDX1 | 1 | |
4 |
------------------------------------------------------------------------
---
________________________________
From: Gogala, Mladen [mailto:MGogala@xxxxxxxxxxxxxxxxxxxx]
Sent: Tuesday, September 20, 2005 2:20 PM
To: Hameed, Amir; oracle-l@xxxxxxxxxxxxx
Subject: RE: Index skip scan
Generally speaking, I've never seen CBO chose skip scan by
itself. INDEX_SS may help, if you're
certain that it will improve performance.
--
Mladen Gogala
Ext. 121
________________________________
From: Hameed, Amir [mailto:Amir.Hameed@xxxxxxxxx]
Sent: Tuesday, September 20, 2005 12:58 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Index skip scan
Folks,
Does anyone know that in order for the optimizer to choose
"Index skip scan" on a concatenated index where the first column is not
very selective but the second one is, approximately what type of
selectivity on the leading column the Optimizer look for before deciding
to take this route?
Thanks
Amir
Other related posts:
