先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7
深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！
因此收集整理了一份《2024年最新软件测试全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。
既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！
由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新
如果你需要这些资料，可以添加V获取：vip1024b （备注软件测试）
正文
为了提高效率
.
改写为
:
(
方法一
:
高效
)
SELECT ….
FROM EMP A,DEPT B
WHERE A.DEPT_NO = B.DEPT(+)
AND B.DEPT_NO IS NULL
AND B.DEPT_CAT(+) = ‘A’
(
方法二
:
最高效
)
SELECT ….
FROM EMP E
WHERE NOT EXISTS (SELECT ‘X’
FROM DEPT D
WHERE D.DEPT_NO = E.DEPT_NO
AND DEPT_CAT = ‘A’);
20.
用表连接替换
EXISTS
通常来说
,
采用表连接的方式比
EXISTS
更有效率
SELECT ENAME
FROM EMP E
WHERE EXISTS (SELECT ‘X’
FROM DEPT
WHERE DEPT_NO = E.DEPT_NO
AND DEPT_CAT = ‘A’);
(
更高效
)
SELECT ENAME
FROM DEPT D,EMP E
WHERE E.DEPT_NO = D.DEPT_NO
AND DEPT_CAT = ‘A’ ;
(
译者按
:
在
RBO
的情况下
,
前者的执行路径包括
FILTER,
后者使用
NESTED LOOP)
21.
用
EXISTS
替换
DISTINCT
当提交一个包含一对多表信息
(
比如部门表和雇员表
)
的查询时
,
避免在
SELECT
子句中使用
DISTINCT.
一般可以考虑用
EXIST
替换
例如
:
低效
:
SELECT DISTINCT DEPT_NO,DEPT_NAME
FROM DEPT D,EMP E
WHERE D.DEPT_NO = E.DEPT_NO
高效
:
SELECT DEPT_NO,DEPT_NAME
FROM DEPT D
WHERE EXISTS ( SELECT ‘X’
FROM EMP E
WHERE E.DEPT_NO = D.DEPT_NO);
EXISTS
使查询更为迅速
,
因为
RDBMS
核心模块将在子查询的条件一旦满足后
,
立刻返回结果
.
22.
识别
’
低效执行
’
的
SQL
语句
用下列
SQL
工具找出低效
SQL:
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;
(
译者按
:
虽然目前各种关于
SQL
优化的图形化工具层出不穷
,
但是写出自己的
SQL
工具来解决问题始终是一个最好的方法
)
23.
使用
TKPROF
工具来查询
SQL
性能状态
SQL trace
工具收集正在执行的
SQL
的性能状态数据并记录到一个跟踪文件中
.
这个跟踪文件提供了许多有用的信息
,
例如解析次数
.
执行次数
,CPU
使用时间等
.
这些数据将可以用来优化你的系统
.
设置
SQL TRACE
在会话级别
:
有效
ALTER SESSION SET SQL_TRACE TRUE
设置
SQL TRACE
在整个数据库有效仿
,
你必须将
SQL_TRACE
参数在
init.ora
中设为
TRUE, USER_DUMP_DEST
参数说明了生成跟踪文件的目录
(
译者按
:
这一节中
,
作者并没有提到
TKPROF
的用法
,
对
SQL TRACE
的用法也不够准确
,
设置
SQL TRACE
首先要在
init.ora
中设定
TIMED_STATISTICS,
这样才能得到那些重要的时间状态
.
生成的
trace
文件是不可读的
,
所以要用
TKPROF
工具对其进行转换
,TKPROF
有许多执行参数
.
大家可以参考
ORACLE
手册来了解具体的配置
. )
24.
用
EXPLAIN PLAN
分析
SQL
语句
EXPLAIN PLAN
是一个很好的分析
SQL
语句的工具
,
它甚至可以在不执行
SQL
的情况下分析语句
.
通过分析
,
我们就可以知道
ORACLE
是怎么样连接表
,
使用什么方式扫描表
(
索引扫描或全表扫描
)
以及使用到的索引名称
.
你需要按照从里到外
,
从上到下的次序解读分析的结果
. EXPLAIN PLAN
分析的结果是用缩进的格式排列的
,
最内部的操作将被最先解读
,
如果两个操作处于同一层中
,
带有最小操作号的将被首先执行
.
NESTED LOOP
是少数不按照上述规则处理的操作
,
正确的执行路径是检查对
NESTED LOOP
提供数据的操作
,
其中操作号最小的将被最先处理
.
译者按
:
通过实践
,
感到还是用
SQLPLUS
中的
SET TRACE
功能比较方便
.
举例
:
SQL> list
1 SELECT *
2 FROM dept, emp
3* WHERE emp.deptno = dept.deptno
SQL> set autotrace traceonly /*traceonly
可以不显示执行结果
*/
SQL> /
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF ‘EMP’
3 1 TABLE ACCESS (BY INDEX ROWID) OF ‘DEPT’
4 3 INDEX (UNIQUE SCAN) OF ‘PK_DEPT’ (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
30 consistent gets
0 physical reads
0 redo size
2598 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
通过以上分析
,
可以得出实际的执行步骤是
:
1.
TABLE ACCESS (FULL) OF ‘EMP’
2.
INDEX (UNIQUE SCAN) OF ‘PK_DEPT’ (UNIQUE)
3.
TABLE ACCESS (BY INDEX ROWID) OF ‘DEPT’
4.
NESTED LOOPS (JOINING 1 AND 3)
注
:
目前许多第三方的工具如
TOAD
和
ORACLE
本身提供的工具如
OMS
的
SQL Analyze
都提供了极其方便的
EXPLAIN PLAN
工具
.
也许喜欢图形化界面的朋友们可以选用它们
.
25.
用索引提高效率
索引是表的一个概念部分
,
用来提高检索数据的效率
.
实际上
,ORACLE
使用了一个复杂的自平衡
B-tree
结构
.
通常
,
通过索引查询数据比全表扫描要快
.
当
ORACLE
找出执行查询和
Update
语句的最佳路径时
, ORACLE
优化器将使用索引
.
同样在联结多个表时使用索引也可以提高效率
.
另一个使用索引的好处是
,
它提供了主键
(primary key)
的唯一性验证
.
除了那些
LONG
或
LONG RAW
数据类型
,
你可以索引几乎所有的列
.
通常
,
在大型表中使用索引特别有效
.
当然
,
你也会发现
,
在扫描小表时
,
使用索引同样能提高效率
.
虽然使用索引能得到查询效率的提高
,
但是我们也必须注意到它的代价
.
索引需要空间来
存储
,
也需要定期维护
,
每当有记录在表中增减或索引列被修改时
,
索引本身也会被修改
.
这意味着每条记录的
INSERT , DELETE , UPDATE
将为此多付出
4 , 5
次的磁盘
I/O .
因为索引需要额外的存储空间和处理
,
那些不必要的索引反而会使查询反应时间变慢
.
译者按
:
定期的重构索引是有必要的
. ALTER INDEX REBUILD
26.
索引的操作
ORACLE
对索引有两种访问模式
.
索引唯一扫描
( INDEX UNIQUE SCAN)
大多数情况下
,
优化器通过
WHERE
子句访问
INDEX.
例如
:
表
LODGING
有两个索引
:
建立在
LODGING
列上的唯一性索引
LODGING_PK
和建立在
MANAGER
列上的非唯一性索引
LODGING$MANAGER.
SELECT *
FROM LODGING
WHERE LODGING = ‘ROSE HILL’;
在内部
,
上述
SQL
将被分成两步执行
,
首先
, LODGING_PK
索引将通过索引唯一扫描的方式被访问
,
获得相对应的
ROWID,
通过
ROWID
访问表的方式执行下一步检索
.
如果被检索返回的列包括在
INDEX
列中
,ORACLE
将不执行第二步的处理
(
通过
ROWID
访问表
).
因为检索数据保存在索引中
,
单单访问索引就可以完全满足查询结果
.
下面
SQL
只需要
INDEX UNIQUE SCAN
操作
.
SELECT LODGING
FROM LODGING
WHERE LODGING = ‘ROSE HILL’;
索引范围查询
(INDEX RANGE SCAN)
适用于两种情况
:
基于一个范围的检索
基于非唯一性索引的检索
例
1:
SELECT LODGING
FROM LODGING
WHERE LODGING LIKE ‘M%’;
WHERE
子句条件包括一系列值
, ORACLE
将通过索引范围查询的方式查询
LODGING_PK .
由于索引范围查询将返回一组值
,
它的效率就要比索引唯一扫描
低一些
.
例
2:
SELECT LODGING
FROM LODGING
WHERE MANAGER = ‘BILL GATES’;
这个
SQL
的执行分两步
, LODGING
M
A
N
A
G
E
R
的索引范围查询
(
得到所有符合条件记录的
R
O
W
I
D
)
和下一步同过
R
O
W
I
D
访问表得到
L
O
D
G
I
N
G
列的值
.
由于
L
O
D
G
I
N
G
MANAGER 的索引范围查询 ( 得到所有符合条件记录的 ROWID) 和下一步同过 ROWID 访问表得到 LODGING 列的值 . 由于 LODGING
MANAGER的索引范围查询(得到所有符合条件记录的ROWID)和下一步同过ROWID访问表得到LODGING列的值.由于LODGINGMANAGER
是一个非唯一性的索引
,
数据库不能对它执行索引唯一扫描
.
由于
SQL
返回
LODGING
列
,
而它并不存在于
LODGING$MANAGER
索引中
,
所以在索引范围查询后会执行一个通过
ROWID
访问表的操作
.
WHERE
子句中
,
如果索引列所对应的值的第一个字符由通配符
(WILDCARD)
开始
,
索引将不被采用
.
SELECT LODGING
FROM LODGING
WHERE MANAGER LIKE
‘
％
HANMAN’;
在这种情况下，
ORACLE
将使用全表扫描
.
27.
基础表的选择
基础表
(Driving Table)
是指被最先访问的表
(
通常以全表扫描的方式被访问
).
根据优化器的不同
, SQL
语句中基础表的选择是不一样的
.
如果你使用的是
CBO (COST BASED OPTIMIZER),
优化器会检查
SQL
语句中的每个表的物理大小
,
索引的状态
,
然后选用花费最低的执行路径
.
如果你用
RBO (RULE BASED OPTIMIZER) ,
并且所有的连接条件都有索引对应
,
在这种情况下
,
基础表就是
FROM
子句中列在最后的那个表
.
举例
:
SELECT A.NAME , B.MANAGER
FROM
WORKER A,
LODGING B
WHERE
A.LODGING = B.LODING;
由于
LODGING
表的
LODING
列上有一个索引
,
而且
WORKER
表中没有相比较的索引
, WORKER
表将被作为查询中的基础表
.
28.
多个平等的索引
当
SQL
语句的执行路径可以使用分布在多个表上的多个索引时
, ORACLE
会同时使用多个索引并在运行时对它们的记录进行合并
,
检索出仅对全部索引有效的记录
.
在
ORACLE
选择执行路径时
,
唯一性索引的等级高于非唯一性索引
.
然而这个规则只有
当
WHERE
子句中索引列和常量比较才有效
.
如果索引列和其他表的索引类相比较
.
这种子句在优化器中的等级是非常低的
.
如果不同表中两个想同等级的索引将被引用
, FROM
子句中表的顺序将决定哪个会被率先使用
. FROM
子句中最后的表的索引将有最高的优先级
.
如果相同表中两个想同等级的索引将被引用
, WHERE
子句中最先被引用的索引将有最高的优先级
.
举例
:
DEPTNO
上有一个非唯一性索引
,EMP_CAT
也有一个非唯一性索引
.
SELECT ENAME,
FROM EMP
WHERE DEPT_NO = 20
AND EMP_CAT = ‘A’;
这里
,DEPTNO
索引将被最先检索
,
然后同
EMP_CAT
索引检索出的记录进行合并
.
执行路径如下
:
TABLE ACCESS BY ROWID ON EMP
AND-EQUAL
INDEX RANGE SCAN ON DEPT_IDX
INDEX RANGE SCAN ON CAT_IDX
29.
等式比较和范围比较
当
WHERE
子句中有索引列
,
ORACLE
不能合并它们
,ORACLE
将用范围比较
.
举例
:
DEPTNO
上有一个非唯一性索引
,EMP_CAT
也有一个非唯一性索引
.
SELECT ENAME
FROM EMP
WHERE DEPTNO > 20
AND EMP_CAT = ‘A’;
这里只有
EMP_CAT
索引被用到
,
然后所有的记录将逐条与
DEPTNO
条件进行比较
.
执行路径如下
:
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON CAT_IDX
30.
不明确的索引等级
当
ORACLE
无法判断索引的等级高低差别
,
优化器将只使用一个索引
,
它就是在
WHERE
子句中被列在最前面的
.
举例
:
DEPTNO
上有一个非唯一性索引
,EMP_CAT
也有一个非唯一性索引
.
SELECT ENAME
FROM EMP
WHERE DEPTNO > 20
AND EMP_CAT > ‘A’;
这里
, ORACLE
只用到了
DEPT_NO
索引
.
执行路径如下
:
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON DEPT_IDX
译者按
:
我们来试一下以下这种情况
:
SQL> select index_name, uniqueness from user_indexes where table_name = ‘EMP’;
INDEX_NAME UNIQUENES
------------------------------ ---------
EMPNO UNIQUE
EMPTYPE NONUNIQUE
SQL> select * from emp where empno >= 2 and emp_type = ‘A’ ;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘EMP’
2 1 INDEX (RANGE SCAN) OF ‘EMPTYPE’ (NON-UNIQUE)
虽然
EMPNO
是唯一性索引
,
但是由于它所做的是范围比较
,
等级要比非唯一性索引的等式比较低
!
31.
强制索引失效
如果两个或以上索引具有相同的等级
,
你可以强制命令
ORACLE
优化器使用其中的一个
(
通过它
,
检索出的记录数量少
) .
举例
:
SELECT ENAME
FROM EMP
WHERE EMPNO = 7935
AND DEPTNO + 0 = 10
/*DEPTNO
上的索引将失效
*/
AND EMP_TYPE || ‘’ = ‘A’
/*EMP_TYPE
上的索引将失效
*/
这是一种相当直接的提高查询效率的办法
.
但是你必须谨慎考虑这种策略
,
一般来说
,
只有在你希望单独优化几个
SQL
时才能采用它
.
这里有一个例子关于何时采用这种策略
,
假设在
EMP
表的
EMP_TYPE
列上有一个非唯一性的索引而
EMP_CLASS
上没有索引
.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = ‘A’
AND EMP_CLASS = ‘X’;
优化器会注意到
EMP_TYPE
上的索引并使用它
.
这是目前唯一的选择
.
如果
,
一段时间以后
,
另一个非唯一性建立在
EMP_CLASS
上
,
优化器必须对两个索引进行选择
,
在通常情况下
,
优化器将使用两个索引并在他们的结果集合上执行排序及合并
.
然而
,
如果其中一个索引（
EMP_TYPE
）接近于唯一性而另一个索引（
EMP_CLASS
）上有几千个重复的值
.
排序及合并就会成为一种不必要的负担
.
在这种情况下
,
你希望使优化器屏蔽掉
EMP_CLASS
索引
.
用下面的方案就可以解决问题
.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = ‘A’
AND EMP_CLASS
||’’
= ‘X’;
32.
避免在索引列上使用计算．
WHERE
子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．
举例
:
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效
:
SELECT …
FROM DEPT
WHERE SAL > 25000/12;
译者按
:
这是一个非常实用的规则，请务必牢记
33.
自动选择索引
如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性．
在这种情况下，
ORACLE
将使用唯一性索引而完全忽略非唯一性索引．
举例
:
SELECT ENAME
FROM EMP
WHERE EMPNO = 2326
AND DEPTNO = 20 ;
这里，只有
EMPNO
上的索引是唯一性的，所以
EMPNO
索引将用来检索记录．
TABLE ACCESS BY ROWID ON EMP
INDEX UNIQUE SCAN ON EMP_NO_IDX
34.
避免在索引列上使用
NOT
通常， 我们要避免在索引列上使用
NOT, NOT
会产生在和在索引列上使用函数相同的
影响
.
当
ORACLE”
遇到
”NOT,
他就会停止使用索引转而执行全表扫描
.
举例
:
-
低效
-
(
这里
,
不使用索引
)
SELECT …
FROM DEPT
WHERE DEPT_CODE NOT = 0;
-
高效
-
(
这里
,
使用了索引
)
SELECT …
FROM DEPT
WHERE DEPT_CODE > 0;
需要注意的是
,
在某些时候
, ORACLE
优化器会自动将
NOT
转化成相对应的关系操作符
.
NOT > to <=
NOT >= to <
NOT < to >=
NOT <= to >
译者按
:
在这个例子中
,
作者犯了一些错误
.
例子中的低效率
SQL
是不能被执行的
.
我做了一些测试
:
SQL> select * from emp where NOT empno > 1;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘EMP’
2 1 INDEX (RANGE SCAN) OF ‘EMPNO’ (UNIQUE)
SQL> select * from emp where empno <= 1;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘EMP’
2 1 INDEX (RANGE SCAN) OF ‘EMPNO’ (UNIQUE)
两者的效率完全一样，也许这符合作者关于
”
在某些时候
, ORACLE
优化器会自动将
NOT
转化成相对应的关系操作符
”
的观点．
35.
用
>=
替代
>
如果
DEPTNO
上有一个索引
,
高效
:
SELECT *
FROM EMP
WHERE DEPTNO >=4
低效
:
SELECT *
FROM EMP
WHERE DEPTNO >3
两者的区别在于
,
前者
DBMS
将直接跳到第一个
DEPT
等于
4
的记录而后者将首先定位到
DEPTNO=3
的记录并且向前扫描到第一个
DEPT
大于
3
的记录
.
36.
用
UNION
替换
OR (
适用于索引列
)
通常情况下
,
用
UNION
替换
WHERE
子句中的
OR
将会起到较好的效果
.
对索引列使用
OR
将造成全表扫描
.
注意
,
以上规则只针对多个索引列有效
.
如果有
column
没有被索引
,
查询效率可能会因为你没有选择
OR
而降低
.
在下面的例子中
, LOC_ID
和
REGION
上都建有索引
.
高效
:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = “MELBOURNE”
低效
:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
如果你坚持要用
OR,
那就需要返回记录最少的索引列写在最前面
.
注意
:
WHERE KEY1 = 10 (
返回最少记录
)
OR KEY2 = 20 (
返回最多记录
)
ORACLE
内部将以上转换为
WHERE KEY1 = 10 AND
((NOT KEY1 = 10) AND KEY2 = 20)
译者按
:
下面的测试数据仅供参考
: (a = 1003
返回一条记录
, b = 1
返回
1003
条记录
)
SQL> select * from unionvsor /*1st test*/
2 where a = 1003 or b = 1;
1003 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 CONCATENATION
2 1 TABLE ACCESS (BY INDEX ROWID) OF ‘UNIONVSOR’
3 2 INDEX (RANGE SCAN) OF ‘UB’ (NON-UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF ‘UNIONVSOR’
5 4 INDEX (RANGE SCAN) OF ‘UA’ (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
144 consistent gets
0 physical reads
0 redo size
63749 bytes sent via SQL*Net to client
7751 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1003 rows processed
SQL> select * from unionvsor /*2nd test*/
2 where b = 1 or a = 1003 ;
1003 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 CONCATENATION
2 1 TABLE ACCESS (BY INDEX ROWID) OF ‘UNIONVSOR’
3 2 INDEX (RANGE SCAN) OF ‘UA’ (NON-UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF ‘UNIONVSOR’
5 4 INDEX (RANGE SCAN) OF ‘UB’ (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
143 consistent gets
0 physical reads
0 redo size
63749 bytes sent via SQL*Net to client
7751 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1003 rows processed
SQL> select * from unionvsor /*3rd test*/
2 where a = 1003
3 union
4 select * from unionvsor
5 where b = 1;
1003 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 UNION-ALL
3 2 TABLE ACCESS (BY INDEX ROWID) OF ‘UNIONVSOR’
4 3 INDEX (RANGE SCAN) OF ‘UA’ (NON-UNIQUE)
5 2 TABLE ACCESS (BY INDEX ROWID) OF ‘UNIONVSOR’
6 5 INDEX (RANGE SCAN) OF ‘UB’ (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
63735 bytes sent via SQL*Net to client
7751 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1003 rows processed
用
UNION
的效果可以从
consistent gets
和
SQL*NET
的数据交换量的减少看出
37.
用
IN
来替换
OR
下面的查询可以被更有效率的语句替换
:
低效
:
SELECT….
FROM LOCATION
WHERE LOC_ID = 10
OR LOC_ID = 20
OR LOC_ID = 30
高效
SELECT…
FROM LOCATION
WHERE LOC_IN IN (10,20,30);
译者按
:
这是一条简单易记的规则，但是实际的执行效果还须检验，在
ORACLE8i
下，两者的执行路径似乎是相同的．
38.
避免在索引列上使用
IS NULL
和
IS NOT NULL
避免在索引中使用任何可以为空的列，
ORACLE
将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录
.
对于复合索引，如果每个列都为空，索引中同样不存在此记录
.
如果至少有一个列不为空，则记录存在于索引中．
举例
:
如果唯一性索引建立在表的
A
列和
B
列上
,
并且表中存在一条记录的
A,B
值为
(123,null) , ORACLE
将不接受下一条具有相同
A,B
值（
123,null
）的记录
(
插入
).
然而如果
所有的索引列都为空，
ORACLE
将认为整个键值为空而空不等于空
.
因此你可以插入
1000
条具有相同键值的记录
,
当然它们都是空
!
因为空值不存在于索引列中
,
所以
WHERE
子句中对索引列进行空值比较将使
ORACLE
停用该索引
.
举例
:
-
低效
-
(
索引失效
)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL;
-
高效
-
(
索引有效
)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE >=0;
39.
总是使用索引的第一个列
如果索引是建立在多个列上
,
只有在它的第一个列
(leading column)
被
where
子句引用时
,
优化器才会选择使用该索引
.
译者按
:
这也是一条简单而重要的规则
.
见以下实例
.
SQL> create table multiindexusage ( inda number , indb number , descr varchar2(10));
Table created.
SQL> create index multindex on multiindexusage(inda,indb);
Index created.
SQL> set autotrace traceonly
SQL> select * from multiindexusage where inda = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘MULTIINDEXUSAGE’
2 1 INDEX (RANGE SCAN) OF ‘MULTINDEX’ (NON-UNIQUE)
SQL> select * from multiindexusage where indb = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF ‘MULTIINDEXUSAGE’
很明显
,
当仅引用索引的第二个列时
,
优化器使用了全表扫描而忽略了索引
40.
ORACLE
内部操作
当执行查询时
,ORACLE
采用了内部的操作
.
下表显示了几种重要的内部操作
.
| ORACLE Clause | |
| 内部操作 | |
| ORDER BY | |
| SORT ORDER BY | |
| UNION | |
| UNION-ALL | |
| MINUS | |
| MINUS | |
| INTERSECT | |
| INTERSECT | |
| DISTINCT,MINUS,INTERSECT,UNION | |
| SORT UNIQUE | |
| MIN,MAX,COUNT | |
| SORT AGGREGATE | |
| GROUP BY | |
| SORT GROUP BY | |
| ROWNUM | |
| COUNT or COUNT STOPKEY | |
| Queries involving Joins | |
| SORT JOIN,MERGE JOIN,NESTED LOOPS | |
| CONNECT BY | |
| CONNECT BY | |
41.
用
UNION-ALL
替换
UNION (
如果有可能的话
)
当
SQL
语句需要
UNION
两个查询结果集合时
,
这两个结果集合会以
UNION-ALL
的方式被合并
,
然后在输出最终结果前进行排序
.
如果用
UNION ALL
替代
UNION,
这样排序就不是必要了
.
效率就会因此得到提高
.
举例
:
低效：
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
高效
:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
译者按
:
需要注意的是，
UNION ALL
将重复输出两个结果集合中相同记录
.
因此各位还是
要从业务需求分析使用
UNION ALL
的可行性
.
UNION
将对结果集合排序
,
这个操作会使用到
SORT_AREA_SIZE
这块内存
.
对于这
块内存的优化也是相当重要的
.
下面的
SQL
可以用来查询排序的消耗量
Select substr(name,1,25) “Sort Area Name”,
substr(value,1,15) “Value”
from v$sysstat
where name like ‘sort%’
42.
使用提示
(Hints)
对于表的访问
,
可以使用两种
Hints.
FULL
和
ROWID
FULL hint
告诉
ORACLE
使用全表扫描的方式访问指定表
.
例如
:
SELECT /*+ FULL(EMP) */ *
FROM EMP
WHERE EMPNO = 7893;
ROWID hint
告诉
ORACLE
使用
TABLE ACCESS BY ROWID
的操作访问表
.
通常
,
你需要采用
TABLE ACCESS BY ROWID
的方式特别是当访问大表的时候
,
使用这种方式
,
你需要知道
ROIWD
的值或者使用索引
.
如果一个大表没有被设定为缓存
(CACHED)
表而你希望它的数据在查询结束是仍然停留
在
SGA
中
,
你就可以使用
CACHE hint
来告诉优化器把数据保留在
SGA
中
.
通常
CACHE hint
和
FULL hint
一起使用
.
例如
:
SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ *
FROM WORK;
索引
hint
告诉
ORACLE
使用基于索引的扫描方式
.
你不必说明具体的索引名称
例如
:
SELECT /*+ INDEX(LODGING) */ LODGING
FROM LODGING
WHERE MANAGER = ‘BILL GATES’;
在不使用
hint
的情况下
,
以上的查询应该也会使用索引
,
然而
,
如果该索引的重复值过多而你的优化器是
CBO,
优化器就可能忽略索引
.
在这种情况下
,
你可以用
INDEX hint
强制
ORACLE
使用该索引
.
ORACLE hints
还包括
ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HASH
等等
.
译者按
:
使用
hint ,
表示我们对
ORACLE
优化器缺省的执行路径不满意
,
需要手工修改
.
这是一个很有技巧性的工作
.
我建议只针对特定的
,
少数的
SQL
进行
hint
的优化
.
对
ORACLE
的优化器还是要有信心
(
特别是
CBO)
43.
用
WHERE
替代
ORDER BY
ORDER BY
子句只在两种严格的条件下使用索引
.
ORDER BY
中所有的列必须包含在相同的索引中并保持在索引中的排列顺序
.
ORDER BY
中所有的列必须定义为非空
.
WHERE
子句使用的索引和
ORDER BY
子句中所使用的索引不能并列
.
例如
:
表
DEPT
包含以下列
:
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL
非唯一性的索引
(DEPT_TYPE)
-
低效
-
(
索引不被使用
)
SELECT DEPT_CODE
FROM DEPT
ORDER BY DEPT_TYPE
EXPLAIN PLAN:
SORT ORDER BY
TABLE ACCESS FULL
-
高效
-
(
使用索引
)
SELECT DEPT_CODE
FROM DEPT
WHERE DEPT_TYPE > 0
EXPLAIN PLAN:
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON DEPT_IDX
译者按
:
ORDER BY
也能使用索引
!
这的确是个容易被忽视的知识点
.
我们来验证一下
:
SQL> select * from emp order by empno;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘EMP’
2 1 INDEX (FULL SCAN) OF ‘EMPNO’ (UNIQUE)
44.
避免改变索引列的类型
.
当比较不同数据类型的数据时
, ORACLE
自动对列进行简单的类型转换
.
假设
EMPNO
是一个数值类型的索引列
.
SELECT …
FROM EMP
WHERE EMPNO = ‘123’
实际上
,
经过
ORACLE
类型转换
,
语句转化为
:
SELECT …
FROM EMP
WHERE EMPNO = TO_NUMBER(‘123’)
幸运的是
,
类型转换没有发生在索引列上
,
索引的用途没有被改变
.
现在
,
假设
EMP_TYPE
是一个字符类型的索引列
.
SELECT …
FROM EMP
WHERE EMP_TYPE = 123
这个语句被
ORACLE
转换为
:
SELECT …
FROM EMP
WHERE TO_NUMBER(EMP_TYPE)=123
因为内部发生的类型转换
,
这个索引将不会被用到
!
译者按
:
为了避免
ORACLE
对你的
SQL
进行隐式的类型转换
,
最好把类型转换用显式表现出来
.
注意当字符和数值比较时
, ORACLE
会优先转换数值类型到字符类型
.
45.
需要当心的
WHERE
子句
某些
SELECT
语句中的
WHERE
子句不使用索引
.
这里有一些例子
.
在下面的例子里
, ‘!=’
将不使用索引
.
记住
,
索引只能告诉你什么存在于表中
,
而不能告诉你什么不存在于表中
.
不使用索引
:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT !=0;
使用索引
:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT >0;
下面的例子中
, ‘||’
是字符连接函数
.
就象其他函数那样
,
停用了索引
.
不使用索引
:
SELECT ACCOUNT_NAME,AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME||ACCOUNT_TYPE=’AMEXA’;
使用索引
:
SELECT ACCOUNT_NAME,AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME = ‘AMEX’
AND ACCOUNT_TYPE=’ A’;
下面的例子中
, ‘+’
是数学函数
.
就象其他数学函数那样
,
停用了索引
.
不使用索引
:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE AMOUNT + 3000 >5000;
使用索引
:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE AMOUNT > 2000 ;
下面的例子中
,
相同的索引列不能互相比较
,
这将会启用全表扫描
.
不使用索引
:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME = NVL(:ACC_NAME,ACCOUNT_NAME);
使用索引
:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME,’%’);
译者按
:
如果一定要对使用函数的列启用索引
, ORACLE
新的功能
:
基于函数的索引
(Function-Based Index)
也许是一个较好的方案
.
CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*
建立基于函数的索引
*/
SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’; /*
将使用索引
*/
46.
连接多个扫描
如果你对一个列和一组有限的值进行比较
,
优化器可能执行多次扫描并对结果进行合并连接
.
举例
:
SELECT *
FROM LODGING
WHERE MANAGER IN (‘BILL GATES’,’KEN MULLER’);
优化器可能将它转换成以下形式
网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。
需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注软件测试）
一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
DE
FROM DEPT
ORDER BY DEPT_TYPE
EXPLAIN PLAN:
SORT ORDER BY
TABLE ACCESS FULL
-
高效
-
(
使用索引
)
SELECT DEPT_CODE
FROM DEPT
WHERE DEPT_TYPE > 0
EXPLAIN PLAN:
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON DEPT_IDX
译者按
:
ORDER BY
也能使用索引
!
这的确是个容易被忽视的知识点
.
我们来验证一下
:
SQL> select * from emp order by empno;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘EMP’
2 1 INDEX (FULL SCAN) OF ‘EMPNO’ (UNIQUE)
44.
避免改变索引列的类型
.
当比较不同数据类型的数据时
, ORACLE
自动对列进行简单的类型转换
.
假设
EMPNO
是一个数值类型的索引列
.
SELECT …
FROM EMP
WHERE EMPNO = ‘123’
实际上
,
经过
ORACLE
类型转换
,
语句转化为
:
SELECT …
FROM EMP
WHERE EMPNO = TO_NUMBER(‘123’)
幸运的是
,
类型转换没有发生在索引列上
,
索引的用途没有被改变
.
现在
,
假设
EMP_TYPE
是一个字符类型的索引列
.
SELECT …
FROM EMP
WHERE EMP_TYPE = 123
这个语句被
ORACLE
转换为
:
SELECT …
FROM EMP
WHERE TO_NUMBER(EMP_TYPE)=123
因为内部发生的类型转换
,
这个索引将不会被用到
!
译者按
:
为了避免
ORACLE
对你的
SQL
进行隐式的类型转换
,
最好把类型转换用显式表现出来
.
注意当字符和数值比较时
, ORACLE
会优先转换数值类型到字符类型
.
45.
需要当心的
WHERE
子句
某些
SELECT
语句中的
WHERE
子句不使用索引
.
这里有一些例子
.
在下面的例子里
, ‘!=’
将不使用索引
.
记住
,
索引只能告诉你什么存在于表中
,
而不能告诉你什么不存在于表中
.
不使用索引
:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT !=0;
使用索引
:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT >0;
下面的例子中
, ‘||’
是字符连接函数
.
就象其他函数那样
,
停用了索引
.
不使用索引
:
SELECT ACCOUNT_NAME,AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME||ACCOUNT_TYPE=’AMEXA’;
使用索引
:
SELECT ACCOUNT_NAME,AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME = ‘AMEX’
AND ACCOUNT_TYPE=’ A’;
下面的例子中
, ‘+’
是数学函数
.
就象其他数学函数那样
,
停用了索引
.
不使用索引
:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE AMOUNT + 3000 >5000;
使用索引
:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE AMOUNT > 2000 ;
下面的例子中
,
相同的索引列不能互相比较
,
这将会启用全表扫描
.
不使用索引
:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME = NVL(:ACC_NAME,ACCOUNT_NAME);
使用索引
:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME,’%’);
译者按
:
如果一定要对使用函数的列启用索引
, ORACLE
新的功能
:
基于函数的索引
(Function-Based Index)
也许是一个较好的方案
.
CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*
建立基于函数的索引
*/
SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’; /*
将使用索引
*/
46.
连接多个扫描
如果你对一个列和一组有限的值进行比较
,
优化器可能执行多次扫描并对结果进行合并连接
.
举例
:
SELECT *
FROM LODGING
WHERE MANAGER IN (‘BILL GATES’,’KEN MULLER’);
优化器可能将它转换成以下形式
网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。
需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注软件测试）
[外链图片转存中…(img-h60Vwx6u-1713341396310)]
一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
249
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
