Half the query using index and remaining not. [message #402544] |
Mon, 11 May 2009 07:38  |
dhanamukesh
Messages: 51 Registered: January 2009
|
Member |
|
|
in the query with UNION ,the first half of the query using the index and the query after union is not taking the index.
I have created the index on EXPRESS_PAY_ORDER (RCV_COLUMBUS_MAINOFFICE, COL_AGENT_ID).So that the first part of the query 's cost and cpu cost reduced.But The second part is not using the index.
Please find my attached query below.I want to know what column should i include in the above index to make use of second query also.
The query will be
SELECT SUM(CNT)
FROM (SELECT COUNT(1) CNT
FROM EXPRESS_PAY_ORDER EPO
WHERE EPO.RCV_COLUMBUS_MAINOFFICE = :B17
AND NVL(EPO.SND_AMT, :B16) BETWEEN :B16 AND :B15
AND DECODE(:B14,
:B13,
EPO.RCV_COLUMBUS_MAINOFFICE,
:B12,
EPO.COL_AGENT_ID) = :B11
AND DECODE(:B1, :B10, EPO.RCV_DATE, EPO.ORDER_DATE) BETWEEN :B9 AND :B8
AND ((:B6 IS NULL) OR
(:B6 = :B7 AND EPO.TRK_EFF_DATE IS NOT NULL) OR
(:B6 = :B5 AND EPO.TRK_EFF_DATE IS NULL))
AND (:B4 IS NULL OR EPO.SND_CUST_FIRST_NAME LIKE UPPER(:B4))
AND (:B3 IS NULL OR EPO.SND_CUST_LAST_NAME LIKE UPPER(:B3))
AND (:B2 IS NULL OR EPO.SND_CUST_PHONE_NBR LIKE :B2)
AND (:B1 IS NULL OR EPO.STATUS = UPPER(:B1))
UNION ALL
SELECT COUNT(1) CNT
FROM EXPRESS_PAY_ORDER EPO, AGENT AGT
WHERE EPO.RCV_COLUMBUS_MAINOFFICE = :B17
AND AGT.PARENTAGENT_ID = :B11
AND AGT.AGENT_ID = EPO.COL_AGENT_ID
AND NVL(EPO.SND_AMT, :B16) BETWEEN :B16 AND :B15
AND :B14 = :B18
AND DECODE(:B1, :B10, EPO.RCV_DATE, EPO.ORDER_DATE) BETWEEN :B9 AND :B8
AND ((:B6 IS NULL) OR
(:B6 = :B7 AND EPO.TRK_EFF_DATE IS NOT NULL) OR
(:B6 = :B5 AND EPO.TRK_EFF_DATE IS NULL))
AND (:B4 IS NULL OR EPO.SND_CUST_FIRST_NAME LIKE UPPER(:B4))
AND (:B3 IS NULL OR EPO.SND_CUST_LAST_NAME LIKE UPPER(:B3))
AND (:B2 IS NULL OR EPO.SND_CUST_PHONE_NBR LIKE :B2)
AND (:B1 IS NULL OR EPO.STATUS = UPPER(:B1)))
Regards,
Dhanalakshmi.P
|
|
|
Re: Half the query using index and remaining not. [message #402547 is a reply to message #402544] |
Mon, 11 May 2009 07:50   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
dhanamukesh wrote on Mon, 11 May 2009 08:38 |
AND DECODE(:B1, :B10, EPO.RCV_DATE, EPO.ORDER_DATE) BETWEEN :B9 AND :B8
|
Your code is not formatting, thus giving me a headache, but this is not how you compare DATEs in Oracle. You need to use the TO_DATE function.
And a typical first response would be to show us the explain plan.
[Updated on: Mon, 11 May 2009 07:52] Report message to a moderator
|
|
|
|
|
|
|