oracle性能优化1

发表于 更新于 阅读次数：

SQL执行计划

explain使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
SCOTT@PROD> explain plan for SELECT * FROM EMP where ename='scott';

Explained.

SCOTT@PROD> @?/rdbms/admin/utlxplp

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME"='scott')

13 rows selected.
``` 

### Operation参数说明       
INDEX UNIQUE SCAN 唯一索引      
TABLE ACCESS FULL 全表扫描      

### like 模糊查询       
%O% 不走索引(TABLE ACCESS FULL)                  
S% 索引生效(INDEX RANGE SCAN)    

### 组合索引

### 位图索引

### 范围查询
#### between and
```sql
select * from emp where sal >= 5000 AND SAL<= 10000;
select * from emp where sal BETWEEN 5000 AND 10000;

in exists

1
2
3
4
5
6
7
8
9
10
-- 使用in筛选数据
select * from emp where sal in (5000, 10000);

-- 使用exists筛选数据
with sal_values as (
    select 5000 as sal from dual
    UNION
    select 10000 as sal from dual
)
select * from emp a where exists (select * from sal_values b where b.sal=a.sal );
  • 14条数据,筛选值2条
    • in => table access FULL
    • exists => SKIP SCAN

any(some) all

1
2
3
4
5
6
7
-- any表示条件满足于任意子查询结果的数据(与some作用相同)
-- 查询大于部门平均工资最小值的所有人员
select * from emp where sal > any(select avg(sal) from emp group by deptno); 

-- all表示条件满足于所有子查询结果的数据
-- 查询大于部门平均工资最大值的所有人员
select * from emp where sal> all(select avg(sal) from emp group by deptno);

参考

看懂ORACLE执行计划
Oracle 执行计划（Explain Plan） 说明
各种Oracle索引类型介绍
Performance Tuning Guide