Re: help figuring out why query doing full table scan

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

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/