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