Oracle Connect by和哈希连接

Question

我有一个问题1：

select *
from (
select 'null' parentcode, u.unititemcode childcode, null unititempartnbr, null partoid,
null proccode, null unititemtype, null qualcat, null unititemgm2, null rsncode
from wi_unititem u where
u.prodproccode='PM11' and
u.prodendtime>sysdate-10
union all
select p.unititemcode parentcode, u.unititemcode childcode, 0 unititempartnbr, p.oid partoid,
p.proccode, u.unititemtype, u.qualcat, u.unititemgm2, u.rsncode
from wi_uitempart p, wi_unititem u where
p.unititemparttype='USEDSET' and
u.prodproccode=p.proccode and
u.setid=p.setid    
) m
where m.unititemtype<>'SKID'
start with m.parentcode='null'
connect by prior m.childcode=m.parentcode

解释查询1的计划：

SELECT STATEMENT  ALL_ROWSCost: 64  Bytes: 2 745  Cardinality: 9                                    
23 FILTER                               
    22 CONNECT BY WITH FILTERING                            
        9 VIEW TIPSMSY. Cost: 12  Bytes: 610  Cardinality: 2                        
            8 UNION-ALL                     
                2 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UNITITEM Cost: 4  Bytes: 29  Cardinality: 1                  
                    1 INDEX RANGE SCAN INDEX TIPSMSY.WI_UNITITEM_PROCCODE_ENDTIME Cost: 3  Cardinality: 1           
                7 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UNITITEM Cost: 3  Bytes: 46  Cardinality: 1                  
                    6 NESTED LOOPS  Cost: 8  Bytes: 113  Cardinality: 1             
                        4 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UITEMPART Cost: 5  Bytes: 67  Cardinality: 1         
                            3 INDEX RANGE SCAN INDEX (UNIQUE) TIPSMSY.WI_UITEMPART_PK Cost: 4  Cardinality: 1   
                        5 INDEX RANGE SCAN INDEX TIPSMSY.WI_UNITITEM_SETID Cost: 2  Cardinality: 1          
        21 VIEW TIPSMSY. Cost: 64  Bytes: 2 745  Cardinality: 9                         
            20 UNION-ALL                    
                12 FILTER               
                    11 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UNITITEM Cost: 4  Bytes: 29  Cardinality: 1             
                        10 INDEX RANGE SCAN INDEX TIPSMSY.WI_UNITITEM_PROCCODE_ENDTIME Cost: 3  Cardinality: 1          
                19 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UNITITEM Cost: 3  Bytes: 46  Cardinality: 1                 
                    18 NESTED LOOPS  Cost: 60  Bytes: 904  Cardinality: 8           
                        16 NESTED LOOPS         
                            13 CONNECT BY PUMP      
                            15 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UITEMPART Cost: 18  Bytes: 938  Cardinality: 14     
                                14 INDEX RANGE SCAN INDEX (UNIQUE) TIPSMSY.WI_UITEMPART_PK Cost: 4  Cardinality: 43  
                        17 INDEX RANGE SCAN INDEX TIPSMSY.WI_UNITITEM_SETID Cost: 2  Cardinality: 1         

和查询2：

select *
from (
select 'null' parentcode, u.unititemcode childcode, null unititempartnbr, null partoid,
null proccode, null unititemtype, null qualcat, null unititemgm2, null rsncode
from wi_unititem u where
u.prodproccode='PM11' and
u.prodendtime>sysdate-20
union all
select p.unititemcode parentcode, u.unititemcode childcode, 0 unititempartnbr, p.oid partoid,
p.proccode, u.unititemtype, u.qualcat, u.unititemgm2, u.rsncode
from wi_uitempart p, wi_unititem u where
p.unititemparttype='USEDSET' and
u.prodproccode=p.proccode and
u.setid=p.setid    
) m
where m.unititemtype<>'SKID'
start with m.parentcode='null'
connect by prior m.childcode=m.parentcode

解释查询2的计划：

SELECT STATEMENT  ALL_ROWSCost: 122  Bytes: 82 655  Cardinality: 271                                    
23 FILTER                               
    22 CONNECT BY WITH FILTERING                            
        9 VIEW TIPSMSY. Cost: 70  Bytes: 80 520  Cardinality: 264                       
            8 UNION-ALL                     
                2 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UNITITEM Cost: 62  Bytes: 7 627  Cardinality: 263                
                    1 INDEX RANGE SCAN INDEX TIPSMSY.WI_UNITITEM_PROCCODE_ENDTIME Cost: 4  Cardinality: 263             
                7 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UNITITEM Cost: 3  Bytes: 46  Cardinality: 1                  
                    6 NESTED LOOPS  Cost: 8  Bytes: 113  Cardinality: 1             
                        4 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UITEMPART Cost: 5  Bytes: 67  Cardinality: 1         
                            3 INDEX RANGE SCAN INDEX (UNIQUE) TIPSMSY.WI_UITEMPART_PK Cost: 4  Cardinality: 1   
                        5 INDEX RANGE SCAN INDEX TIPSMSY.WI_UNITITEM_SETID Cost: 2  Cardinality: 1          
        21 VIEW TIPSMSY. Cost: 122  Bytes: 82 655  Cardinality: 271                         
            20 UNION-ALL                    
                12 FILTER               
                    11 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UNITITEM Cost: 62  Bytes: 7 627  Cardinality: 263           
                        10 INDEX RANGE SCAN INDEX TIPSMSY.WI_UNITITEM_PROCCODE_ENDTIME Cost: 4  Cardinality: 263        
                19 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UNITITEM Cost: 3  Bytes: 46  Cardinality: 1                 
                    18 NESTED LOOPS  Cost: 60  Bytes: 904  Cardinality: 8           
                        16 HASH JOIN        
                            13 CONNECT BY PUMP      
                            15 TABLE ACCESS BY INDEX ROWID TABLE TIPSMSY.WI_UITEMPART Cost: 18  Bytes: 938  Cardinality: 14     
                                14 INDEX FULL SCAN INDEX (UNIQUE) TIPSMSY.WI_UITEMPART_PK Cost: 4  Cardinality: 43  
                        17 INDEX RANGE SCAN INDEX TIPSMSY.WI_UNITITEM_SETID Cost: 2  Cardinality: 1         

查询1包含"sysdate-10"，查询2包含"sysdate-20"，这是唯一的区别，查询1运行得很快，查询2运行得很慢。

如果我们比较执行计划，则可以在以14和16开头的行中发现差异:查询1使用嵌套循环连接+索引范围扫描查询2使用散列连接+索引完全扫描

是否也可以对查询2使用查询1的执行计划？

Answer 1

复制执行计划的最简单方法是使用完整的大纲提示集。

使用这两条语句生成包含大纲数据的解释计划。这也是生成解释计划的最佳方法，原因在我的答案here中描述了几个原因。

explain plan for select * from dual; --Add your real query here.
select * from table(dbms_xplan.display(format => '+outline'));

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "DUAL"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.1.0.2')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

使用大纲数据作为提示，以保证执行计划保持不变。尽管“保证”可能是一个强烈的词；但这些未记录的提示可能在不同的版本中不起作用，或者如果查询在语义上不同。

select 
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "DUAL"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.1.0.2')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
* from dual;

这应该是可行的，但有点丑陋。理想情况下，您希望找到Oracle做出糟糕决策的根本原因-调查估计基数与实际基数、统计数据等。但是有很多方法可以做到这一点，这需要很长时间。作为一种折衷方案，您可能希望使用提示，并尝试将其缩小到修复性能问题所需的一两个提示。