RE: Index skip scan

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Tue, 20 Sep 2005 14:49:16 -0400
Message-ID: <77A4D80DB2ADD74EB5D7F1D31626F0C0024F960D@usa0300ms03.na.xerox.net>

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_at_allegientsystems.com] 
	Sent: Tuesday, September 20, 2005 2:20 PM
	To: Hameed, Amir; oracle-l_at_freelists.org
	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_at_xerox.com] 
	Sent: Tuesday, September 20, 2005 12:58 PM
	To: oracle-l_at_freelists.org
	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 

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 20 2005 - 13:52:23 CDT