Optimization by replacing subquery with named subquery having INLINE hint
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.