what is your version
what is your blocksize

and can you help me reproduce, I tried in 8i..10gr2:

ops$xp8i\tkyte@ORA8IR3W> drop table t;

Table dropped.

ops$xp8i\tkyte@ORA8IR3W>
ops$xp8i\tkyte@ORA8IR3W> declare
  2      l_cols long;
  3  begin
  4      for i in 1 .. 200
  5      loop
  6          l_cols := l_cols || ', object_name x' || i;
  7      end loop;
  8      execute immediate '
  9  create table t
 10  as
 11  select all_objects.* ' || l_cols || ', rpad( ''*'', 1000, ''*'' ) data
 12    from all_objects
 13   where rownum <= 1000';
 14  end;
 15  /

PL/SQL procedure successfully completed.

ops$xp8i\tkyte@ORA8IR3W> select count(*) from t;

  COUNT(*)
----------
      1000

ops$xp8i\tkyte@ORA8IR3W>
ops$xp8i\tkyte@ORA8IR3W> select *
  2    from ( select t.*, row_number() over (partition by owner order by created) rn
  3             from t
  4         )
  5   where rn = 1;

OWNER                          OBJECT_NAME
.... 

your blocksize is?
your tables look like


I've got the code for stragg already - but nothing else to reproduce or look at the issue with?


ops$tkyte@ORA10GR2> create table t ( a char(2000), b char(2000), c char(2000), x_id number, y_id number, z_id number, session_id number  );

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select session_id, stragg(distinct x_id), stragg(distinct y_id), stragg(distinct z_id)
  2    from t
  3   group by session_id, a, b, c;

no rows selected
 

ops$tkyte@ORA9IR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

ops$tkyte@ORA9IR2> show parameter block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

ops$tkyte@ORA9IR2> select session_id, stragg(distinct x_id), stragg(distinct y_id), stragg(distinct z_id)
  2  from t
  3  group by session_id, a, b, c;

no rows selected


I cannot reproduce... (9204 or 9206)

what non-default init.ora's do you have?
 

8k is likely the "best" these days - for most everything.

There is an internal implementation restriction, that varies by release (pretty much always on the "increase")


ops$tkyte@ORA734> create table t ( x varchar2(&1), y varchar2(&1) );
Enter value for 1: 750
Enter value for 1: 750
old   1: create table t ( x varchar2(&1), y varchar2(&1) )
new   1: create table t ( x varchar2(750), y varchar2(750) )
 
Table created.
 
ops$tkyte@ORA734> select value from v$parameter where name like '%block_s%';
 
VALUE
-------------------------------------------------------------------------------
8192
 
ops$tkyte@ORA734>
ops$tkyte@ORA734> select max(decode(rownum,0,x)), max(decode(rownum,1,x)), max(decode(rownum,2,x)), max(decode(rownum,3,x)),
  2             max(decode(rownum,4,x)), max(decode(rownum,5,x)), max(decode(rownum,6,x)), max(decode(rownum,7,x)),
  3         max(decode(rownum,8,x)), max(decode(rownum,9,x)), max(decode(rownum,10,x)), max(decode(rownum,11,x)),
  4         max(decode(rownum,12,x)), max(decode(rownum,13,x)), max(decode(rownum,14,x)), max(decode(rownum,15,x)),
  5         max(decode(rownum,16,x)), max(decode(rownum,17,x)), max(decode(rownum,18,x)), max(decode(rownum,19,x)),
  6         max(decode(rownum,20,x)), max(decode(rownum,21,x)), max(decode(rownum,22,x)), max(decode(rownum,23,x)),
  7         max(decode(rownum,24,x)), max(decode(rownum,25,x)), max(decode(rownum,26,x)), max(decode(rownum,27,x)),
  8         max(decode(rownum,28,x)), max(decode(rownum,29,x)), max(decode(rownum,30,x)), max(decode(rownum,31,x)),
  9         max(decode(rownum,32,x)), max(decode(rownum,33,x)), max(decode(rownum,34,x)), max(decode(rownum,35,x)),
 10         max(decode(rownum,36,x)), max(decode(rownum,37,x)), max(decode(rownum,38,x)), max(decode(rownum,39,x)),
 11         max(decode(rownum,40,x)), max(decode(rownum,41,x)), max(decode(rownum,42,x)), max(decode(rownum,43,x)),
 12         max(decode(rownum,44,x)), max(decode(rownum,45,x)), max(decode(rownum,46,x)), max(decode(rownum,47,x)),
 13         max(decode(rownum,48,x)), max(decode(rownum,49,x))
 14    from t
 15   group by y;
  from t
       *
