If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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

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.