树形查询SQL优化一例

最新推荐文章于 2024-05-27 15:51:17 发布
最新推荐文章于 2024-05-27 15:51:17 发布
阅读量3.3k 收藏
点赞数 1
分类专栏： Oracle sql优化 Oracle SQL 优化 文章标签： 树形查询 SQL优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wanbin6470398/article/details/78707000
版权

上周五一哥们发了条SQL，让我看看，代码如下：

SELECT COUNT(1)
  FROM (select m.sheet_id
          from cpm_main_sheet_history m, cpm_service_warn_config s
         where m.sheet_type_id in
               (select t.row_id
                  from  tbl_class_trees   t
                 start with t.row_id = s.sheet_type_id
                connect by t.parent_row_id = prior t.row_id)
           and m.service_type  in
               (select tt.row_id
                  from  tbl_class_trees  tt
                 start with tt.row_id = s.business_type_id
                connect by tt.parent_row_id = prior tt.row_id)
           and m.accept_time >=
               TO_CHAR(SYSDATE - time_interval / 24, 'yyyy-mm-dd hh24:mi:ss')
           and s.row_id = 'AS170904165251'
           and m.no_area in ('0000')) c__

--执行计划
 PLAN_TABLE_OUTPUT
Plan hash value: 2710926849
 
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                         |     1 |   143 | 96246   (1)| 00:19:15 |
|*  1 |  FILTER                                    |                         |       |       |            |          |
|   2 |   NESTED LOOPS                             |                         |  1681 |   234K|   746   (1)| 00:00:09 |
|   3 |    TABLE ACCESS BY INDEX ROWID             | CPM_SERVICE_WARN_CONFIG |     1 |    56 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN                      | PK_CONFIG_ROW_ID        |     1 |       |     0   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID             | CPM_MAIN_SHEET_HISTORY  |  1681 |   142K|   745   (1)| 00:00:09 |
|*  6 |     INDEX RANGE SCAN                       | IDX_CPM_NO_AREA_TIME1   |   449 |       |    62   (0)| 00:00:01 |
|*  7 |   FILTER                                   |                         |       |       |            |          |
|*  8 |    CONNECT BY NO FILTERING WITH SW (UNIQUE)|                         |       |       |            |          |
|   9 |     TABLE ACCESS FULL                      | TBL_CLASS_TREES         |  9527 |   493K|   113   (0)| 00:00:02 |
|* 10 |   FILTER                                   |                         |       |       |            |          |
|* 11 |    CONNECT BY NO FILTERING WITH SW (UNIQUE)|                         |       |       |            |          |
|  12 |     TABLE ACCESS FULL                      | TBL_CLASS_TREES         |  9527 |   493K|   113   (0)| 00:00:02 |
----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( EXISTS (SELECT 0 FROM "TBL_CLASS_TREES" "T" WHERE "T"."ROW_ID"=:B1 START WITH "T"."ROW_ID"=:B2 
              CONNECT BY "T"."PARENT_ROW_ID"=PRIOR "T"."ROW_ID") AND  EXISTS (SELECT 0 FROM "TBL_CLASS_TREES" "TT" WHERE 
              "TT"."ROW_ID"=:B3 START WITH "TT"."ROW_ID"=:B4 CONNECT BY "TT"."PARENT_ROW_ID"=PRIOR "TT"."ROW_ID"))
   4 - access("S"."ROW_ID"='AS170904165251')
   6 - access("M"."ACCEPT_TIME">=TO_CHAR(SYSDATE@!-"TIME_INTERVAL"/24,'yyyy-mm-dd hh24:mi:ss') AND 
              "M"."NO_AREA"='0000' AND "M"."ACCEPT_TIME" IS NOT NULL)
       filter("M"."NO_AREA"='0000')
   7 - filter("T"."ROW_ID"=:B1)
   8 - access("T"."PARENT_ROW_ID"=PRIOR "T"."ROW_ID")
       filter("T"."ROW_ID"=:B1)
  10 - filter("TT"."ROW_ID"=:B1)
  11 - access("TT"."PARENT_ROW_ID"=PRIOR "TT"."ROW_ID")
       filter("TT"."ROW_ID"=:B1)