ERROR at line 14:
ORA-01467: sort key too long


ops$tkyte@ORA817DEV> create table t ( x varchar2(&1), y varchar2(&1) );
Enter value for 1: 750
Enter value for 1: 750
old   1: create table t ( x varchar2(&1), y varchar2(&1) )
new   1: create table t ( x varchar2(750), y varchar2(750) )
 
Table created.
 
ops$tkyte@ORA817DEV> select value from v$parameter where name like '%block_s%';
 
VALUE
-------------------------------------------------------------------------------
8192
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select max(decode(rownum,0,x)), max(decode(rownum,1,x)), max(decode(rownum,2,x)), max(decode(rownum,3,x)),
  2             max(decode(rownum,4,x)), max(decode(rownum,5,x)), max(decode(rownum,6,x)), max(decode(rownum,7,x)),
  3         max(decode(rownum,8,x)), max(decode(rownum,9,x)), max(decode(rownum,10,x)), max(decode(rownum,11,x)),
  4         max(decode(rownum,12,x)), max(decode(rownum,13,x)), max(decode(rownum,14,x)), max(decode(rownum,15,x)),
  5         max(decode(rownum,16,x)), max(decode(rownum,17,x)), max(decode(rownum,18,x)), max(decode(rownum,19,x)),
  6         max(decode(rownum,20,x)), max(decode(rownum,21,x)), max(decode(rownum,22,x)), max(decode(rownum,23,x)),
  7         max(decode(rownum,24,x)), max(decode(rownum,25,x)), max(decode(rownum,26,x)), max(decode(rownum,27,x)),
  8         max(decode(rownum,28,x)), max(decode(rownum,29,x)), max(decode(rownum,30,x)), max(decode(rownum,31,x)),
  9         max(decode(rownum,32,x)), max(decode(rownum,33,x)), max(decode(rownum,34,x)), max(decode(rownum,35,x)),
 10         max(decode(rownum,36,x)), max(decode(rownum,37,x)), max(decode(rownum,38,x)), max(decode(rownum,39,x)),
 11         max(decode(rownum,40,x)), max(decode(rownum,41,x)), max(decode(rownum,42,x)), max(decode(rownum,43,x)),
 12         max(decode(rownum,44,x)), max(decode(rownum,45,x)), max(decode(rownum,46,x)), max(decode(rownum,47,x)),
 13         max(decode(rownum,48,x)), max(decode(rownum,49,x))
 14    from t
 15   group by y;
 
no rows selected

<b>but...</b>

ops$tkyte@ORA817DEV> create table t ( x varchar2(&1), y varchar2(&1) );
old   1: create table t ( x varchar2(&1), y varchar2(&1) )
new   1: create table t ( x varchar2(2000), y varchar2(2000) )
 
Table created.
 
ops$tkyte@ORA817DEV> select value from v$parameter where name like '%block_s%';
 
