Home » RDBMS Server » Performance Tuning » selectivity in index usage (Oracle 9i)
selectivity in index usage [message #338648] Tue, 05 August 2008 09:48 Go to next message
krshnn592
Messages: 5
Registered: August 2008
Junior Member
Hi,

While going through the Oracle 9i performane tuning guide and refence,in chapter 1,page 18(section :-Understanding Access Paths for the CBO) I have found that:--

If a statement retrieves small number of rows then Index is used,and in case of large portion of rows full scan is used.

So my understanding is that if query has low selectivity(%age of rows retrived by query) then index is used,otherwise(high selectivity) full scan is used.
But in next line it is written that---In OLTP applications short running queries with high selectivity(for me it should be low selectivity) are characterized by index access path.
I think one is contradictory to othrs,Is it? PLEASE HELP ME.
Re: selectivity in index usage [message #338656 is a reply to message #338648] Tue, 05 August 2008 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So my understanding is that if query has low selectivity(%age of rows retrived by query) then index is used,otherwise(high selectivity) full scan is used.

No, this is the opposite.
Low selectivity -> "many" rows returned -> FTS
High selectivity -> "few" rows returned -> Index

Regards
Michel
Re: selectivity in index usage [message #338666 is a reply to message #338656] Tue, 05 August 2008 10:35 Go to previous messageGo to next message
krshnn592
Messages: 5
Registered: August 2008
Junior Member
thanks for response,
could u please tell me one thing:-How is this selectivity different from Selctivty measurement generated by Estimator in CBO.Here selectivity 0.0 means no rows selected,0.1 means less number of rows and 1.0 means all rows are selected. Thanks
Re: selectivity in index usage [message #338670 is a reply to message #338666] Tue, 05 August 2008 10:50 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand the question. This is the same unless one is ratio and the other ones number of rows, but basically it is the same.

Regards
Michel
Previous Topic: QUERY REWRITING
Next Topic: buffer cache handles
Goto Forum:
  


Current Time: Fri Dec 09 13:59:39 CST 2016

Total time taken to generate the page: 0.10461 seconds