Re: SQL parsing efficiency

If you would submit variant 1 with different hardcoded literals this would result in parsing each individual statement. This is one of the safest ways to kill performance and should be avoided as much as possible. Variant 2 should be parsed only once, as variant 3 should also.

Hth,

Sybrand Bakker, Oracle DBA

"Todd Barry" <toddbarry_at_earthlink.net> wrote in message news:9s7a5t831gl5pgcj5fr2o6vqopsdcn7ppm_at_4ax.com...
> Is there any benefit (parsing, optimization, etc.) in writing PL/SQL
> conforming to modes 2 and 3 shown below?
>
> Basically, is there a performance benefit to using variables or
> constants instead of hard-coded values?
>
> 1.
> cursor csrTest is
> select company_id
> from companies
> where type_code = 10; -- hard-coded value
>
> 2.
> nTypeCode companies.type_code%type := 10;
>
> cursor csrTest is
> select company_id
> from companies
> where type_code = nTypeCode; -- variable value
>
> 3.
> cnTypeCode constant companies.type_code%type := 10;
>
> cursor csrTest is
> select company_id
> from companies
> where type_code = cnTypeCode; -- constant value
>
> Thanks,
> Todd
Received on Thu Jan 04 2001 - 23:19:29 CST