Joined: 02 Nov 2004 Posts: 18 Location: Noida, India
1. i have two tables TABLEA and TABLEB and joining them with inner join.
2. TABLEA and TABLEB continas 250000 and 4750000 records respectively.
3. Query i want to execute is :-
Select count(*)
from TABLEA A, TABLEB B
WHERE
(A.INVOICE_DATE >= '2004-09-01' AND
A.INVOICE_DATE <= '2004-10-31') AND
A.CUST_TYPE_CODE IN ('01', '02') AND
(A.SO_ORDR_TYPE_CODE NOT IN ('5', '6', '0') AND
A.SHPMENT_TYPE_CODE <> 'D') AND
(A.SALES_OFFICE = B.SALES_OFFICE AND
A.SALES_ORDR_NO = B.SALES_ORDR_NO AND
A.INVOICE_NO = B.INVOICE_NO);
4. The following query with just the needed Join condition only is taking a real hard time.
Select count(*)
from TABLEA A, TABLEB B
WHERE
(A.SALES_OFFICE = B.SALES_OFFICE AND
A.SALES_ORDR_NO = B.SALES_ORDR_NO AND
A.INVOICE_NO = B.INVOICE_NO);
5. If i include the rest of the condition(s) i.e. like step 3, it is all gone in sleep state.
6. Can anyone help me in optimizing the query.
7. it will be appreciated if u could please mail it on my personal id also which is there in my signature. thanks
Joined: 06 Dec 2004 Posts: 211 Location: Keane Inc., Minneapolis USA.
Hi Bhardhwaj,
I dont know much but i will give you some suggestions for optimization. If it works well thanks GOD. ok.
1. Index The Tables using SALES_OFFICE,SALES_ORDER_NO and INVOICE_NO
2. Change Join Condition as ...
....WHERE (A.SALES_OFFICE,A.SALES_ORDER_NO,A.INVOICE_NO) IN (B.SALES_OFFICE,B.SALES_ORDER_NO,B.INVOICE_NO)
3. Other Condition YOU can write as.......
(i) A.INVOICE_DATE BETWEEN TO_DATE('2004-09-01') AND
TO_DATE('2004-10-31')
(ii) Avoid using NOT IN change it to possitive using IN if possible
(iii) Change "<>" to "IN" and give remaining values.
I am not sure but it will reduce the Stage 2 operations and improves performance.
.jpg)
