Oracle IN Clause

[getjbb]

Hello

When I run the following select statement it takes a long time to return results:

SELECT START.DEPOSIT_DTE,
START.ACCT,
START.NAME,
START.EXPIRE_DTE,
START.PUB,
START.RTE,
START.TO_CC,
START.SWITCH_DTE,
START.START_SWITCH_DTE,
START.AMT_PAID,
START.AMT_CHECKED,
START.ERROR_FOUND,
START.ERROR_MSG,
START.OFFSET_FLAG,
TRANS.CODE,
TRANS.APPLIED_DTE,
TRANS.ENTRY_DTE
FROM START, TRANS
WHERE START.ACCT = TRANS.ACCT
AND (START.DEPOSIT_DTE BETWEEN variable_date_from and variable_date_to)
AND (TRANS.APPLIED_DTE BETWEEN variable_applied_from and variable_applied_to)
AND (variable_include_no_errors='Y' OR START.error_found='Y')
AND TRANS.CODE IN ('AV','AG','AP','AW','MT','PO','PZ','RC','TF','VI')
AND START.PUB = variable_pub
AND ( (TRANS.BATCH_NUM IS NULL) OR (TRANS.BATCH_NUM IS NOT NULL) )
AND TRANS.AMOUNT = -(START.AMT_PAID)
AND START.AMT_PAID = ABS(TRANS.AMOUNT)
GROUP BY START.DEPOSIT_DTE,
START.ACCT,
START.NAME,
START.EXPIRE_DTE,
START.PUB,
START.RTE,
START.TO_CC,
START.SWITCH_DTE,
START.START_SWITCH_DTE,
START.AMT_PAID,
START.AMT_CHECKED,
START.ERROR_FOUND,
START.ERROR_MSG,
START.OFFSET_FLAG,
TRANS.CODE,
TRANS.APPLIED_DTE,
TRANS.ENTRY_DTE

I am thinking that it is the IN clause in the where statement that is causing the slowness.

Would anyone have any idea how to make this more efficient?

Thanks.

getjbb

 

[Thargy]

It's impossible to say, anything could be the cause of the problem. You need to provide more information, such as the explain plan for the query, so that we can see what it's doing.

Is this query bringing back rows from a table with tens of millions of records, or a few thousand? I ask, because if its the former, then a few minutes may be a perfectly reasonable response time.

You might start by replacing

FROM  START, TRANS
WHERE START.ACCT  = TRANS.ACCT

with

FROM  START INNER JOIN TRANS USING (ACCT)

and see what happens. Occsionally I have found that to help in the past. The other thing is, why do you need all that grouping? Is the query really doing what you need it to?

Regards

T

 

[carp]

Out of curiosity, what is the purpose of this line in your WHERE clause?

( (TRANS.BATCH_NUM IS NULL) OR (TRANS.BATCH_NUM IS NOT NULL) )

 

[SantaMufasa]

<off topic>
Carp ! Great to see you back...where in the world have you been? We've missed you here. Please don't be scarce.
</off topic>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[carp]

Well, with the economy being the way it is, I've had to make myself look invaluable around here. Now that I've survived my 16th round of layoffs, I've got a little more time to pursue more pleasurable pursuits. But I digress - back to the issues at hand!

 

[getjbb]

Hello,

I replaced:

FROM START, TRANS
WHERE START.ACCT = TRANS.ACCT

with FROM START INNER JOIN TRANS USING (ACCT).

And I removed ( (TRANS.BATCH_NUM IS NULL) OR (TRANS.BATCH_NUM IS NOT NULL) ) which I do not need.

The results are coming back much faster.

Thank you all for your input.

getjbb