Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> help figuring out why query doing full table scan
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)
Received on Thu Dec 21 2000 - 11:36:28 CST