Oracle - Use WITH clause like a text macro

Question

I have an SQL query that reuses a complex subquery in two different places across a union

SELECT A.PROJECT_ID, B.INFO B.MORE_INFO
FROM A
INNER JOIN (
     -- Complex subquery
) B ON A.PROJECT_ID = B.PROJECT_ID
WHERE -- Filter to a disjoint subset --

UNION

SELECT C.PROJECT_ID, B.INFO B.MORE_INFO
FROM C
INNER JOIN (
     --  SAME Complex subquery
) B ON C.PROJECT_ID = B.PROJECT_ID
WHERE -- Filter to a disjoint subset --

In order to reduce repeated code, I'd like to use the WITH clause so I only have to write the complex subquery once.

WITH B AS (
    -- Complex subquery
)

SELECT A.PROJECT_ID, B.INFO B.MORE_INFO
FROM A
INNER JOIN B ON A.PROJECT_ID = B.PROJECT_ID
WHERE -- Filter to a disjoint subset --

UNION

SELECT C.PROJECT_ID, B.INFO B.MORE_INFO
FROM C
INNER JOIN B ON A.PROJECT_ID = B.PROJECT_ID
WHERE -- Filter to a disjoint subset --

This works, but now the query takes 4 times longer to execute. Looking at the explain plan for both of these queries, I think I know what's going on: The WITH clause is creating a temporary table that's not indexed, so when joining A and B, a full table scan is being executed. In the plan for the original query, a pushdown predicate is being used, reducing the work needed for the join. This corroborates with this modern SQL article I found talking about how WITH clauses can adversely impact performance.

Given all of this, is there a way where I can use the WITH clause more like a text macro a la C's preprocessor without creating a temporary table? Maybe there is a plan hint I could use? Or is there possibly some other native feature in Oracle or SQL that allows me to do the same thing?

In the end, I'd like to see Oracle do the same plan as the original query, while also taking advantage of the code modularity the WITH clause offers

Some notes:

• Oracle 12c
• I cannot eliminate the UNION
• The two WHERE filters are different, resulting with two disjoint sets

Answer 1

The simplest thing you can try is the inline hint.

Alas, for some reason that I personally do not understand, this hint is not documented (same as its counterpart, the materialize hint), even though they are widely used. Depending on your organization's rules, you may or may not be able to use this solution. I assume you know about hints, but didn't know about this particular one; if you need more help with it please say so. In any case, you may want to try it in a test environment to see what could be done.

EDIT (since others may read this as well):

The with clause should look like this:

with
  b as ( select /*+ inline */ ...... complex query ....... )
.......

Answer 2

This answers the original version of the question.

As you have structured the query, you can use or:

SELECT A.PROJECT_ID, B.INFO B.MORE_INFO
FROM A INNER JOIN
     (
     -- Complex subquery
     ) B
     ON A.PROJECT_ID = B.PROJECT_ID
WHERE -- Filter to a disjoint subset -- OR
      -- Filter to a disjoint subset --

If you need to remove duplicates, you can add SELECT DISTINCT.