Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Question Unanswered: Using Perl:DBI with Oracle

    I'm using Perl and DBI to access our Oracle database.

    For some reason, when my query involves using
    ' ... where id like ? ... '

    basically, "like" with bind variable (entered from command line), the Oracle
    server does a full table scan instead of hitting the index. If I replace the ?
    with a fixed VARCHAR value like "ppozon%", it works fine (fast, and no errors).
    Likewise, if I replace the "like" with "=", it's works fine, except the results are
    not what I expect.

    Any hints on what this behavior is, and anything I might be doing wrong?

    Thanks in advance for any information,
    -pp
    Last edited by ppozon; 05-01-04 at 02:53.

  2. #2
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    167
    Without any code it is hard to say, whether you do anything wrong or not...
    board.perl-community.de - The German Perl-Community

  3. #3
    Join Date
    Apr 2004
    Posts
    2

    Lightbulb

    The code was basically something like this:

    $sth->prepare("select * from customers where last_name like ?");
    $sth->execute($ARGV[0]);

    A fellow coworker answered my question. Apparently, the server will not optimize by using the index if I use bind variable. It treats it "opaquely" somehow and so is forced to do a full table scan. Constructing the SQL dynamically:

    $sth->prepare("select * from customers where last_name like $ARGV[0]");
    $sth->execute();

    avoids this behavior and gets better performance by using the index.

    More info here (at the very very bottom):
    http://www.rittman.net/archives/000832.html
    Last edited by ppozon; 05-01-04 at 02:55.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •