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>


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

Original text of this message