RE: Silly SQL Question

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 17 Nov 2003 19:34:26 -0800
Message-ID: <F001.005D6F5C.20031117193426@fatcity.com>

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: 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 Mon Nov 17 2003 - 21:34:26 CST