Merge queries with difference in conditions

Question

I have 2 SQL (Oracle 11g) queries :

select x1,x2,x3
from X
where x1 = a and x2 = b;

select x1,x2,x3
from X
where x1 = a and x2 = b and x3 = c; 

They select the same columns in table X but difference in conditions. I use UNION for merge result:

select x1,x2,x3,'Q1' as QueryCode
from X
where x1 = a and x2 = b
  UNION
select x1,x2,x3,'Q2' as QueryCode
from X
where x1 = a and x2 = b and x3 = c; 

But in this case, my table have too large data and I don't want to select it too many times. Can someone give me an idea for optimal way to build a query returning the same results?

Answer 1

We can fetch all the needed rows, adding OR between first and second filters, and then separate them in UNION. And using hint /*+ materialize */ we assure that data from original_table is selected only once and filtered results stored in memory as sub_table for current query execution.

Yes, it's not beatiful to duplicate code (x1 = a AND x2 = b) and (x1 = a AND x2 = b AND x3 = c), but in this case of too large data we make another good trade-off: a small duplication for a brilliant performance.

WITH
  sub_table AS (SELECT /*+ materialize */ x1, x2, x3
                  FROM original_table
                 WHERE (x1 = a AND x2 = b)             -- first filter
                    OR (x1 = a AND x2 = b AND x3 = c)  -- second filter
  )
SELECT  x1, x2, x3, 'Q1' AS querycode
  FROM sub_table
 WHERE x1 = a AND x2 = b              -- first filter (repeated)

UNION

SELECT x1, x2, x3, 'Q2' AS querycode
  FROM sub_table
 WHERE x1 = a AND x2 = b AND x3 = c;  -- second filter (repeated)

If we don't care of rows order, there is another approach wothout UNION:

SELECT x1, x2, x3,
       CASE
       WHEN x1 = a AND x2 = b THEN 'Q1'
       WHEN x1 = a AND x2 = b AND x3 = c THEN 'Q2'
       END AS marker
  FROM original_table
 WHERE CASE
       WHEN x1 = a AND x2 = b THEN 'Q1'
       WHEN x1 = a AND x2 = b AND x3 = c THEN 'Q2'
       END IS NOT NULL;

Still have an imperfection of code duplication, but it's a price for querying table with large data. In other words, for a small table we could use concise code with subquery, which is more memory-intensive:

SELECT *
  FROM (SELECT x1, x2, x3,
               CASE
               WHEN x1 = a AND x2 = b THEN 'Q1'
               WHEN x1 = a AND x2 = b AND x3 = c THEN 'Q2'
               END AS marker
          FROM original_table) t
 WHERE t.marker IS NOT NULL;

And finally, in Oracle 12c we can incapsulate this duplicated CASE into a function:

WITH
  FUNCTION get_marker(x1 CHAR, x2 CHAR, x3 CHAR) RETURN CHAR DETERMINISTIC
  IS
    BEGIN

      RETURN CASE
             WHEN x1 = a AND x2 = b THEN 'Q1'
             WHEN x1 = a AND x2 = b AND x3 = c THEN 'Q2'
             END;

    END
SELECT x1, x2, x3,
       get_marker(x1, x2, x3) AS marker
  FROM original_table
 WHERE get_marker(x1, x2, x3) IS NOT NULL;