SQL Optimization

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 interpreted  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 Tue Nov 09 1993 - 17:05:36 CET