暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Oracle “合并加入笛卡尔” 仅当我使用列别名时

Oracle “合并加入笛卡尔” 仅当我使用列别名时

ASKTOM 2019-07-12
265

问题描述

嗨
我在一个简单的查询中挣扎了将近一个星期。
主要问题是查询在服务器的特定db实例上运行 (我通常每天使用的db istance)
在解释计划输出中产生合并加入笛卡尔，而如果我从另一个db istance \ sever运行该查询，则执行计划向我显示不同的访问权限，在这种情况下，10分钟后的查询会给我一个输出。

这是查询:
--from Oracle 11.2.0.4.0
WITH first_event_year AS (
SELECT  DISTINCT key_paz,
        first_value(RI) over(PARTITION BY key_paz ORDER BY t_erog) AS fri
FROM    (
                SELECT key_ass, t_erog, ROWID AS ri
                FROM SC0029  /*synonim for a table out of the db istance the query is running (Oracle 12.1.0.2.0)*/
                WHERE anno = '2017'
                AND ( c_prest = '87.37.1' OR c_catamb  = 'C00270100' ) 
                AND C_REGAPP = '070'
                AND TIP_PRES = 'S'
        ) s,
        CORRELAZIONE K  /*synonim for a table out of the db istance the query is running (same db istance of SC0029)*/
WHERE S.key_ass = K.key_ass
)
--main query
SELECT  asl_dedotta,
        COUNT(*)
FROM    (
            SELECT  s2.c_comres  /*I get in this case only a field, but i could retrieve more fieds*/
            FROM    SC0029 s2, /*synonim for a table out of the db istance the query is running*/
                    first_event_year P
            WHERE s2.ROWID = p.fri
        ) Y,
        (
            SELECT codice, asl_dedotta
            FROM comuni     -- local table
            WHERE codreg = '070'
        ) C
WHERE Y.c_comres = C.codice
GROUP BY C.asl_dedotta
ORDER BY C.asl_dedotta

复制

仅考虑临时表 (first_event_year)，如果我运行explain计划工具，我看不到任何奇怪的东西，并且输出在大约10分钟内生成。
我说的是表 (SC0029) 中的60,000条记录，其中包含约200万条记录 (20M X年2010年)
但是如果我运行整个语句的解释计划工具，我会看到

