Home » SQL & PL/SQL » SQL & PL/SQL » Half the query using index and remaining not. (Oracle 10G /Linux)
Half the query using index and remaining not. [message #402544] Mon, 11 May 2009 07:38 Go to next message
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 Go to previous messageGo to next message
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

Re: Half the query using index and remaining not. [message #402555 is a reply to message #402544] Mon, 11 May 2009 08:16 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
First query deals with only one table EXPRESS_PAY_ORDER.
Second one with EXPRESS_PAY_ORDER and AGENT.
How can you expect both should use the same index?
Second query's leading table may be AGENT....

By
Vamsi
Re: Half the query using index and remaining not. [message #402558 is a reply to message #402555] Mon, 11 May 2009 08:22 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
Agent is MatWview.
Re: Half the query using index and remaining not. [message #402572 is a reply to message #402544] Mon, 11 May 2009 09:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 29 April 2009 13:17
From your previous post:
BlackSwan wrote on Fri, 27 March 2009 07:00
Did you read & follow the bouncing RED ball advice as stated in URL below?
http://www.orafaq.com/forum/f/1/136107/
Did you read & follow Posting Guidelines as contained in URL below?
http://www.orafaq.com/forum/t/88153/0/

Another one:
gentlebabu wrote on Thu, 26 February 2009 14:26
Please post your explain plan stat. report.

SQL> set autotrace on explain statistics
SQL>

Execute your sql query.

Another one:
Michel Cadot wrote on Wed, 18 February 2009 11:20
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel

And so on.


Re: Half the query using index and remaining not. [message #402573 is a reply to message #402558] Mon, 11 May 2009 09:47 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
dhanamukesh wrote on Mon, 11 May 2009 06:22
Agent is MatWview.


& how were we to know this from what you initially posted.
& you decided we do not need to know anything about its structure

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Previous Topic: SQL Query
Next Topic: How does Java fit into PL/SQL-Oracle?
Goto Forum:
  


Current Time: Thu Feb 06 20:47:30 CST 2025