Re: SQL Optimization

In article <657_at_ncrcetc.WichitaKS.NCR.COM>, gkeim_at_ncrcetc.WichitaKS.NCR.COM (Gail Keim) writes:
>From: gkeim_at_ncrcetc.WichitaKS.NCR.COM (Gail Keim)
>Newsgroups: comp.databases.oracle,ncr.databases.oracle
>Subject: 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 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!
>

One or two thoughts spring to mind :-

Try putting the line part.prod_family = prod_family.prod_family LAST in the where clause

Never use IN on a subselect. Always use where EXISTS instead. The performance improvements from this alone may solve your problem.

My understanding of this (somebody correct me if I am wrong)

 IN - EVERY value is checked before a true or false is returned EVEN if the

       first value found matches the selection.
       This is particularly relevant is the subselect hits a large table.

  EXISTS - returns true as soon as a matching value is found   

Hope this helps or at least gives you a pointer to another direction

Ian

-- 
+--------------------------+-----------------------------+---------------------+
| Ian Bainbridge           |Working Philosophy:          |Sensible Advice :    |
| bainbridge_i_at_bgers.co.uk |                             |                     |
| British Gas ERS,         |Just trying to get to Friday |Don't Eat The Yellow |
| Newcastle Upon Tyne, UK  |with as little harrasment as |Snow  - Frank Zappa? |
| Phone: 091-216-0202      |possible                     |                     |
+--------------------------+-----------------------------+---------------------+