Re: help figuring out why query doing full table scan

I don't think that's the answer, David.

For the hash join there are no conditions to eliminate rows from USER (how do you
get a table with a reserved word as its name?) so the reported cardinality will match the number of rows in the table, and histograms will not affect that..

I would guess (and I stress guess) that the key is in the numbers, though.

The cardinality of the join (i.e. result set) is 20,492 so Oracle has probably worked out that getting the data through a nested loop will require 20,492 index probes and table access for a cost of 20,492 * 5 plus the 1970 cost of the scan on SUMMARY_BILLING_EVENT A total of 104,430 - much higher than the cost of the hash join.

If you know that Oracle's caching of the relevant index is going to make its I/O cost incorrect, you could adjust the parameter

    optimizer_index_cost_adj
Default value = 100 - implying that every index block request will turn into a physical I/O - set it to (say) 10% to indicate that a large fraction of index block requests will be met from the buffer cache.

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

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



David Fitzjarrell wrote in message <91tncv$mmf$1_at_nnrp1.deja.com>...


>In our last gripping episode bad <bad_at_yahoo.com> wrote:

>> Running Oracle 8.0.5 on Solaris 2.6

>>

>> We have the following statement within a stored procedure that has all

>> the sudden

>> decided to do a full table scan on the user table.  I don't understand

>> why it all the sudden started doing this, as the statistics are up to

>> date.  The USER table has tens of millions of rows, so this just isn't

>> going to work.  Any ideas???????

>>

>> SELECT COUNT(DISTINCT USER.USER_ID || '@'  || USER.USER_DOMAIN )

 FROM


>> USER,SUMMARY.SUMMARY_EVENT SE  WHERE USER.USAGE_USED_BE_ID =

>> SE._EVENT_ID  AND SE.CUSTOMER_SUMMARY_ID = :b1

>>

>> With the following explain plan output.....

>>

>> SELECT STATEMENT Optimizer=CHOOSE (Cost=84353 Card=1 Bytes=2991832)

>> SORT (GROUP BY)

>> HASH JOIN (Cost=84353 Card=20492 Bytes=2991832)

>> TABLE ACCESS (BY INDEX ROWID) OF SUMMARY_BILLING_EVENT (Cost=1970

>> Card=6002 Bytes=168056)

>> INDEX (RANGE SCAN) OF SBE_CUSTOMER_SUMMARY_ID_IDX (NON-UNIQUE)

 (Cost=47


>> Card=6002)

>> PARTITION (CONCATENATED)

>> TABLE ACCESS (FULL) OF USER (Cost=73345 Card=32531311

 Bytes=3838694698)


>>

>>0

>

>The key is the Card value for the scan of USER -- the optimiser is

>expecting 32,531,311 rows to be returned.  It would appear that your

>indexed column values are not evenly distributed; I would try

>rebuilding the index or indexes on USER and create histograms.  This

>will indicate to the optimiser the skew of the data.

>

>--

>David Fitzjarrell

>Oracle Certified DBA

>

>

>Sent via Deja.com

>http://www.deja.com/