Join with a + 0
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Join with a + 0

  1. #1
    Join Date
    Oct 2008
    Posts
    1

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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.
    this space intentionally left blank

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    Quote 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.
    this space intentionally left blank

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote 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.

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,458

    Talking 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
  •  



Click Here to Expand Forum to Full Width