SQL优化前：

耗时：20s
count(1)返回: 147条数据

分析执行计划，执行计划中有filter关键字且有3个子级，这种sql是最容易引起性能问题的，所以第一时间是反应是sql有没有走索引，能不能改写。

尝试1：

建索引优化：

在TBL_CLASS_TREES表(row_id,parent_row_id)上建索引
执行计划：
PLAN_TABLE_OUTPUT
Plan hash value: 135779572
 
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                         |     1 |   155 | 34147   (1)| 00:06:50 |
|*  1 |  FILTER                                    |                         |       |       |            |          |
|   2 |   NESTED LOOPS                             |                         |  3021 |   457K|   816   (1)| 00:00:10 |
|   3 |    TABLE ACCESS BY INDEX ROWID             | CPM_SERVICE_WARN_CONFIG |     1 |    62 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN                      | PK_CONFIG_ROW_ID        |     1 |       |     0   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID             | CPM_MAIN_SHEET_HISTORY  |  3021 |   274K|   815   (1)| 00:00:10 |
|*  6 |     INDEX RANGE SCAN                       | IDX_CPM_NO_AREA_TIME1   |   563 |       |   136   (0)| 00:00:02 |
|*  7 |   FILTER                                   |                         |       |       |            |          |
|*  8 |    CONNECT BY NO FILTERING WITH SW (UNIQUE)|                         |       |       |            |          |
|   9 |     INDEX FAST FULL SCAN                   | IDX_ROW_ID              |  9527 |   493K|    20   (0)| 00:00:01 |
|* 10 |   FILTER                                   |                         |       |       |            |          |
|* 11 |    CONNECT BY NO FILTERING WITH SW (UNIQUE)|                         |       |       |            |          |
|  12 |     INDEX FAST FULL SCAN                   | IDX_ROW_ID              |  9527 |   493K|    20   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( EXISTS (SELECT 0 FROM "TBL_CLASS_TREES" "T" WHERE "T"."ROW_ID"=:B1 START WITH "T"."ROW_ID"=:B2 
              CONNECT BY "T"."PARENT_ROW_ID"=PRIOR "T"."ROW_ID") AND  EXISTS (SELECT 0 FROM "TBL_CLASS_TREES" "TT" WHERE 
              "TT"."ROW_ID"=:B3 START WITH "TT"."ROW_ID"=:B4 CONNECT BY "TT"."PARENT_ROW_ID"=PRIOR "TT"."ROW_ID"))
   4 - access("S"."ROW_ID"='AS170904165251')
   6 - access("M"."ACCEPT_TIME">=TO_CHAR(SYSDATE@!-"TIME_INTERVAL"/24,'yyyy-mm-dd hh24:mi:ss') AND 
              "M"."NO_AREA"='0000' AND "M"."ACCEPT_TIME" IS NOT NULL)
       filter("M"."NO_AREA"='0000')
   7 - filter("T"."ROW_ID"=:B1)
   8 - access("T"."PARENT_ROW_ID"=PRIOR "T"."ROW_ID")
       filter("T"."ROW_ID"=:B1)
  10 - filter("TT"."ROW_ID"=:B1)
  11 - access("TT"."PARENT_ROW_ID"=PRIOR "TT"."ROW_ID")
       filter("TT"."ROW_ID"=:B1)

--效果还是一样慢，此优化失败。

尝试2：

利用with改写sql优化

with t as (select /*+ materialize */ row_id,parent_row_id from tbl_class_trees)
SELECT COUNT(1)
  FROM (select m.sheet_id
          from cpm_main_sheet_history m,cpm_service_warn_config s
         where m.sheet_type_id in
               (select t.row_id
                  from  t
                 start with t.row_id = s.sheet_type_id
                connect by t.parent_row_id = prior t.row_id)
           and m.service_type  in
               (select t.row_id
                  from t
                 start with t.row_id = s.business_type_id
                connect by t.parent_row_id = prior t.row_id)
           and m.accept_time >=
               TO_CHAR(SYSDATE - time_interval / 24, 'yyyy-mm-dd hh24:mi:ss')
           and s.row_id = 'AS170904165251'
           and m.no_area in ('0000')) c__

