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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s