最近看到一个问题(原帖地址: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,分别进行一次全表扫描。
分享到:
相关推荐
总的来说,SQL优化是一个系统性的工作,需要结合硬件配置、数据库设计、SQL编写等多个方面进行综合考虑。而借助专业的SQL优化工具,这个过程可以变得更加高效和精确,从而确保数据库系统的稳定和高效运行。
SQL 查询优化是数据库优化的重要部分,查询优化器是 SQL SERVER 中的一个组件,可以自动优化查询语句,提高查询效率。本文将详细介绍查询优化器的工作原理、SARG 的定义和应用、查询优化的 tips 等。 一、查询优化...
在“基于案例学习SQL优化”的课程中,我们主要探讨如何提升数据库性能,特别是针对SQL查询的优化技巧。DBA(数据库管理员)作为关键角色,需要掌握这些技能来确保系统的高效运行。以下是根据课程标题和描述提炼出的...
优化器把使用 LIKE 操作符和一个没有通配符的表达式组成的检索表达式转换为一个"="操作符表达式。例如,优化器会把表达式 `ename LIKE 'SMITH'` 转换为 `ename = 'SMITH'`。优化器只能转换涉及到可变长数据类型的...
第2章 风驰电掣——有效缩短SQL优化过程 24 2.1 SQL调优时间都去哪儿了 25 2.1.1 不善于批处理频频忙交互 25 2.1.2 无法抓住主要矛盾瞎折腾 25 2.1.3 未能明确需求目标白费劲 26 2.1.4 没有分析操作难度乱调优...
- **宏观策略的意义**:通过这些策略,可以构建起一个从宏观到微观的优化思路,帮助读者更好地理解SQL优化的“道”。 #### 4. 解决SQL问题的具体技术 - **体系结构**:了解数据库的整体架构对于优化至关重要。 - **...
基于Oracle的SQL优化
Oracle数据库的性能优化直接关系到系统的运行效率,而影响数据库性能的一个重要因素就是SQL性能问题。本书是作者十年磨一剑的成果之一,深入分析与解剖OracleSQL优化与调优技术,主要内容包括: 第一篇“执行计划”...
《收获,不止SQL优化》是一本专注于数据库性能优化的书籍,尤其关注Oracle数据库系统的SQL调优。这本书通过实例和深入的解析,帮助读者理解和掌握如何提升SQL查询的效率,从而优化整个数据库系统的性能。在阅读这...
在《1从案例中推导SQL优化的总体思路与误区》这个文件中,我们可能会学习到以下几点: 1. **优化总体思路**:这通常包括分析查询执行计划,识别性能瓶颈,调整索引,以及优化查询语句结构。 2. **常见误区**:比如...
这四个原则贯穿于整个SQL优化过程,是提升查询效率的基础。 1. 减少查询次数:通过联合查询、子查询优化和存储过程等方式,将多次数据库交互合并为一次,降低网络传输和数据库处理的压力。 2. 减小数据量:通过...
根据提供的文件信息,本文将对《基于Oracle的SQL优化》这一主题进行深入解析,包括但不限于SQL优化的重要性、Oracle数据库的特点以及具体的SQL优化方法等。 ### SQL优化的重要性 SQL(Structured Query Language)...
SQL优化不仅涉及查询语句的结构调整,还包含索引管理、表设计、数据库配置等多个层面。 ### SQL优化的关键策略 1. **索引使用**:合理创建和利用索引是SQL优化的重要手段。索引可以加快数据检索速度,减少全表扫描...
SQL优化涉及到多个层面,包括查询设计、索引策略、存储过程优化、执行计划分析以及资源管理等。本篇文章将深入探讨这些方面,帮助读者理解如何针对SQL Server进行有效的SQL优化。 首先,查询设计是SQL优化的基础。...
然而,SQL虽然实现简单可乐,却极易引发性能问题,那时广大SQL使用人员可要“愁”就一个字,心碎无数次了。 缘何有性能问题?原因也一字概括:“量”。当系统数据量、并发访问量上去后,不良SQL就会拖跨整个系统,...
- **及时处理过程中发生的意外和变化**:性能优化是一个动态过程,需要灵活应对各种突发情况。 - **80/20定律**:通常情况下,20%的优化工作可以带来80%的性能提升。 - **SQL优化机制**: - **SQL语句处理过程**...
在IT行业中,SQL优化是一项至关重要的技能,尤其是在大型企业或数据密集型应用中。Oracle SQL优化是数据库管理员和开发人员日常工作中不可或缺的部分,因为它直接影响到系统的性能和响应时间。以下是对"sql优化经验...
这个"Oracle_SQL优化脚本_完整实用资源"压缩包包含了一系列工具和方法,旨在帮助你优化在Oracle数据库上运行的SQL查询,从而提高数据库的响应速度和整体效率。 1. **SQL执行计划分析**:在Oracle中,通过`EXPLAIN ...
ORACLE SQL性能优化是数据库管理员和开发者非常关心的一个话题。为了提高数据库的性能,ORACLE 提供了多种优化技术。下面我们将详细介绍 ORACLE SQL 性能优化系列中的一些重要知识点。 一、访问表的方式 ORACLE ...