oracle - (Oracle)有条件地跳过扫描表(SKIP CTE conditionally)

Question

我的目标是仅当 MYREF 为 NULL 时才在表 HISTORY 中使用 THEDATE 和 VAL。下面是我的代码。当 MYREF_VAL 临时表的结果有行时，如何修改以跳过扫描 HISTORY 并返回 MYREF_VAL 的结果？因为，HISOTY 中的实际数据是海量的，如果 MYREF 有价值，我想确保不会发生对 HISTROY 的扫描。

CREATE TABLE HISTORY (THEDATE VARCHAR(20),VAL NUMBER);
INSERT INTO HISTORY VALUES('20170101', 3);
INSERT INTO HISTORY VALUES('20200923', 4);
CREATE TABLE MYREF (THEDATE VARCHAR(20), VAL NUMBER);
INSERT INTO MYREF VALUES( NULL, NULL);


WITH MYREF_VAL AS( SELECT THEDATE, VAL VAL FROM MYREF)
,HISTORY_VAL AS (SELECT THEDATE, VAL FROM HISTORY)
SELECT * FROM HISTORY_VAL  
--Works but, How to modify in order to skip scanning HISTORY and return the result of MYREF_VAL, when the result of MYREF_VAL temporary table have not null?
--such as
--if MYREF has all NULL row, then SELECT * FROM HISTORY 
--if MYREF has any not NULL, then SELECT * FROM MYREF

标签： oraclecommon-table-expression

Answer 1

Oracle 可以使用 FILTER 操作根据另一个表中的数据从一个表中动态读取。如果您使用NOT EXISTSor创建谓词NOT IN，Oracle 可能会解决这个问题。FILTER 操作没有完整记录，我无法准确告诉您它何时工作，因此您需要检查说明计划以确保其正常工作。

例如，添加一些谓词并为此查询生成解释计划：

explain plan for
WITH MYREF_VAL AS( SELECT THEDATE, VAL VAL FROM MYREF)
    ,HISTORY_VAL AS (SELECT THEDATE, VAL FROM HISTORY)
SELECT * FROM MYREF_VAL WHERE THEDATE IS NOT NULL AND VAL IS NOT NULL
UNION ALL
SELECT * FROM HISTORY_VAL WHERE NOT EXISTS (SELECT 1 FROM MYREF_VAL WHERE THEDATE IS NOT NULL OR VAL IS NOT NULL);

检索解释计划，并注意NOT EXISTS条件如何连接到 Id=3 的 FILTER 操作：

select * from table(dbms_xplan.display);


Plan hash value: 4052518700
 
-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     3 |    98 |     9   (0)| 00:00:01 |
|   1 |  UNION-ALL          |         |       |       |            |          |
|*  2 |   TABLE ACCESS FULL | MYREF   |     1 |    25 |     3   (0)| 00:00:01 |
|*  3 |   FILTER            |         |       |       |            |          |
|   4 |    TABLE ACCESS FULL| HISTORY |     2 |    24 |     3   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| MYREF   |     1 |    25 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("THEDATE" IS NOT NULL AND "VAL" IS NOT NULL)
   3 - filter( NOT EXISTS (SELECT 0 FROM "MYREF" "MYREF" WHERE 
              "THEDATE" IS NOT NULL OR "VAL" IS NOT NULL))
   5 - filter("THEDATE" IS NOT NULL OR "VAL" IS NOT NULL)