SW vendor insists on RBO

Our database will finally migrate from 7.3.4 to 8.1.7, it's the highest version our SW vendor supports, just as 7.3.4 has been the highest version for ages. I've always been told they tested their application against the 7.3.4 CBO and had bad experiences with respect to performance. I didn't really dispute this because I know the 7.3.4 CBO had its less fruitful days.

Now, migrating to 8.1.7 I think time has come to use the CBO and so, after migrating the db (actually I exp-ed and imp-ed it) I generated stats for all the tables, en we started running.

We ran across a problem I never would have imagined: CBO provoked an ORA-1722 (invalid number) error. I'll explain.

We have this rather small table (the smallness is crucial) which is used to store (and query of course) domains. There is a column for low values, one for high values. The table is queried by supplying the domain name (which is part of an index) and supplying the low value field. Since the numeric low and high values are stored in a VARCHAR2 the to_number function is being used to do the conversion. Now were getting close.

Running RBO the index on the table is being used, and we only read those rows that really contain numeric values in the VARCHAR2 column: no problem. Than we run statistics and CBO decides using the index is too expensive: we'll do an FTS (small table, remember). ALL rows are read, and every low and high value column is handled by to_number. As soon as it hits a non-numeric value it errors out with an ORA-1722.

Now we're getiing to my point. I really intended to use the CBO, even when our SW vendor advised against it, I'm pretty sure 8.1.7 CBO is usually better than RBO. But now I have my doubts. What other surprises can I expect? I never would have thought I'd encounter software problems because of using another optimizer. Any ideas, experiences, advices? TIA.

Peter Received on Fri Oct 18 2002 - 17:43:04 CDT