Re: Optimzer use for an index

Sorry about the delay in replying - I've been in the US for the last week, and it can make email and news a little difficult to deal with.

My strategy for dealing with interpreting things like 10053 is to guess, predict then check (a process known as the 'scientific method'). Provided the results I come up with are reasonably accurate, I assume my guesses are roughly on target - until an example comes up that proves me wrong (or Oracle changes the version number). One of the difficulties of doing this with 10053 traces is that some numbers are causes, some are effects, so sometimes my guess reads:

    X is true because B
when is should read

    X is true because A, and (function(A) = B)

Looking at your data then - I do not understand why the tablescan cost is roughly table-blocks/10. In my experience it has usually been very close to table-blocks/multiblock_read-count - do you have an extent size of 10 blocks on this table ? Do I need to review examples of tablescan costs ?

Looking at the cluster factor of the 3-column index, it is 64,458 - this means that if Oracle scanned the index from end to end, it would go through the process of 'find another table block' that many times. We also see that the index has 1,397 leaf blocks, and we also see that Oracle has worked out that partial use of this index would typically acquire 1/15 of the total table (density = 6.666 x 10^-2). Oracle has deduced this from the number of distinct values of the two used columns.

calculations then - scanning the correct fifteenth of the index, to visit the fifteenth of the table, the number of read requests is:

   (64458 + 1,397) / 15 = 4,390

which is close enough to the quoted cost of 4,394 to make me think I am on the right track.

Applying the same rules to the other index: The cluster factor is 39,800, the leaf count is 544, we are still going to get 1/15 of the table, cost is:

    (39,800 + 544) /15 = 2,690
against a quoted cost of 2,693.

If I haven't previously said so, Oracle's basic costing method revolves around - "how many read requests do I have to make ?" coupled with "if I have to make a read request, it's another physical read, if I stay inside the data provided from the previous read request it is free".

So for the tablescan - Oracle has estimated 3,740 read requests (rather than the 2,430 that I would have anticipated). For index 1 the estimate is 4,394, and for index 2 the estimate is 2,693. Hence the choice you see.

>I do not see the numbers for how may data blocks are read after selection
 is
>made from the index

Inferred (perhaps indirectly) from the cluster factor.

>Why on the new index IXSEL=0.00e+0

I don't know - possibly for an index equality on an index which does not allow nulls the index selectivity is not calculated.

--
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 ...


>Good morning!

>

>> You've actually shown us three different queries-

>

>I must admit you are right. There are little differences which I thought

>were not important. The *real* statement makes use of bind variables. This

>one I picked from v$sql_area. To run it I had to supply actual values, and

>yes, once I forgot the quotes for the COMPANY_CODE field. I'm glad (anyway)

>I made the mistake because it helps me understand some more how everything

>works.

>

>1st observation in your analysis: Oracle can`t choose the index-only path.

>True and this was clear to me from the beginning because there is an extra

>field to get, that does not belong to the index. The sql statement I posted

>did not make this clear to you though.

>But that does not make a difference in that Oracle will use the new index

>when it exists, but will never use the original index.

>

>2nd observation: The BEFORE can`t use the index and there are no column

>stats for COMPANY_CODE.

>I wondered why. Now I know. So I did it right this time and attached a new

>BEFORE.TXT.

>

>3rd observation: the AFTER query shows weird numbers. I did not do an

>analyze on the index after I created it. That probably explains the

 numbers.


>Attached is a new AFTER.TXT that I did after running the analyze.

>

>MY observations now:

>The query returns 2 rows.

>

>db_file_multiblock_read_count = 16

>db_block_size = 8192

>

>Is it less costly to Oracle to use the new index because:

>544 new index blocks + ?? data blocks < 38844 blocks full table scan < 1397

>original index blocks + ?? data blocks  ? (i..e. it is more costly to read

>the original index + the data blocks than it is to read all of the

>datablocks ?).

>I do not see the numbers for how may data blocks are read after selection

 is


>made from the index (?? above) so it's hard to figure why full table scan

>(38844 blocks) would be less costly than using the original index (1397

>index blocks + ?? data blocks).

>

>Why on the new index IXSEL=0.00e+0

>

>

>Thanks for helping me in this matter. It is much appreciated.

>--

>