tuning the SQL..Reduce the response time
Hi All,
I am using oracle 8.1.7,solaris 2.8.
I have this query which is taking 3 min for execution.
SELECT COUNT(*) FROM CART_STATS WHERE COMPANY_ID = 8170926 AND ( ((SHIPMENT_STATUS = 'N' or PAYMENT_CLEARED = 'N')
and ARCHIVE != 'D' and STATUS != 'D' and STATUS != 'X' and NUM_TRANSACTIONS > 0) OR ((((SHIPMENT_STATUS = 'Y'
and PAYMENT_CLEARED = 'Y' and STATUS != 'D') OR (STATUS = 'X')) and ARCHIVE != 'D')
and NUM_TRANSACTIONS > 0 and DATE_MODIFIED >= (sysdate - 60)) )
and the execution plan is
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=283 Card=1 Bytes=25)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CART_STATS' (Cost=283 Card=1
29 Bytes=3225)
3 2 INDEX (RANGE SCAN) OF 'CART_PS_IDX' (NON-UNIQUE) (Cost
=5 Card=129)
Its using the index which is built on the following columns(composite index)
CART_PS_IDX COMPANY_ID
CART_PS_IDX PAYMENT_CLEARED
CART_PS_IDX SHIPMENT_STATUS
CART_PS_IDX STATUS
CART_PS_IDX ARCHIVE
CART_PS_IDX CART_ID
No of distinct values in each of these columns are
SQL> select count(distinct company_id) from cart;
COUNT(DISTINCTCOMPANY_ID)
-------------------------
88342
SQL> select count(distinct PAYMENT_CLEARED) from cart;
COUNT(DISTINCTPAYMENT_CLEARED)
------------------------------
2
SQL> select count(distinct shipment_status) from cart;
COUNT(DISTINCTSHIPMENT_STATUS)
------------------------------
2
SQL> select count(distinct ARCHIVE) from cart;
COUNT(DISTINCTARCHIVE)
----------------------
3
SQL> select count(distinct status) from cart;
COUNT(DISTINCTSTATUS)
---------------------
6
How can i reduce the time take by this query, will generation of histograms help on this. Please advice.
regards
anandkl