-
Join with a + 0
Hello everyone,
I'm trying to understand a oracle sql query which was generated from an application and got captured through Toad.
select a.cust, a.date. a.qnty
from orders a
where a.numb + 0 = 1002
a.numb is an integer. I'm trying to understand what this + 0 means. Note there are no braces. The query takes long time to execute with the presence of + 0. If I remove + 0 (i.e, a.numb = 1002), it executes much faster.
Appreciate if anyone could shed some light on what is going on.
Oracle version is 7.3
Thanks
CKR
-
A zillion years back in time I remember seeing something like that in a paleolithic Bull machine, I think it was a program written in Basic and the programmer's “reason” was something in the line of “making sure precision is right”.
May be the same guy got a job writting pl/sql code.
By the way, I have good reasons to believe that at that time the guy might be right, it is a fact that in the same machine you had to do a=round(2*2) instead of a=2*2 if you wanted to get a=4 ... rather than getting a=3.99999 with sixteen 9's after the decimal point.
You know what? kinda I miss that time
Last edited by PAVB; 10-08-2008 at 11:39 AM.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
I would assume the the "+ 0" tells Oracle to do a full table scan. There are people who believe that a full table scan is always cheaper than an index lookup. So someone might force the full table scan. It would be the same thing if someone were to do an upper() or lower() or to_char() on the left side of an equation. If you have something other than just a column name on the left side Oracle might decide that a full table scan is the only way to go. The exception is that if you had a newer version of Oracle and a function based index.
It should be safe to remove the "+ 0", but test anyway.
-
Originally Posted by PAVB
May be the same guy got a job writting pl/sql code.
As i sometimes write PL/SQL code, I feel that I should take exception to your comments.
-
Originally Posted by gandolf989
As i sometimes write PL/SQL code, I feel that I should take exception to your comments.
Please don't get me wrong, I ddin't wanted to insult pl/sql coders at all.
I was just wondering about the chances the same guy ended up writting that piece of code
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Dinosour
I also remember when in some programming languages which did not have support for "accounting" type numbers but rather integers and float you did have to add the +0 to remove the "precision" issue.
Past reminiscing...
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|