tuning the SQL..Reduce the response time

Hi Dapi,
Since i have other condition to be matched in the QUERY, thats why i got 54k in the original query and 1.4 Lakhs in the second query.

Is there an alternative for 'OR' condition so that the query can perform fast


regards
anandkl

try union all

Composite Index will not help for this query.
Just create an index on company_id only. And use hint within the SELECT.

Or Try this:

SELECT /*+ full(CS) parallel(CS,8) */
COUNT(*) FROM CART_STATS CS
Where ....................
....................

Tamil

Hi Tamil,

I don't agree with your comments on the index.

The index with COMPANY_ID in first position is already being used - I would be amazed if using a new index on only that column would have a noticable effect. I'm convinced that it is the retrieval of 54K or 140K rows from the table is taking the time - I would hope to reduce that by improving the selectivity of the the index range scan.

pando's idea looks good, providing the conditions that are OR'ed together really are mutually exclusive - union all keeps duplicates (but anandkl seems to have hidden part of the logic from us). That will divide the query into two pieces which can be tuned individually, each would probably need its own composite index.


. . . now you may be right that a parallel FTS would be better, a good fraction (10% ?) of the table is currently being retrieved - we don't know what fraction of the blocks that is, it could be most of them.

Dapi,
I think UNION ALL may increase Logical IOs.

Anandkl,

Do you have any idea how many blocks are used by the table?

set autot trace statis
run your query and the hint I suggested and post here the statistics.


Tamil

A couple of thoughts while driving home last night:

- if UNION ALL is not applicable then, providing the index selectivity can be improved, UNION would be OK (hopefully only a few thousand PK's to sort).

- if this is the worst case of the query - i.e. if for other values of COMPANY_ID it performs OK (fewer rows to recover) - then FTS is probabaly not the answer. You could reduce the worst case from 3 mins to (say) 30 secs, but increase all the 1 sec response times to 30 secs as well.


Tamil, yes, by itself UNION ALL will only make it worse. But it gives the opportunity to tune the sub-queries independantly.

Code:

---


Select
(
SELECT
  COUNT(*)
FROM
  CART_STATS
WHERE
  COMPANY_ID = 8170926 AND
  SHIPMENT_STATUS = 'N' AND
  STATUS IN ('Rest Of The Values Apart From D & X') AND
  Archive IN ('Rest Of The Values Apart From D') AND
  NUM_TRANSACTIONS > 0
)
+
(
SELECT
  COUNT(*)
FROM
  CART_STATS
WHERE
  COMPANY_ID = 8170926 AND
  PAYMENT_CLEARED = 'N' AND
  STATUS IN ('Rest Of The Values Apart From D & X') AND
  Archive IN ('Rest Of The Values Apart From D') AND
  NUM_TRANSACTIONS > 0
)
+
(
SELECT
  COUNT(*)
FROM
  CART_STATS
WHERE
  COMPANY_ID = 8170926 AND
  SHIPMENT_STATUS = 'Y' AND
  PAYMENT_CLEARED = 'Y' AND
  STATUS IN ('Rest Of The Values Apart From D') AND
  Archive IN ('Rest Of The Values Apart From D') AND
  NUM_TRANSACTIONS > 0 and
  DATE_MODIFIED >= (sysdate - 60)
)
+
(
SELECT
  COUNT(*)
FROM
  CART_STATS
WHERE
  COMPANY_ID = 8170926 AND
  STATUS = 'X' AND
  Archive IN ('Rest Of The Values Apart From D') AND
  NUM_TRANSACTIONS > 0 and
  DATE_MODIFIED >= (sysdate - 60)
)
From
DUAL
;

---

How about this one?

Abhay.

Or Even

Code:

---


 SELECT
  COUNT(*)
 FROM
  CART_STATS
 WHERE
  COMPANY_ID = 8170926 AND
  Archive IN ('Rest Of The Values Apart From D') AND
  NUM_TRANSACTIONS > 0 AND
  EXISTS
  (
  Select
    NULL
  from
    Dual
  Where 
    SHIPMENT_STATUS = 'N' AND
    STATUS IN ('Rest Of The Values Apart From D & X')
  Union All
  Select
    NULL
  from
    Dual
  Where 
    COMPANY_ID = 8170926 AND
    PAYMENT_CLEARED = 'N' AND
    STATUS IN ('Rest Of The Values Apart From D & X')
  Union All
  Select
    NULL
  from
    Dual
  Where 
    SHIPMENT_STATUS = 'Y' AND
    PAYMENT_CLEARED = 'Y' AND
    STATUS IN ('Rest Of The Values Apart From D') AND
    DATE_MODIFIED >= (sysdate - 60)
  Union All
  Select
    NULL
  from
    Dual
  Where 
    STATUS = 'X' AND
    DATE_MODIFIED >= (sysdate - 60)
  )
;

---

Hi Abhay,

That's a move in the right direction*. Your 2nd and 3rd queries can take better advantage of the existing index - HOWEVER the 1st and 4th won't. By itself this change could more than double the execution time! It will need other indexes.

I don't see much point in re-wrting the query in detail, since we aren't quite sure what it actually is!

Quote:

---

Since i have other condition to be matched in the QUERY, thats why i got 54k in the original query and 1.4 Lakhs in the second query.

---

* this refers to the COUNT(*) + COUNT(*) . . . . version.

Quote:

---

Originally posted by DaPi
Hi Abhay,

That's a move in the right direction*. Your 2nd and 3rd queries can take better advantage of the existing index - HOWEVER the 1st and 4th won't. By itself this change could more than double the execution time! It will need other indexes.

---

I dont see any reason why 1st and 4th should not take better advantage of existing composite index. :confused:

Well what ever you said will be correct if COMPANY_ID is not much duplicated over differect combinations of Status, Archive, Shipment_Status and so...But its not the case here (just guessing).

Abhay.