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.