Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Oracle SQL: Row equates VS Math in a where clause

Posted on 2014-09-23
Medium Priority
384 Views
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
Question by:GNOVAK
• 7
• 5
• 4
• +1

LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 600 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

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

LVL 78

Expert Comment

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 38

Expert Comment

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

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

ID: 40340153
LOL - of course :)

What is the type of those fields?
0

LVL 78

Expert Comment

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

Gerwin Jansen, EE MVE earned 300 total points
ID: 40340158
Could be that the first one is doing a full table scan where the 2nd one isn't.
0

LVL 78

Expert Comment

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

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

Author Comment

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

LVL 78

Expert Comment

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 38

Expert Comment

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 78

Expert Comment

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

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

LVL 38

Assisted Solution

Geert Gruwez earned 600 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

ID: 40341388
Thanks for the ideas.
0

LVL 78

Expert Comment

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

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.
###### Suggested Courses
Course of the Month11 days, 20 hours left to enroll