Solved

Oracle SQL: Row equates VS Math in a where clause

Posted on 2014-09-23
18
362 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

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!

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

740 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