I have NLS_LANG=AMERICAN_AMERICA.AL32UTF8 as recommended in DBD::Oracle
when using utf8 and I need to as my data is utf8 in Perl.
Grossly simplified my code does:
o select integer_primary_key_field from table
o prepare(select from another_table where field = ?)
o execute($inter_primary_key_value_retrieved_from_select)
This query is vastly more complex than this really
Even though the field retrieved from the first table is an integer when
I look at it, Perl has utf8 flag set. When these utf8 encoded integers
are then passed into the execute for a select on another table the
execute takes 0.7s. Now that may not sound a lot to you but this query
gets runs a lot. If I downgrade the integer parameter with
utf8::downgrade before passing it to execute the execute takes 0.01s.
When I look at the QEP the filter contains a TO_NUMBER(:p1) and I think
Oracle has decided it cannot use an index on the column.
I tried binding the parameter as ora_number but that does not help. The
only thing which seems to work is to downgrade the parameter from utf8.
Any ideas?
Thanks.
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
