Oracle SQL: Row equates VS Math in a where clause

I had a query that looked like this:
Select
sum(field1)
sum(field2)/sum(field3)
from
XXX
inner join YYY on [something]....
where Field5 = field6

This was run up against 30 million records and took 24 minutes before it crashed using up temporary file space.

I then rewrote it as :
Select
sum(field1)
sum(field2)/sum(field3)
from
XXX
inner join YYY on [something]....
where Field5 -field6 = 0

It ran in 4 min and gave results.

Does anyone understand why the equate in the where clause causes excessive time/resources?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
check the execution plans between them.

You can do this without actually executing the query:

explain plan for
sum(field1)
sum(field2)/sum(field3)
from
XXX
inner join YYY on [something]....
where Field5 = field6;

Then to display the plan:
select * from table(dbms_xplan.display);

then do the same for the second query.

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
It basically tells me that the filter is taking the time.
The question is why that is...anyone?
Commented:
No idea other than a guess.

Math comparisons to a literal might just be faster then comparing the two values?

Hopefully another Expert that is better in SQL nuts and bolts will have some insight.
What is the ratio of the amount of records for which field5=field6 compared to the amount of records for which field5-field6=0? I'm thinking this is a >6 to 1 ratio. Less records would mean you get the result faster.
Author Commented:
Since field5= field6 is the same as field5-field6=0
Doesnt it have to do 30 million either way?
LOL - of course :)

What is the type of those fields?
Commented:
I wonder if caching has anything to do with the two.  Maybe it cached enough from the first one before it crashed to take advantage of that work for the second one?
Could be that the first one is doing a full table scan where the 2nd one isn't.
Commented:
>>Could be that the first one is doing a full table scan where the 2nd one isn't.

The plans would have shown that.
Last thougt: any indexes on fields5 and 6?
Author Commented:
no indexes .
The crash was much earlier. Session was closed and restarted.
Both fields are numeric.
Commented:
Just because the session closed does not mean the database blocks and/or result cache has been flushed.

Granted that is a long shot guess but could explain it.
Oracle dbaCommented:
someone else was running a sort query and was occupying temp ?
it filled up while the queries were running simultaneously ... 2nd time you were the only one

the someone else could be multiple others
Commented:
Actually that makes a LOT more sense about the error.  Another HUGE query might also account for the time difference.
Can you re-run both queries to see whether the difference is still there?
Oracle dbaCommented:
sometimes a simple explanation can be a best guess    :)

also, check if the temp tablespace has tempfiles with autoextend property active
if they have grown to their maximum size, switch the autoextend off
i've seen this similar problem occuring more frequently when the autoextend is on for temp
somehow the db thinks it doesn't have to cleanup temp in that case.
Author Commented:
Thanks for the ideas.
Commented:
Given your other question on the TEMP tablespace, I believe Geert might be on to something.  You had a small size for the NEXT size on the datafile you added.

The time difference could easily be because of the TEMP file auto extending.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.