Re: Is there better SQL statement than this ?

Park Yeon Jo wrote:

> It takes too long time to execute the following SQL statement ?

Yes.

> Is there better SQL statement than this ?

Yes.

> I didn't make any Indexes on two tables yet.

That's just part of the problem.

> Please give me the hints.

Limit the number of scans on a table.

> select pmc.oid from productModelParam pmp, productModelCategory
> pmc
> where pmc.oid = pmp.oid
> and pmc.categoryValue = '3E965F2E-F64E-9ECE-1B3872409AC42428'
> and pmp.paramValue = 'TH'

Without indexes, this performs a full table scan of both pmp and pmc tables.

> 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'

Without indexes, this performs a full table scan of both pmp and pmc tables.

> 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'

Without indexes, this performs a full table scan of both pmp and pmc tables.

Result : the query scans the pmc and pmp tables 3 times.

Why scan these table 3 times when you can do it in one scan?

If you change the paramValue clause to an IN (as Allan suggested), you will be doing a full table scan of these two tables only once.

Adding an index on (oid) on both tables will result in a much faster join. Adding a (categoryValue) and/or (paramValue) index ill cause an index scan that could likely be be many times faster on your table, than a full table scan.

I suggest you familiarise yourself with Oracle join methods and indexes and how to use EXPLAIN PLAN sql statement for writing effective SQL.

--
Billy