VALUE
-------------------------------------------------------------------------------
8192
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select max(decode(rownum,0,x)), max(decode(rownum,1,x)), max(decode(rownum,2,x)), max(decode(rownum,3,x)),
  2             max(decode(rownum,4,x)), max(decode(rownum,5,x)), max(decode(rownum,6,x)), max(decode(rownum,7,x)),
  3         max(decode(rownum,8,x)), max(decode(rownum,9,x)), max(decode(rownum,10,x)), max(decode(rownum,11,x)),
  4         max(decode(rownum,12,x)), max(decode(rownum,13,x)), max(decode(rownum,14,x)), max(decode(rownum,15,x)),
  5         max(decode(rownum,16,x)), max(decode(rownum,17,x)), max(decode(rownum,18,x)), max(decode(rownum,19,x)),
  6         max(decode(rownum,20,x)), max(decode(rownum,21,x)), max(decode(rownum,22,x)), max(decode(rownum,23,x)),
  7         max(decode(rownum,24,x)), max(decode(rownum,25,x)), max(decode(rownum,26,x)), max(decode(rownum,27,x)),
  8         max(decode(rownum,28,x)), max(decode(rownum,29,x)), max(decode(rownum,30,x)), max(decode(rownum,31,x)),
  9         max(decode(rownum,32,x)), max(decode(rownum,33,x)), max(decode(rownum,34,x)), max(decode(rownum,35,x)),
 10         max(decode(rownum,36,x)), max(decode(rownum,37,x)), max(decode(rownum,38,x)), max(decode(rownum,39,x)),
 11         max(decode(rownum,40,x)), max(decode(rownum,41,x)), max(decode(rownum,42,x)), max(decode(rownum,43,x)),
 12         max(decode(rownum,44,x)), max(decode(rownum,45,x)), max(decode(rownum,46,x)), max(decode(rownum,47,x)),
 13         max(decode(rownum,48,x)), max(decode(rownum,49,x))
 14    from t
 15   group by y;
  from t
       *
ERROR at line 14:
ORA-01467: sort key too long



ops$tkyte@ORA10GR2> create table t ( x varchar2(&1), y varchar2(&1) );
old   1: create table t ( x varchar2(&1), y varchar2(&1) )
new   1: create table t ( x varchar2(2000), y varchar2(2000) )
 
Table created.
 
ops$tkyte@ORA10GR2> select value from v$parameter where name like '%block_s%';
 
VALUE
-------------------------------------------------------------------------------
8192
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select max(decode(rownum,0,x)), max(decode(rownum,1,x)), max(decode(rownum,2,x)), max(decode(rownum,3,x)),
  2         max(decode(rownum,4,x)), max(decode(rownum,5,x)), max(decode(rownum,6,x)), max(decode(rownum,7,x)),
  3         max(decode(rownum,8,x)), max(decode(rownum,9,x)), max(decode(rownum,10,x)), max(decode(rownum,11,x)),
  4         max(decode(rownum,12,x)), max(decode(rownum,13,x)), max(decode(rownum,14,x)), max(decode(rownum,15,x)),
  5         max(decode(rownum,16,x)), max(decode(rownum,17,x)), max(decode(rownum,18,x)), max(decode(rownum,19,x)),
  6         max(decode(rownum,20,x)), max(decode(rownum,21,x)), max(decode(rownum,22,x)), max(decode(rownum,23,x)),
  7         max(decode(rownum,24,x)), max(decode(rownum,25,x)), max(decode(rownum,26,x)), max(decode(rownum,27,x)),
  8         max(decode(rownum,28,x)), max(decode(rownum,29,x)), max(decode(rownum,30,x)), max(decode(rownum,31,x)),
  9         max(decode(rownum,32,x)), max(decode(rownum,33,x)), max(decode(rownum,34,x)), max(decode(rownum,35,x)),
 10         max(decode(rownum,36,x)), max(decode(rownum,37,x)), max(decode(rownum,38,x)), max(decode(rownum,39,x)),
 11         max(decode(rownum,40,x)), max(decode(rownum,41,x)), max(decode(rownum,42,x)), max(decode(rownum,43,x)),
 12         max(decode(rownum,44,x)), max(decode(rownum,45,x)), max(decode(rownum,46,x)), max(decode(rownum,47,x)),
 13         max(decode(rownum,48,x)), max(decode(rownum,49,x))
 14    from t
 15   group by y;
 
no rows selected


 

it is all about sets....

consider if you had this set:

