Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> help figuring out why query doing full table scan

help figuring out why query doing full table scan

From: bad <bad_at_yahoo.com>
Date: Thu, 21 Dec 2000 09:36:28 -0800
Message-ID: <3A423F9C.8CF3F6F3@yahoo.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US