Re: SQL Optimization

If you are not dependent on using an index on param1, you could use the phrase
lpad(param1,z,'0') between lpad(to_char(:X),z,'0') and lpad(to_char(:Y,z,'0') (where z is the maximum number of digits any numeric value could to the left of the decimal point in your conversion).

The lpads would allow character compares to give the same result as numeric comparisons.

If you need to use an index on param1, it gets more complicated. Feel free to E-mail me for further suggestions if this is the case.

gkeim_at_ncrcetc.WichitaKS.NCR.COM (Gail Keim) writes:

>I am trying to optimize the following SQL statement:
 

> select * from part, prod_family
> where part.prod_family = prod_family.prod_family
> and prod_family.description like :Z
> and part.param_1 between :X and :Y
 

>The difficulty arises because PART.PARAM_1 is a varchar column,
>and depending on the type of parameter passed, it may be inter-
>preted as character or numeric (as in the above example). When
>Oracle parses this statement, it does the to_number conversion
>on PARAM_1 first, and fails with Oracle error 01722, even though
>the clause PART.PROD_FAMILY = PROD_FAMILY.PROD_FAMILY should
>filter out only numeric values for PARAM_1.
 

>I can make this work by doing a subquery:
 

> select * from part
> where part.part_no in
> (select part_no from prod_family
> where prod_family.description like :Z)
> and part.param_1 between :X and :Y
 

>but with a considerable performance hit (from about 1 sec.
>to 3 minutes).
 

>Any ideas? Please send comments to gail.keim_at_WichitaKS.NCR.COM.
 

>Thanks!
Received on Wed Nov 10 1993 - 23:22:52 CET