Re: Silly SQL Question

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: vladimir.begun_at_oracle.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).