Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with performance of select

Re: Problem with performance of select

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 20 Oct 2003 06:40:59 -0700
Message-ID: <2687bb95.0310200540.7218eb9a@posting.google.com>


"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US