Re: Is there any better SQL Statement than this ? Help !!

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 27 Jan 2003 09:35:17 -0800
Message-ID: <b13qkl0e8o@drn.newsguy.com>

In article <d7fe264d.0301270848.696e126_at_posting.google.com>, kudos12_at_netian.com says...
>
>Is there better SQL Statement than this with this SQL statements ?
>
>It takes long so a time to execute the following SQL statement.
>
>Three select SQL And Two intersect SQL.
>
>Two tables has 350,000 records respectively.
>
>
>=======================================
>

select distinct pmc.oid
  from productModelParam pmp, productModelCategory pmc

 where pmc.oid = pmp.oid
   and pmc.categoryValue = '3E965F2E-F64E-9ECE-1B3872409AC42428'
   and pmp.paramValue in( 'TH', '50Vdc', '0603' )

having count(distinct pmp.paramValue) = 3;

is an alternate to try....

>select pmc.oid from productModelParam pmp, productModelCategory pmc
> where pmc.oid = pmp.oid
> and pmc.categoryValue = '3E965F2E-F64E-9ECE-1B3872409AC42428'
> and pmp.paramValue = 'TH'
> intersect
> select pmc.oid from productModelParam pmp, productModelCategory pmc
> where pmc.oid = pmp.oid
> and pmc.categoryValue = '3E965F2E-F64E-9ECE-1B3872409AC42428'
> and pmp.paramValue = '50Vdc'
> intersect
> select pmc.oid from productModelParam pmp, productModelCategory pmc
> where pmc.oid = pmp.oid
> and pmc.categoryValue = '3E965F2E-F64E-9ECE-1B3872409AC42428'
> and pmp.paramValue = '0603'
>
>===================================================
>
>Many Thanks !!

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Mon Jan 27 2003 - 11:35:17 CST