Oracle 联合所有查询缺失行

你好，汤姆和汤姆，

链接的实时sql显示了我们在数据库上使用的精简和 “移动到双” 查询。这是几个嵌套的 “union all” 语句，我们期望最外面的union (UNION2) 交付两个第二最外面的union (SUBSUBQ1和subsubq2) 的结果的union。但是，如您所见，对UNION2的查询仅传递一行，一行是由subsubq1产生的。SUBSUBQ2的那个丢了。这是怎么发生的？

这个问题取决于，顺便说一句，是 “mm” 和 “mm” (额外的空间) 之间的区别。这对SUBSUBQ2来说似乎并不重要，但似乎对UNION2来说，它以一种不可预测的方式很重要...

提前感谢你的洞察力，乔

好的。看起来这是一个从与子句中实现结果的错误。

如果实现subq1，则subsubq1不返回任何行。但是，如果你内联最终的并集，那么你会得到两行:

SQL> set serveroutput off
SQL> with
  2  DUAL1 as (
  3    select 'MM ' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID, 100 as SHIFT
  4          from dual
  5    union all
  6    select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID, 100 as SHIFT
  7          from dual ),
  8  DUAL2 as (
  9    select 'MM' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID from dual
 10    union all
 11    select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID from dual ),
 12  UNION1 as (
 13    select ORG_SYS_TYPE,FIN_ID, SHIFT from DUAL1
 14    union all
 15    select ORG_SYS_TYPE,FIN_ID, null SHIFT from DUAL2
 16  ),
 17  SUBQ1 as (
 18    select * from UNION1 where SHIFT = '100' ),
 19  SUBSUBQ1 as (
 20    select * from SUBQ1 where ORG_SYS_TYPE = 'MM' ),
 21  SUBSUBQ2 as (
 22    select * from SUBQ1 where ORG_SYS_TYPE = 'DER' ),
 23  UNION2 as (
 24    select * from SUBSUBQ1
 25    union all
 26    select * from SUBSUBQ2
 27  )
 28  select /*+ gather_plan_statistics */q.* from SUBSUBQ1 q;

ORG FIN_ID           SHIFT
--- ----------- ----------
MM  LOST_FIN_ID        100

SQL>
SQL> select *
  2  from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1sftw2x173ub4, child number 0
-------------------------------------
with DUAL1 as (   select 'MM ' as ORG_SYS_TYPE, 'LOST_FIN_ID' as
FIN_ID, 100 as SHIFT         from dual   union all   select 'DER' as
ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID, 100 as SHIFT         from dual ),
DUAL2 as (   select 'MM' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID from
dual   union all   select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID
from dual ), UNION1 as (   select ORG_SYS_TYPE,FIN_ID, SHIFT from DUAL1
  union all   select ORG_SYS_TYPE,FIN_ID, null SHIFT from DUAL2 ),
SUBQ1 as (   select * from UNION1 where SHIFT = '100' ), SUBSUBQ1 as (
 select * from SUBQ1 where ORG_SYS_TYPE = 'MM' ), SUBSUBQ2 as (
select * from SUBQ1 where ORG_SYS_TYPE = 'DER' ), UNION2 as (   select
* from SUBSUBQ1   union all   select * from SUBSUBQ2 ) select /*+
gather_plan_statistics */q.* from SUBSUBQ1 q

Plan hash value: 2919767015