--效果还是一样慢，此优化失败。

再次分析原SQL执行计划：

id=8,id=11的执行计划关键词是：CONNECT BY NO FILTERING WITH SW (UNIQUE)。
这个为树形查询在11g中的新特性，尝试让sql不使用这个新特性。

于是使用以下hint：/*+ connect_by_filtering */  进行优化：

SELECT COUNT(1)
  FROM (select m.sheet_id
          from cpm_main_sheet_history m, cpm_service_warn_config s
         where m.sheet_type_id in
               (select /*+ connect_by_filtering */ t.row_id
                  from  tbl_class_trees   t
                 start with t.row_id = s.sheet_type_id
                connect by t.parent_row_id = prior t.row_id)
           and m.service_type  in
               (select /*+ connect_by_filtering */ tt.row_id
                  from  tbl_class_trees  tt
                 start with tt.row_id = s.business_type_id
                connect by tt.parent_row_id = prior tt.row_id)
           and m.accept_time >=
               TO_CHAR(SYSDATE - time_interval / 24, 'yyyy-mm-dd hh24:mi:ss')
           and s.row_id = 'AS170904165251'
           and m.no_area in ('0000')) c__  

PLAN_TABLE_OUTPUT
Plan hash value: 2824841339
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         |     1 |   246 |   188K  (1)| 00:37:47 |
|*  1 |  FILTER                        |                         |       |       |            |          |
|   2 |   NESTED LOOPS                 |                         |  3021 |   725K|   816   (1)| 00:00:10 |
|   3 |    TABLE ACCESS BY INDEX ROWID | CPM_SERVICE_WARN_CONFIG |     1 |   106 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN          | PK_CONFIG_ROW_ID        |     1 |       |     0   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID | CPM_MAIN_SHEET_HISTORY  |  3021 |   413K|   815   (1)| 00:00:10 |
|*  6 |     INDEX RANGE SCAN           | IDX_CPM_NO_AREA_TIME1   |   563 |       |   136   (0)| 00:00:02 |
|*  7 |   FILTER                       |                         |       |       |            |          |
|*  8 |    CONNECT BY WITH FILTERING   |                         |       |       |            |          |
|   9 |     TABLE ACCESS BY INDEX ROWID| TBL_CLASS_TREES         |     1 |   107 |     2   (0)| 00:00:01 |
|* 10 |      INDEX UNIQUE SCAN         | PK_TBL_CLASS_TREESS     |     1 |       |     1   (0)| 00:00:01 |
|* 11 |     HASH JOIN                  |                         |       |       |            |          |
|  12 |      CONNECT BY PUMP           |                         |       |       |            |          |
|  13 |      TABLE ACCESS FULL         | TBL_CLASS_TREES         |     6 |   318 |   113   (0)| 00:00:02 |
|* 14 |   FILTER                       |                         |       |       |            |          |
|* 15 |    CONNECT BY WITH FILTERING   |                         |       |       |            |          |
|  16 |     TABLE ACCESS BY INDEX ROWID| TBL_CLASS_TREES         |     1 |   107 |     2   (0)| 00:00:01 |
|* 17 |      INDEX UNIQUE SCAN         | PK_TBL_CLASS_TREESS     |     1 |       |     1   (0)| 00:00:01 |
|* 18 |     HASH JOIN                  |                         |       |       |            |          |
|  19 |      CONNECT BY PUMP           |                         |       |       |            |          |
|  20 |      TABLE ACCESS FULL         | TBL_CLASS_TREES         |     6 |   318 |   113   (0)| 00:00:02 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( EXISTS (SELECT /*+ CONNECT_BY_FILTERING */ 0 FROM "TBL_CLASS_TREES" "T" WHERE 
              "T"."ROW_ID"=:B1 START WITH "T"."ROW_ID"=:B2) AND  EXISTS (SELECT /*+ CONNECT_BY_FILTERING */ 0 
              FROM "TBL_CLASS_TREES" "TT" WHERE "TT"."ROW_ID"=:B3 START WITH "TT"."ROW_ID"=:B4))
   4 - access("S"."ROW_ID"='AS170904165251')
   6 - access("M"."ACCEPT_TIME">=TO_CHAR(SYSDATE@!-"TIME_INTERVAL"/24,'yyyy-mm-dd hh24:mi:ss') 
              AND "M"."NO_AREA"='0000' AND "M"."ACCEPT_TIME" IS NOT NULL)
       filter("M"."NO_AREA"='0000')
   7 - filter("T"."ROW_ID"=:B1)
   8 - access("T"."PARENT_ROW_ID"=PRIOR "T"."ROW_ID")
  10 - access("T"."ROW_ID"=:B1)
  11 - access("T"."PARENT_ROW_ID"=PRIOR "T"."ROW_ID")
  14 - filter("TT"."ROW_ID"=:B1)
  15 - access("TT"."PARENT_ROW_ID"=PRIOR "TT"."ROW_ID")
  17 - access("TT"."ROW_ID"=:B1)
  18 - access("TT"."PARENT_ROW_ID"=PRIOR "TT"."ROW_ID")

