Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

UNION ALL performance

Solomon YakobsonOct 24 2024 — edited Oct 25 2024

Oracle 19.24. I have a view that is:

CREATE OR REPLACE
  VIEW PROBLEM_VIEW
    AS
       SELECT  *
         FROM  COMPLEX_VIEW1
      UNION ALL
       SELECT *
         FROM COMPLEX_VIEW2
      UNION ALL
       SELECT *
         FROM COMPLEX_VIEW3
/

If I run SELECT against each part of UNION ALL:

SQL> SELECT  ORGNZTN_ID
  2    FROM  COMPLEX_VIEW1
  3    WHERE ORGNZTN_ID = 22596673485
  4      AND VLTN_DATE = DATE '2022-12-31'
  5  /

no rows selected

Elapsed: 00:00:00.04
SQL> SELECT  ORGNZTN_ID
  2    FROM  COMPLEX_VIEW2
  3    WHERE ORGNZTN_ID = 22596673485
  4      AND VLTN_DATE = DATE '2022-12-31'
  5  /

no rows selected

Elapsed: 00:00:00.00
SQL> SELECT  ORGNZTN_ID
  2    FROM  COMPLEX_VIEW3
  3    WHERE ORGNZTN_ID = 22596673485
  4      AND VLTN_DATE = DATE '2022-12-31'
  5  /

...

148 rows selected.

Elapsed: 00:00:06.19
SQL>

So first two complete in no time and third one in 6 seconds. However when I do UNION ALL:

SQL>  SELECT  ORGNZTN_ID
  2     FROM  COMPLEX_VIEW1
  3     WHERE ORGNZTN_ID = 22596673485
  4       AND VLTN_DATE = DATE '2022-12-31'
  5  UNION ALL
  6   SELECT  ORGNZTN_ID
  7     FROM  COMPLEX_VIEW2
  8     WHERE ORGNZTN_ID = 22596673485
  9       AND VLTN_DATE = DATE '2022-12-31'
 10  UNION ALL
 11   SELECT  ORGNZTN_ID
 12     FROM  COMPLEX_VIEW3
 13     WHERE ORGNZTN_ID = 22596673485
 14       AND VLTN_DATE = DATE '2022-12-31'
 15  /

...

148 rows selected.

Elapsed: 00:00:37.51
SQL>

It completes in 38 seconds. Materializing doesn't help:

SQL> WITH U1 AS (
  2              SELECT  /*+ MATERIALIZE */
  3                      ORGNZTN_ID
  4                FROM  COMPLEX_VIEW1
  5                WHERE ORGNZTN_ID = 22596673485
  6                  AND VLTN_DATE = DATE '2022-12-31'
  7              ),
  8        U2 AS (
  9              SELECT  /*+ MATERIALIZE */
 10                      ORGNZTN_ID
 11                FROM  COMPLEX_VIEW2
 12                WHERE ORGNZTN_ID = 22596673485
 13                  AND VLTN_DATE = DATE '2022-12-31'
 14              ),
 15        U3 AS (
 16               SELECT  /*+ MATERIALIZE */
 17                      ORGNZTN_ID
 18                FROM  COMPLEX_VIEW3
 19                WHERE ORGNZTN_ID = 22596673485
 20                  AND VLTN_DATE = DATE '2022-12-31'
 21              )
 22   SELECT  *
 23     FROM  U1
 24  UNION ALL
 25   SELECT  *
 26     FROM  U2
 27  UNION ALL
 28   SELECT  *
 29     FROM  U3
 30  /

...

148 rows selected.

Elapsed: 00:00:34.95
SQL>

It seems that optimizer detects predicates in each part of the UNION are the same and doesn't push them all the way down. If I make predicate values different:

SQL>  SELECT  ORGNZTN_ID
  2     FROM  COMPLEX_VIEW1
  3     WHERE ORGNZTN_ID = 22596673487
  4       AND VLTN_DATE = DATE '2024-12-31'
  5  UNION ALL
  6   SELECT  ORGNZTN_ID
  7     FROM  COMPLEX_VIEW2
  8     WHERE ORGNZTN_ID = 22596673486
  9       AND VLTN_DATE = DATE '2023-12-31'
 10  UNION ALL
 11   SELECT  ORGNZTN_ID
 12     FROM  COMPLEX_VIEW3
 13     WHERE ORGNZTN_ID = 22596673485
 14       AND VLTN_DATE = DATE '2022-12-31'
 15  /

...

148 rows selected.

Elapsed: 00:00:06.18
SQL>

As you can see, now it completes in 6 seconds.

SY.

Comments
Post Details
Added on Oct 24 2024
9 comments
169 views