Index Usage

[default]

Hi,

Is there any minumum number of rows a table should have inorder for oracle to make use of an index against that table?

I do know selectivity of an index is a measure of the index's usefulness .

what if a table has 50 rows with empno as unique? does oracle use the index for selects. if not why?

Thanks in Advance

[tamilselvan]

If the cost of index searches I/O is greater than table search, then index will not be used.

[default]

Hi,

Correct me if i am wrong.

Accessing an index is accessing the rowid right! Then if i have a unique index on a table which ha 50 rows isn't it more efficient to do an index scan and return the data rather than doing a full table scan.

Thanks

[tamilselvan]

It depends upon how many rows returned by the Query.
The emp table has 50 rows.

For example,
Select empid, empname
from emp
where empid between 1 and 30 ;
In this case index will not be used as 60 % of the rows are returned.


Select empid, empname
from emp
where empid between 1 and 3 ;
In this case index will be used as 6 % of the rows are returned.

[default]

Hi,

I have a table with 25 rows. Column (A) is unique and indexed.

So i say..

Select * from table where a=xxxx;

Shouldn't be a index used in this scenario. I am select just one row...

Thanks

[pando]

index will be used coz of high selectivity although not always high selectivity means index usage, if data is not distributed uniformly the optimizer will choose FTS if there are no histograms and you are not using bind variables

but in your case index will be used since your index is unique

[default]

Hi,

I performed a small test based on my previous example and the index is not being used . It is doing a full table scan.


Thanks

[pando]

well how did you create your test table and how did you insert your unique record?