-------------------------------------------------------------------------
| Id  | Operation        | Name | Starts | E-Rows | A-Rows |   A-Time   |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |      1 |        |      1 |00:00:00.01 |
|   1 |  VIEW            |      |      1 |      3 |      1 |00:00:00.01 |
|   2 |   UNION-ALL      |      |      1 |        |      1 |00:00:00.01 |
|   3 |    VIEW          |      |      1 |      2 |      1 |00:00:00.01 |
|   4 |     UNION-ALL    |      |      1 |        |      1 |00:00:00.01 |
|   5 |      FAST DUAL   |      |      1 |      1 |      1 |00:00:00.01 |
|*  6 |      FILTER      |      |      1 |        |      0 |00:00:00.01 |
|   7 |       FAST DUAL  |      |      0 |      1 |      0 |00:00:00.01 |
|*  8 |    FILTER        |      |      1 |        |      0 |00:00:00.01 |
|   9 |     VIEW         |      |      0 |      2 |      0 |00:00:00.01 |
|  10 |      UNION-ALL   |      |      0 |        |      0 |00:00:00.01 |
|  11 |       FAST DUAL  |      |      0 |      1 |      0 |00:00:00.01 |
|* 12 |       FILTER     |      |      0 |        |      0 |00:00:00.01 |
|  13 |        FAST DUAL |      |      0 |      1 |      0 |00:00:00.01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter(NULL IS NOT NULL)
   8 - filter(NULL IS NOT NULL)
  12 - filter(NULL IS NOT NULL)


43 rows selected.

SQL> with
  2  DUAL1 as (
  3    select 'MM ' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID, 100 as SHIFT
  4          from dual
  5    union all
  6    select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID, 100 as SHIFT
  7          from dual ),
  8  DUAL2 as (
  9    select 'MM' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID from dual
 10    union all
 11    select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID from dual ),
 12  UNION1 as (
 13    select ORG_SYS_TYPE,FIN_ID, SHIFT from DUAL1
 14    union all
 15    select ORG_SYS_TYPE,FIN_ID, null SHIFT from DUAL2
 16  ),
 17  SUBQ1 as (
 18    select /*+ materialize */* from UNION1 where SHIFT = '100' ),
 19  SUBSUBQ1 as (
 20    select * from SUBQ1 where ORG_SYS_TYPE = 'MM' ),
 21  SUBSUBQ2 as (
 22    select * from SUBQ1 where ORG_SYS_TYPE = 'DER' ),
 23  UNION2 as (
 24    select * from SUBSUBQ1
 25    union all
 26    select * from SUBSUBQ2
 27  )
 28  select /*+ gather_plan_statistics */q.* from SUBSUBQ1 q;

no rows selected

SQL>
SQL> select *
  2  from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d27h9uj6z29xp, child number 0
-------------------------------------
with DUAL1 as (   select 'MM ' as ORG_SYS_TYPE, 'LOST_FIN_ID' as
FIN_ID, 100 as SHIFT         from dual   union all   select 'DER' as
ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID, 100 as SHIFT         from dual ),
DUAL2 as (   select 'MM' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID from
dual   union all   select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID
from dual ), UNION1 as (   select ORG_SYS_TYPE,FIN_ID, SHIFT from DUAL1
  union all   select ORG_SYS_TYPE,FIN_ID, null SHIFT from DUAL2 ),
SUBQ1 as (   select /*+ materialize */* from UNION1 where SHIFT = '100'
), SUBSUBQ1 as (   select * from SUBQ1 where ORG_SYS_TYPE = 'MM' ),
SUBSUBQ2 as (   select * from SUBQ1 where ORG_SYS_TYPE = 'DER' ),
UNION2 as (   select * from SUBSUBQ1   union all   select * from
SUBSUBQ2 ) select /*+ gather_plan_statistics */q.* from SUBSUBQ1 q

