RE: Bug in Oracle driver ?

If the query has where clause

where division_id = :1 and division_order_id = :2

execution tooks way too long taking into account that division_id (VARCHAR2) + division_order_id (VARCHAR2) is the primary key in this table.


A simple addition forcing division_order_id to be VARCHAR, dramatically improves performance

where division_id = :1 and division_order_id = '' || :2


So, the code obviously binds the second parameter incorrectly.

Thank you,

Igor


-----Original Message-----
From: Tim Bunce [mailto:Tim.Bunce@xxxxxxxxx]
Sent: Wednesday, December 21, 2005 10:23 AM
To: Igor Korolev
Cc: dbi-users@xxxxxxxx; c.w.huling@xxxxxxxxx; Tim.Bunce@xxxxxxxxx
Subject: Re: Bug in Oracle driver ?

On Wed, Dec 21, 2005 at 09:29:18AM -0600, Igor Korolev wrote:
> Wayne, Tim,
>
> Any ideas why DBD::Oracle incorrectly binds even non-numeric value '1130979589-6919-090637' from the
> below example ?

I don't understand the question.

Tim.

> Thank you,
>
> Igor
>
> _____________________________________________
> From: Igor Korolev
> Sent: Thursday, December 15, 2005 4:28 PM
> To: 'dbi-users@xxxxxxxx'
> Subject: RE: Bug in Oracle driver ?
>
> Hello,
>
> It looks like the Oracle driver incorrectly binds VARCHAR2 variables in case a number is passed from
> perl.
>
> Here is an example. This table has a primary key on division_id + division_order_id, so such selects
> should run very quickly (and they do run very quickly from sqlplus or toad).
>
> However, if I execute this using selectrow_array method of DBI.pm
>
> my $sql = 'select ' . join(',',@$sum_fields) . ' from ' . $table ..
> qq[ where division_id = :1 and division_order_id = :2];
>
> It takes up to 1 second per select:
>
> Order [1], select took: [0.730479001998901]
> Order [2], select took: [0.441529989242554]
> Order [1130979589-6919-090637], select took: [0.462771892547607]
> Order [3], select took: [0.424475193023682]
> Order [947647153], select took: [0.513856172561646]
> Order [4], select took: [0.49094295501709]
> Order [6], select took: [0.523995161056519]
> Order [7], select took: [0.558786153793335]
> Order [8], select took: [0.452331066131592]
> Order [131635406], select took: [0.451704978942871]
>
> However, a simple addition forcing VARCHAR2, dramatically improves performance
>
> my $sql = 'select ' . join(',',@$sum_fields) . ' from ' . $table ..
> qq[
];
>
> Order [1], select took: [0.00937604904174805]
> Order [2], select took: [0.00504183769226074]
> Order [1130979589-6919-090637], select took: [0.0115630626678467]
> Order [3], select took: [0.00729489326477051]
> Order [947647153], select took: [0.00545406341552734]
> Order [4], select took: [0.00602984428405762]
> Order [6], select took: [0.00556111335754395]
> Order [7], select took: [0.00576210021972656]
> Order [8], select took: [0.00520086288452148]
> Order [131635406], select took: [0.00546598434448242]
>
> Thank you,
>
> Igor
.