Oracle 通过多个表连接-需要什么子句？

我有一个问题，我总是感到困惑。“Connect By” 上的大多数演示都有一个表，“Connect” 子句很简单

Connect By Prior emp_id = mgr_id 
复制

或者类似的。然而，我有一种情况，我需要在做连接之前将几个表连接在一起-因为我需要通过一个表上的字段来排序结果集兄弟姐妹，这是从层次结构表中几个表的方式。

要生成一个简单的示例，请考虑我有一个表，该表可以保存员工层次结构的多个副本，其中一个被标记为主要层次结构。

Create View hierarchy_versions As
  Select 1 version, 'N' primary_hier From dual Union All
  Select 2 version, 'Y' primary_hier From dual;
  
Create View emp_hierarchy As
  Select employee_id, first_name, last_name, manager_id, 1 version From hr.employees Union All
  Select employee_id, first_name, last_name, manager_id, 2 version From hr.employees;
复制

现在，如果我想查询回我的主要层次结构，我的第一个本能是这样做:

Select level, first_name, last_name
  From emp_hierarchy h,
       hierarchy_versions v
 Where h.version = v.version
   And v.primary_hier = 'Y'
Start With h.employee_id = 100
Connect By Prior h.employee_id = h.manager_id;
复制

因为如您所见，我将hierarchy_versions表限制为仅返回一行 (primary_hier = 'Y')。然而，我发现这给了我完全错误的结果。相反，我必须这样做:

Select level, first_name, last_name
  From emp_hierarchy h,
       hierarchy_versions v
 Where h.version = v.version
   And v.primary_hier = 'Y'
Start With h.employee_id = 100
Connect By Prior h.employee_id = h.manager_id
       And Prior h.version = h.version;
复制

似乎Oracle没有执行查询来获取结果集 (即主要层次结构中的所有员工)and then开始将行链接在一起-相反，它似乎是连锁的before应用where子句中的谓词。对吗？

1) 因此，在具有connect by的多表查询中，我需要在connect by子句中引用哪些表 (或其键)？

2) 如果在我上面的例子中，我没有层次结构表上的员工名称，只有在另一个表上，即emp_names，因此我必须加入该表以获取名称，我现在 (理论上) 需要将其添加到连接中吗？

Create View emp_names As 
  Select employee_id, first_name, last_name From hr.employees;
复制

Select level, n.first_name, n.last_name
  From emp_hierarchy h,
       hierarchy_versions v, 
       emp_names n
 Where h.version = v.version
   And n.employee_id = h.employee_id
   And v.primary_hier = 'Y'
Start With h.employee_id = 100
Connect By Prior h.employee_id = h.manager_id
       And Prior h.version = h.version;
复制

假设emp_names表实际上包含多个名称-在不同的语言中:

Create or Replace View emp_names As 
  Select employee_id, first_name, last_name, 'UK' lang From hr.employees Union All
  Select employee_id, first_name, last_name || 'O', 'SPANISH' lang From hr.employees;
复制

我想查看我的等级与西班牙语名称:

Select level, n.first_name, n.last_name
  From emp_hierarchy h,
       hierarchy_versions v, 
       emp_names n
 Where h.version = v.version
   And n.employee_id = h.employee_id
   And v.primary_hier = 'Y'
   And n.lang='SPANISH'
Start With h.employee_id = 100
Connect By Prior h.employee_id = h.manager_id
       And Prior h.version = h.version;
复制

它又坏了。解决方法是添加 “And Prior n.lang = n.lang”。

所以它是我在哪里添加任何表是连接和约束的谓词 (即LANG上面)，我需要将这些列添加到连接通过？？

因此，Oracle数据库处理connect by的顺序是:

A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates.

The CONNECT BY condition is evaluated.

Any remaining WHERE clause predicates are evaluated.

https://docs.oracle.com/database/122/SQLRF/Hierarchical-Queries.htm#SQLRF52332

所以当你有:

Select level, first_name, last_name
  From emp_hierarchy h,
       hierarchy_versions v
 Where h.version = v.version
   And v.primary_hier = 'Y'
Start With h.employee_id = 100
Connect By Prior h.employee_id = h.manager_id;
复制

首先，它将emp_hierarchy_versions连接到hierarchy_versions。所以每个员工有两排。

然后它处理connect by。

此时，您有两行员工100:

EMPLOYEE_ID  FIRST_NAME   LAST_NAME    MANAGER_ID  VERSION  VERSION  PRIMARY_HIER  
100          Steven       King                     1        1        N             
100          Steven       King                     2        2        Y 
复制

他的每个直接报告都有两行:

EMPLOYEE_ID  FIRST_NAME   LAST_NAME    MANAGER_ID  VERSION  VERSION  PRIMARY_HIER
101          Neena        Kochhar      100         1        1        N             
101          Neena        Kochhar      100         2        2        Y             
102          Lex          De Haan      100         1        1        N             
102          Lex          De Haan      100         2        2        Y
... 
复制

