I want to tune the below query for performance issue can u please help me the query is SELECT DISTINCTA.BUSINESS_UNIT,A.CUST_ID,A.ASOF_DTFROM PS_ITEM_ACTIVITY A WHERE A.BUSINESS_UNIT = '1100G'AND A.ITEM LIKE 'OA%' AND A.PO_LINE = 0AND A.ENTRY_REASON 'CLEAR'AND A.ASOF_DT > '01-JAN-1900'AND A.USER1 = ' 'UNIONSELECT DISTINCTA.BUSINESS_UNIT,A.CUST_ID,A.ASOF_DTFROM PS_PENDING_ITEM A WHERE A.BUSINESS_UNIT = '1100G'AND A.ITEM LIKE 'OA%' AND A.PO_LINE = 0AND A.ENTRY_REASON 'CLEAR'AND A.ASOF_DT > '01-JAN-1900'AND A.USER1 = ' 'AND A.POSTED_FLAG = 'N'AND ERROR_FLAG = ' '

Chiranjeevi

• Oct 5th, 2006

 

As the query uses UNION, I suggest you not to use DISTINCT again as UNION eliminates repitions.

snehal

• Oct 17th, 2006

 

hi Chiranjeevi,

thanks for ur help but i can't eliminate the use of distinct as i want to select distinct rows from each the tables with particular combination which i select and after selecting the distinct combination from both tables i have to union them so please suggest some other solution if u can thanks once again.

Sushant

• Oct 17th, 2006

 

instead of using distinct use group by....

its faster..

group by is pre condition where as distinct is post condition so groupy by is expctd to b much faster and the diffrence is quite visible if u hav lots of data

Tejinder

• Nov 3rd, 2006

 

create a view of the above query without using distinct clause and then execute query by using view with distinct clause and group by function.

Radhika

• Feb 22nd, 2007

 

If you are sure that each query will fetch distinct set of records i.e, records returned by query 1 will not be available in query 2 then Union All can be used.Union tries to eliminate the duplicate records which results in a performance overhead. Union all will return all records.

nagarajbng

 Profile Answers by nagarajbng 

• Oct 3rd, 2009

 

If you are using the UNION keyword then there is no need of using the distinct keyword in your query or else you can use UNION ALL keyword

or

Below I am sending the modified query

In your query which column will filter more then that column you have to place in the last position less filter before that last column and which one will filter least that column you have to place in the first position in the where clause.

SELECT DISTINCT
A.BUSINESS_UNIT,
A.CUST_ID,
A.ASOF_DT
FROM PS_ITEM_ACTIVITY A
WHERE A.PO_LINE = 0
AND A.BUSINESS_UNIT = '1100G'
AND A.ENTRY_REASON <> 'CLEAR'
AND A.ASOF_DT > '01-JAN-1900'
AND A.USER1 = ' '
AND A.ITEM LIKE 'OA%'

UNION

SELECT DISTINCT
A.BUSINESS_UNIT,
A.CUST_ID,
A.ASOF_DT
FROM PS_PENDING_ITEM A
WHERE A.PO_LINE = 0
AND A.BUSINESS_UNIT = '1100G'
AND A.ENTRY_REASON <> 'CLEAR'
AND A.ASOF_DT > '01-JAN-1900'
AND A.USER1 = ' '
AND A.POSTED_FLAG = 'N'
AND ERROR_FLAG = ' '
AND A.ITEM LIKE 'OA%'

Thanks & Regards,
NTR

kvsubramanian

 Profile Answers by kvsubramanian 

• Feb 2nd, 2010

 

Try to Replace "AND A.ITEM like 'OA%'" with

" AND substr(A.ITEM,1,2) = 'OA' ".