Re: Is there better SQL statement than this ?

I think Allans response works for SELECT union SELECT union SELECT rather than intersect giving you potentially three rows. Intersect only works if the rows are found in both sides of the intersect. Therefore what you seem to want is a pmc.oid where there is a linked pmc record for values TH and 50Vdc and 603. If that's the case try the following (I've not tested these so there might be typos but the fundamentals are fine)

select pmc.oid from productModelCategory pmc where pmc.categoryValue = '3E965F2E-F64E-9ECE-1B3872409AC42428' and exists (

    select 'x' from productModelParam pmp     where pmc.oid = pmp.oid
    and pmp.paramValue = 'TH'
)
 and exists (

    select 'x' from productModelParam pmp     where pmc.oid = pmp.oid
    and pmp.paramValue = '50Vdc'
)
 and exists (

    select 'x' from productModelParam pmp     where pmc.oid = pmp.oid
    and pmp.paramValue = '603'
)

Create an index on productModelCategory( categoryValue ) if it is selective enough to improve action of identifying which pmc rows might be the ones you're after and one on productModelParam( paramValue ) to prevent needing to do three full table scans on this for each record in pmc found to match categoryValue.

Try this and compare it to the performance (and results to be sure) of the intersect option. One index on the table isn't likely to be an issue.

Alternatively try

select pmc.oid from (

    select pmc.oid, count(*)
    from productModelParam pmp, productModelCategory pmc     where pmc.categoryValue = '3E965F2E-F64E-9ECE-1B3872409AC42428'     and pmc.oid = pmp.oid
    and pmp.paramValue IN ('TH','50Vdc','603')     group by pmc.oid
    having count(*) = 3
)

Index pmc(categoryValue) and pmp(oid, paramValue)

Andy

"Park Yeon Jo" <kudos12_at_netian.com> wrote in message news:d7fe264d.0301270442.7e9fa9bd_at_posting.google.com...
> It takes too long time to execute the following SQL statement ?
>
> Is there better SQL statement than this ?
>
> I didn't make any Indexes on two tables yet.
>
> Please give me the hints.
>
>
>
>
> ==================================================================
> 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'
Received on Mon Jan 27 2003 - 07:36:45 CST