Plan hash value: 1299862411

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |      1 |        |      0 |00:00:00.01 |       2 |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |      1 |        |      0 |00:00:00.01 |       2 |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6783_4168A29 |      1 |        |      0 |00:00:00.01 |       1 |
|   3 |    VIEW                                  |                            |      1 |      3 |      2 |00:00:00.01 |       0 |
|   4 |     UNION-ALL                            |                            |      1 |        |      2 |00:00:00.01 |       0 |
|   5 |      VIEW                                |                            |      1 |      2 |      2 |00:00:00.01 |       0 |
|   6 |       UNION-ALL                          |                            |      1 |        |      2 |00:00:00.01 |       0 |
|   7 |        FAST DUAL                         |                            |      1 |      1 |      1 |00:00:00.01 |       0 |
|   8 |        FAST DUAL                         |                            |      1 |      1 |      1 |00:00:00.01 |       0 |
|*  9 |      FILTER                              |                            |      1 |        |      0 |00:00:00.01 |       0 |
|  10 |       VIEW                               |                            |      0 |      2 |      0 |00:00:00.01 |       0 |
|  11 |        UNION-ALL                         |                            |      0 |        |      0 |00:00:00.01 |       0 |
|  12 |         FAST DUAL                        |                            |      0 |      1 |      0 |00:00:00.01 |       0 |
|  13 |         FAST DUAL                        |                            |      0 |      1 |      0 |00:00:00.01 |       0 |
|* 14 |   VIEW                                   |                            |      1 |      3 |      0 |00:00:00.01 |       0 |
|  15 |    TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6783_4168A29 |      1 |      3 |      2 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - filter(NULL IS NOT NULL)
  14 - filter("ORG_SYS_TYPE"='MM')


44 rows selected.

SQL> with
  2  DUAL1 as (
  3    select 'MM ' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID, 100 as SHIFT
  4          from dual
  5    union all
  6    select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID, 100 as SHIFT
  7          from dual ),
  8  DUAL2 as (
  9    select 'MM' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID from dual
 10    union all
 11    select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID from dual ),
 12  UNION1 as (
 13    select ORG_SYS_TYPE,FIN_ID, SHIFT from DUAL1
 14    union all
 15    select ORG_SYS_TYPE,FIN_ID, null SHIFT from DUAL2
 16  ),
 17  SUBQ1 as (
 18    select /*+ inline */* from UNION1 where SHIFT = '100' ),
 19  SUBSUBQ1 as (
 20    select * from SUBQ1 where ORG_SYS_TYPE = 'MM' ),
 21  SUBSUBQ2 as (
 22    select * from SUBQ1 where ORG_SYS_TYPE = 'DER' ),
 23  UNION2 as (
 24    select * from SUBSUBQ1
 25    union all
 26    select * from SUBSUBQ2
 27  )
 28  select /*+ gather_plan_statistics */q.* from UNION2 q;

ORG FIN_ID           SHIFT
--- ----------- ----------
MM  LOST_FIN_ID        100
DER FIN_ID_B           100

SQL>
SQL> select *
  2  from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  75cty08wfm3wk, child number 0
-------------------------------------
with DUAL1 as (   select 'MM ' as ORG_SYS_TYPE, 'LOST_FIN_ID' as
FIN_ID, 100 as SHIFT         from dual   union all   select 'DER' as
ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID, 100 as SHIFT         from dual ),
DUAL2 as (   select 'MM' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID from
dual   union all   select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID
from dual ), UNION1 as (   select ORG_SYS_TYPE,FIN_ID, SHIFT from DUAL1
  union all   select ORG_SYS_TYPE,FIN_ID, null SHIFT from DUAL2 ),
SUBQ1 as (   select /*+ inline */* from UNION1 where SHIFT = '100' ),
SUBSUBQ1 as (   select * from SUBQ1 where ORG_SYS_TYPE = 'MM' ),
SUBSUBQ2 as (   select * from SUBQ1 where ORG_SYS_TYPE = 'DER' ),
UNION2 as (   select * from SUBSUBQ1   union all   select * from
SUBSUBQ2 ) select /*+ gather_plan_statistics */q.* from UNION2 q

Plan hash value: 2646163190