ops$tkyte@ORA9IR2> variable x varchar2(4000)
ops$tkyte@ORA9IR2> exec :x := '11,1 11,1 11,1 11,1 11,2 11,2 11,2 11,2 23,1 23,1 23,1 23,1 23,1 23,1 45,1 45,1 61,1 84,2 84,2 84,2 84,2 84,2 84,2 84,2 84,2 134,4 134,4 134,4 134,4 134,5 134,5 134,5 134,5'

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> with data
  2  as
  3  (
  4  select l,
  5         to_number( substr(token,1,instr(token,',')-1) ) id1,
  6             to_number( substr(token,instr(token,',')+1 ) ) id2
  7    from (
  8  select level l,
  9         substr (txt,
 10                 instr (txt, '|', 1, level  ) + 1,
 11                 instr (txt, '|', 1, level+1) - instr (txt, '|', 1, level) -1 )
 12           as token
 13    from (
 14  select '|'||replace(:x,' ','|')||'|' txt
 15    from dual
 16         )
 17  connect by level <= length(txt)-length(replace(txt,'|',''))-1
 18         )
 19  )
 20  select * from data;

         L        ID1        ID2
---------- ---------- ----------
         1         11          1
         2         11          1
         3         11          1
         4         11          1
         5         11          2
         6         11          2
         7         11          2
         8         11          2
         9         23          1
        10         23          1
        11         23          1
        12         23          1
        13         23          1
        14         23          1
        15         45          1
        16         45          1
        17         61          1
        18         84          2
        19         84          2
        20         84          2
        21         84          2
        22         84          2
        23         84          2
        24         84          2
        25         84          2
        26        134          4
        27        134          4
        28        134          4
        29        134          4
        30        134          5
        31        134          5
        32        134          5
        33        134          5

33 rows selected.


Which we obviously do.  It is orderable (L) - it is full of numbers (convenient, that is your datatype too!).  We can select from it like a table (because it in fact is a table...).  We can use it in an "in", we can join to it....

The possibilities :)

Try this query, I believe it to be correct - I've commented inline a bit the logic:


ops$tkyte@ORA9IR2> with data
  2  as
  3  (
  4  select l,
  5         to_number( substr(token,1,instr(token,',')-1) ) containerid,
  6             to_number( substr(token,instr(token,',')+1 ) ) containerquantity
  7    from (
  8  select level l,
  9         substr (txt,
 10              instr (txt, '|', 1, level  ) + 1,
 11              instr (txt, '|', 1, level+1) - instr (txt, '|', 1, level) -1 )
 12           as token
 13    from (
 14  select '|'||replace(:x,' ','|')||'|' txt
 15    from dual
 16         )
 17  connect by level <= length(txt)-length(replace(txt,'|',''))-1
 18         )
 19  ),

<b>Our important set of containerids/containerquantities to find, ordered by L</b>

 20  possibles
 21  as
 22  (select *
 23     from (select  t3.stateprescriptionid
 24              , t1.containerid
 25              , t1.containerquantity
 26              , row_number ()
 27                 over (partition by t3.stateprescriptionid
 28                       order by t1.containerid, t1.containerquantity
 29                      ) rn,
 30                count(*)
 31                  over (partition by t3.stateprescriptionid
 32                       ) cnt
 33        from (select *
 34                from statecheckcontainer
 35               where statecheckid in
 36                  (select statecheckid
 37                     from statecheckcontainer
 38                    where (containerid,containerquantity) in
 39                          (select containerid, containerquantity
 40                             from data)
 41                  )
 42             ) t1 join
 43             statecheck t2
 44                on t1.statecheckid = t2.statecheckid join
 45             stateprescription t3
 46                on t2.stateprescriptionid = t3.stateprescriptionid
 47       )
 48   where cnt = (select count(*) from data)
 49  ),


<b>these are the only stateprescriptionid values we need to consider.  And notice that we may be able to use a nice index on T1 if it would be deemed useful.

We use row_number() to be able to sequence them just like you did, and we use count(*) over to figure out how many we found for each.  We only really keep the truly possible possibles now (where cnt=....). 

NOTE: care was taken with T1 there - to get all of the needed rows to be considered, we are getting the same set of rows you were ultimately comparing.

Be very nice to perhaps have index on t1(containerid,containerquantity,statecheckid) and t1(statecheckid) perhaps - to find these rows rapidly (assuming this is selective enough of course) </b>

 50  jointo
 51  as
 52  (select data.l, data.containerid,
 53          data.containerquantity, possibles.stateprescriptionid
 54     from data,
 55          (select distinct stateprescriptionid
 56             from possibles) possibles
 57  )


