Quote:
Originally posted by Chucks_k
SELECT * FROM CUSTOMERS c, ACCOUNT_OWNERSHIPS ao
WHERE ao.scv_cust_id = c.SCV_CUST_ID
AND c.CUSTOMER_NUMBER = 'xxxxxxxxxx';
Below are the 2 explain plans. The first one for when the query is run now and the second one for when it was run before.
Explain Plan (full table scan on ACCOUNT_OWNERSHIPS) ...
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 3 M 1899
NESTED LOOPS 3 M 305 M 1899
TABLE ACCESS FULL SCV_OWNER.ACCOUNT_OWNERSHIPS 3 M 64 M 1899
TABLE ACCESS BY INDEX ROWID SCV_OWNER.CUSTOMERS 45 K 3 M
INDEX RANGE SCAN SCV_OWNER.CUS_SCV_I1 45 K
Expect to see something like (no full table scans) ...
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 6 K 61
NESTED LOOPS 6 K 750 K 61
TABLE ACCESS BY INDEX ROWID SCV_OWNER.CUSTOMERS 19 1 K 4
INDEX RANGE SCAN SCV_OWNER.CUS_SCV_I1 19 3
TABLE ACCESS BY INDEX ROWID SCV_OWNER.ACCOUNT_OWNERSHIPS 10 M 259 M 3
INDEX RANGE SCAN SCV_OWNER.AOP_CUS_DK_I 10 M 2
Thanks in advance,
Chucks_k
The first plan virtually ** SUCKS **..