---------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      2 |00:00:00.01 |
|   1 |  VIEW              |      |      1 |      6 |      2 |00:00:00.01 |
|   2 |   UNION-ALL        |      |      1 |        |      2 |00:00:00.01 |
|   3 |    VIEW            |      |      1 |      3 |      1 |00:00:00.01 |
|   4 |     UNION-ALL      |      |      1 |        |      1 |00:00:00.01 |
|   5 |      VIEW          |      |      1 |      2 |      1 |00:00:00.01 |
|   6 |       UNION-ALL    |      |      1 |        |      1 |00:00:00.01 |
|   7 |        FAST DUAL   |      |      1 |      1 |      1 |00:00:00.01 |
|*  8 |        FILTER      |      |      1 |        |      0 |00:00:00.01 |
|   9 |         FAST DUAL  |      |      0 |      1 |      0 |00:00:00.01 |
|* 10 |      FILTER        |      |      1 |        |      0 |00:00:00.01 |
|  11 |       VIEW         |      |      0 |      2 |      0 |00:00:00.01 |
|  12 |        UNION-ALL   |      |      0 |        |      0 |00:00:00.01 |
|  13 |         FAST DUAL  |      |      0 |      1 |      0 |00:00:00.01 |
|* 14 |         FILTER     |      |      0 |        |      0 |00:00:00.01 |
|  15 |          FAST DUAL |      |      0 |      1 |      0 |00:00:00.01 |
|  16 |    VIEW            |      |      1 |      3 |      1 |00:00:00.01 |
|  17 |     UNION-ALL      |      |      1 |        |      1 |00:00:00.01 |
|  18 |      VIEW          |      |      1 |      2 |      1 |00:00:00.01 |
|  19 |       UNION-ALL    |      |      1 |        |      1 |00:00:00.01 |
|* 20 |        FILTER      |      |      1 |        |      0 |00:00:00.01 |
|  21 |         FAST DUAL  |      |      0 |      1 |      0 |00:00:00.01 |
|  22 |        FAST DUAL   |      |      1 |      1 |      1 |00:00:00.01 |
|* 23 |      FILTER        |      |      1 |        |      0 |00:00:00.01 |
|  24 |       VIEW         |      |      0 |      2 |      0 |00:00:00.01 |
|  25 |        UNION-ALL   |      |      0 |        |      0 |00:00:00.01 |
|* 26 |         FILTER     |      |      0 |        |      0 |00:00:00.01 |
|  27 |          FAST DUAL |      |      0 |      1 |      0 |00:00:00.01 |
|  28 |         FAST DUAL  |      |      0 |      1 |      0 |00:00:00.01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - filter(NULL IS NOT NULL)
  10 - filter(NULL IS NOT NULL)
  14 - filter(NULL IS NOT NULL)
  20 - filter(NULL IS NOT NULL)
  23 - filter(NULL IS NOT NULL)
  26 - filter(NULL IS NOT NULL)


61 rows selected.

可能是因为在物化的某个地方有一个char -> varchar2转换。您也可以通过在subsubq1中铸造MM作为char(3) 来克服这一点:

SQL> with
  2  DUAL1 as (
  3    select 'MM ' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID, 100 as SHIFT
  4          from dual
  5    union all
  6    select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID, 100 as SHIFT
  7          from dual ),
  8  DUAL2 as (
  9    select 'MM' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID from dual
 10    union all
 11    select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID from dual ),
 12  UNION1 as (
 13    select ORG_SYS_TYPE,FIN_ID, SHIFT from DUAL1
 14    union all
 15    select ORG_SYS_TYPE,FIN_ID, null SHIFT from DUAL2
 16  ),
 17  SUBQ1 as (
 18    select * from UNION1 where SHIFT = '100' ),
 19  SUBSUBQ1 as (
 20    select * from SUBQ1 where ORG_SYS_TYPE = cast('MM' as char(3)) ),
 21  SUBSUBQ2 as (
 22    select * from SUBQ1 where ORG_SYS_TYPE = 'DER' ),
 23  UNION2 as (
 24    select * from SUBSUBQ1
 25    union all
 26    select * from SUBSUBQ2
 27  )
 28  select /*+ gather_plan_statistics */q.* from UNION2 q;

ORG FIN_ID           SHIFT
--- ----------- ----------
MM  LOST_FIN_ID        100
DER FIN_ID_B           100