| Subject: | Oracle LOB/non-LOB handling is still messy! |
After the update to the newest version of DBIx::Class (with LOB handling
code rewritten), some of my code started running very, VERY slow. Trace
revealed the bottleneck:
UPDATE LIVRARIA_1129 SET ATUALIZADO = :p1, DATA_ATUALIZACAO = sysdate
WHERE
( ( UTL_RAW.CAST_TO_VARCHAR2(RAWTOHEX(DBMS_LOB.SUBSTR(ISBN13, 2000, 1)))
=
:p2 ) )
The funny thing is: I have *NO* LOB columns in this table! 'ISBN13' is
actually a TEXT one. So I tried to disable all the new LOB handling
code, and got this:
ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR: error
possibly near <*> indicator at char 88 in 'UPDATE LIVRARIA_1129 SET
ATUALIZADO = :p1, DATA_ATUALIZACAO = sysdate WHERE ( ISBN13 = :<*>p2 )')
Somehow, my TEXT is still interpreted as LOB. Now, if I at least ensure
that the RAWTOHEX transcoding only apply to non-plaintext LOB, my code
gets several hundreds times faster.
Not sure if it is a correct way of doing that, still.
| Subject: | oracle-lob-fix.patch |
diff --git a/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm b/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm
index f582b94..450e564 100644
--- a/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm
+++ b/lib/DBIx/Class/Storage/DBI/Oracle/Generic.pm
@@ -544,12 +544,29 @@ sub _prep_for_execute {
my $col_equality_re = qr/ (?<=\s) ([\w."]+) (\s*=\s*) $/x;
+ # After the update to the newest version of DBIx::Class (with LOB handling code rewritten),
+ # some of my code started running very, VERY slow. Trace revealed the bottleneck:
+ #
+ # UPDATE LIVRARIA_1129 SET ATUALIZADO = :p1, DATA_ATUALIZACAO = sysdate
+ # WHERE
+ # ( ( UTL_RAW.CAST_TO_VARCHAR2(RAWTOHEX(DBMS_LOB.SUBSTR(ISBN13, 2000, 1))) =
+ # :p2 ) )
+ #
+ # The funny thing is: I have *NO* LOB columns in this table! 'ISBN13' is actually a TEXT one. So I tried to disable all the new LOB handling code, and got this:
+ #
+ # ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR: error possibly near <*> indicator at char 88 in 'UPDATE LIVRARIA_1129 SET ATUALIZADO = :p1, DATA_ATUALIZACAO = sysdate WHERE ( ISBN13 = :<*>p2 )')
+ #
+ # Somehow, my TEXT is still interpreted as LOB. Now, if I at least ensure that the RAWTOHEX transcoding only apply to non-plaintext LOB, my code gets several hundreds times faster.
+ # Not sure if it is a correct way of doing that, still.
+
for my $b_idx (0 .. $#$bind) {
my $bound = $bind->[$b_idx];
if (
$lob_bind_indices->{$b_idx}
and
+ $self->_is_binary_lob_type($bound->[0]->{sqlt_datatype})
+ and
my ($col, $eq) = $sql_parts[0] =~ $col_equality_re
) {
my $data = $bound->[1];