Yes, your query was much better. I keep on forgetting about those analytic functions. Shame on me.
I still think using a PL/SQL function to be able to easily change the IN list is worth the time and trouble. Plus it makes the explain plan is more interesting with the str_to_tbl function, you get to see the "COLLECTION ITERATOR (PICKLER FETCH)"
> -----Original Message-----
> Vladimir Begun
>
> ...
>
> PL/SQL is not needed to solve this task as SQL task. There
> reason when it would be wise to rewrite it is out of scope
> of this topic (but the reason is obvious).
>
> I'm just thinking that the query proposed by you is a bit
> expensive. So, I've re-scribbled mine:
>
> SELECT usr
> FROM (
> SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER
> (PARTITION BY usr) cnt
> FROM gab
> )
> WHERE val IN (1, 5, 7)
> AND cnt = 3
> GROUP BY
> usr
> , cnt
> HAVING COUNT(*) = cnt
> /
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: Jacques.Kilchoer_at_quest.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri Nov 14 2003 - 19:19:26 CST