--优化后，SQL能在5s返回结果
DB-Engineer
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
多表查询SQL 语句优化
张远山的专栏
07-03 1万+
    数据多个表关联查询时，由于表之间的连接关系(内连接、外连接、交叉连接)，导致数据库服务器常常从几万条甚至更多的数据记录中查找符合条件的记录，如果sql查询语句设计不好查询的复杂度就会直线上升，甚至是指数级上升，导致查询时间长甚至失去相应，这里讲两种从sql语句优化查询的方法。1.把外连接变成交叉连接或内连接(对不起，在写改良例子的时候发现这种方法并不能改善查询的速度，这是一个错误)：
SQL查询优化
weixin_45819075的博客
05-16 164
一、为什么要对SQL进行优化 我们开发项目上线初期，由于业务数据量相对较少，一些SQL的执行效率对程序运行效率的影响不太明显，而开发和运维人员也无法判断SQL对程序的运行效率有多大，故很少针对SQL进行专门的优化，而随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。 二、SQL优化的一些方法 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行
Sql性能优化梳理
weixin_34194087的博客
09-07 166
前言 本文主要针对的是关系型数据数据库MySql。键值类数据库可以参考最简大数据Redis。先简单梳理下Mysql的基本概念，然后分创建时和查询时这两个阶段的优化展开。 1.0 基本概念简述 1.1 逻辑架构 第一层：客户端通过连接服务，将要执行的sql指令传输过来 第二层：服务器解析并优化sql，生成最终的执行计划并执行 第三层：存储引擎，负责数据的储存和提取 1.2 锁 数据库通过锁机制...
关于树型结构的SQL查询优化
The Shio的博客
06-18 821
如何修改使得不要嵌套这么多的Id也能实现该操作。 弊端 如果区域表的数据节点经常改变的话,那需要额外维护其他表和区域表的AreaCode。
数据库语法树优化
最新发布
naozibuok的博客
05-27 996
where表示的条件可以分为两部分，一部分是题目真正想查询的结果，另一部分是多表连接的条件，where语句强调的是条件，也就是选择，章节一中说到。如果题目要求用等值连接，或者题目给出的关系代数表达式用的等值连接，那么画图也要用等值连接。最后则剩下关系表的连接，表的连接在语法树中笛卡尔积，符号用 x 表示。关系R上的投影是从R中选择出若干属性列组成新的关系。在对应关系模式前，添加题目查询所需的列属性和连接条件的投影。把题目想查询的结果的选择条件移动到对应关系模式前。把表的连接条件拆开，移动到对应关系模式前。
超级详细的查询树优化！数据库笔记GET！！！
qq_43103778的博客
06-11 1万+
超级详细的查询树优化！数据库笔记GET！！！ 主要内容数据库 代数优化 查询树优化 等价变换规则和启发式规则
数据库树形结构怎么查 四种方法解决
weixin_46757028的博客
04-27 7241
2.一次性全部查询出来，用Java代码实现数据的树形分解。2.2 树形结构可能变化，采用数据库的递归进行查询。注：以上Java代码粘贴自网络。1.使SQL实现树形查询。2.Java代码实现。
树形结构查询
weixin_36870769的博客
01-11 2464
java后端返回树形结构的数据
Mysql树形递归查询的实现方法
09-09
在MySQL中实现树形递归查询，由于MySQL本身并不直接支持类似于Oracle的`START WITH ... CONNECT BY`语法，因此需要采用其他策略。通常，我们可以利用自定义函数或递归查询的方式来处理树形结构数据。以下将详细介绍...
BBS.zip_jsp 树形
09-22
【标题】"BBS.zip_jsp 树形" 指的是一个基于JSP技术构建的论坛系统，该系统能够以树形结构展示用户发帖和回帖的内容，同时提供了后台管理功能，使得管理员可以对论坛进行有效维护。 在JSP（JavaServer Pages）技术...
无限级读取子类树形显示.zip
05-09
总的来说，"无限级读取子类树形显示"涉及到数据库设计、SQL查询、C#编程、前端展示和异步加载等多个环节，理解并掌握这些技术对于开发具有层级关系的系统至关重要。在实践中，需要综合考虑性能、用户体验和代码可...
树形结构的数据库表Schema设计1
08-08
总的来说，设计树形结构的数据库表Schema是一项挑战，需要平衡直观性、存储效率和查询性能。根据实际场景和需求，可以选择直观的`{Node_id, Parent_id}`方案，或者优化后的基于左右值编码的方法。理解并掌握这两种...
sql实现多行合并一行
09-26
在本例中，我们将探讨如何使用纯SQL实现这一功能，特别是针对Oracle数据库。 首先，我们来看一个示例表`m_researcher_stock_rel`，它包含了股票`n_sec_code`和研究员`c_researcher_code`之间的映射关系。由于一只...
《数据库》查询树优化
热门推荐
我的E家
03-08 1万+
查询树的启发式优化 典型的启发式规则： 1.选择运算应尽可能先做。在优化策略中这是最重要、最基本的一条 2.把投影运算和选择运算同时进行 如有若干投影和选择运算，并且它们都对同一个关系操作，则可以在扫描此关系的同时完成所有的这些运算以避免重复扫描关系 3.把投影同其前或其后的双目运算结合起来 4.把某些选择同在它前面要执行的笛卡尔积结合起来成为一个连接运算 5.找
查询优化——查询树结构
HighGO
03-12 592
查询树作为PG优化器的输入参数，了解此类数据结构，在此基础上了解PG优化的规则。
SQL递归查询树型分类数据
weixin_58403235的博客
03-18 2281
使用SQL实现递归查询树形分类结构，并结合mybatis实现了分类查询
sql查询【树形结构数据】
浆果酒的博客
03-26 1682
WITH T AS (SELECT TO_CHAR(I.ITEMNO) ID, '-1' AS PID, TO_CHAR(I.CNNAME) AS NAME, 0 AS ISLEAF FROM BO_ACT_DICT_KV_ITEM I WHERE DICTKEY = 'INFOPUBLICADD.CODE.INFOTYPE' ...
关于树结构的查询优化，及权限树的查询优化
qq_33304139的博客
01-01 2044
在有限的经验下，主要讨论以下两部分内容 1.对树形数据结构的查询,且层级较少，采用添加分级字段的方式优化 2.针对前端使用ztree ，list直接返回结果集，存在授权问题的查询，采用添加层次码的方案 1.针对需要树结构的查询 在有限的多级目录情况下，可以添加分级字段，（1级目录，2级目录，3级目录）， 1.在这个基础上假设只有三级目录，那么我们可以直接分3次查询，此时得到了3个关于1、 2、 3层级对应的list； 2.上一步的操作，使我们极大的改善了数据库查询的效率，只需要3次查询即可拿到所有的数
oracle connect by优化小探
u011165335的博客
09-23 4428
前一篇介绍了connect by的用法 https://blog.csdn.net/u011165335/article/details/82822224 这里在看看如何优化: 环境跟上一篇的一样:11g -- 表结构 drop table menu; create table menu( mid varchar2(64) not null, parent_id varchar2(64) ...
SQL中带有NOT IN 子查询改写
风中清淡
12-04 1万+
NOT IN子查询改写LEFT JOIN

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值