Comparing number with datatype doesn't work in 8.1.7.1.3
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Comparing number with datatype doesn't work in 8.1.7.1.3

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    We have just upgraded our database from 7.3.4.4.0 to 8.1.7.1.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?
    Thanks

  2. #2
    Join Date
    Jan 2002
    Posts
    57
    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
    on number.


    --
    Paul

  3. #3
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    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.

  4. #4
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    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.

    Oh, well.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width