-
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
anandkl
-
Possible solution
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
-
Re: Possible solution
Originally posted by apunhiran
Hi,
TO tune the query, first of all you can use count(1) INSTEAD of count(*).
what the...?!?!
-
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 . . . ?
-
Total no of records from this query is 54712
and the table has 14.5M records
regards
anandkl
anandkl
-
Re: Possible solution
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.
-
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
Code:
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
Last edited by DaPi; 12-18-2003 at 07:26 AM.
-
-
That must be a typing error - how do you get more than 54,712?
-
how about changing
NUM_TRANSACTIONS > 0
NOT NUM_TRANSACTIONS = 0
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|