Optimizer Cost Model vs Implicit Sorting and Datatype Conversion

I recently stumbled on an issue for the first time, while migrating an application from 9i to 10g.

When migrating an application to 10g, one thing that needs to be considered is the optimizer cost model change.

In Oracle 9i, the cost model parameter (_optimizer_cost_model) is set to IO by default, while in 10G it is set to CHOOSE, seems to default to CPU in all cases however.

It doesn’t seem like much, but the implications are serious.

The IO cost model provides implicit data conversion and sorting.

In the case of my application, the developers had relied on implicit data conversion and data sorting when designing the reporting module.

In short, they had not included any ORDER BY clauses and were comparing CHAR’s with NUMBER’s.

 

Obviously when there were only 1 or 2 predicates in the WHERE clause, it didn’t make a difference wether sorting was implicit or not.

 

So after a migration, if you see strange sorting order and the ORA-01722: invalid number error, check your code.

A workaround is to set the  _optimizer_cost_model value to IO.

 

Pierre