CODEWITHSUNDEEP
sqloracle-database
Mike
Mike

Reputation: 25

Work Around for SQL Query 'NOT IN' that takes forever?

I am trying to run a query on an Oracle 10g DB to try and view 2 groups of transactions. I want to view basically anyone who has a transaction this year (2014) that also had a transaction in the previous 5 years. I then want to run a query for anyone who has a transaction this year (2014) that hasn't ordered from us in the last 5 years. I assumed I could do this with the 'IN' and 'NOT IN' features. The 'IN' query runs fine but the 'NOT IN' never completes. DB is fairly large which is probably why. Would love any suggestions from the experts!

*Notes, [TEXT] is a description of our Customer's Company name, sometimes the accounting department didn't tie this to our customer ID which left NULL values, so using TEXT as my primary grouping seemed to work although the name is obscure. CODE_D is a product line just to bring context to the name.

Below is my code:

SELECT CODE_D, sum(coalesce(credit_amount, 0) - coalesce(debet_amount,0)) as TOTAL 
FROM 
gen_led_voucher_row_tab
WHERE ACCOUNTING_YEAR like '2014'
and TEXT NOT IN
    (select TEXT
    from gen_led_voucher_row_tab
    and voucher_date >= '01-JUN-09'
    and voucher_date < '01-JUN-14'
    and (credit_amount > '1' or debet_amount > '1')
    )
GROUP BY CODE_D
ORDER BY TOTAL DESC

Upvotes: 0

Views: 439

Answers (2)

Barmar
Barmar

Reputation: 781833

Try using a LEFT JOIN instead of NOT IN:

SELECT t1.CODE_D, sum(coalesce(t1.credit_amount, 0) - coalesce(t1.debet_amount,0)) as TOTAL
FROM gen_led_voucher_row_tab AS t1
LEFT JOIN gen_led_voucher_row_tab AS t2
ON t1.TEXT = t2.TEXT
    AND t2.voucher_date >= '01-JUN-09'
    AND t2.voucher_date < '01-JUN-14'
    AND (credit_amount > '1' or debet_amount > '1')
WHERE t2.TEXT IS NULL
    AND t1.ACCOUNTING_YEAR = '2014'
GROUP BY CODE_D
ORDER BY TOTAL DESC

ALso, make sure you have an index on the TEXT column.

Upvotes: 1

Siyual
Siyual

Reputation: 16917

You can increase your performance by changing the Not In clause to a Where Not Exists like as follows:

Where Not Exists
(
    Select  1
    From    gen_led_voucher_row_tab b
    Where   voucher_date >= '01-JUN-09'
    and     voucher_date < '01-JUN-14'
    and     (credit_amount > '1' or debet_amount > '1')
    And     a.Text = b.Text
)

You'll need to alias the first table as well to a for this to work. Essentially, you're pulling back a ton of data to just discard it. Exists invokes a Semi Join which does not pull back any data at all, so you should see significant improvement.

Edit

Your query, as of the current update to the question should be this:

SELECT  CODE_D, 
        sum(coalesce(credit_amount, 0) - coalesce(debet_amount,0)) as TOTAL 
FROM    gen_led_voucher_row_tab a
Where   ACCOUNTING_YEAR like '2014'
And Not Exists
(
    Select  1
    From    gen_led_voucher_row_tab b
    Where   voucher_date >= '01-JUN-09'
    and     voucher_date < '01-JUN-14'
    and     (credit_amount > '1' or debet_amount > '1')
    And     a.Text = b.Text
)
GROUP BY CODE_D
ORDER BY TOTAL DESC

Upvotes: 0

Related Questions