Re: TABLE ACCESS FULL

"Jay" <no_at_spam.com> wrote

> I'm trying to speed up my query which selects only 30 records out of
> 3million records.
> All my conditions are indexed but when I explains it, it says TABLE ACCESS
> FULL.
The view that a Full Table Scan (FTS) is a bad thing.. is nothing but an OWT (old wifes' tale).

If the cost of using indexes is bigger than using a FTS, then Oracle will use a FTS.

> I use "between" and, it seemingly, when I reduce range of "between" then it
> does INDEX RANGE SCAN.

Exactly!!! And there you have stated WHY there is a difference in execution plan. Pity that you did not spend some time thinking this through as you would have arrived at the answer.

Simple scenario. I have a 3GB index. I do a select with a BETWEEN clause.

Case 1. The value range specified in the between clause covers 2GB of data in that index (e.g. WHERE surname BETWEEN 'A' AND 'RZ')

Case 2. The value range specified covers 10MB of index data (e.g. WHERE surname BETWEEN 'SMITH' AND 'SMYTHE')

Now look at this from the work that the database needs to do.

In case 1, the db looks at this as says "hmm.. you are covering 80% of all index values. That means that 80% or even more of the data in the table will satisfy the criteria."

Thus the database, being the clever bugger it is (unlike what many seems to think), decides NOT to use the index.

80% of the data in the table will match the criteria. So instead of reading 80% of the index followed by reading 80% of the table, it rather reads 100% of the table and not use the index at all. (80% being an example - the actual percentage value is indexes & tables specific)

Q. what is the most expensive operation in a database? A. I/O

Case 2. The exact opposite of case 1. Thus the index IS used.

You have proved two things.
a) Oracle CBO works as it should (and better than you thought) b) You could spend some more time with the Oracle manuals :-)

> Is there anyway I can force to do INDEX RANGE SCAN?

IMO never ever enforce an index range scan (yes it is possible using hints). Index range scans can be a lot more painful than a FTS. When dealing with unknown index ranges, let the database (i.e. CBO) decides.

--
Billy