`

一个SQL的优化

阅读更多
最近看到一个问题(原帖地址:http://topic.csdn.net/u/20120604/09/b56a0996-3c5a-4c35-9423-8b68d1284db6.html)
-- 表TB1
  START_ID     END_ID
---------- ----------
         1          3
         4          6
         7          9
        10         12
        13         15
        16         18
        19         21
        22         24
        25         27
        28         30

-- 表TB2
       TID
----------
         1
         2
         3
        31

-- 查询TB2的结果是在TB1的范围中
-- 期望结果:
       TID
----------
         1
         2
         3

简单的写法:
SELECT t2.tid
  FROM tb1 t1,
       tb2 t2
 WHERE t2.tid BETWEEN t1.start_id AND t1.end_id

俩个表数据少的情况,该写法没有什么问题,数据稍微大的话,再看看什么结果。构造tb1的数据1w条,构造tb2的数据10w条。
插入语句:
INSERT INTO tb1
SELECT s ,e
  FROM (SELECT LEVEL s,
               LEVEL + 2 e
          FROM DUAL
        CONNECT BY LEVEL <= 30000) m
 WHERE MOD(m.s-1, 3) = 0;

INSERT INTO tb2
    SELECT LEVEL
      FROM DUAL
    CONNECT BY LEVEL <= 100000;


执行上面sql,查看autotrace

SELECT t2.tid
  FROM tb1 t1,
       tb2 t2
 WHERE t2.tid BETWEEN t1.start_id AND t1.end_id;

30074行が選択されました。

経過: 00:02:18.07

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1538 Card=2640000 Bytes=102960000)
   1    0   MERGE JOIN (Cost=1538 Card=2640000 Bytes=102960000)
   2    1     SORT (JOIN) (Cost=90 Card=10000 Bytes=260000)
   3    2       TABLE ACCESS (FULL) OF 'TB1' (TABLE) (Cost=13 Card=10000 Bytes=260000)
   4    1     FILTER
   5    4       SORT (JOIN) (Cost=571 Card=105600 Bytes=1372800)
   6    5         TABLE ACCESS (FULL) OF 'TB2' (TABLE) (Cost=54 Card=105600 Bytes=1372800)

統計
----------------------------------------------------------
          9  recursive calls
          1  db block gets
        352  consistent gets
          0  physical reads
        176  redo size
     481806  bytes sent via SQL*Net to client
      22547  bytes received via SQL*Net from client
       2006  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
      30074  rows processed

上面SQL执行了2分18秒,效率很不好,看一下执行计划,tb1和tb2进行了FILTER操作,(FILTER类似NESTED LOOP,它内部维护一个hash table,当一个值满足条件时,把这个值放到hash中,下次遇到相同的值时,直接去hash中去取,避免再一次全表扫描,所以效率优于NESTED LOOP。)。tb1有10000条记录,tb2有100000条记录,最坏的情况10000*100000次全表扫描,这就是效率慢的原因。
思路:为了避免嵌套循环,考虑使用hash join 来减少全表扫描次数,由于hash join只能用于等值连接,将tb1表数据缺失的条件构造出来,使Oracle选择hash join。
优化后的SQL
SELECT m2.tid
  FROM (SELECT t1.start_id + t2.lv tid
          FROM tb1 t1,
               (SELECT LEVEL - 1 lv
                  FROM (SELECT MAX(end_id - start_id) + 1 g
                          FROM tb1)
                CONNECT BY LEVEL <= g) t2
         WHERE t1.end_id >= t1.start_id + t2.lv) m1,
       tb2 m2
 WHERE m1.tid = m2.tid;
30074行が選択されました。

経過: 00:00:00.02
実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=83 Card=960 Bytes=
          49920)
   1    0   HASH JOIN (Cost=83 Card=960 Bytes=49920)
   2    1     NESTED LOOPS (Cost=27 Card=500 Bytes=19500)
   3    2       VIEW (Cost=13 Card=1 Bytes=13)
   4    3         CONNECT BY (WITHOUT FILTERING)
   5    4           COUNT
   6    5             VIEW (Cost=13 Card=1 Bytes=13)
   7    6               SORT (AGGREGATE)
   8    7                 TABLE ACCESS (FULL) OF 'TB1' (TABLE) (Cost=13 Card=10000 Bytes=260000)
   9    2       TABLE ACCESS (FULL) OF 'TB1' (TABLE) (Cost=13 Card=500Bytes=13000)
  10    1     TABLE ACCESS (FULL) OF 'TB2' (TABLE) (Cost=54 Card=105600 Bytes=1372800)

統計
----------------------------------------------------------
         14  recursive calls
          0  db block gets
       2583  consistent gets
          0  physical reads
          0  redo size
     419088  bytes sent via SQL*Net to client
      22547  bytes received via SQL*Net from client
       2006  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      30074  rows processed

上面SQL执行了0.02秒,效率很好,m1和m2进行hash join,分别进行一次全表扫描。
分享到:
评论

相关推荐

    pandas-1.3.5-cp37-cp37m-macosx_10_9_x86_64.zip

    pandas whl安装包,对应各个python版本和系统(具体看资源名字),找准自己对应的下载即可! 下载后解压出来是已.whl为后缀的安装包,进入终端,直接pip install pandas-xxx.whl即可,非常方便。 再也不用担心pip联网下载网络超时,各种安装不成功的问题。

    基于java的大学生兼职信息系统答辩PPT.pptx

    基于java的大学生兼职信息系统答辩PPT.pptx

    基于java的乐校园二手书交易管理系统答辩PPT.pptx

    基于java的乐校园二手书交易管理系统答辩PPT.pptx

    tornado-6.4-cp38-abi3-musllinux_1_1_i686.whl

    tornado-6.4-cp38-abi3-musllinux_1_1_i686.whl

    Android Studio Ladybug(android-studio-2024.2.1.10-mac.zip.002)

    Android Studio Ladybug 2024.2.1(android-studio-2024.2.1.10-mac.dmg)适用于macOS Intel系统,文件使用360压缩软件分割成两个压缩包,必须一起下载使用: part1: https://download.csdn.net/download/weixin_43800734/89954174 part2: https://download.csdn.net/download/weixin_43800734/89954175

    基于ssm框架+mysql+jsp实现的监考安排与查询系统

    有学生和教师两种角色 登录和注册模块 考场信息模块 考试信息模块 点我收藏 功能 监考安排模块 考场类型模块 系统公告模块 个人中心模块: 1、修改个人信息,可以上传图片 2、我的收藏列表 账号管理模块 服务模块 eclipse或者idea 均可以运行 jdk1.8 apache-maven-3.6 mysql5.7及以上 tomcat 8.0及以上版本

    tornado-6.1b2-cp38-cp38-macosx_10_9_x86_64.whl

    tornado-6.1b2-cp38-cp38-macosx_10_9_x86_64.whl

    Android Studio Ladybug(android-studio-2024.2.1.10-mac.zip.001)

    Android Studio Ladybug 2024.2.1(android-studio-2024.2.1.10-mac.dmg)适用于macOS Intel系统,文件使用360压缩软件分割成两个压缩包,必须一起下载使用: part1: https://download.csdn.net/download/weixin_43800734/89954174 part2: https://download.csdn.net/download/weixin_43800734/89954175

    基于MATLAB车牌识别代码实现代码【含界面GUI】.zip

    matlab

    基于java的毕业生就业信息管理系统答辩PPT.pptx

    基于java的毕业生就业信息管理系统答辩PPT.pptx

    基于Web的毕业设计选题系统的设计与实现(springboot+vue+mysql+说明文档).zip

    随着高等教育的普及和毕业设计的日益重要,为了方便教师、学生和管理员进行毕业设计的选题和管理,我们开发了这款基于Web的毕业设计选题系统。 该系统主要包括教师管理、院系管理、学生管理等多个模块。在教师管理模块中,管理员可以新增、删除教师信息,并查看教师的详细资料,方便进行教师资源的分配和管理。院系管理模块则允许管理员对各个院系的信息进行管理和维护,确保信息的准确性和完整性。 学生管理模块是系统的核心之一,它提供了学生选题、任务书管理、开题报告管理、开题成绩管理等功能。学生可以在此模块中进行毕业设计的选题,并上传任务书和开题报告,管理员和教师则可以对学生的报告进行审阅和评分。 此外,系统还具备课题分类管理和课题信息管理功能,方便对毕业设计课题进行分类和归档,提高管理效率。在线留言功能则为学生、教师和管理员提供了一个交流互动的平台,可以就毕业设计相关问题进行讨论和解答。 整个系统设计简洁明了,操作便捷,大大提高了毕业设计的选题和管理效率,为高等教育的发展做出了积极贡献。

    机器学习(预测模型):2000年至2015年期间193个国家的预期寿命和相关健康因素的数据

    这个数据集来自世界卫生组织(WHO),包含了2000年至2015年期间193个国家的预期寿命和相关健康因素的数据。它提供了一个全面的视角,用于分析影响全球人口预期寿命的多种因素。数据集涵盖了从婴儿死亡率、GDP、BMI到免疫接种覆盖率等多个维度,为研究者提供了丰富的信息来探索和预测预期寿命。 该数据集的特点在于其跨国家的比较性,使得研究者能够识别出不同国家之间预期寿命的差异,并分析这些差异背后的原因。数据集包含22个特征列和2938行数据,涉及的变量被分为几个大类:免疫相关因素、死亡因素、经济因素和社会因素。这些数据不仅有助于了解全球健康趋势,还可以辅助制定公共卫生政策和社会福利计划。 数据集的处理包括对缺失值的处理、数据类型转换以及去重等步骤,以确保数据的准确性和可靠性。研究者可以使用这个数据集来探索如教育、健康习惯、生活方式等因素如何影响人们的寿命,以及不同国家的经济发展水平如何与预期寿命相关联。此外,数据集还可以用于预测模型的构建,通过回归分析等统计方法来预测预期寿命。 总的来说,这个数据集是研究全球健康和预期寿命变化的宝贵资源,它不仅提供了历史数据,还为未来的研究和政策制

    基于微信小程序的高校毕业论文管理系统小程序答辩PPT.pptx

    基于微信小程序的高校毕业论文管理系统小程序答辩PPT.pptx

    基于java的超市 Pos 收银管理系统答辩PPT.pptx

    基于java的超市 Pos 收银管理系统答辩PPT.pptx

    基于java的网上报名系统答辩PPT.pptx

    基于java的网上报名系统答辩PPT.pptx

    基于java的网上书城答辩PPT.pptx

    基于java的网上书城答辩PPT.pptx

    婚恋网站 SSM毕业设计 附带论文.zip

    婚恋网站 SSM毕业设计 附带论文 启动教程:https://www.bilibili.com/video/BV1GK1iYyE2B

    基于java的戒烟网站答辩PPT.pptx

    基于java的戒烟网站答辩PPT.pptx

    基于微信小程序的“健康早知道”微信小程序答辩PPT.pptx

    基于微信小程序的“健康早知道”微信小程序答辩PPT.pptx

    机器学习(预测模型):自行车共享使用情况的数据集

    Capital Bikeshare 数据集是一个包含从2020年5月到2024年8月的自行车共享使用情况的数据集。这个数据集记录了华盛顿特区Capital Bikeshare项目中自行车的租赁模式,包括了骑行的持续时间、开始和结束日期时间、起始和结束站点、使用的自行车编号、用户类型(注册会员或临时用户)等信息。这些数据可以帮助分析和预测自行车共享系统的需求模式,以及了解用户行为和偏好。 数据集的特点包括: 时间范围:覆盖了四年多的时间,提供了长期的数据观察。 细节丰富:包含了每次骑行的详细信息,如日期、时间、天气条件、季节等,有助于深入分析。 用户分类:数据中区分了注册用户和临时用户,可以分析不同用户群体的使用习惯。 天气和季节因素:包含了天气情况和季节信息,可以研究这些因素对骑行需求的影响。 通过分析这个数据集,可以得出关于自行车共享使用模式的多种见解,比如一天中不同时间段的使用高峰、不同天气条件下的使用差异、季节性变化对骑行需求的影响等。这些信息对于城市规划者、交通管理者以及自行车共享服务提供商来说都是非常宝贵的,可以帮助他们优化服务、提高效率和满足用户需求。同时,这个数据集也

Global site tag (gtag.js) - Google Analytics