How to tune the following sql in order that the performance improve 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 = ' '

ARUN KUMAR

• Oct 4th, 2006

 

In this qry we can create an index on the base conditions to improve the qry performance.

Vasanth Kumar

• Oct 6th, 2006

 

1) Creating index for tuning is one step.2) You can remove Union and replace it with Union ALL.Union ALL is much faster than Union.Since you already have distinct clause in your SQL query you can very well remove UNION and replace it with UNION ALL.3) Check whether it would be possible for you to combine this to one single query.4) Check the Query plan.The above are some of the tips to optimise the query.

zskhan

 Profile Answers by zskhan 

• Oct 12th, 2006

 

Remove DISTINCT from both queries.

Since UNION is being used, we are going to get distinct rows anyway and queries with distinct are quite expensive.

raji_4u

 Profile Answers by raji_4u 

• Sep 19th, 2008

 

Remove union and place union all

Make sure the where conditions is in order of
1. =
2. >
3. <>
4. like.

Make sure that the condition that returns least records comes first.