开发者社区> flzhang> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL优化实例

简介: 示例一:hint滥用 select /*+ ordered use_nl(b a c d)*/        *   from b,a,c,d  where b.
+关注继续查看

示例一:hint滥用

select /*+ ordered use_nl(b a c d)*/
       *
  from b,a,c,d
 where b.col1 = a.col1
   and a.col2 = c.col2
   and a.col3 = d.col3
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt <   to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')

分析:
1)表之间的关系:
2)数据量,索引情况   b 200万,a 200万 ,过滤后50行,c 200行, d 1000行
3)优化器模式  RBO  or  CBO
4)不同的优化器模式采用不用的优化策略

优化措施:---》
对于CBO,把添加的hint去掉就可以了,Oracle会根据数据量和索引情况生成高效的执行计划
select *
  from b,a,c,d
 where b.col1 = a.col1
   and a.col2 = c.col2
   and a.col3 = d.col3
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt <   to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')


--------------------------------------------------------------

示例二:多余的表或列访问

select a.col1,b.col2,a.dt
  from a,c,b
 where a.col1 =  c.col1
   and c.col1 = b.col1
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')

分析:
1)表之间的关系 a,c 1:1  c,b 1:1
2)select里没有出现c表的列,此时可以考虑c表是否是必须的
3)如果分析表之间的关系及数据的特点发现c表不是必须的,可以去掉c表的访问
4)说明:当然也有些情况下c表可能是必须的,具体情况具体分析

优化措施:--》
如果经过分析发现c表的访问确实是多余的,那么上述语句可以改为:
select a.col1,b.col2,a.dt
  from a,b
 where a.col1 = b.col1
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')


select col1,col2,dt
  from
(
select a.*,b.col2,a.rownum rn
  from a,b
 where a.col1 = b.col1
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')
   order by a.col1
)
where rn between 10 and 20

分析:
1)内层查询使用a.*,而外层只需要a的col1这一列,所以把内层查询的c.*具体化,减少资源和时间的消耗

优化措施:--》
select col1,col2,dt
  from
(
select a.col1,b.col2,a.rownum rn
  from a,b
 where a.col1 = b.col1
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')
   order by a.col1
)
where rn between 10 and 20

--------------------------------------------------------

示例三:索引使用的灵活处理

select a.dt,b.timezone,a.col1,a.col2,a.col3
  from a,b
 where a.col1 = b.col1
   and a.dt   >= to_date('20010101 00:00:00','YYYYMMDD 24:MI:SS') - b.timezone
   and a.dt   < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS') -b.timezone
保证同一时间段统计


分析:
1)表之间的关系:
2)数据量,索引情况 a 100万  b 200    a.dt有索引, a.col1,b.col1有索引
3)条件中a.dt列的比较范围是变化的,所以导致dt列的索引无法使用
4)分析b.timezone的数值范围,在-12:00~12:00之间
5)为了使用a.dt列的索引,考虑可以把a.dt的范围条件放大到固定值,然后再对结果集进行过滤。

优化措施:--》

select *
from
(
select a.dt,b.timezone,a.col1,a.col2,a.col3
  from a,b
 where a.col1 = b.col1
   and a.dt   >= to_date('20010101 00:00:00','YYYYMMDD 24:MI:SS') -1
   and a.dt  < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')+1
) ab

   where ab.dt   >= to_date('20010101 00:00:00','YYYYMMDD 24:MI:SS') -ab.timezone
   and ab.dt  < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')- ab.timezone

----------------------------------------------------------

示例四:有没有合适的索引可用

select a.col1,a.col2,a.col3,b.col4,c.col5
  from a,b,c
 where a.col1 = b.col1
   and a.col2 = b.col2
   and a.col3 = c.col3
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')

分析:
1)表之间的关系
2)数据量,索引情况   a.dt, b.col1, b.col2, c.col3列分别有索引
3)但是单独使用b.col1列的索引或者b.col2列的索引,数据筛选效果都不好
4)这样考虑为b表的col1,col2创建联合索引

优化措施:--》
为b表的col1,col2列创建联合索引

------------------------------------------------------

示例五:不必要的外连接

select a.col1,b.col2,b.col3
  from a,b
 where a.col1 = b.col2(+)
   and b.col3 > 1000

分析:
1)条件b.col3>1000意味着原本使用外连接多出来的列要被排除掉,所以此处外连接是不需要的

优化措施:--》

select a.col1,b.col2,b.col3
  from a,b
 where a.col1 = b.col2
   and b.col3 > 1000

-----------------------------------------------------

示例六:with子句的使用

select ab.col1,ab.col3,c.col2
  from c,
(
select a.col1,b.col3
  from a,b
 where a.col1 = b.col1
   and b.col3 > 1000
) ab
where c.col1 = ab.col1
union
select ab.col1,ab.col3,d.col2
  from d,
(
select a.col1,b.col3
  from a,b
 where a.col1 = b.col1
   and b.col3 > 1000
) ab
where d.col1 = ab.col1

分析:
1)该语句中出现了多处相同的子查询,可以使用with子句来进行简化,减少数据访问,提高效率

优化措施:--》
with ab as
(
select a.col1,b.col3
  from a,b
 where a.col1 = b.col1
   and b.col3 > 1000
)
select ab.col1,ab.col3,c.col2
  from c,ab
where c.col1 = ab.col1
union
select ab.col1,ab.col3,d.col2
  from d,ab
where d.col1 = ab.col1



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
软件测试最常用的 SQL 命令 | 通过实例掌握基本查询、条件查询、聚合查询
软件测试最常用的 SQL 命令 | 通过实例掌握基本查询、条件查询、聚合查询
0 0
SQL CREATE TABLE 实例
SQL CREATE TABLE 实例
0 0
SQL CREATE VIEW 实例
SQL CREATE VIEW 实例
0 0
SQL MID() 实例
SQL MID() 实例
0 0
SQL GROUP BY 实例
SQL GROUP BY 实例
0 0
SQL TOP 实例
SQL TOP 实例
0 0
SQL TOP PERCENT 实例
SQL TOP PERCENT 实例
0 0
SQL SELECT * 实例
SQL SELECT * 实例
0 0
SQL SELECT 实例
SQL SELECT 实例
0 0
SQL UNION 实例
SQL UNION 实例
0 0
+关注
flzhang
某外企dba一枚,擅长java,shell自动化运维与SQL优化等,乐于钻研技术和分享技术
文章
问答
文章排行榜
最热
最新
相关电子书
更多
用SQL做数据分析
立即下载
阿里云流计算 Flink SQL 核心功能解密
立即下载
Comparison of Spark SQL with Hive
立即下载