Query Perfomance

Try analyzing the coulmn CUSTOMER_NUMBER

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 **..

Do either of the things .. Put a hint as below to force oracle to choose CUSTOMERS as driving table.

Code:

---

SELECT /*+ Ordered */ * FROM CUSTOMERS c, ACCOUNT_OWNERSHIPS ao
WHERE ao.scv_cust_id = c.SCV_CUST_ID
AND c.CUSTOMER_NUMBER = 'xxxxxxxxxx';

---

Or Delete the Column Stats on CUSTOMER_NUMBER ( I would expect this to be evenly distributed and having many distinct values )

Abhay.

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';

---

I assume ACCOUNT_OWNERSHIP.CUSTOMER_NUMBER is of datatype NUMBER. IF so, change the above query to:

Code:

---

SELECT * FROM CUSTOMERS c, ACCOUNT_OWNERSHIPS ao
WHERE ao.scv_cust_id = c.SCV_CUST_ID
AND c.CUSTOMER_NUMBER = TO_NUMBER('xxxxxxxxxx');

---

Or simply dont put the quotes ('')

Thanks all for the input.

How do i analyze/delete stats for a particular column on a table?..Is it just a case of using dbms_stats? Is there another way?



Thanks in advance,
Chucks.

yes using dbms_stats

Hi all,

I deleted stats for that column(customer_number) in the table and re-analyzed the table. Re-ran the query and it is stil not picking up that index. Is it a case for rebuilding it(the index)?.


Thanks,
Chucks

Rebuilding wont do any good.. use hint as i said or use to_number fn as Jurij said..

BTW, how are u analyzing?

Abhay.

Hi there,

I tried using the hint and it came back with a even greater cost!. I analyzed the stats for the table like so:

analyze table customers compute statistics;

The customer_number is of datatype VARCHAR(2).

Thanks in advance,
Chucks

After you did analyze like u said above... Just use dbms_stats to delete stats on the column customer_number.. and see how oracle reacts.. or just delete stats using analyze and reanalyze as

Analyze Talble xxx compute statistics for table for all indexes

PS Sometimes histograms aint produce good plans.

Abhay.