Let's have these two tables:

```
create table table_x(
  x_id varchar2(100) primary key
);

create table table_y(
  x_id varchar2(100) references table_x(x_id),
  stream varchar2(10),
  val_a number,
  val_b number
);

create index table_y_idx on table_y (x_id, stream);

```

Suppose we have millions of rows in each table and `table_y` contains from 0 to 10 rows per each `x_id`.

The queries in the following examples return 200 rows by filter `substr(x_id, 2, 1) = 'B'`.

It's required to optimize the query:

```
QUERY 1

select
    x.x_id,
    y.val_a,
    y.val_b
  from table_x x

  left join (select
                 x_id,
                 max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
                 max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
               from table_y
              group by x_id
   ) y on x.x_id = y.x_id

 where substr(x.x_id, 2, 1) = 'B'; -- intentionally not use the primary key filter

------
PLAN 1
----------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows    | Bytes    | Cost  | Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |   10000 |  2400000 | 22698 | 00:04:33 |
| * 1 |   HASH JOIN OUTER      |         |   10000 |  2400000 | 22698 | 00:04:33 |
| * 2 |    TABLE ACCESS FULL   | TABLE_X |   10000 |   120000 |   669 | 00:00:09 |
|   3 |    VIEW                |         |   10692 |  2437776 | 22029 | 00:04:25 |
|   4 |     SORT GROUP BY      |         |   10692 |   245916 | 22029 | 00:04:25 |
|   5 |      TABLE ACCESS FULL | TABLE_Y | 1069200 | 24591600 | 19359 | 00:03:53 |
----------------------------------------------------------------------------------
* 1 - access("X"."X_ID"="Y"."X_ID"(+))
* 2 - filter(SUBSTR("X"."X_ID", 2, 1)='B')

```

There's a way of significant optimization, so `QUERY 2` returns rows 2-3 times faster than `QUERY 1`. The `INLINE` hint is сritically important, because without it the second performs as slow as the first one.

```
QUERY 2

with
  table_y_total as (
    select --+ INLINE
        x_id,
        max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
        max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
      from table_y
     group by x_id
)
select
    x.x_id,
    (select val_a from table_y_total y where y.x_id = x.x_id) as val_a,
    (select val_b from table_y_total y where y.x_id = x.x_id) as val_b
  from table_x x
 where substr(x.x_id, 2, 1) = 'B';

------
PLAN 2
-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes  | Cost | Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             | 10000 | 120000 |  669 | 00:00:09 |
|   1 |   SORT GROUP BY NOSORT         |             |     1 |     19 |  103 | 00:00:02 |
|   2 |    TABLE ACCESS BY INDEX ROWID | TABLE_Y     |   100 |   1900 |  103 | 00:00:02 |
| * 3 |     INDEX RANGE SCAN           | TABLE_Y_IDX |   100 |        |    3 | 00:00:01 |
|   4 |   SORT GROUP BY NOSORT         |             |     1 |     20 |  103 | 00:00:02 |
|   5 |    TABLE ACCESS BY INDEX ROWID | TABLE_Y     |   100 |   2000 |  103 | 00:00:02 |
| * 6 |     INDEX RANGE SCAN           | TABLE_Y_IDX |   100 |        |    3 | 00:00:01 |
| * 7 |   TABLE ACCESS FULL            | TABLE_X     | 10000 | 120000 |  669 | 00:00:09 |
----------------------------------------------------------------------------------------- 
* 3 - access("X_ID"=:B1)
* 6 - access("X_ID"=:B1)
* 7 - filter(SUBSTR("X"."X_ID", 2, 1)='B')

```

Since the first query uses less code duplication I would prefer to keep it.

Is there a hint or another trick to meet the following conditions both?

* keep the first query code (`QUERY 1`)
* force optimizer use the second plan (`PLAN 2`)