Re: Optimization of query finding related products for a certain product
From: Vucko <mvucic_at_barok.foi.hr>
Date: 8 Sep 2001 16:01:37 GMT
Message-ID: <newscache$7vpcjg$sz9$1_at_tower.foi.hr>
Date: 8 Sep 2001 16:01:37 GMT
Message-ID: <newscache$7vpcjg$sz9$1_at_tower.foi.hr>
In comp.databases.oracle.misc Jacob Nordgren <jacob_nordgren_at_hotmail.com> 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
>PRODUCT_ID INT *
>VISITDATE DATE *
You don't like to use cursors?
declare cursor c1 is select distinct user_id from visit where product_id = 1883577330;
rec1 = c1%rowtype; begin for rec1 in c1 loop select product_id from visit where user_id = rec1.user_id and bla bla... ; end loop; end;Received on Sat Sep 08 2001 - 18:01:37 CEST