Newsgroups: comp.databases.oracle,ncr.databases.oracle
From: bainbridge_i@perc03@bgers.co.uk (Ian Bainbridge)
Subject: Re: SQL Optimization
Message-ID: <CGBJyB.EnK@bgers.co.uk>
Lines: 68
Sender: news@bgers.co.uk
Reply-To: Ian Bainbridge
Organization: British Gas Plc
X-Newsreader: mxrn 6.18-4
References:  <657@ncrcetc.WichitaKS.NCR.COM>
Date: Thu, 11 Nov 1993 08:37:23 GMT



In article <657@ncrcetc.WichitaKS.NCR.COM>, gkeim@ncrcetc.WichitaKS.NCR.COM (Gail Keim) writes:
>From: gkeim@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@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@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                     |                     |
+--------------------------+-----------------------------+---------------------+