Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: O9i: bind variable questions (special)

Re: O9i: bind variable questions (special)

From: Thorsten Kettner <thorsten.kettner_at_web.de>
Date: 5 Apr 2007 01:05:46 -0700
Message-ID: <1175760346.203786.110180@y80g2000hsf.googlegroups.com>


On 3 Apr., 12:04, Andreas Mosmann <mosm..._at_expires-30-04-2007.news- group.org> wrote:
> Hi ng,
>
> maybe that questions sound a bit stupid:
>
> If I use bind variables in queries I can increase the performance
> because
> a) the statement is parsed only 1 time
> b) a statement keeps in cache longer because the same statement with
> different parameters still is the same statement.

Bind variables can increase performance and they can decrease it. You must decide when it is appropriate to use them and when not.

Imagine a table of 500.000 records, 99% containing one value, say '0000000000' (haha) and the others containing different values.

select ... from ... where value = '1234567890'; runs fast provided there are statistics available, because Oracle knows it can use an index to pick the few records.
select ... from ... where value = '0000000000'; runs fast enough provided there are statistics available, because Oracle knows it must not use an index, for it will at last access 99% of all rows anyhow. select ... from ... where value = :myvalue; may run fast or not, because Oracle must decide for an execution plan that is fine for any value. If it decides for a full table scan, then the query with '1234567890' will be rather slow. If it decides for an index, then the query with '0000000000' will get extremely slow. Hence: Better not use a bind variable in this select statement.

If you want to create a dynamic in clause by a string operation like where instr(:mystringlist, value) > 0 to be able to use lists of different length then be aware that Oracle won't probably use an index then, as it might do with where value in ('A','B') and thus perform much worse.

Then there are selects that have many optional parameters resulting in thousands of possible combinations. You can write them very tricky and use bind vars and you can just write completely different statements. The latter is usually much more performant than the former. Say there are two optional values. That makes four combinations:

select ... from ...;
select ... from ... where a='A';
select ... from ... where b='B';
select ... from ... where a='A' and b='B';

or

select from ... where a = decode(:A, NULL, a, :A) and b = decode(:B, NULL, b, :B);

You see it is possible to make it just one select. But does it look like it will be quickly executed? Remember there would be just one execution plan for whatever values will be given. Received on Thu Apr 05 2007 - 03:05:46 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US