Optimization by replacing subquery with named subquery having INLINE hint 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)
TL;DR Rewriting the query is not going to get you anywhere here. Use functional indexes to cut the cost of accessing 200 rows out of your millions.



Ok, replacing subquery with CTE and INLINE hint has nothing to do with optimization. Increased readablity? Sure. Performance? Hardly.

The plans differ, because your queries are not really equivalent. First query performs join. Second query has separate subqueries in SELECT clause, and for optimizer that is obviously different.

To make them "sort of equivalent" you would have to join resultsets in 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,
    y.val_a,
    y.val_b
from table_x x
left outer join table_y on y.x_id = x.x_id
where substr(x.x_id, 2, 1) = 'B';


No "code duplication" as well. The plan would probably match the one for the first query.

What happens in your case is you have two tables with table_x having 100 times fewer rows than table_y. By inlining the access to table_y in SELECT you leave optimizer no choice but use index. Accessing 1% of rows via index is usually faster than a fullscan. No surprises here.

However, you specified the scale of millions of rows in each table. I don't know how the cardinalities of X and Y are going to correlate, but we don't really need to know.

Fullscans do not scale well.

On the other hand, whether you only have 10 thousand rows or 10 milllion rows you always need only 200 that match the substr(x_id, 2, 1) = 'B' criterion, right?

Functional indexes will definitely work here.

create index table_x_2nd_char_of_pk_idx on table_x (
  substr(x_id, 2, 1) ASC);


Once you dropped the cardinality to these 200 rows, accessing table_y via its FK is a piece of cake.



Ok, I've added this part after "fullscan on x is not a problem". I'm sure it is, but you can deal with it separately.

Whether you index access predicates or not you can make sure access is isolated and cardinality is low enough when the bigger table is joined.

with
  table_x_filtered as (
    select x.x_id
    from   table_x x
    where  substr(x.x_id, 2, 1) = 'B'
    and    rownum &gt; 0) -- well, better than hints, right?
select 
    x.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_x_filtered X 
  left outer join table_y y on y.x_id = x.x_id
 group by x.x_id


If table_x is going to stay really small forever, the optimizer should choose to fullscan it first, apply filter and then join. If not, well, you can always use leading, as @panick suggests, but without proper indexing it's going to get slower and slower anyway.