Plan
10 SELECT STATEMENT  ALL_ROWS
Cost: 587.360; Bytes: 21.310; Cardinality: 5; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
Partition #: 0;       
 9 SORT ORDER BY  
 Projection: (#keys=1) NLSSORT("ASL_DEDOTTA",'nls_sort=''WEST_EUROPEAN''')[34], "ASL_DEDOTTA"[VARCHAR2,3], COUNT(*)[22]; 
 Cost: 587.360; Bytes: 21.310; Cardinality: 5; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
 Partition #: 0;      
  8 HASH GROUP BY  
  Projection: (#keys=1) "ASL_DEDOTTA"[VARCHAR2,3], COUNT(*)[22]; 
  Cost: 587.360; Bytes: 21.310; Cardinality: 5; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
  Partition #: 0;     
   7 NESTED LOOPS  
   Projection: (#keys=0) "ASL_DEDOTTA"[VARCHAR2,3]; 
   Cost: 672.152; Bytes: 1.467.641.548; Cardinality: 11.429.442; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
   Partition #: 0;    
    5 MERGE JOIN CARTESIAN  
    Projection: (#keys=0) "P"."FRI"[ROWID,4000], "CODICE"[VARCHAR2,6], "ASL_DEDOTTA"[VARCHAR2,3]; 
    Cost: 634; Bytes: 35.058.980; Cardinality: 8.732; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
    Partition #: 0;   
     2 VIEW ARS. 
     Projection: "P"."FRI"[ROWID,4000]; 
     Cost: 86; Bytes: 148.074; Cardinality: 37; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
     Partition #: 0;  
      1 REMOTE SERIAL_FROM_REMOTE  DBFNEW.REGLIG
      EXPLAIN PLAN SET STATEMENT_ID='E0D58D52' INTO "ARS"."SQLN_EXPLAIN_PLAN"@! FOR SELECT DISTINCT "A1"."KEY_PAZ",FIRST_VALUE("A2".ROWID) OVER ( PARTITION BY "A1"."KEY_PAZ" ORDER BY "A2"."T_EROG" RANGE  BETWEEN  UNBOUNDED  PRECEDING  AND  CURRENT ROW ) FROM "SISG"."SC0029" "A2","SISG"."CORRELAZIONE" "A1" WHERE "A2"."KEY_ASS"="A1"."KEY_ASS" AND "A2"."ANNO"='2017' AND ("A2"."C_PREST"='87.37.1' OR "A2"."C_CATAMB"='C00270100') AND "A2"."C_REGAPP"='070' AND "A2"."TIP_PRES"='S'
      Projection: "KEY_PAZ"[NUMBER,22], FIRST_VALUE(ROWID) OVER ( PARTITION BY "KEY_PAZ" ORDER BY "T_EROG" RANGE  BETWEEN  UNBOUNDED  PRECEDING  AND  CURRENT ROW )[4000]; 
      Cost: 0; Bytes: 0; Cardinality: 0; CPU Cost: 0; IO Cost: 0; 
      Partition #: 0; 
     4 BUFFER SORT  
     Projection: (#keys=0) "CODICE"[VARCHAR2,6], "ASL_DEDOTTA"[VARCHAR2,3]; 
     Cost: 634; Bytes: 3.068; Cardinality: 236; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
     Partition #: 0;  
      3 TABLE ACCESS FULL ARS.COMUNI 
      Filter: "CODREG"='070'; 
      Projection: "CODICE"[VARCHAR2,6], "ASL_DEDOTTA"[VARCHAR2,3]; 
      Cost: 15; Bytes: 3.068; Cardinality: 236; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
      Partition #: 0; 
    6 REMOTE SERIAL_FROM_REMOTE SC0029 DBFNEW.REGLIG
    SELECT ROWID,"C_COMRES" FROM "SISG"."SC0029" "S2" WHERE "C_COMRES"=:1 AND ROWID=:2
    Projection: "S2".ROWID[ROWID,4000], "S2"."C_COMRES"[VARCHAR2,6]; 
    Cost: 67; Bytes: 323.323; Cardinality: 1.309; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
    Partition #: 0;   

复制


有了 “合并加入笛卡尔”，查询不会在周末的有用时间抛出任何结果!

然后，我也尝试从其他服务器视图中反转查询。
为此，我必须授予COMUNI表的选择特权。
这是 “反向” 查询:
WITH first_event_year AS (
SELECT  DISTINCT key_paz,
        first_value(RI) over(PARTITION BY key_paz ORDER BY t_erog) AS fri
FROM    (
                SELECT key_ass, t_erog, ROWID AS ri
                FROM SC0029  --LOCAL TABLE NOW
                WHERE anno = '2017'
                AND ( c_prest = '87.37.1' OR c_catamb  = 'C00270100' ) 
                AND C_REGAPP = '070'
                AND TIP_PRES = 'S'
        ) s,
        CORRELAZIONE K  --LOCAL TABLE NOW
WHERE S.key_ass = K.key_ass
)

SELECT  asl_dedotta,
        COUNT(*)
FROM    (
            SELECT  s2.c_comres
            FROM    SC0029 s2,
                    first_event_year P
            WHERE s2.ROWID = p.fri
        ) Y,
        (
            SELECT codice, asl_dedotta
            FROM ARS.comuni@MY_DBLINK     /*table throught dblink*/
            WHERE codreg = '070'
        ) C
WHERE Y.c_comres = C.codice
GROUP BY C.asl_dedotta
ORDER BY C.asl_dedotta.

复制


以这种方式编写的查询不会生成任何合并加入笛卡尔，并在大约10分钟内结束。

但是 (对我来说) 一个星期后发现的非常奇怪的事情是，如果我收回第一个查询并更改查询行...
来自:
first_value(RI) over(PARTITION BY key_paz ORDER BY t_erog) AS fri
复制


致:
first_value(S.ROWID) over(PARTITION BY key_paz ORDER BY t_erog) AS fri
复制


相同的查询不会在大约10分钟内生成任何合并加入笛卡尔结束与这个执行计划:
Plan
9 SELECT STATEMENT  ALL_ROWS
Cost: 16.204; Bytes: 1.360; Cardinality: 5; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
Partition #: 0;      
 8 SORT ORDER BY  
 Projection: (#keys=1) NLSSORT("ASL_DEDOTTA",'nls_sort=''WEST_EUROPEAN''')[34], "ASL_DEDOTTA"[VARCHAR2,3], COUNT(*)[22]; 
 Cost: 16.204; Bytes: 1.360; Cardinality: 5; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
 Partition #: 0;     
  7 HASH GROUP BY  
  Projection: (#keys=1) "ASL_DEDOTTA"[VARCHAR2,3], COUNT(*)[22]; 
  Cost: 16.204; Bytes: 1.360; Cardinality: 5; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
  Partition #: 0;    
   6 HASH JOIN  
   Access: "P"."FRI"=("S2".ROWID); 
   Projection: (#keys=1) "ASL_DEDOTTA"[VARCHAR2,3]; 
   Cost: 15.957; Bytes: 621.761.536; Cardinality: 2.285.888; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
   Partition #: 0;   
    2 VIEW ARS. 
    Projection: "P"."FRI"[ROWID,10]; 
    Cost: 86; Bytes: 444; Cardinality: 37; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
    Partition #: 0;  
     1 REMOTE SERIAL_FROM_REMOTE  DBFNEW.REGLIG
     EXPLAIN PLAN SET STATEMENT_ID='E0D593CE' INTO "ARS"."SQLN_EXPLAIN_PLAN"@! FOR SELECT DISTINCT "A1"."KEY_PAZ",FIRST_VALUE("A2".ROWID) OVER ( PARTITION BY "A1"."KEY_PAZ" ORDER BY "A2"."T_EROG" RANGE  BETWEEN  UNBOUNDED  PRECEDING  AND  CURRENT ROW ) FROM "SISG"."SC0029" "A2","SISG"."CORRELAZIONE" "A1" WHERE "A2"."KEY_ASS"="A1"."KEY_ASS" AND "A2"."ANNO"='2017' AND ("A2"."C_PREST"='87.37.1' OR "A2"."C_CATAMB"='C00270100') AND "A2"."C_REGAPP"='070' AND "A2"."TIP_PRES"='S'
     Projection: "KEY_PAZ"[NUMBER,22], FIRST_VALUE("S".ROWID) OVER ( PARTITION BY "KEY_PAZ" ORDER BY "T_EROG" RANGE  BETWEEN  UNBOUNDED  PRECEDING  AND  CURRENT ROW )[10]; 
     Cost: 0; Bytes: 0; Cardinality: 0; CPU Cost: 0; IO Cost: 0; 
     Partition #: 0; 
    5 NESTED LOOPS  
    Projection: (#keys=0) "ASL_DEDOTTA"[VARCHAR2,3], "S2".ROWID[ROWID,4000]; 
    Cost: 62.406; Bytes: 1.606.300.020; Cardinality: 6.178.077; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
    Partition #: 0;  
     3 TABLE ACCESS FULL ARS.COMUNI 
     Filter: "CODREG"='070'; 
     Projection: "CODICE"[VARCHAR2,6], "ASL_DEDOTTA"[VARCHAR2,3]; 
     Cost: 16; Bytes: 3.068; Cardinality: 236; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
     Partition #: 0; 
     4 REMOTE SERIAL_FROM_REMOTE SC0029 DBFNEW.REGLIG
     SELECT ROWID,"C_COMRES" FROM "SISG"."SC0029" "S2" WHERE "C_COMRES"=:1
     Projection: "S2".ROWID[ROWID,4000], "S2"."C_COMRES"[VARCHAR2,6]; 
     Cost: 67; Bytes: 6.465.966; Cardinality: 26.178; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
     Partition #: 0;

复制


怎么可能是一个列别名改变这么重的执行计划？

比你提前

专家解答

在第一个查询中，数据库将first_event_year连接到comuni。

但是这些之间没有联系!所以它必须使用合并联接。

而在第二个查询中，它将comuni连接到sc0029。它确实有一个连接谓词。然后链接到first_event_year子查询。

我不确定为什么会这样!

检查数据库链接有很多限制。没有别名，我怀疑数据库不再能够推断FRI从子查询返回ROWID。导致合并联接。

无论如何，我看到你访问SC0029两次。

这真的有必要吗？

如果您可以更改查询，因此您只访问一次，不仅由于减少了表访问次数，您的查询可能会更快，优化器出错的机会也更少!
oracleasktom
文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论