逻辑读高的SQL请教

xteitxu

lfree 发表于 2020-11-9 19:36
SELECT SYS_CONNECT_BY_PATH(TREE.INDICATION_ID, '|#|') ID_PATH, SYS_CONNECT_BY_PATH(TREE.CN_NAME, '|# ...

谢谢版主回复。修改过的SQL查不出数据。原SQL是有数据的，我也看不出哪里的问题。感谢指导。新的执行计划：
------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                       |     2 |   536 |    28  (15)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING         |                       |       |       |            |          |
|*  2 |   HASH JOIN                        |                       |     1 |   171 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID     | BD_INDICATION_INFO    |     1 |    52 |     2   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN              | PK_BD_INDICATION_INFO |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID     | BD_INDICATION_TREE    |     1 |   119 |     2   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION TO ROWIDS    |                       |       |       |            |          |
|   7 |      BITMAP AND                    |                       |       |       |            |          |
|   8 |       BITMAP CONVERSION FROM ROWIDS|                       |       |       |            |          |
|*  9 |        INDEX RANGE SCAN            | IDX_BIT_INDICATION_ID |    22 |       |     1   (0)| 00:00:01 |
|  10 |       BITMAP CONVERSION FROM ROWIDS|                       |       |       |            |          |
|* 11 |        INDEX RANGE SCAN            | IDX_BIT_PID           |    22 |       |     1   (0)| 00:00:01 |
|  12 |   NESTED LOOPS                     |                       |     1 |   193 |    22  (10)| 00:00:01 |
|  13 |    NESTED LOOPS                    |                       |     1 |    74 |     7  (15)| 00:00:01 |
|  14 |     TABLE ACCESS BY INDEX ROWID    | BD_INDICATION_INFO    |     1 |    52 |     2   (0)| 00:00:01 |
|* 15 |      INDEX UNIQUE SCAN             | PK_BD_INDICATION_INFO |     1 |       |     1   (0)| 00:00:01 |
|  16 |     CONNECT BY PUMP                |                       |       |       |            |          |
|  17 |    TABLE ACCESS BY INDEX ROWID     | BD_INDICATION_TREE    |     1 |   119 |    22  (10)| 00:00:01 |
|  18 |     BITMAP CONVERSION TO ROWIDS    |                       |       |       |            |          |
|  19 |      BITMAP AND                    |                       |       |       |            |          |
|  20 |       BITMAP CONVERSION FROM ROWIDS|                       |       |       |            |          |
|* 21 |        INDEX RANGE SCAN            | IDX_BIT_INDICATION_ID |    22 |       |     1   (0)| 00:00:01 |
|  22 |       BITMAP CONVERSION FROM ROWIDS|                       |       |       |            |          |
|* 23 |        INDEX RANGE SCAN            | IDX_BIT_PID           |    22 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

   1 - access("BIT"."PID"=PRIOR "BIT"."ID")
   2 - access("BII"."ID"="BIT"."INDICATION_ID")
   4 - access("BII"."ID"=U'1201876611926851663')
   9 - access("BIT"."INDICATION_ID"=U'1201876611926851663')
  11 - access("BIT"."PID"=U'-1')
  15 - access("BII"."ID"=U'1201876611926851663')
  21 - access("BIT"."INDICATION_ID"=U'1201876611926851663')
       filter("BII"."ID"="BIT"."INDICATION_ID")
  23 - access("BIT"."PID"="connect$_by$_pump$_004"."PRIOR ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        406  bytes sent via SQL*Net to client
        513  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed

ZALBB

我对 START WITH TREE.PID = '-1' CONNECT BY TREE.PID = PRIOR ID 不熟悉，下面是我的一点理解：

--原始语句
SELECT SYS_CONNECT_BY_PATH(TREE.INDICATION_ID, '|#|') ID_PATH,
       SYS_CONNECT_BY_PATH(TREE.CN_NAME, '|#|') NAME_PATH
FROM
( SELECT ID, PID, INDICATION_ID, EN_NAME,
  (SELECT CN_NAME FROM BD_INDICATION_INFO BII WHERE BII. ID = BIT.INDICATION_ID) AS CN_NAME FROM BD_INDICATION_TREE BIT) TREE
  WHERE TREE.INDICATION_ID IN('8766119') START WITH TREE.PID = '-1' CONNECT BY TREE.PID = PRIOR ID

---解释：
关于 start with, connect by 语句，我理解的执行计划：语句一开始并不处理 WHERE 段的过滤条件，而是先从  start with 开始，
接着再走 connect by 关系，得到结果集后，再对此结果集走  where 段的条件，这就是为何上面的执行计划的最后一段，会是一团：

|*  9 |    HASH JOIN                  |                       |     10 |   5731 |  13481 |00:00:00.33 |   12440 |   958K|   958K| 1362K (0)|
|  10 |     CONNECT BY PUMP           |                       |     10 |        |  13509 |00:00:00.01 |       0 |       |       |          |
|  11 |     TABLE ACCESS FULL         | BD_INDICATION_TREE    |     10 |  13509 |    135K|00:00:00.09 |   12440 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------
因为得在表里搜索/实现这个关系： CONNECT BY TREE.PID = PRIOR ID，此时对两关联字段走 HASH JOIN，应该是最好的选择，

为何不先走 where 段过滤条件，因为走了 where 段的过滤条件，得到的结果集，无法推导出符合这个关系 START WITH TREE.PID = '-1' CONNECT BY TREE.PID = PRIOR ID
的数据（可以把这关系看作一个过滤条件），反之，从 START WITH TREE.PID = '-1' CONNECT BY TREE.PID = PRIOR ID 条件得到的结果集，却可以再用来走
where 段的条件。

但我看不懂第7, 8步骤：

|   7 |    TABLE ACCESS BY INDEX ROWID| BD_INDICATION_INFO    |  13367 |      1 |  13367 |00:00:00.09 |   26740 |       |       |          |
|*  8 |     INDEX UNIQUE SCAN         | PK_BD_INDICATION_INFO |  13367 |      1 |  13367 |00:00:00.04 |   13373 |       |       |          |

这是在做什么操作？为何会这么消耗逻辑读？

另外，依照ORACLE的官文对执行计划的执行顺序的解释：先里后外，先上后下，得到的执行顺序是，4,3,6,5,8,7,11,10,9。
但我理解为：应该先 11,10,9，再后面的436587， 也就是，先执行 START WITH TREE.PID = '-1' CONNECT BY TREE.PID = PRIOR ID
再：WHERE TREE.INDICATION_ID IN('8766119')，最后，标量子查询及 SYS_CONNECT_BY_PATH 函数。感觉遇上标量子查询语句时就看不懂。

请指正。

xteitxu

ZALBB 发表于 2020-11-10 09:33
我对 START WITH TREE.PID = '-1' CONNECT BY TREE.PID = PRIOR ID 不熟悉，下面是我的一点理解：--原始语 ...

谢谢福哥的分析，SQL是开发写的，从AWR里看到逻辑读太高，想进行一下优化。start with, connect by用法我也不太懂，来些求助大神。

xteitxu

不要沉啊。。。。

xteitxu

ZALBB 发表于 2020-11-10 09:33
我对 START WITH TREE.PID = '-1' CONNECT BY TREE.PID = PRIOR ID 不熟悉，下面是我的一点理解：--原始语 ...

我把SQL改动了一下，执行计划变成了HASH JOIN，逻辑读有所下降。这应该是最好的选择吧。
SELECT SYS_CONNECT_BY_PATH(TREE.INDICATION_ID, '|#|') ID_PATH,
       SYS_CONNECT_BY_PATH(TREE.CN_NAME, '|#|') NAME_PATH
  FROM (SELECT BIT.ID,
               BIT.PID,
               BIT.INDICATION_ID,
               BIT.EN_NAME,
               BII.CN_NAME CN_NAME
          FROM BD_INDICATION_TREE BIT,
               BD_INDICATION_INFO BII
         WHERE BII.ID = BIT.INDICATION_ID
           ) TREE
           where TREE.INDICATION_ID IN ('1201876611926851663')
START WITH TREE.PID = '-1'
CONNECT BY TREE.PID = PRIOR ID;

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                    |  1818K|   464M| 68932 (100)| 00:13:48 |
|*  1 |  FILTER                                  |                    |       |       |            |          |
|*  2 |   CONNECT BY NO FILTERING WITH START-WITH|                    |       |       |            |          |
|*  3 |    HASH JOIN                             |                    |  1818K|   296M|   518   (2)| 00:00:07 |
|   4 |     TABLE ACCESS FULL                    | BD_INDICATION_INFO |  5990 |   304K|   170   (0)| 00:00:03 |
|   5 |     TABLE ACCESS FULL                    | BD_INDICATION_TREE | 13509 |  1569K|   341   (0)| 00:00:05 |
---------------------------------------------------------------------------------------------------------------

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

   1 - filter("BIT"."INDICATION_ID"=U'1201876611926851663')
   2 - access("BIT"."PID"=PRIOR "BIT"."ID")
       filter("BIT"."PID"=U'-1')
   3 - access("BII"."ID"="BIT"."INDICATION_ID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1870  consistent gets
          0  physical reads
          0  redo size
        897  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed   

ZALBB

xteitxu 发表于 2020-11-10 15:00
我把SQL改动了一下，执行计划变成了HASH JOIN，逻辑读有所下降。这应该是最好的选择吧。SELECT SYS_CONNECT ...

看到了，你修改了标量子查询，原先这个怎么走的？

xteitxu

ZALBB 发表于 2020-11-10 15:06
看到了，你修改了标量子查询，原先这个怎么走的？

没太明白哪个之前的计划，最早之前的执行计划就是刚发贴的那个。我在lfree修改后的SQL上做了调整，就是现在最终的执行计划了。 这么修改标量子查询，应该与之前 的SQL是等价是的吧？目前查询是没问题。

ZALBB

xteitxu 发表于 2020-11-10 16:37
没太明白哪个之前的计划，最早之前的执行计划就是刚发贴的那个。我在lfree修改后的SQL上做了调整，就是现在 ...

不等价，标量子查询改到 FROM 主语句里，得和主表 LEFT JOIN，也就是，不管它有无值，都得匹配，没值就用空行和主表记录匹配，你上面用了 INNER JOIN，当不匹配时，过滤掉主表的一些数据了，

xteitxu

ZALBB 发表于 2020-11-10 19:33
不等价，标量子查询改到 FROM 主语句里，得和主表 LEFT JOIN，也就是，不管它有无值，都得匹配，没值就用空 ...

谢谢福哥   看来我这个问题还是没有彻底解决。

xteitxu

快周末了，顶一下这个问题