Solved

Oracle SQL: Row equates VS Math in a where clause

Posted on 2014-09-23
18
351 Views
Last Modified: 2014-09-24
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?
0
Comment
Question by:GNOVAK
  • 7
  • 5
  • 4
  • +1
18 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 200 total points
ID: 40339979
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.
0
 

Author Comment

by:GNOVAK
ID: 40340070
It basically tells me that the filter is taking the time.
The question is why that is...anyone?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40340077
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.
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 40340086
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.
0
 

Author Comment

by:GNOVAK
ID: 40340145
Since field5= field6 is the same as field5-field6=0
Doesnt it have to do 30 million either way?
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 40340153
LOL - of course :)

What is the type of those fields?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40340155
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?
0
 
LVL 37

Assisted Solution

by:Gerwin Jansen
Gerwin Jansen earned 100 total points
ID: 40340158
Could be that the first one is doing a full table scan where the 2nd one isn't.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40340161
>>Could be that the first one is doing a full table scan where the 2nd one isn't.

The plans would have shown that.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 40340182
Last thougt: any indexes on fields5 and 6?
0
 

Author Comment

by:GNOVAK
ID: 40340262
no indexes .
The crash was much earlier. Session was closed and restarted.
Both fields are numeric.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40340323
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.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 40340353
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40340392
Actually that makes a LOT more sense about the error.  Another HUGE query might also account for the time difference.
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 40340405
Can you re-run both queries to see whether the difference is still there?
0
 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 200 total points
ID: 40341057
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.
0
 

Author Closing Comment

by:GNOVAK
ID: 40341388
Thanks for the ideas.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40341403
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.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now