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????

well what was the error

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.

Address is VArchar2

your explaination make sense

thanks

Quote:

---

Originally posted by learning_bee
your explaination make sense

---

But your query doesn't.... :D

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.