Re: Silly SQL Question

[Vladimir Begun]

Jacques,

you can use my first name -- "Mr." is too official for this list :). You have modified 
the query,
however I would suggest you to check execution plan (and present it here) and remove
LENGTH(TRANSLATE(:list, ',0123456789-E', ',')) by replacing it using number of 
elements in the
list i.e., in your case, 4. As I already said, it was just an example, in real life I 
would think
is it Ok or not Ok to use it.
Timing is not everything you can check, consider statisticts. Did you consider 
indexing val?
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.


Jacques Kilchoer wrote:
Mr. Begun,
I didn't answer your comment about making the query work with the decode vs. using a 
user-written PL/SQL function because I wanted to compare timings for both. I created a 
table with 200,000 rows and compared the VB (Vladimir Begun) query to the JRK (my) 
query. The runtimes were identical (using SET TIMING ON in SQL*Plus).
I agree that PL/SQL isn't necessary but I think it's easier to read, and the 
performance is the same. So there!
In any case Ms. Bellows had a correct solution, which I mistakenly thought was false, so all this was just an intellectual exercise.

With a 4-element list
execute :list := '3,4,5,6,'
SET TIMING ON results: VB query 11.87 JRK query: 11.67 (each query was run twice)
If you don't "hardcode" the number of elements in the list in the 'AND cnt = ' clause, the times were slightly longer but still the same: 14.71 vs. 14.32

VB query:
SELECT usr
   FROM (
         SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt
           FROM gab
        )
  WHERE val IN (SELECT DISTINCT element FROM (
   SELECT TO_NUMBER(SUBSTR(:list, p, c - p)) element
     FROM (
          SELECT DECODE(ROWNUM - 1, 0, 1, INSTR(:list, ',', 1, ROWNUM - 1) + 1) p
               , INSTR(:list, ',', 1, ROWNUM) c
            FROM gab
           WHERE ROWNUM <= LENGTH(TRANSLATE(:list, ',0123456789-E', ','))
          )
)
)
    AND cnt = 4 -- it's "for nothing", because count can be give by caller
  GROUP BY
        usr
      , cnt
HAVING COUNT(*) = 4 ;
JRK query:
select a.usr
 from
  (select distinct
      b.usr, b.val, count (distinct b.val) over (partition by b.usr) cnt
    from gab b
  ) a
 where
   val in (select *
                 from
                   the (select
                           cast (str_to_tbl (:num_list) as my_number_table)
                         from dual
                    )
            )
   and cnt = 4
 group by
   usr, cnt
 having
   count(*) = cnt ;
Test data creation:
drop table gab;
create table gab
   (usr varchar2(10) not null, val number not null) ;
declare
   insert_cnt constant pls_integer := 200000 ;
   commit_cnt constant pls_integer := 2000 ;
   i pls_integer ;
   j pls_integer ;
   k pls_integer ;
   l pls_integer ;
   n pls_integer ;
   usr gab.usr%type ;
   type usrt is table of gab.usr%type index by binary_integer ;
   usra usrt ;
   type valt is table of gab.val%type index by binary_integer ;
   vala valt ;
begin
   dbms_random.initialize (dbms_utility.get_time) ;
   i := 1 ;
   while i <= insert_cnt
   loop
      usr := chr (ascii ('A') + mod (abs (dbms_random.random), 26))
             || chr (ascii ('A') + mod (abs (dbms_random.random), 26))
             || chr (ascii ('A') + mod (abs (dbms_random.random), 26)) ;
      n := mod (abs (dbms_random.random), 5) + 1 ;
      j := mod (i - 1, commit_cnt) + 1 ;
      k := least (commit_cnt, j + n - 1) ;
      for l in j..k
      loop
         usra (l) := usr ;
         vala (l) := mod (abs (dbms_random.random), 9) + 1 ;
      end loop ;
      i := i + k - j + 1 ;
      if k >= commit_cnt or i >= insert_cnt
      then
         forall m in 1..k
            insert into gab (usr, val)
            values (usra (m), vala (m)) ;
         commit ;
      end if ;
   end loop ;
   commit ;
end ;
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
 INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).