Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with performance of select
"Søren Kongstad" <kongstad_at_kongstad.net> wrote in message news:<bn081f$rsit4$1_at_ID-177910.news.uni-berlin.de>...
> Hi
>
> I have a query looking something like this
>
> select
> productid,
> Somefunction1(productid) as s1,
> Somefunction2(productid) as s2,
> Somefunction3(productid) as s3,
> Somefunction4(productid) as s4,
> Anyfunction1(productid) as a1,
> Anyfunction2(productid) as a2,
> Anyfunction3(productid) as a3,
> Anyfunction4(productid) as a4
> from
> products
>
> Which takes about a minute to retrieve.
>
> Now I want to discard all results where s1=a2, s2=a2,s3=a3,s4=a4 since i
> have to find only those products with different values in one or more place.
>
> When I try this query
>
> select * from
> (
> select
> productid,
> Somefunction1(productid) as s1,
> Somefunction2(productid) as s2,
> Somefunction3(productid) as s3,
> Somefunction4(productid) as s4,
> Anyfunction1(productid) as a1,
> Anyfunction2(productid) as a2,
> Anyfunction3(productid) as a3,
> Anyfunction4(productid) as a4
> from
> products
> )
> where
> s1 <> a1
> or s2 <> a2
> or s3 <> a3
> or s4 <> a4
>
> The query time is extended dramatically (It hadn't finished after an hour!)
>
> What can I do differently to get the result?
>
>
> Sincerely
>
> Søren Kongstad
Soren, your query is going through the entire table to apply the
functions to every row then going through the data again to filter it.
Try eliminating the unnecessary outer query and filter the original
pass:
where somefunction1(product_id) != anyfunction1(product_id)
....
You should compare the explain plans for both queries to get an idea
of what you asked Oracle to do.
HTH -- Mark D Powell -- Received on Mon Oct 20 2003 - 08:40:59 CDT