<b>Jointo is needed in 9i - but won't be in 10g (with a PARTITIONED OUTER JOIN).  What we need is the set of all possible stateprescriptionid's and "datas" - we need to join data to each "set of stateprescriptionid's in possibles" - cartesian join does that for us</b>

 58  select p.stateprescriptionid
 59    from possibles p, jointo j
 60   where p.stateprescriptionid = j.stateprescriptionid
 61     and p.containerid = j.containerid
 62     and p.containerquantity = j.containerquantity
 63     and p.rn = j.l
 64   group by p.stateprescriptionid
 65  having count(*) = (select count(*) from data)
 66  /

STATEPRESCRIPTIONID
-------------------
           10000005

<b>then we just join possibles to jointo by the full key - count up the resulting joined rows by statprescriptionid - and only keep those that have the same count as the rows in data (eg: we found ALL of the rows)</b> 

ops$tkyte%ORA10GR2> create table t
  2  as
  3  select rpad(rownum,4000,rownum) x,
  4         rpad(rownum,4000,rownum) y,
  5         rpad(rownum,4000,rownum) z,
  6             rownum a
  7    from dual connect by level <= 10000
  8  /

Table created.

ops$tkyte%ORA10GR2> set autotrace traceonly
ops$tkyte%ORA10GR2> select x, y, z, sum(a) from t group by x,y,z;

9892 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 47235625

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 11478 |    65M|  1168   (2)| 00:00:09 |
|   1 |  HASH GROUP BY     |      | 11478 |    65M|  1168   (2)| 00:00:09 |
|   2 |   TABLE ACCESS FULL| T    | 11478 |    65M|  1167   (1)| 00:00:09 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
        198  recursive calls
          0  db block gets
      33323  consistent gets
      27806  physical reads
          0  redo size
  119373847  bytes sent via SQL*Net to client
       7649  bytes received via SQL*Net from client
        661  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9892  rows processed

ops$tkyte%ORA10GR2> set autotrace off

ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> variable x refcursor
ops$tkyte%ORA10GR2> declare
  2          l_query long := 'create table t ( c1 char(2000) ';
  3  begin
  4          for i in 2 .. 100
  5          loop
  6                  l_query := l_query || ', c' || i || ' char(2000) default rpad(''x'',2000,''x'') ';
  7          end loop;
  8          l_query := l_query || ')';
  9          execute immediate l_query;
 10
 11          for i in 1..5
 12          loop
 13                  execute immediate 'insert into t (c1) values ( to_char(:x) )' using i;
 14          end loop;
 15          commit;
 16
 17          l_query := 'select count(*) ';
 18          for i in 1 .. 100
 19          loop
 20                  l_query := l_query || ', c' || i;
 21          end loop;
 22          l_query := l_query || ' from t group by c1';
 23          for i in 2 .. 100
 24          loop
 25                  l_query := l_query || ', c' || i;
 26          end loop;
 27          open :x for l_query;
 28  end;
 29  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print x

  COUNT(*)
----------
C1
----------------------------------------------------------------------------------------------------
C2
----------------------------------------------------------------------------------------------------
C3
----------------------------------------------------------------------------------------------------
C4
....
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


ops$tkyte%ORA10GR2> spool off

ops$tkyte%ORA9IR2> create table t ( a char(2000), b char(2000), c char(2000), d char(2000), e char(2000), f char(2000) );

Table created.

ops$tkyte%ORA9IR2> insert into t values ( 1, 2, 3, 4, 5, 6 );

1 row created.

ops$tkyte%ORA9IR2> commit;

Commit complete.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select count(*) from t group by a,b,c,d,e,f order by a,b,c,d,e,f;
select count(*) from t group by a,b,c,d,e,f order by a,b,c,d,e,f
                     *
ERROR at line 1:
ORA-01467: sort key too long


----------------------------------------------------------
ops$tkyte%ORA10GR2> create table t ( a char(2000), b char(2000), c char(2000), d char(2000), e char(2000), f char(2000) );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( 1, 2, 3, 4, 5, 6 );

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select count(*) from t group by a,b,c,d,e,f order by a,b,c,d,e,f;

  COUNT(*)
----------
         1