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

Home -> Community -> Usenet -> comp.databases.theory -> Re: Optimization of query finding related products for a certain product

Re: Optimization of query finding related products for a certain product

From: Ron Reidy <ron_at_indra.com>
Date: Sat, 08 Sep 2001 12:58:40 -0600
Message-ID: <3B9A6A60.431ABD31@indra.com>


Jacob Nordgren wrote:
>
> Hi everybody,
>
> This query finds the related products for a certain product based on
> user visits.
>
> Today this query takes 3 seconds. Is it possible to make it faster
> with the same result?
>
> SELECT PRODUCT_ID FROM VISIT WHERE
> USER_ID IN (SELECT DISTINCT USER_ID FROM VISIT WHERE PRODUCT_ID =
> 1883577330)
> AND PRODUCT_ID <> 1883577330 GROUP BY PRODUCT_ID ORDER BY
> COUNT(DISTINCT USER_ID) DESC
>
> This is the table:
>
> VISIT
> -----
> USER_ID INT *
> PRODUCT_ID INT *
> VISITDATE DATE *
>
> Thank you for your help!
>
> / Jacob

So, have you run EXPLAIN PLAN? Gotten a trace and run it through tkpro? Using the CBO and have valid, current stats?

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.
Received on Sat Sep 08 2001 - 13:58:40 CDT

Original text of this message

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