index usage - total no of records - doubt

[madhugp]

hi, for Index can be used , if the table has how many records?
i.e for few records is is not useful right?

[slimdave]

Not neccessarily. If you have a single record in a tabvle with its highwater mark at 8Mb then an index is going to be faster at finding it.

[simply_dba]

A table rebuild is nessesary in this case. Isn't it dave?

[chduarte]

If you are using CBO (Cost Based Optimizer) there is no way to know if an index is going to be used or not. Oracle will get statistics on data dictionary and based on this cost will let index to be used or not.

That's way Oracle is advising that RBO (Rule Based Optimizer) will be obsolete in further releases.

Just keep your database with statistics updated and you may have a good access plan.