tuning the SQL..Reduce the response time

[anandkl]

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

[apunhiran]

Hi,
TO tune the query, first of all you can use count(1) INSTEAD of count(*). Secondly create an index on company_id and analyze the table.
I hope this should work. Such heavy composite indexes are usually useless and moreover cause contention than actually help. Try to giving hint /*+INDEX(table_name the index on company_id) */ and try running the query and see if the response time has reduced.
Thanks

[pando]

Originally posted by apunhiran
Hi,
TO tune the query, first of all you can use count(1) INSTEAD of count(*).

what the...?!?!

[DaPi]

Count(1) vs. Count(*) is a urban myth, and I don't really think the complex index will be a significant overhead.

The plan looks OK to me. So, how many rows are selected by:
SELECT COUNT(*) FROM CART_STATS WHERE COMPANY_ID = 8170926;

It could be that you are having to read a vast number of rows in order to do the rest of the logic . . . ?

[anandkl]

Total no of records from this query is 54712

and the table has 14.5M records

regards
anandkl

[engiri]

Originally posted by apunhiran
Hi,
TO tune the query, first of all you can use count(1) INSTEAD of count(*).

I read somewhere in Tom's guide
that count(*) and count(1) will work on same method.

[DaPi]

So, there are a lot of rows . . . all 54'712 have to be read from the table - that could be enough to explain 3 minutes if most are not in buffers (depends on your hardware - I make that 3ms/row).

(check my logic here) I think you can rewrite the query as

SELECT COUNT(*) 
FROM  CART_STATS 
WHERE COMPANY_ID = 8170926
and   ARCHIVE != 'D' 
and   NUM_TRANSACTIONS > 0
and   
(
 (LOTS OF STUFF)
 OR 
 (LOTS OF STUFF)
)

in which case an index on (COMPANY_ID, ARCHIVE, NUM_TRANSACTIONS) could be useful if these extra columns are selective.

So how many rows from:
SELECT COUNT(*)
FROM CART_STATS
WHERE COMPANY_ID = 8170926
and ARCHIVE != 'D'
and NUM_TRANSACTIONS > 0

[anandkl]

The count is

145591

[DaPi]

That must be a typing error - how do you get more than 54,712?

[onlysimon]

how about changing

NUM_TRANSACTIONS > 0

NOT NUM_TRANSACTIONS = 0