Solved

Oracle SQL: Row equates VS Math in a where clause

Posted on 2014-09-23
18
364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 4
  • +1
18 Comments
 
LVL 77

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 77

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
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 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40340153
LOL - of course :)

What is the type of those fields?
0
 
LVL 77

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 38

Assisted Solution

by:Gerwin Jansen, EE MVE
Gerwin Jansen, EE MVE 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 77

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 38

Expert Comment

by:Gerwin Jansen, EE MVE
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 77

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 77

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 38

Expert Comment

by:Gerwin Jansen, EE MVE
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 77

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

734 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