Re: Optimzer use for an index

You've actually shown us three different queries-

Your original query showed bind variables for company and warehouse - if that's really what you've got, then histograms won't help.

Your before and after queries introduce a column (activation_code) that isn't in the original query, which helps to explain why Oracle doesn't choose and index only path.

The AFTER query shows:

    A1.COMPANY_CODE='6'
but the BEFORE query shows:

    A1.COMPANY_CODE=6 Because the BEFORE has to do a to_char() on the company_code, it is not able to use the index anyway, which explains why no single path with the index appears in the trace, and why no column stats appear for that column.

The AFTER query shows some really weird numbers for the two-column index -

    TOTAL :: LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF: 800 Leaf blocks = 25 - not possible with 155519 rows unless it is a bitmap index, each row in a non-unique b-tree needs seven bytes in the index for the rowid, so your index MUST have at least 132 8K leaf blocks.

Datablocks per key = 1 - but you have 15 key values, 155519 rows, and 3 rows per block (8K blocks, rowsize = 1666), so this has to be wrong.

Cluster Factor = 800: Not possible when the table has 38,884 blocks. The cluster factor (for a b-tree) has to be at least as large as the table block count if all the columns have no null values.

Given the completely undersized values for this index, it is not surprising that the cost comes out very low, and the index is chosen.

The question really is why does this take only 2 seconds - in theory it has to access 10,000 rows by index, hitting about 3,350 scattered blocks, whereas the tablescan has to hit 38,884 blocks in 16 block I/Os for a total of 2,430 I/Os.

There are two options - (a) most of the data you want is buffered, so the 3,350 I/Os are all at CPU speed, or (b) the specific warehouse your are after is very small.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Syltrem wrote in message ...


>Hi!

>

>COMPATIBLE=8.0.5.1 (and db is 8051)

>

>Adding SIZE 10 to the ANALYZE does not change plan.

>

>attached are the 2 trace files - before and after adding the new index.

>--

>

>Syltrem

>http://pages.infinit.net/syltrem (OpenVMS related web site)

>

>

>"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> a écrit dans le message news:

>7lv0et0n4aeptr5sibogsgqf4dtjfl1la5_at_4ax.com...

>> On Fri, 20 Apr 2001 13:51:49 -0400, "Syltrem" <syltrem_at_videotron.ca>

>> wrote:

>>

>> >Answers embedded below

>> >Thanks for bringing some light into this!

>>

>>

>> This is a weird, but very interesting one.

>> My guesses are as follows:

>> - The query selects the major part of the table

>> - the order by can *not* be prevented by using the index

>>  (index on columns a,b,c order by on a, *c*, so one missing column)

>> - it is still more advantageous (given db_file_multiblock_read_count)

>> to run a full table scan instead of using the index.

>> Why the same line of reasoning isn't followed in the second scenario,

>> I really don't know.

>> Maybe I have been not clear enough in my initial reply with respect to

>> using indexes.

>> What I was trying to say: if you have a clause on all columns of an

>> index, the optimizer will use all columns. It is not going to suppress

>> a column out of itself. If you would have been using a different

>> column, the path would have been different.

>> So more questions:

>> - you are not using by any chance using some weird low value for the

>> compatible parameter, suppressing new CBO functions?

>> - in itself your analyze is correct, but I usually take more buckets

>> for the histogram, say 10. Could you try to add size 10 to the analyze

>> command

>> - and now for the final firecracker (c/o of Jonathan Lewis, Practical

>> Oracle 8i)

>> could you issue

>> drop the second index

>> alter session set events '10053 trace name context forever, level 1'

>> /* this will dump the optimizers reasoning in an ordinary trace file

>> */

>> reissue the statement

>> recreate the second index

>> reissue the statement

>> post the results?

>>

>> Regards,

>>

>> Sybrand Bakker, Oracle DBA

>>

>> Sybrand Bakker, Oracle DBA

>

>

>