|
【赛迪网－it技术报道】问题:
下面的这条sql应该怎么优化?
select * from sys_user
where user_code = 'zhangyong'
or user_code in
(select grp_code
from sys_grp
where sys_grp.user_code = 'zhangyong')
execution plan
----------------------------------------------------------
0 select statement optimizer=rule
1 0 filter
2 1 table access (full) of 'sys_user'
3 1 index (unique scan) of 'pk_sys_grp' (unique)
statistics
----------------------------------------------------------
14 recursive calls
4 db block gets
30590 consistent gets
0 physical reads
0 redo size
1723 bytes sent via sql*net to client
425 bytes received via sql*net from client
2 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
|
里面的查询返回的记录数一般只有一两条，但sys_user表的数据很多，怎么样才能让这条sql以sys_grp为驱动表？
表中记录情况如下:
sql> select count(*) from sys_grp;
count(*)----------25130
sql> select count(*) from sys_user;
count(*)
----------
15190
|
优化:
降低逻辑读是优化sql的基本原则之一
我们尝试通过降低逻辑读来加快sql的执行.
这里我们使用or展开来改写sql查询:
select * from sys_user where user_code = 'zhangyong'
union all
select * from sys_user where user_code <> 'zhangyong'
and user_code in (select grp_code from sys_grp
where sys_grp.user_code = 'zhangyong')
statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
130 consistent gets
0 physical reads
0 redo size
1723 bytes sent via sql*net to client
425 bytes received via sql*net from client
2 sql*net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
execution plan
----------------------------------------------------------
0 select statement optimizer=rule
1 0 union-all
2 1 table access (by index rowid) of 'sys_user'
3 2 index (unique scan) of 'pk_sys_user' (unique)
4 1 nested loops
5 4 view of 'vw_nso_1'
6 5 sort (unique)
7 6 table access (by index rowid) of 'sys_grp'
8 7 index (range scan) of 'fk_sys_user_code' (non-unique)
9 4 table access (by index rowid) of 'sys_user'
10 9 index (unique scan) of 'pk_sys_user' (unique)
|
我们注意到,通过改写,逻辑读减少到130，从30590到130这是一个巨大的提高,减少逻辑读最终会减少资源消耗,提高sql的执行效率.
这个改写把filter改为了nest loop,索引得以充分利用.从而大大提高了性能.
我们同时注意到，这里引入了一个排序
排序来自于这一步:
----------------------------------
6 5 sort (unique)
7 6 table access (by index rowid) of 'sys_grp'
8 7 index (range scan) of 'fk_sys_user_code' (non-unique)
----------------------------------
|
注释：在'sys_grp'表中，user_code 是非唯一键值，在in值判断里，要做sort unique排序，去除重复值，这里的union all是不需要排序的。
|
