with a as (

select grp_factor from (select distinct grp_factor from numbers where id < 10 order by grp_factor) t

where rownum <= 5)

select b.id, a.grp_factor

from a cross apply(select id from (select id from numbers b where a.grp_factor = b.grp_factor order by id) t where rownum<=3)b

--use v$sqlarea to find out your query by searching with key words. this might --take a little bit long.

select sql_id, sql_text from v$sqlarea where sql_text like '%cross apply%';

--copy the sql_id and place in the first place of parameters in the

--function "dbms_xplan.display_cursor" . in our case, it is 1gbpuv6zfq64s

select * from table(dbms_xplan.display_cursor('1gbpuv6zfq64s',null,'typical'));

SQL_ID  1gbpuv6zfq64s, child number 0

-------------------------------------

with a as ( select grp_factor from (select distinct grp_factor from

numbers where id < 10 order by grp_factor) t  where rownum <= 5)

select b.id, a.grp_factor from a cross apply(select id from (select id

from numbers b where a.grp_factor = b.grp_factor order by id) t where

rownum<=3)b

Plan hash value: 3737636938

------------------------------------------------------------------------------------------------------------

| Id  | Operation                                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                         |                 |       |       |     2 (100)|          |

|   1 |  NESTED LOOPS                            |                 |     1 |    26 |     2   (0)| 00:00:01 |

|   2 |   VIEW                                   |                 |     1 |    13 |     1   (0)| 00:00:01 |

|*  3 |    COUNT STOPKEY                         |                 |       |       |            |          |

|   4 |     VIEW                                 |                 |     1 |    13 |     1   (0)| 00:00:01 |

|*  5 |      SORT UNIQUE STOPKEY                 |                 |     1 |    26 |     1   (0)| 00:00:01 |

|   6 |       TABLE ACCESS BY INDEX ROWID BATCHED| NUMBERS         |     1 |    26 |     1   (0)| 00:00:01 |

|*  7 |        INDEX RANGE SCAN                  | SYS_C009920     |     1 |       |     1   (0)| 00:00:01 |

|   8 |   VIEW                                   | VW_LAT_A83890C2 |     1 |    13 |     1   (0)| 00:00:01 |

|*  9 |    COUNT STOPKEY                         |                 |       |       |            |          |

|  10 |     VIEW                                 |                 |     1 |    13 |     1   (0)| 00:00:01 |

|* 11 |      TABLE ACCESS BY INDEX ROWID         | NUMBERS         |     1 |    26 |     1   (0)| 00:00:01 |

|  12 |       INDEX FULL SCAN                    | SYS_C009920     |     1 |       |     1   (0)| 00:00:01 |

------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter(ROWNUM<=5)

   5 - filter(ROWNUM<=5)

   7 - access("ID"<10)

   9 - filter(ROWNUM<=3)

  11 - filter("A"."GRP_FACTOR"="B"."GRP_FACTOR")

SQL_ID  1gbpuv6zfq64s, child number 1

-------------------------------------

with a as ( select grp_factor from (select distinct grp_factor from

numbers where id < 10 order by grp_factor) t  where rownum <= 5)

select b.id, a.grp_factor from a cross apply(select id from (select id

from numbers b where a.grp_factor = b.grp_factor order by id) t where

rownum<=3)b

Plan hash value: 3737636938

------------------------------------------------------------------------------------------------------------

| Id  | Operation                                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------------------