Solved

Oracle SQL: Row equates VS Math in a where clause

Posted on 2014-09-23
18
359 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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
 
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 37

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 37

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

786 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