bind variables

From: Brad Howerter <bhower_at_wgc.woodward.com>
Date: 1997/10/24
Message-ID: <877732690.29530@dejanews.com>#1/1

I have a program that takes 8 seconds to run when I don't use a bind variable for a date, but it takes 14 seconds when I do. This query uses the index ap_checks_ndx, which is on columns org_unit_id and vendor_id. Has anyone seen a similar problem with bind variables? Is this a bug?

I've seen something similar to this with "like :1". Oracle assumes you are going to give it a bind value beginning with %, and doesn't use an index on the column if there is alternative. But in this case the date isn't even indexed!

The 8 second statement is this:
select c.check_no

   FROM ap_checks c
   WHERE c.ap_bank_id = :1 AND

   c.org_unit_id = :1 and
   c.check_no   >= 0 and
   c.ap_pymt_id  'ACH' AND


   (c.check_date <= TO_DATE('31-AUG-1997','DD-MON-YYYY') and

(c.clear_date >= TO_DATE('01-AUG-1997','DD-MON-YYYY') or
   c.void_date >= TO_DATE('01-AUG-1997','DD-MON-YYYY'))) ORDER BY 1

:1 is bound with '04'

The 14 second statement is this:
select c.check_no

   FROM ap_checks c
   WHERE c.ap_bank_id = :1 AND

   c.org_unit_id = :1 and
   c.check_no   >= 0 and
   c.ap_pymt_id  'ACH' AND

(c.check_date <= TO_DATE('31-AUG-1997','DD-MON-YYYY') and
(c.clear_date >= TO_DATE(:2,'DD-MON-YYYY') or
   c.void_date >= TO_DATE('01-AUG-1997','DD-MON-YYYY'))) ORDER BY 1

:1 is bound with '04', :2 is bound with '01-AUG-1997'

Please reply via email.

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Fri Oct 24 1997 - 00:00:00 CDT