您只加入manager_id等于以前的employee_id的行。因此，Neena的非主要行与Steven的两个行都结合在一起。她的初选也是如此。所以你有四排尼娜。依此类推。此时您的数据看起来像:

LEVEL  EMPLOYEE_ID  FIRST_NAME  LAST_NAME  MANAGER_ID  VERSION  VERSION  PRIMARY_HIER  
1      100          Steven      King                   2        2        Y             
1      100          Steven      King                   1        1        N             
2      101          Neena       Kochhar    100         2        2        Y             
2      101          Neena       Kochhar    100         1        1        N             
2      101          Neena       Kochhar    100         2        2        Y             
2      101          Neena       Kochhar    100         1        1        N
复制

一旦构建了树，数据库最终会过滤掉主不是 'Y' 的行。但是Neena仍然有两行，其中primary = 'Y'!这就是为什么你最终把所有的孩子都加倍。

如果您不确定操作的顺序，我发现使用rowstats和谓词查看执行计划很有帮助:

set serveroutput off
alter session set statistics_level = all;
Select level, h.*, v.*
  From emp_hierarchy h,
       hierarchy_versions v
 Where h.version = v.version
   And v.primary_hier = 'Y'
Start With h.employee_id = 100
Connect By Prior h.employee_id = h.manager_id
order by level, employee_id;

select *
from   table ( dbms_xplan.display_cursor (
    null,
    null,
    'ROWSTATS LAST +PREDICATE'
  ) );

PLAN_TABLE_OUTPUT                                                                                    
SQL_ID  40hvc9sm8t0jb, child number 1                                                                
-------------------------------------                                                                
Select level, h.*, v.*   From emp_hierarchy h,                                                       
hierarchy_versions v  Where h.version = v.version    And v.primary_hier                              
= 'Y' Start With h.employee_id = 100 Connect By Prior h.employee_id =                                
h.manager_id order by level, employee_id                                                             
                                                                                                     
Plan hash value: 1579310819                                                                          
                                                                                                     
---------------------------------------------------------------------------------------------------  
| Id  | Operation                                 | Name               | Starts | E-Rows | A-Rows |  
---------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                          |                    |      1 |        |    437 |  
|   1 |  SORT ORDER BY                            |                    |      1 |      2 |    437 |  
|*  2 |   FILTER                                  |                    |      1 |        |    437 |  
|*  3 |    CONNECT BY NO FILTERING WITH START-WITH|                    |      1 |        |    874 |  
|*  4 |     HASH JOIN                             |                    |      1 |      4 |    214 |  
|   5 |      VIEW                                 | HIERARCHY_VERSIONS |      1 |      2 |      2 |  
|   6 |       UNION-ALL                           |                    |      1 |        |      2 |  
|   7 |        FAST DUAL                          |                    |      1 |      1 |      1 |  
|   8 |        FAST DUAL                          |                    |      1 |      1 |      1 |  
|   9 |      VIEW                                 | EMP_HIERARCHY      |      1 |    214 |    214 |  
|  10 |       UNION-ALL                           |                    |      1 |        |    214 |  
|  11 |        TABLE ACCESS FULL                  | EMPLOYEES          |      1 |    107 |    107 |  
|  12 |        TABLE ACCESS FULL                  | EMPLOYEES          |      1 |    107 |    107 |  
---------------------------------------------------------------------------------------------------  
                                                                                                     
Predicate Information (identified by operation id):                                                  
---------------------------------------------------                                                  
                                                                                                     
   2 - filter("V"."PRIMARY_HIER"='Y')                                                                
   3 - access("H"."MANAGER_ID"=PRIOR NULL)                                                           
       filter("H"."EMPLOYEE_ID"=100)                                                                 
   4 - access("H"."VERSION"="V"."VERSION")
复制

在这里，您可以看到连接首先发生 (步骤4)，然后是连接 (步骤3)，最后是过滤 (步骤2)。查看A (虚拟) 行还可以帮助您查看自己是否在正确的轨道上。请注意，在步骤3有874行。比表上的要多!所以可能不是你想要的。

有几种方法可以解决这个问题。检查以前的版本是否与当前版本相匹配是一种方法。这阻止了Neena的两行与King的两行。只是那些版本相同的，停止加倍。

或者您可以加入子查询。然后通过结果连接:

select level,
       v.*
from (
    select h.employee_id,
           h.manager_id
    from emp_hierarchy h,
         hierarchy_versions v
    where h.version        = v.version
      and v.primary_hier   = 'Y'
  ) v
start with  employee_id = 100
connect by  prior employee_id = manager_id
order by 1, 2;

LEVEL  EMPLOYEE_ID  MANAGER_ID  
1      100                      
2      101          100         
2      102          100         
2      114          100         
2      120          100
... 
复制