We have just upgraded our database from 18.104.22.168.0 to 22.214.171.124.3 and we are now experencing problems with views which compare a number datatype to a varchar2 database, we have had to put a workaround in with to_char,
e.g. was 'where number = varchar2'
now 'where to_char(number)=varchar2'
these views are now poor on performane due to it not using the index. Does anyone know why this is now happening? and does anyone have a better workaround?
Could you use a function based index on to_char(number)?
If only one of the columns is indexed, number, then try
number=to_number(varchar2). This should use the index
I have deleted the analyze stats. from all schemas and the performance has improved. Obviously there are still some issues with the Cost Based Optimizer, even though I have put optimizer_index_cost_adj=10 and optimizer_index_caching=99 in the init.ora file to force CBO to use the indexes.
There may be some othere factors.
It has always been the case that when you rely on autoconversion (where number=varchar2), indexes were disabled (assuming the index was on number).
To use the index, it shoud be (where number=to_num(varchar2)).
We have also seen major performance differences between 7.3 and 8.1.7 in our systems. Basically, we have found we have to re-optimize much of our SQL and table organizations (statistical optimization seems to happen even if the mode is set to rule if one of the tables in the query is partioned) because of the difference in the optimizers and structures.
At the time we were told (by 'them') it was normal, if unpleasant. We have also been warned that we may face the same thing again when we upgrade to 9i. 'They' made changes to the optimizer again.
Joseph R.P. Maloney, CSP,CDP,CCP
'The answer is 42'
Click Here to Expand Forum to Full Width