CODEWITHSUNDEEP
sqloracle-databasequery-optimizationunion
Hana
Hana

Reputation: 824

Merge queries with difference in conditions

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?

Upvotes: 2

Views: 74

Answers (2)

diziaq
diziaq

Reputation: 7825

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;

Upvotes: 1

Utsav
Utsav

Reputation: 8103

If for X3='c' if you really need 2 rows, one with Q1 and another with Q1, then the union query is the best one.

I tried creating a CTE with X1=a and X2=b and then did a union later. The cost was bit more than the normal union query.

So go ahead with union.

Also if these columns are not indexed, try indexing them. Performance would improve.

Upvotes: 1

Related Questions