Re: full table scan cheaper than index??

On Thu, 12 Jul 2001 13:58:55 +0200, Jan Haase <jh_at_informationsdesign.de> wrote:

>Hi.
>
>I've got a serious problem: Yesterday I found out that some queries to a table
>were very slow, so I gave the "analyze"-command (estimate) to get new
>information about the data.
>Now the optimizer seems to be completely nuts, it doesn't use any index at
>all!
>"Set autotrace on" tells me that the cost of using an index is much higher
>than a full table scan.
>
>The database (Oracle 7.3.4) runs in "CHOOSE"-Mode, so it uses cost-based
>analysis.
>I used "analyze table xxx estimate statistics sample 10 percent;" and another
>"analyze"-command for the index to be used didn't change oracle's behaviour.
>Even deleting the analysis-data (by typing "analyze table xxx delete
>statistics") didn't help.
>
>Please help me, for I don't see where I think in the wrong direction.
>
>Thanks in advance,
>Jan
>

Full scans on joined tables is frequently faster than index scans. It depends on how many rows you're trying to retrive, and how many are in the table. Generally speaking, if you're retrieving more than 10%of the rows in a table, a table scan is faster because it's sequential and do larger io's. If you really want it to use the index, hint the query, or try reducing db_file_multiblock_read_count. That'll favor indexes a little more.

Also, you don't need to analyze both the table and it's indexes. An "analyze table" without the "for table" clause will do all of the indexes automatically.

Chuck
Do not be deceived, God is not mocked;
for whatever a man sows, this he will also reap. (Gal 6:7 NASB) Received on Sat Jul 21 2001 - 16:33:23 CDT