Peter J. Holzer wrote:
On 2007-09-14 18:03:15 +0100, Martin Evans wrote:
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?
This may be the same problem I ran into a few weeks ago. See
http://www.mail-archive.com/dbi-users@perl.org/msg30138.html
I have a patch for this but I still haven't gotten around to testing it,
so even though it's only a few lines I don't want to post it yet. Feel
free to contact me off-list if you want to try it.
The workaround which I actually use in production code is to set
NLS_NCHAR=US7ASCII. Of course this isn't a good idea if you have
nvarchar2 or nclob columns. Explicitely binding with the correct cs_form
also works:
$sth->bind_param(1, $name, { ora_csform => SQLCS_IMPLICIT });
hp
Thank you Peter. Rather stupidly, I had marked the post your refer to as
particularly noteworthy but forgot I'd seen it.
My time for the query has come down from at best .7s (some were a lot
worse) to 0.035s - a huge difference. All I've done to achieve this is:
1) bound the select columns which are integers as ORA_NUMBER (which I
don't think is having an real affect as the numbers I get back are still
marked utf-8).
2) added SQLCS_IMPLICIT to the bound parameters which are numbers (keys
in my case).
Like you found, when I look at the QEP, I find Oracle is doing a lot of
different things now including the creation of a view and use of a index
it was not using before.
All my data is utf8 so this problem probably exists elsewhere as well.
Does anyone know what the disadvantage of changing DBD::Oracle to
default to SQLCS_IMPLICIT instead of SQLCS_NCHAR is?
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
