SQL> drop table xt;

Table dropped.

Elapsed: 00:00:00.03
SQL>
SQL> create table xt(a, b not null,x1,x2,x3) as
  2  select trunc(level/1e5) a, dbms_random.value b,
  3  rownum x1, rownum x2, rownum x3
  4  from dual connect by level <= 2e6-1;

Table created.

Elapsed: 00:00:15.45
SQL>
SQL> create index xt_i on xt(a,b desc,x1,x2,x3);

Index created.

Elapsed: 00:00:11.32
SQL>
SQL> select *
  2    from (select xt.*, rank() over(partition by a order by b desc) rn
  3            from xt)
  4   where rn = 1;

20 rows selected.

Elapsed: 00:00:04.45

Execution Plan
----------------------------------------------------------
Plan hash value: 3812567171

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1576K|   117M| 14846   (1)| 00:02:59 |
|*  1 |  VIEW             |      |  1576K|   117M| 14846   (1)| 00:02:59 |
|*  2 |   WINDOW NOSORT   |      |  1576K|    97M| 14846   (1)| 00:02:59 |
|   3 |    INDEX FULL SCAN| XT_I |  1576K|    97M| 14846   (1)| 00:02:59 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN"=1)
   2 - filter(RANK() OVER ( PARTITION BY "A" ORDER BY
              SYS_OP_DESCEND("B"))<=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      14633  consistent gets
          0  physical reads
          0  redo size
       1758  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed

SQL> select a,
  2         max(b) b,
  3         max(x1) keep(dense_rank first order by b desc) x1,
  4         max(x2) keep(dense_rank first order by b desc) x2,
  5         max(x3) keep(dense_rank first order by b desc) x3
  6    from xt
  7   group by a;

20 rows selected.

Elapsed: 00:00:02.57

Execution Plan
----------------------------------------------------------
Plan hash value: 3894939874

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1576K|    97M|  3538   (3)| 00:00:43 |
|   1 |  SORT GROUP BY     |      |  1576K|    97M|  3538   (3)| 00:00:43 |
|   2 |   TABLE ACCESS FULL| XT   |  1576K|    97M|  3470   (1)| 00:00:42 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      12577  consistent gets
      12573  physical reads
          0  redo size
       1683  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         20  rows processed

SQL> with t_unique(a) as
  2   (select min(t1.a) a from xt t1
  3    union all
  4    select (select min(t1.a) a from xt t1 where t1.a > t.a) from t_unique t w
here a is not null),
  5  xt_with_max_rowids AS
  6   (SELECT t1.a,
  7           (select t2.column_value rid
  8              FROM TABLE(cast(multiset (select t2.rowid rid
  9                                 from xt t2
 10                                where t1.a = t2.a
 11                                  and rownum = 1
 12                                order by t2.b desc) as sys.odcivarchar2list)
 13                         ) t2) rid_max
 14      from t_unique t1)
 15  SELECT * FROM xt t1 WHERE rowid IN (SELECT rid_max FROM xt_with_max_rowids)
;

20 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 723862557

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

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

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

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

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

|   0 | SELECT STATEMENT                             |          | 31533 |    62M

|     9  (12)| 00:00:01 |

|   1 |  NESTED LOOPS                                |          | 31533 |    62M

|     9  (12)| 00:00:01 |

|   2 |   COLLECTION ITERATOR SUBQUERY FETCH         |          |     1 |     2
|     2   (0)| 00:00:01 |

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

|*  4 |     INDEX RANGE SCAN                         | XT_I     | 15767 |   585K

|    62   (0)| 00:00:01 |

|   5 |   VIEW                                       | VW_NSO_1 |     2 |  4004
|     7  (15)| 00:00:01 |

|   6 |    HASH UNIQUE                               |          |     2 |    26
|     7  (15)| 00:00:01 |

|   7 |     VIEW                                     |          |     2 |    26
|     6   (0)| 00:00:01 |

|   8 |      UNION ALL (RECURSIVE WITH) BREADTH FIRST|          |       |
|            |          |

|   9 |       SORT AGGREGATE                         |          |     1 |    13
|            |          |

|  10 |        INDEX FULL SCAN (MIN/MAX)             | XT_I     |     1 |    13
|     3   (0)| 00:00:01 |

|  11 |       SORT AGGREGATE                         |          |     1 |    13
|            |          |

|  12 |        FIRST ROW                             |          |     1 |    13
|     3   (0)| 00:00:01 |

|* 13 |         INDEX RANGE SCAN (MIN/MAX)           | XT_I     |     1 |    13
|     3   (0)| 00:00:01 |

|* 14 |       RECURSIVE WITH PUMP                    |          |       |
|            |          |

|* 15 |   TABLE ACCESS BY USER ROWID                 | XT       | 15767 |  1185K

|     1   (0)| 00:00:01 |

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

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


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(ROWNUM=1)
   4 - access("T2"."A"=:B1)
  13 - access("T1"."A">:B1 AND "T1"."A" IS NOT NULL)
  14 - filter("A" IS NOT NULL)
  15 - access(CHARTOROWID("RID_MAX"))

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        125  consistent gets
          0  physical reads
          0  redo size
       1683  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         22  sorts (memory)
          0  sorts (disk)
         20  rows processed

SQL>