这样一条sql应该怎么优化？
这样一条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
----------------------------------------------------------
0SELECT STATEMENT Optimizer=RULE
10 FILTER
21 TABLE ACCESS (FULL) OF 'SYS_USER'
31 INDEX (UNIQUE SCAN) OF 'PK_SYS_GRP' (UNIQUE)
Statistics
----------------------------------------------------------
14recursive calls
4db block gets
30590 consistent gets
0physical reads
0redo size
1723bytes sent via SQL*Net to client
425bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
3rows 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
----------------------------------------------------------
0recursive calls
0db block gets
130 consistent gets
0physical reads
0redo size
1723bytes sent via SQL*Net to client
425bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
1sorts (memory)
0sorts (disk)
3rows 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,索引得以充分利用.从而大大提高了性能.
我们同时注意到，这里引入了一个排序
排序来自于这一步:
-----------------------------------------------------------------------------------------
65SORT (UNIQUE)
76 TABLE ACCESS (BY INDEX ROWID) OF 'SYS_GRP'
87 INDEX (RANGE SCAN) OF 'FK_SYS_USER_CODE' (NON-UNIQUE)
------------------------------------------------------------------------------------------
在'SYS_GRP'表中，user_code 是非唯一键值
在in值判断里，要做sort unique排序，去除重复值
这里的union all是不需要排序的
问题答案都在一起了 不好玩 前几天遇到一个问题。
一个存储过程 使用REF CURSOR作为返回值
把SQL单拿出来，很快 1秒左右 如果放在存储过程里调用 就要用26-32秒。
后来发现我使用其他方法 替换了OR 在存储过程中也能在一秒中得到结果。
个人分析：
由于OR会引起执行计划的混乱。所以剃掉Or 使其向效率高的执行计划上偏移。
碰到or的时候，如果执行计划有问题，可以考虑使用union all。
但是具体情况还是要具体分析。
分析执行计划还是基本功啊？ 请问，非相关子查询Oracle会怎么处理？
是先将子查询作一遍得到结果，然后父查询中的每一行数据直接应用这结果？还是对于父查询中的每一行数据，都要重新进行一次子查询？ 张，这个问题很复杂，细细看看我的帖子：6篇关于semi join和anti join。
这对我们的优化非常重要。 SQL> create table scott.tabcol as select owner,table_name,column_name,data_type,data_type_mod,data_type_owner from dba_tab_cols;
Table created.
SQL> create table scott.tabgrp as select column_name from dba_tab_cols group by column_name;
Table created.
SQL> alter table tabgrp add constraint pk_column primary key(column_name);
SQL> exec dbms_stats.gather_index_stats('SCOTT','PK_COLUMN');
SQL> exec dbms_stats.gather_table_stats('SCOTT','TABGRP');
SQL> create index idx_column on tabcol(column_name);
SQL> exec dbms_stats.gather_index_stats('SCOTT','IDX_COLUMN');
SQL> exec dbms_stats.gather_table_stats('SCOTT','TABCOL');
set autot trace exp stat;
select * from tabcol
where column_name = 'NAME'
or column_name in
(select column_name
from tabgrp
where column_name = 'NAME');
Execution Plan
----------------------------------------------------------
Plan hash value: 339360689
---------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |2130 | 85200 | 74 (2)| 00:00:01 |
|*1 |FILTER | | | | | |
| 2 | TABLE ACCESS FULL | TABCOL | 42311 |1652K| 74 (2)| 00:00:01 |
|*3 | FILTER | | | | | |
|*4 | INDEX UNIQUE SCAN| PK_COLUMN | 1 | 12 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COLUMN_NAME"='NAME' OREXISTS (SELECT /*+ */ 0 FROM
"TABGRP" "TABGRP" WHERE :B1='NAME' AND "COLUMN_NAME"=:B2))
3 - filter(:B1='NAME')
4 - access("COLUMN_NAME"=:B1)
select t1.* from tabcol t1,(select column_name from tabgrp where column_name = 'NAME') t2
where t1.column_name = t2.column_name and t1.column_name = 'NAME';
Execution Plan
----------------------------------------------------------
Plan hash value: 2228261001
-------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 3 (0)| 00:00:01 |
| 1 |NESTED LOOPS | | 1 | 52 | 3 (0)| 00:00:01 |
|*2 | INDEX UNIQUE SCAN | PK_COLUMN| 1 | 12 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TABCOL | 1 | 40 | 2 (0)| 00:00:01 |
|*4 | INDEX RANGE SCAN | IDX_COLUMN | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COLUMN_NAME"='NAME')
4 - access("T1"."COLUMN_NAME"='NAME')
filter("T1"."COLUMN_NAME"="COLUMN_NAME")
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
----------------------------------------------------------
0SELECT STATEMENT Optimizer=RULE
10 FILTER
21 TABLE ACCESS (FULL) OF 'SYS_USER'
31 INDEX (UNIQUE SCAN) OF 'PK_SYS_GRP' (UNIQUE)
故意使用RBO?如果换成CBO呢？
页:
[1]