CREATE TABLE HW_PERMISSIONS 
( 
 HW_OID_LO           NUMBER                     NOT NULL, 
 HW_USER_OR_GROUP    VARCHAR2(512)              NOT NULL, 
 HW_TYPE             NUMBER(1)                  NOT NULL, 
 HW_PERMISSIONS      NUMBER                     NOT NULL, 
 CONSTRAINT hw_per_pk PRIMARY KEY ( hw_oid_lo, 
                                    hw_user_or_group, 
                                    hw_type ) 
) 
ORGANIZATION INDEX 

ALTER TABLE      HW_PERMISSIONS 
  ADD CONSTRAINT HW_PER_CO_TYPE CHECK ( hw_type IN ( 0, 1, 2, 3 ) ) 

CREATE TABLE HW_EFFECTIVE_GROUPS 
( 
 HW_SESSION_ID NUMBER        NOT NULL, 
 HW_GROUPNAME  VARCHAR2(512) NOT NULL, 
 HW_TYPE       NUMBER        NOT NULL 
) 
NOLOGGING 
CACHE 

CREATE INDEX HW_EGRP_IND1 
    ON HW_EFFECTIVE_GROUPS ( HW_SESSION_ID ) 

CREATE TABLE HW_ITERATORS ( 
 HW_ITERATOR_ID          NUMBER     NOT NULL ENABLE, 
 HW_ITERATOR_ID_INTERNAL NUMBER     NOT NULL ENABLE, 
 HW_ITERATOR_INDEX       NUMBER, 
 HW_OID32                NUMBER(10), 
 HW_OID_LO               NUMBER, 
 HW_VERSION              NUMBER, 
 HW_CATEGORY             NUMBER, 
 HW_SCORE                NUMBER, 
 HW_SOURCE               NUMBER 
) 
NOLOGGING 

CREATE INDEX HW_ITER_IND1 
    ON HW_ITERATORS ( HW_ITERATOR_ID, 
                      HW_ITERATOR_ID_INTERNAL, 
                      HW_ITERATOR_INDEX ) 

insert into hw_permissions
select * from (
       with random_data as (
         select object_name as groupname,
                trunc(dbms_random.value(0,4)) as typ,
				object_id as perm
       	   from all_objects
          where object_type = 'SEQUENCE'
         union all
         select 'everyone' as groupname, 1 as typ, 42 as perm from dual) 
       select d.id,
              r.groupname,
              r.typ,
              r.perm
         from random_data r, 
		      ( select 23456*power(2,48)+dbms_random.value(1,9999999) id
			      from ( select 1 from dual connect by level < 500 ),
				       ( select 1 from dual connect by level < 100/*0*/ ) ) d
       order by d.id, r.groupname);
commit;

insert into hw_effective_groups
       with random_session as (
         select dbms_random.string('A',17) as groupname,
                trunc(dbms_random.value(0,4)) as typ
       	from all_objects
          where rownum < dbms_random.value(200,600)
         union all
         select 'everyone' as groupname, 1 as typ from dual) 
       select d.num, r.*
	     from random_session r,
		      ( select rownum num from dual connect by level < 20 ) d;
commit;

insert into hw_iterators
       with data as (
         select rownum as id, hw_oid_lo
           from hw_permissions SAMPLE(1)
          where rownum < 4000 )
       select 1000001,
              d.id,
       	      r.id,
       	      trunc(dbms_random.value(1,power(2,31))),
       	      r.hw_oid_lo,
       	      0,
       	      13,
       	      100,
       	      16
         from data r,
              ( select rownum as id from dual connect by level < 3 ) d;
commit;   

variable it_id number = 1000001
variable it_id_int number = 2
variable sess_id number = 17

set autotrace traceonly

SELECT :it_id,
       :it_id_int+1,
       ROWNUM,
       x.oid32,
       x.oid_lo,
       x.version,
       x.category,
       x.score,
       x.source
  FROM ( SELECT it.hw_oid32         oid32,
                MAX(it.hw_oid_lo)   oid_lo,
                MAX(it.hw_version)  version,
                MAX(it.hw_category) category,
                MAX(it.hw_score)    score,
                MAX(it.hw_source)   source,
                MAX(it.hw_iterator_index)
           FROM ( SELECT /* + leading( i ) */
			             i.hw_oid32,
                         hw_oid_lo,
                         i.hw_version,
                         i.hw_category,
                         i.hw_score,
                         i.hw_source,
                         p.hw_user_or_group,
                         p.hw_type,
                         i.hw_iterator_index
                  FROM hw_iterators i
			            INNER JOIN hw_permissions p USING (hw_oid_lo)
						INNER JOIN hw_effective_groups g
						       ON ( p.hw_user_or_group = g.hw_groupname
							        AND p.hw_type = g.hw_type )
                 WHERE i.hw_iterator_id = :it_id
                   AND i.hw_iterator_id_internal = :it_id_int
					AND g.hw_session_id = :sess_id ) it
          GROUP BY it.hw_oid32
          ORDER BY MAX(it.hw_iterator_index) ) x;