Why Error Without Analyzing
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Why Error Without Analyzing

  1. #1
    Join Date
    Oct 2003
    Posts
    312

    Why Error Without Analyzing

    I have the query below :

    SELECT MIN(to_number(a.ADDRESS)) + 1
    FROM BUSINESS a
    WHERE upper(a.ADDRESS) = lower(a.ADDRESS)
    AND to_number(a.ADDRESS) >= 11511
    AND (to_number(a.ADDRESS)+1) NOT IN
    (SELECT to_number(b.ADDRESS)
    FROM BUSINESS b
    WHERE upper(b.ADDRESS) = lower(b.ADDRESS) )


    it failed at first so I analyze the table and it run ok. why is it failed in the first place without analyzing the table????

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    well what was the error

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    wtf is this?
    Code:
    SELECT to_number(b.ADDRESS)
    FROM BUSINESS b
    WHERE upper(b.ADDRESS) = lower(b.ADDRESS)
    Is "address" a number or what?

    Analyzing the table enabled the use of the cost-based optimizer instead of the RBO, and i expect that this "exposed" an error in converting address to a number that the RBO didn't see, because it scanned index instead of table, or something.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Oct 2003
    Posts
    312
    Address is VArchar2

    your explaination make sense

    thanks

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by learning_bee
    your explaination make sense
    But your query doesn't....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I'm guessing that "upper(b.ADDRESS) = lower(b.ADDRESS)" is a test to see if the column is numeric. Except it's not a very good one.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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