Re: Full table scan query

On Tue, 22 Jan 2002 15:01:08 -0000, Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote:

>Afternoon all, here is today's daft question !
>
>Oracle 8.0.5. (I know, but it is customer driven so I can't upgrade yet)
>:o)
>The optimiser is CHOOSE
>The statistics are up to date because I re-computed them.
>DB_FILE_MULTIBLOCK_READ_COUNT is 25
>HP-UX 11:00 32 bit.
>
>
>I have a table with 2,500,000 rows in it.
>There is an index on two columns in it and there are 175 unique values
>in the first column and 350,000 in the second column of the index.
>
>A developer is running the statement :
>
> select * from the_table where first_index_column = 'some_value';
>
>This will return 9 rows from the table.
>
>
>The explain plan gives a full table scan for the select above.
>When hinted to use the index, it refuses and again gives a FTS.
>When I created histograms on the indexed columns, I still get a FTS.
>
>However, if I create an index on the first column only, the optimizer
>uses that index whether or not I have histograms.
>But, because this new index has the same leading column as the original
>one, this is a redundant index (I thought) and will waste time inserting
>rows into the table.
>
>How on earth do I get the statement to use the first index?
>Should I try to use it ?
>And can someone explain why thie above is happening please? I suspect
>the reason is that there are too few values in the index, but, why then
>does it use the new index which only has 175 unique values in 2,500,000
>entries?
>
>I suspect that the existing index on the table has not been rebuilt for
>some time, but would that make the slightest difference - I don't think
>so.
>
>Thanks in advance.
>
>Regrads, Norman.
>
>------------------------------------------------------------------------
>-----
>Norman Dunbar EMail: Norman.Dunbar_at_LFS.co.uk
>Database/Unix administrator Phone: 0113 289 6265
> Fax: 0113 289 3146
>Lynx Financial Systems Ltd. URL: http://www.Lynx-FS.com
>------------------------------------------------------------------------
>-----

Hi Norman,

Given the fact your db_file_multiblock_read_count is 25 (which is way to high, as it should not exceed 64k when multiplied with db_block_size), it looks like almost every execution plan with a full table scan will be cheaper than using the index. The only other issue that is a likely culprit is the clustering_factor of both indexes. I have a feeling the clustering factor of the extra index is lower (I ran into the same issue several times, the clustering_factor always was nearing the number of blocks (so a good candidate for indexing) instead of the number of records. Rebuilding the index will not help. You will only get a different clustering factor when you reorganise the table and import the rows ordered by the primary key. One reason to use Index Organised Tables in 8i.
At this point a few questions /hints remain - what is the datatype of the affected column? - are you using literals or bind variables (bind variables will not use histograms)
- did you try setting event 10051 trace name context forever, level 1 This will show you the reasoning of the optimiser in your trace file.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Jan 22 2002 - 09:47:03 CST