ORACLE数据库SQL语句编写优化总结
ORACLE数据库
SQL语句编写优化总结
ORACLE (3)
1、选用适合的ORACLE优化器 (3)
2、访问Table的方式 (3)
3、共享SQL语句 (4)
4、选择最有效率的表名顺序（只在基于规则的优化器中有效） (5)
5、WHERE子句中的连接顺序． (6)
6、SELECT子句中避免使用 ‘*’ (6)
7、减少访问数据库的次数 (6)
8、使用DECODE函数来减少处理时间 (7)
9、整合简单，无关联的数据库访问 (8)
10、删除重复记录 (8)
11、用TRUNCATE替代DELETE (9)
12、尽量多使用COMMIT (9)
13、计算记录条数 (9)
14、用Where子句替换HA VING子句 (9)
15、减少对表的查询 (10)
16、通过内部函数提高SQL效率。 (11)
17、使用表的别名（Alias） (12)
18、用EXISTS替代IN (12)
19、用NOT EXISTS替代NOT IN (12)
20、用表连接替换EXISTS (13)
21、用EXISTS替换DISTINCT (13)
22、识别’低效执行’的SQL语句 (14)
23、使用TKPROF 工具来查询SQL性能状态 (14)
24、用EXPLAIN PLAN 分析SQL语句 (14)
25、用索引提高效率 (15)
26、索引的操作 (16)
27、基础表的选择 (17)
28、多个平等的索引 (18)
29、等式比较和范围比较 (18)
30、不明确的索引等级 (19)
31、强制索引失效 (20)
32。避免在索引列上使用计算 (20)
33、自动选择索引 (21)
34、避免在索引列上使用NOT (21)
35。用>=替代> (22)
36、用UNION替换OR （适用于索引列） (22)
37、用IN来替换OR (25)
38、避免在索引列上使用IS NULL和IS NOT NULL (25)
39、总是使用索引的第一个列 (26)
40、ORACLE内部操作 (26)
41、用UNION-ALL 替换UNION （如果有可能的话） (27)
42、使用提示（Hints） (28)
43、用WHERE替代ORDER BY (29)
44、避免改变索引列的类型。 (29)
45、需要当心的WHERE子句 (30)
46、连接多个扫描 (31)
47、CBO下使用更具选择性的索引 (32)
48、避免使用耗费资源的操作 (32)
49、优化GROUP BY (32)
50、使用显式的游标（CURSORS） (33)
51、分离表和索引 (33)
ORACLE
1、选用适合的ORACLE优化器
ORACLE的优化器共有3种:
a、RULE （基于规则）
b、COST （基于成本）
c、CHOOSE （选择性）
设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE、COST、CHOOSE、ALL_ROWS、FIRST_ROWS，当然也可以在SQL句级或是会话（session）级对其进行覆盖。
为了使用基于成本的优化器（CBO，Cost-Based Optimizer），必须经常运行analyze 命令（相当于DB2中的runstats），以增加数据库中的对象统计信息（object statistics）的准确性。
如果数据库的优化器模式设置为选择性（CHOOSE），那么实际的优化器模式将和是否运行过analyze命令有关，如果table已经被analyze过，优化器模式将自动成为CBO，反之，数据库将采用RULE形式的优化器。
在缺省情况下，ORACLE采用的是CHOOSE优化器，如果你的数据库设计的不好，也不经常统计信息，最好用RULE模式，因为在CHOOSE模式下会产生很多全表扫描（FULL SCAN），数据库中最忌讳的就是对大表进行FULL SCAN，但是必须情况下除外。
2、访问Table的方式
ORACLE采用两种访问表中记录的方式：
a、全表扫描
全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入多个数据块（database block）的方式优化全表扫描。对于大表尽量避免产生全表扫描。
b、通过ROWID访问表
可以采用基于ROWID的访问方式访问表记录，提高访问效率。ROWID包含了表中记录的物理位置信息，ORACLE采用索引（INDEX）实现了数据和存放数据的物理位置（ROWID）之间的联系。通常索引提供了快速访问ROWID的方法，因此那些基
于索引列的查询就可以得到性能上的提高。
3、共享SQL语句
为了不重复解析相同的SQL语句，在第一次解析之后，ORACLE将SQL语句存放在内存中。这块位于系统全局区域SGA（system global area）的共享池（shared buffer pool）中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句（有时被称为一个游标）时，如果它和之前的执行过的语句完全相同，ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用。
可惜的是ORACLE只对简单的表提供高速缓冲（cache buffering），这个功能并不适用于多表连接查询。
数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。
当你向ORACLE提交一个SQL语句，ORACLE会首先在这块内存中查找相同的语句。这里需要注明的是，ORACLE对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同（包括空格，换行等）。
共享的语句必须满足三个条件：
A、字符级的比较:
当前被执行的语句和共享池中的语句必须完全相同。
例如:
SELECT * FROM EMP;
和下列每一个都不同
SELECT * from EMP;
Select * From Emp;
SELECT * FROM EMP;
B、两个语句所指的对象必须完全相同:
例如:
用户对象名如何访问
Jack
sal_limit
private
synonym
Work_city public
synonym
Plant_detail public
synonym
Jill
sal_limit
private
synonym
Work_city public
synonym
Plant_detail table
owner
SQL语句能否在这两个用户之间共享，见下表
SQL 能否共享原因
select max(sal_cap) from sal_limit; 不能每个用户都有一个private synonym - sal_limit ，它们是不同的对象
select count（*）from work_city where sdesc like 'NEW%'; 能
两个用户访问相同的对象public
synonym - work_city
select a.sdesc,b.location from work_city
a ,plant_detail
b where a.city_id = b.city_id
不能
用户jack 通过public synonym 访问plant_detail 而jill 是表的所有者，对象不同。
C 、两个SQL 语句中必须使用相同的名字的绑定变量（bind variables ） 例如：第一组的两个SQL 语句是相同的（可以共享），而第二组中的两个语句是不同的（即使在运行时，赋予不同的绑定变量相同的值）
a ．
select pin, name from people where pin = :blk1.pin; select pin, name from people where pin = :blk1.pin; b ．
select pin, name from people where pin = :blk1.ot_ind; select pin, name from people where pin = :blk1.ov_ind;
4、选择最有效率的表名顺序（只在基于规则的优化器中有效）
ORACLE 的解析器按照从右到左的顺序处理FROM 子句中的表名，因此FROM 子句中写在最后的表（基础表 driving table ）将被最先处理。在FROM 子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE 处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表（FROM 子句中最后的那个表）并对记录进行派序，然后扫描第二个表（FROM 子句中最后第二个表），最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。
例如：表 TAB1 16384 条记录
表 TAB2 1 条记录
选择TAB2作为基础表（最好的方法) select count (*) from tab1, tab2
选择TAB1作为基础表 （不佳的方法） select count (*) from tab2, tab1
两者相差的时间可能在几十倍甚至几百倍。
如果有3个以上的表连接查询，那就需要选择交叉表（intersection table ）作为基础表， 交叉表是指那个被其他表所引用的表。
例如: EMP 表描述了LOCATION 表和CATEGORY 表的交集。 SELECT *
FROM LOCATION L, CATEGORY C, EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN 将比下面SQL 更有效率 SELECT * FROM EMP E,
LOCATION L,
CATEGORY C
WHERE E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000
5、WHERE子句中的连接顺序．
ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
例如：
(低效)
SELECT …
FROM EMP E
WHERE SAL > 50000
AND JOB = ‘MANAGER’
AND 25 < (SELECT COUNT (*) FROM EMP
WHERE MGR=E.EMPNO);
(高效）
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT (*) FROM EMP
WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = ‘MANAGER’;
6、SELECT子句中避免使用 ‘*’
当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用 ‘*’ 是一个方便的方法。不幸的是，这是一个非常低效的方法。实际上，ORACLE在解析的过程中，会将‘*’依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。
7、减少访问数据库的次数
当执行每条SQL语句时，ORACLE在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据库的次数，就能实际上减少ORACLE的工作量。
例如
以下有三种方法可以检索出雇员号等于0342或0291的职员。
方法1（最低效）
SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE EMP_NO = 342;
SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE EMP_NO = 291;
方法2 （次低效）
DECLARE
CURSOR C1 （E_NO NUMBER） IS
SELECT EMP_NAME，SALARY，GRADE
FROM EMP
WHERE EMP_NO = E_NO;
BEGIN
OPEN C1（342）;
FETCH C1 INTO …, …, …;
OPEN C1（291）;
FETCH C1 INTO …, …, …;
CLOSE C1;
END;
方法3 （高效）
SELECT A.EMP_NAME , A.SALARY , A.GRADE,
B.EMP_NAME , B.SALARY , B.GRADE
FROM EMP A, EMP B
WHERE A.EMP_NO = 342 AND B.EMP_NO = 291;
注意:
在SQL*Plus，SQL*Forms和Pro*C中重新设置ARRAYSIZE参数，可以增加每次数据库访问的检索数据量，建议值为200。
8、使用DECODE函数来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。
例如:
SELECT COUNT(*), SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020 AND ENAME LIKE ‘SMITH%’;
SELECT COUNT(*), SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0030 AND ENAME LIKE ‘SMITH%’;
你可以用DECODE函数高效地得到相同结果
SELECT COUNT(DECODE(DEPT_NO, 0020, ’X’, NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO, 0030, ’X’, NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO, 0020, SAL, NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO, 0030, SAL, NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%’;
类似的，DECODE函数也可以运用于GROUP BY 和ORDER BY子句中。
9、整合简单，无关联的数据库访问
如果你有几个简单的数据库查询语句，你可以把它们整合到一个查询中（即使它们之间没有关系）
例如：
SELECT NAME
FROM EMP
WHERE EMP_NO = 1234;
SELECT NAME
FROM DPT
WHERE DPT_NO = 10 ;
SELECT NAME
FROM CAT
WHERE CAT_TYPE = ‘RD’;
上面的3个查询可以被合并成一个:
SELECT https://www.360docs.net/doc/fd657454.html,, https://www.360docs.net/doc/fd657454.html,, https://www.360docs.net/doc/fd657454.html,
FROM CAT C, DPT D, EMP E, DUAL X
WHERE NVL(‘X’, X.DUMMY) = NVL(‘X’, E.ROWID(+))
AND NVL(‘X’, X..DUMMY) = NVL(‘X’, D.ROWID(+))
AND NVL(‘X’, X..DUMMY) = NVL(‘X’, C.ROWID(+))
AND E.EMP_NO(+) = 1234
AND D.DEPT_NO(+) = 10
AND C.CAT_TYPE(+) = ‘RD’;
（虽然采取这种方法，效率得到提高，但是程序的可读性大大降低，所以最后实际采用哪种方式还是要权衡之间的利弊）
10、删除重复记录
最高效的删除重复记录方法（因为使用了ROWID）
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN（X.ROWID）
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO）;
11、用TRUNCATE替代DELETE
当删除表中的记录时，在通常情况下，回滚段（rollback segments）用来存放可以被恢复的信息。如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态（准确地说是恢复到执行删除命令之前的状况）
而当运用TRUNCATE时，回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。
（TRUNCATE只在删除全表适用，TRUNCATE是DDL不是DML）
12、尽量多使用COMMIT
只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源：
A．回滚段上用于恢复数据的信息。
B．被程序语句获得的锁
C．redo log buffer 中的空间
D．ORACLE为管理上述3种资源中的内部花费
（在使用COMMIT时必须要注意到事务的完整性，现实中效率和事务完整性往往是鱼和熊掌不可得兼，根据需求自己平衡）
13、计算记录条数
和一般的观点相反，count(*) 比count(1)稍快，当然如果可以通过索引检索，对索引列的计数仍旧是最快的。例如 COUNT（EMPNO），所以索引建立的合理性非常重要。
（在网上，曾经对此有过相当热烈的讨论。通过实际的测试，上述三种方法并没有显著的性能差别，但本人观点并不十分准确，因为这还涉及到很多oracle内部的优化机制，不同版本的oracle也不大相同）
14、用Where子句替换HA VING子句
避免使用HA VING子句，HA VING只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。
例如：
低效:
SELECT REGION, A VG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HA VING REGION REGION != ‘SYDNEY’
AND REGION != ‘PERTH’
高效
SELECT REGION, A VG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != ‘SYDNEY’
AND REGION != ‘PERTH’
GROUP BY REGION
（HA VING 中的条件一般用于对一些集合函数的比较，如COUNT（）等等。除此而外，一般的条件应该写在WHERE子句中）
15、减少对表的查询
在含有子查询的SQL语句中，要特别注意减少对表的查询。
例如:
低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = (SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_VER = (SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604）
高效
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME，DB_VER)
= ( SELECT TAB_NAME, DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604）
Update 多个Column 例子:
低效：
UPDATE EMP
SET EMP_CAT = （SELECT MAX（CATEGORY） FROM EMP_CATEGORIES）， SAL_RANGE = (SELECT MAX (SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;
高效:
UPDATE EMP
SET (EMP_CAT, SAL_RANGE)
= (SELECT MAX(CATEGORY), MAX(SAL_RANGE)
FROM EMP_CATEGORIES）
WHERE EMP_DEPT = 0020;
16、通过内部函数提高SQL效率。
SELECT H.EMPNO, E.ENAME, H.HIST_TYPE, T.TYPE_DESC, COUNT(*)
FROM HISTORY_TYPE T, EMP E, EMP_HISTORY H
WHERE H.EMPNO = E.EMPNO AND H.HIST_TYPE = T.HIST_TYPE
GROUP BY H.EMPNO, E.ENAME, H.HIST_TYPE, T.TYPE_DESC;
通过调用下面的函数可以提高效率。
FUNCTION LOOKUP_HIST_TYPE（TYP IN NUMBER） RETURN V ARCHAR2
AS
TDESC V ARCHAR2（30）;
CURSOR C1 IS
SELECT TYPE_DESC
FROM HISTORY_TYPE
WHERE HIST_TYPE = TYP;
BEGIN
OPEN C1;
FETCH C1 INTO TDESC;
CLOSE C1;
RETURN （NVL（TDESC，’?’））;
END;
FUNCTION LOOKUP_EMP（EMP IN NUMBER） RETURN V ARCHAR2
AS
ENAME V ARCHAR2（30）;
CURSOR C1 IS
SELECT ENAME
FROM EMP
WHERE EMPNO=EMP;
BEGIN
OPEN C1;
FETCH C1 INTO ENAME;
CLOSE C1;
RETURN （NVL（ENAME，’?’））;
END;
SELECT H.EMPNO, LOOKUP_EMP（H.EMPNO），
H.HIST_TYPE, LOOKUP_HIST_TYPE（H.HIST_TYPE）,COUNT（*）
FROM EMP_HISTORY H
GROUP BY H.EMPNO, H.HIST_TYPE;
（经常有人想’能不能用一个SQL写出…’ 的想法，殊不知复杂的SQL往往牺牲了执行效率，这在大数据量时往往是致命的。能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的）
17、使用表的别名（Alias）
当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
（Column歧义指的是由于SQL中不同的表具有相同的Column名，当SQL语句中出现这个Column时，SQL解析器无法判断这个Column的归属）
18、用EXISTS替代IN
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS（或NOT EXISTS）通常将提高查询的效率。
低效:
SELECT *
FROM EMP （基础表）
WHERE EMPNO > 0
AND DEPTNO IN （SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’）
高效:
SELECT *
FROM EMP （基础表）
WHERE EMPNO > 0
AND EXISTS （SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = ‘MELB’）
（在大数据量时，这个操作非常明显，相对来说，用NOT EXISTS替换NOT IN 将更显著地提高效率）
19、用NOT EXISTS替代NOT IN
在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的（因为它对子查询中的表执行了一个全表遍历）。为了避免使用NOT IN，我们可以把它改写成外连接（Outer Joins）或NOT EXISTS。
例如：
SELECT …
FROM EMP
WHERE DEPT_NO NOT IN （SELECT DEPT_NO
FROM DEPT
WHERE DEPT_CAT=’A’）;
为了提高效率，改写为：
（方法一：高效）
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提供数据的操作，其中操作号最小的将被最先处理。
举例:
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工具。喜欢图形化界面的朋友们可以选用它们。
本人最初使用的是oracle自带的分析工具，很简单也很实用，之前在做省行的一个项目时就经常用，可以在不读数据的情况下分析sql语句，但是前提是数据库要经常做analyse，保持统计的数据准确。
25、用索引提高效率
索引是表的一个概念部分，用来提高检索数据的效率。实际上，ORACLE使用了一个复杂的自平衡B-tree结构。通常，通过索引查询数据比全表扫描要快。当ORACLE找出执行查询和Update语句的最佳路径时，ORACLE优化器将使用索引。同样在联结多个表时使用索引也可以提高效率。另一个使用索引的好处是，它提供了主键（primary key）的唯一性
验证。
除了那些LONG或LONG RAW数据类型，可以索引几乎所有的列。通常，在大型表中使用索引特别有效。当然，在扫描小表时，使用索引同样能提高效率。
虽然使用索引能得到查询效率的提高，但是也必须注意到它的代价。索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。这就涉及到你所使用的系统是OLTP还是OLAP类型的，以此决定索引的建立。
注意:
定期的重构索引是非常有必要的。
ALTER INDEX
26、索引的操作
ORACLE对索引有两种访问模式。
A：索引唯一扫描（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’;
B：索引范围查询（INDEX RANGE SCAN）
适用于两种情况:
1、基于一个范围的检索
2、基于非唯一性索引的检索
例1:
SELECT LODGING
FROM LODGING
WHERE LODGING LIKE ‘M%’;
WHERE子句条件包括一系列值，ORACLE将通过索引范围查询的方式查询LODGING_PK。由于索引范围查询将返回一组值，它的效率就要比索引唯一扫描低一些。
例2:
SELECT LODGING
FROM LODGING
WHERE MANAGER = ‘BILL GATES’;
这个SQL的执行分两步，LODGING$MANAGER的索引范围查询（得到所有符合条件记录的ROWID）和下一步同过ROWID访问表得到LODGING列的值。由于LODGING$MANAGER是一个非唯一性的索引，数据库不能对它执行索引唯一扫描。
由于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 https://www.360docs.net/doc/fd657454.html,，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时才能采用它。绝大部分时间oracle都能选择最优的优化策略。
这里有一个例子关于何时采用这种策略。
假设在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子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。
上面31说的使索引失效就是在索引列上面计算了。
举例:
2021年SQL语句大全实例
SQL语句实例 欧阳光明(2021.03. 07) 表操作 例1对于表的教学管理数据库中的表STUDENTS ,可以定义如下： CREATE TABLE STUDENTS (SNO NUMERIC (6, 0) NOT NULL SNAME CHAR (8) NOT NULL AGE NUMERIC(3,0) SEX CHAR(2) BPLACE CHAR(20) PRIMARY KEY(SNO)) 例2对于表的教学管理数据库中的表ENROLLS ,可以定义如下：CREATE TABLE ENROLLS (SNO NUMERIC(6,0) NOT NULL CNO CHAR(4) NOT NULL GRADE INT PRIMARY KEY(SNO,CNO) FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO) FOREIGN KEY(CNO) REFERENCES COURSES(CNO)
*欧阳光明*创编2021.03.07 CHECK ((GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100))) 例3根据表的STUDENTS表，建立一个只包含学号.姓名.年龄的女学生表。 CREATE TABLE GIRL AS SELECT SNO, SNAME, AGE FROM STUDENTS WHERE SEX=女?; 例4删除教师表TEACHER。 DROP TABLE TEACHER 例5在教师表中增加住址列。 ALTER TABLE TEACHERS ADD (ADDR CHAR(50)) 例6把STUDENTS表中的BPLACE列删除，并且把引用 BPLACE列的所有视图和约束也一起删除。 ALTER TABLE STUDENTS DROP BPLACE CASCADE 例7补充定义ENROLLS表的主关键字。 ALTER TABLE ENROLLS ADD PRIMARY KEY (SNO,CNO);
OracleSQL的优化
Oracle SQL的优化 标签：oraclesql优化date数据库subquery 2009-10-14 21:18 18149人阅读评论(21) 收藏举报分类： Oracle Basic Knowledge（208） SQL的优化应该从5个方面进行调整： 1.去掉不必要的大型表的全表扫描 2.缓存小型表的全表扫描 3.检验优化索引的使用 4.检验优化的连接技术 5.尽可能减少执行计划的Cost SQL语句： 是对数据库(数据)进行操作的惟一途径； 消耗了70%~90%的数据库资源；独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低； 可以有不同的写法；易学，难精通。 SQL优化： 固定的SQL书写习惯，相同的查询尽量保持相同，存储过程的效率较高。 应该编写与其格式一致的语句，包括字母的大小写、标点符号、换行的位置等都要一致 ORACLE优化器： 在任何可能的时候都会对表达式进行评估，并且把特定的语法结构转换成等价的结构，这么做的原因是 要么结果表达式能够比源表达式具有更快的速度 要么源表达式只是结果表达式的一个等价语义结构 不同的SQL结构有时具有同样的操作（例如： = ANY (subquery) and IN (subquery)），ORACLE会把他们映射到一个单一的语义结构。 1 常量优化： 常量的计算是在语句被优化时一次性完成，而不是在每次执行时。下面是检索月薪大于2000的的表达式： sal > 24000/12
sal > 2000 sal*12 > 24000 如果SQL语句包括第一种情况，优化器会简单地把它转变成第二种。 优化器不会简化跨越比较符的表达式，例如第三条语句，鉴于此，应尽量写用常量跟字段比较检索的表达式，而不要将字段置于表达式当中。否则没有办法优化，比如如果sal上有索引，第一和第二就可以使用，第三就难以使用。 2 操作符优化： 优化器把使用LIKE操作符和一个没有通配符的表达式组成的检索表达式转换为一个“=”操作符表达式。 例如：优化器会把表达式ename LIKE 'SMITH'转换为ename = 'SMITH' 优化器只能转换涉及到可变长数据类型的表达式，前一个例子中，如果ENAME 字段的类型是CHAR(10)，那么优化器将不做任何转换。 一般来讲LIKE比较难以优化。 其中： ~~IN 操作符优化： 优化器把使用IN比较符的检索表达式替换为等价的使用“=”和“OR”操作符的检索表达式。 例如，优化器会把表达式ename IN ('SMITH','KING','JONES')替换为 ename = 'SMITH' OR ename = 'KING' OR ename = 'JONES‘ oracle 会将 in 后面的东西生成一存中的临时表。然后进行查询。 如何编写高效的SQL: 当然要考虑sql常量的优化和操作符的优化啦，另外，还需要： 1 合理的索引设计： 例：表record有620000行，试看在不同的索引下，下面几个SQL的运行情况：语句A SELECT count(*) FROM record WHERE date >'19991201' and date <'19991214‘and amount >2000 语句B
SQL学习总结
SQL学习 2．1 SQL 简介 当面对一个陌生的数据库是，通常需要一种方式与它进行交换，以完成用户所需要的各种工作，这个时候，就要用到SQL语言了，由于SQL 语言的标准化，所以大多数关系型数据库系统都支持SQL语言，它已经发展成多种平台进行交互操作的底层会话语言。 2．2 SQL 使用入门 2．2．1 SQL分类 SQL 语句主要可以划分为以下3个类别 1）、DDL (Data Definition Languages)语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括create、drop、alter等。 2）、DML(Data Manipulation Languagr)语句：数据操作语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字主要包括insert、delete、update和select等。 3）、DCL(Data Control Language)语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别、主要的语句关键字包括grant、revoke等。 2．2．2 DDL 语句 DDL是数据定义语言的缩写，是对数据库内部的对象进行创建（create）、删除（drop）、修改（alter）的操作语言，它和DML语句的最大区别是DML只是对表内部数据的操作，而不涉及表定义，结构的修改，更不会涉及其它的对象，DDL语句更多地被数据库管理员（DBA）所使用，一般开发人员很少使用。 下面通过一些例子来介绍MySQL中常用的DDL语句的使用方法。 1、创建数据库 因为所有的数据都存储在数据库中，因此需要学习的第一个命令是创建数据库，语法如下：
大数据库优化(SQLServer)
SQL SERVER性能优化综述 近期因工作需要，希望比较全面的总结下SQL SERVER数据库性能优化相关的注意事项，在 网上搜索了一下,发现很多文章,有的都列出了上百条,但是仔细看发现，有很多似是而非或 者过时(可能对SQL SERVER6.5以前的版本或者ORACLE是适用的)的信息，只好自己根据以 前的经验和测试结果进行总结了。 我始终认为，一个系统的性能的提高，不单单是试运行或者维护阶段的性能调优的任务，也不单单是开发阶段的事情，而是在整个软件生命周期都需要注意，进行有效工作才能达到的。所以我希望按照软件生命周期的不同阶段来总结数据库性能优化相关的注意事项。 一、分析阶段 一般来说，在系统分析阶段往往有太多需要关注的地方，系统各种功能性、可用性、可靠性、安全性需求往往吸引了我们大部分的注意力，但是，我们必须注意，性能是很重要的非功能 性需求，必须根据系统的特点确定其实时性需求、响应时间的需求、硬件的配置等。最好能 有各种需求的量化的指标。 另一方面，在分析阶段应该根据各种需求区分出系统的类型，大的方面，区分是OLTP（联机事务处理系统）和OLAP（联机分析处理系统）。 二、设计阶段 设计阶段可以说是以后系统性能的关键阶段，在这个阶段，有一个关系到以后几乎所有性能 调优的过程—数据库设计。 在数据库设计完成后，可以进行初步的索引设计，好的索引设计可以指导编码阶段写出高效 率的代码，为整个系统的性能打下良好的基础。 以下是性能要求设计阶段需要注意的： 1、数据库逻辑设计的规范化 数据库逻辑设计的规范化就是我们一般所说的范式，我们可以这样来简单理解范式： 第1规范：没有重复的组或多值的列，这是数据库设计的最低要求。 第2规范: 每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组 成部分。消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。 第3规范: 一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。 更高的范式要求这里就不再作介绍了，个人认为，如果全部达到第二范式，大部分达到第三
数据库_经典SQL语句大全
一、基础 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1. dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only 5、说明：删除新表 drop table tabname 6、说明：增加一个列 Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明：添加主键：Alter table tabname add primary key(col) 说明：删除主键： Alter table tabname drop primary key(col) 8、说明：创建索引：create [unique] index idxname on tabname(col….)删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。 9、说明：创建视图：create view viewname as select statement 删除视图：drop view viewname 10、说明：几个简单的基本的sql语句 选择：select * from table1 where 范围 插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围 查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料! 排序：select * from table1 order by field1,field2 [desc] 总数：select count as totalcount from table1 求和：select sum(field1) as sumvalue from table1
数据库及SQL代码优化方案
1.1、数据库及SQL代码优化方案 （1）每周检查统计信息是否及时更新。 （2）每周检查各索引是否有效。 （3）每周检查分区是否正确。 （4）每周检查执行计划是否正确。 （5）每天检查RAC和ASM是否正常运行。 （6）每天检查相关日志是否正常备份。 （7）每天检查相关文件系统和表空间的占用率是否在国家税务总局规定的阀值以下。 （8）在每月申报高峰等业务繁忙期采样并找出消耗I/O资源和CPU资源较多的SQL语句。 （9）分析上述SQL语句，与软件服务商充分沟通后，提出优化建议。 （10）在每月申报高峰期每隔15分钟检查一次数据库连接数，发现异常及时处理。 1.1.1、系统数据库索引、表分区和对象优化方案 数据库对象的优化主要包括：表、索引和sequence等对象，通过优化对象参数、调整对象属性（例如分区表、分区索引、反转索引等等）等方法来实现对数据库对象的优化改造。 1.1.1.1表和索引并行参数优化 数据库的表和索引的并行参数值的设置对相关的sql语句的执行计划会造成影响，表和索引的degree值大于1，执行计划就偏向于使用全表和全索引扫描，另外如果并行参数值过大，短时间内也会对主机和数据库的资源造成很大的压力，因此在oltp的数据库下建议将表和索引的degree值设为1。 1.1.1.2热点大表的分区改造 对访问量很大、表的记录数很多、存在热块争用的表，可以考虑对表和索引进行适当的分区改造，分散访问压力，提高数据访问的性能。 对以下表的记录数超过1000万并且记录数持续增长的大表，建议进行分区
改造（地区+时间）： 1.1.1.3分区索引的清理 对最近30天数据库分区索引访问情况进行统计，对访问次数为0的分区索引和应用部门进行确认，若确认为多余的索引，建议进行删除清理。 1.1.1.4Sequence序列优化 加大sequence 的 cache，并使用noorder选项。在RAC中经常会遇到SQ 锁等待，这是因为在RAC环境下，sequence也成为全局性的了，不同节点要生成序列号，就会产生对sequence资源的争用。而目前大多数系统中，sequence 大多数被作为主键发生器来使用，使用的频率十分高，在RAC环境中，需要设置较大的 sequence cache，否则会造成较为严重的争用，从而影响业务。 1.1.2、SQL硬解析优化方案 1.1. 2.1相关知识点介绍 1.1. 2.1.1Oracle的硬解析和软解析 Oracle对sql的处理过程：当发出一条sql语句交付Oracle，在执行和获取结果前，Oracle对此sql将进行几个步骤的处理过程： 1、语法检查(syntax check) 检查此sql的拼写是否语法。 2、语义检查(semantic check) 诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。 3、对sql语句进行解析(prase) 利用内部算法对sql进行解析，生成解析树(parse tree)及执行计划(execution plan)。 4、执行sql，返回结果(execute and return) 其中，软、硬解析就发生在第三个过程里。 Oracle利用内部的hash算法来取得该sql的hash值，然后在library cache
2020年(Oracle管理)如何优化SQL语句以提高Oracle执行效率
（Oracle管理）如何优化SQL语句以提高Oracle执 行效率
（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表drivingtable)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表。 （2）WHERE子句中的连接顺序： Oracle采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。（3）SELECT子句中避免使用‘*’： Oracle在解析的过程中,会将‘*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。 （4）减少访问数据库的次数： Oracle在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等。（5）在SQL*Plus,SQL*Forms和Pro*C中重新设置ARRAYSIZE参数,可以增加每次数据库访问的检索数据量,建议值为200。 （6）使用DECODE函数来减少处理时间： 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。 （7）整合简单,无关联的数据库访问： 如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)。 （8）删除重复记录： 最高效的删除重复记录方法(因为使用了ROWID)例子：DELETEFROMEMPEWHEREE.ROWID>(SELECTMIN(X.ROWID)
SQL语句--Group By总结
1 SQL语句--Group By总结 1. Group By 语句简介： Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。 P.S. 这里真是体会到了一个好的命名的力量，Group By从字面是直接去理解是非常好理解的。恩，以后在命名的环节一定要加把劲:)。话题扯远了。 2. Group By 的使用： 上面已经给出了对Group By语句的理解。基于这个理解和SQL Server 2000的联机帮助，下面对Group By语句的各种典型使用进行依次列举说明。 2.1 Group By [Expressions]: 这个恐怕是Group By语句最常见的用法了，Group By + [分组字段](可以有多个)。在执行了这个操作以后，数据集将根据分组字段的值将一个数据集划分成各个不同的小组。比如有如下数据集，其中水果名称(FruitName)和出产国家(ProductPlace)为联合主键： FruitName ProductPlace Price Apple China $1.1 Apple Japan $2.1 Apple USA $2.5 Orange China $0.8 Banana China
$3.1 Peach USA $3.0 如果我们想知道每个国家有多少种水果，那么我们可以通过如下SQL语句来完成： SELECTCOUNT(*)AS水果种类,ProductPlaceAS出产国 FROMT_TEST_FRUITINFO GROUPBYProductPlace 这个SQL语句就是使用了Group By + 分组字段的方式，那么这句SQL语句就可以解释成“我按照出产国家(ProductPlace)将数据集进行分组，然后分别按照各个组来统计各自的记录数量。”很好理解对吧。这里值得注意的是结果集中有两个返回字段，一个是ProductPlace(出产国), 一个是水果种类。如果我们这里水果种类不是用Count(*)，而是类似如下写法的话： SELECTFruitName,ProductPlaceFROMT_TEST_FRUITINFOGROUPBYProductPlace 那么SQL在执行此语句的时候会报如下的类似错误： 选择列表中的列'T_TEST_FRUITINFO.FruitName'无效，因为该列没有包含在聚合函数或GROUPBY子句中。 这就是我们需要注意的一点，如果在返回集字段中，这些字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。我们可以将Group By操作想象成如下的一个过程，首先系统根据SELECT 语句得到一个结果集，如最开始的那个水果、出产国家、单价的一个详细表。然后根据分组字段，将具有相同分组字段的记录归并成了一条记录。这个时候剩下的那些不存在于Group By语句后面作为分组依据的字段就有可能出现多个值，但是目前一种分组情况只有一条记录，一个数据格是无法放入多个数值的，所以这里就需要通过一定的处理将这些多值的列转化成单值，然后将其放在对应的数据格中，那么完成这个步骤的就是聚合函数。这就是为什么这些函数叫聚合函数(aggregate functions)了。 2.2 Group By All [expressions] ： Group By All + 分组字段, 这个和前面提到的Group By [Expressions]的形式多了一个 关键字ALL。这个关键字只有在使用了where语句的，且where条件筛选掉了一些组的情况才可以看出效果。在SQL Server 2000的联机帮助中，对于Group By All是这样进行描述的：
SQL监控及性能优化
SQL 性能监控及SQL 语句优化 性能监控 作为SQL的数据库服务器，我们可以将其比作一个人，而SQL则是他的心脏，管理员就是他的大脑。要监控心脏是否健康首先要看他这个人是否健康。这两者是相辅相成的，少了一方都是不健康的。 数据库服务器的性能监视器 性能监视器 性能工具的介绍 性能监视器是一种简单而功能强大的可视化工具，用于实时收集系统状态并从日志文件中查看性能数据。 使用性能监视器可以: 获得对诊断系统问题和规划系统资源增长有用的性能数据、了解工作负载及其对系统资源的影响、观察工作负载和资源使用情况的变化和趋势，以便计划未来的升级、通过监视结果来测试配置变化、诊断问题并确定需要优化的组件或进程。 现在，可以开始选择这些对象和要监视的计数器了。 https://www.360docs.net/doc/fd657454.html, 应用程序性能计数器有关https://www.360docs.net/doc/fd657454.html, 应用程序性能计数器的大部分信息最近已被合并到一个题为“改善 .NET 应用程序的性能和伸缩性”的综合文档中。下表描述了一些可用于监视和优化 https://www.360docs.net/doc/fd657454.html, 应用程序（包括 Reporting Services）性能的重要计数器。
除了上表中介绍的这些核心监视要素之外，在您试图诊断 https://www.360docs.net/doc/fd657454.html, 应用程序具有的特定性能问题时，下表中的性能计数器也可对您有所帮助。
Reporting Services 性能计数器 Reporting Services 包括一组它自己的性能计数器，用于收集有关报告处理和资源消耗方面的信息。可通过 Windows 性能监视器工具中出现的两个对象来监视实例和组件的状态和活动：MSRS 2005 Web Service 和 MSRS 2005 Windows Service 对象。 MSRS 2005 Web Service 性能对象包括一组用来跟踪 Report Server 处理过程的计数器，这些处理过程通常通过在线交互式报告浏览操作而引发。这些计数器在https://www.360docs.net/doc/fd657454.html, 停止该 Web 服务后被重设。下表列出了可用于监视 Report Server 性能的计数器，并描述了它们的目的。 性能对象：RS Web Service
最新常用经典SQL语句大全完整版
常用经典SQL语句大全完整版--详解+实例下列语句部分是Mssql语句，不可以在access中使用。 SQL分类： DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE) DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT) DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) 首先,简要介绍基础语句： 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的device USE master EXEC sp_addumpdevice ’disk’, ’testBack’, ’c:\mssql7backup\MyNwind_1.dat’--- 开始备份 BACKUP DATABASE pubs TO testBack
4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only 5、说明： 删除新表：drop table tabname 6、说明： 增加一个列：Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明： 添加主键：Alter table tabname add primary key(col) 说明： 删除主键：Alter table tabname drop primary key(col) 8、说明： 创建索引：create [unique] index idxname on tabname(col….) 删除索引：drop index idxname
SQL语句从大到小排序
根据下面三个关系模式完成下面习题：答案已设为白色需要就全选设为黑色学生表student 第一章课件：编写基本的sql语句。 1．查询所有学生情况。 3．查询所有学生的姓名，性别以及年龄。 5．查询所有学生10年后的年龄。 7．查询所有课程（列名用中文显示）。 9．查看竟有那些学生选课（重复学号显示一次）。 11．显示课程表的边结构。第二章课件：约束和排序数据。 01．查询计算机系的所有学生的姓名和年龄。 02．查询体育课的学分。 03．查询年龄小于18的学生。 04．查询年龄大于20的学生。 05．查询年龄介于18和20之间的学生（包括18和20）。 06．查询年龄不在18和20之间的学生。 07．查询年龄为18，20，22的学生。 08．查询年龄不是18，20，22的学生。 09．查询所有姓张的学生。 10．查询所有没有先行课的课程。 11．查询有先行课的课程。 12．在计算机系中找，姓张的男生。 13．在计算机系中找，姓张的或者姓李的男生并且按照年龄从大到小排序。 14．查询所有学生信息，显示结果先按系从大到小排序，再按年龄排序。 第三章课件：多表查询 1．查询每个学生（学号）选了哪门课（课程）得了多少分 2．查询每个学生（姓名）选了哪门课（课程号）得了多少分 3．查询每个学生（姓名）选了哪门课（课程名）得了多少分 4．查询一下王林选可哪门课得了多少分。 5．查询每个学生的成绩类别（优、良还是及格）。 6．查询哪个学生没有选课（用外查询）。 7．查询哪门课没有人选（用外查询）。 第四章课件：组函数
1．查询一下所有课程的平均分，最高分，最低分和总分数。 2．查询一下有多少个学生参加选课。 3．查询一下计算机系有多少人过20岁。 4．统计一下计算机系的男生多少人。 5．查询一下每个学生考试的最高分和最低分。 6．查询每门课（课程号）的最高分和最底分。 7．查询每门课（课程名）的最高分和最底分。 8．查询计算机系中男生多少人，女生多少人。 9，查询人数在三百人以上的系。 10．查询选修人数在三人（包括三人）的课程（课程名）。 11．查询各科考试成绩最低的同学。 12．查询考试成绩小于所选课程平均分的人。（有能力的同学选做） 第五章课件：子查询 1．查询所有比王林大的同学信息。 2．查询和王林同在一个系的所有学生信息。 3．查询一下谁的成绩（所有成绩）最低。 4．查询一下每门课成绩最底的同学（要姓名，和成绩）。 5．查询一下哪个学生没有选课（用子查询）。 6．查询一下哪门课没有人选（用子查询）。 7．查询一下和王林一个系，但是比他年龄大的同学。 第六章课件：ddl语句 1．创建以上四个表，要求每个表必须有主键，表和表之间必须有外间关联。 3．写出insert语句，给表添加以上数据。 5．提交所有操作。 7．将王林的年龄设置为空。 9．将张大民调到计算机系。 11．将体育课的学分设置成和管理学学分一样（update 中带有子查询）。 13．回滚所有操作。 9．某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题：卡里面的“o和0”（哦和零）“i和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据
sql优化方案讲解
Sql优化方案 一．数据库优化技术 1.索引（强烈建议使用） 1.1优点 创建索引可以大大提高系统的性能。 第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 1.2 缺点 第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 1.3 使用准则 索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。 一般来说，应该在这些列上创建索引。 第一，在经常需要搜索的列上，可以加快搜索的速度；
第二，在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； 第三，在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；第四，在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的； 第五，在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； 第六，在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。 同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点： 第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。 第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。 第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。 1.4 总结 1）索引提高了数据库的检索性能，但一定程度上牺牲了修改性能。因此适用于“多查询少修改”（insert，update，delete）的表。 2）对此类表中的外键，需要分组，排序或作为检索条件的字段建立索引 3）对此类表中查询使用少，字段取值少，字段数据量大的不应创建索引
常用经典SQL语句大全完整版教学文案
常用经典S Q L语句大 全完整版
常用经典SQL语句大全完整版--详解+实例下列语句部分是Mssql语句，不可以在access中使用。 SQL分类： DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE) DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT) DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) 首先,简要介绍基础语句： 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice ’disk’, ’testBack’, ’c:\mssql7backup\MyNwind_1.dat’--- 开始备份
BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only 5、说明： 删除新表：drop table tabname 6、说明： 增加一个列：Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明： 添加主键：Alter table tabname add primary key(col) 说明： 删除主键：Alter table tabname drop primary key(col) 8、说明： 创建索引：create [unique] index idxname on tabname(col….)
SQL数据库优化方法
SQL数据库优化方法
目录 1 系统优化介绍 (1) 2 外围优化 (1) 3 SQL优化 (2) 3.1 注释使用 (2) 3.2 对于事务的使用 (2) 3.3 对于与数据库的交互 (2) 3.4 对于SELECT *这样的语句， (2) 3.5 尽量避免使用游标 (2) 3.6 尽量使用count(1) (3) 3.7 IN和EXISTS (3) 3.8 注意表之间连接的数据类型 (3) 3.9 尽量少用视图 (3) 3.10 没有必要时不要用DISTINCT和ORDER BY (3) 3.11 避免相关子查询 (3) 3.12 代码离数据越近越好 (3) 3.13 插入大的二进制值到Image列 (4) 3.14 Between在某些时候比IN 速度更快 (4) 3.15 对Where条件字段修饰字段移到右边 (4) 3.16 在海量查询时尽量少用格式转换。 (4) 3.17 IS NULL 与IS NOT NULL (4) 3.18 建立临时表， (4) 3.19 Where中索引的使用 (5) 3.20 外键关联的列应该建立索引 (5) 3.21 注意UNion和`UNion all 的区别 (5) 3.22 Insert (5) 3.23 order by语句 (5) 3.24 技巧用例 (6) 3.24.1 Sql语句执行时间测试 (6)
1系统优化介绍 在我们的项目中，由于客户的使用时间较长或客户的数据量大，造成系统运行速度慢，系统性能下降就容易造成数据库阻塞。这是个非常痛苦的事情，用户的查询、新增、修改等需要花很多时间，甚至造成系统死机的现象。速度慢的原因主要是来自于资源不足。 数据库的优化通常可以通过对网络、硬件、操作系统、数据库参数和应用程序的优化来进行。最常见的优化手段就是对硬件的升级。根据统计，对网络、硬件、操作系统、数据库参数进行优化所获得的性能提升，全部加起来最多只占数据库系统性能提升的40%左右(我将此暂时称之为外围优化)；其余大部分系统性能提升来自对应用程序的优化，对于应用程序的优化可以分为对源代码的优化及数据库SQL语句的优化。在本文档只介绍外围优化及SQL语句的优化，对于源代码的优化需要相关方面的专家，形成统一的规范。 一个数据库系统的生命周期可以分成：设计、开发和成品三个阶段。在设计阶段进行数据库性能优化的成本最低，收益最大。在成品阶段进行数据库性能优化的成本最高，收益最小。规范的代码和高性能的语句，功在平时，利在千秋。 2外围优化 1、将操作系统与SQL数据库的补丁打到最高版本，WIN2003最高补丁是SP4， SQL SERVER2000最高补丁是SP4(版本号：2039)。 2、在服务器上不要安装与VA程序任何无相关的软件，甚至一些与VA运行 无关的服务都可以停掉。一般只安装SQL数据库、VA服务端服务及杀毒 软件。 3、杀毒软件避免对大文件进行扫描，特别是数据库（MDF和LDF）文件，一 定要从杀毒软件的范围内排除掉。 4、在进行服务器分区时，分区不要太多，两三个分区就可以了。分区最好 都使用NTFS格式。
oraclesql优化笔记
基本的Sql 编写注意事项 尽量少用IN 操作符，基本上所有的IN 操作符都可以用EXISTS 代替。 不用NOT IN操作符，可以用NOT EXISTS或者外连接+替代。 Oracle 在执行IN 子查询时，首先执行子查询，将查询结果放入临时表再执行主查询。而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。NOT EXISTS:匕NOT IN效率稍高。但具体在选择IN或EXIST操作时，要根据主子表数据量大小来具体考虑。 不用“<>”或者“ !=”操作符。对不等于操作符的处理会造成全表扫描，可以用“ <” or “>”代替。 Where子句中出现IS NULL或者IS NOT NULL时，Oracle会停止使用索引而执行全表扫描。可以考虑在设计表时，对索引列设置为NOT NULL这样就可以用其他操作来取代判断NULL的操作。 当通配符“ %”或者“ _”作为查询字符串的第一个字符时，索引不会被使用。 对于有连接的列“ || ”，最后一个连接列索引会无效。尽量避 免连接，可以分开连接或者使用不作用在列上的函数替代。 如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。 Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。 对数据类型不同的列进行比较时，会使索引失效。
用“ >=”替代“ >”。 UNION操作符会对结果进行筛选，消除重复，数据量大的情况 下可能会引起磁盘排序。如果不需要删除重复记录，应该使用UNION ALL。 Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，可以过滤掉最大数量记录的条件必须写在Where子句的末尾。 Oracle从右到左处理From子句中的表名，所以在From子句中包含多个表的情况下，将记录最少的表放在最后。（只在采用RBO 优化时有效，下文详述） Order By 语句中的非索引列会降低性能，可以通过添加索引的方式处理。严格控制在Order By 语句中使用表达式。 不同区域出现的相同的Sql 语句，要保证查询字符完全相同， 以利用SGA共享池，防止相同的Sql语句被多次分析。多利用内部函数提高Sql 效率。 当在Sql 语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间。 需要注意的是，随着Oracle 的升级，查询优化器会自动对Sql 语句进行优化，某些限制可能在新版本的Oracle 下不再是问题。尤其是采用CBO （Cost-Based Optimization ，基于代价的优化方式）时。 我们可以总结一下可能引起全表扫描的操作：
50个经典sql语句总结
一个项目涉及到的50个Sql语句(整理版) --1.学生表 Student(S,Sname,Sage,Ssex) --S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--2.课程表 Course(C,Cname,T) --C --课程编号,Cname 课程名称,T 教师编号 --3.教师表 Teacher(T,Tname) --T 教师编号,Tname 教师姓名 --4.成绩表 SC(S,C,score) --S 学生编号,C 课程编号,score 分数 */ --创建测试数据 create table Student(S varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10)) insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男') insert into Student values('02' , N'钱电' , '1990-12-21' , N'男') insert into Student values('03' , N'孙风' , '1990-05-20' , N'男') insert into Student values('04' , N'李云' , '1990-08-06' , N'男') insert into Student values('05' , N'周梅' , '1991-12-01' , N'女') insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女') insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女') insert into Student values('08' , N'王菊' , '1990-01-20' , N'女') create table Course(C varchar(10),Cname nvarchar(10),T varchar(10)) insert into Course values('01' , N'语文' , '02') insert into Course values('02' , N'数学' , '01') insert into Course values('03' , N'英语' , '03') create table Teacher(T varchar(10),Tname nvarchar(10)) insert into Teacher values('01' , N'张三') insert into Teacher values('02' , N'李四') insert into Teacher values('03' , N'王五') create table SC(S varchar(10),C varchar(10),score decimal(18,1)) insert into SC values('01' , '01' , 80) insert into SC values('01' , '02' , 90) insert into SC values('01' , '03' , 99) insert into SC values('02' , '01' , 70) insert into SC values('02' , '02' , 60) insert into SC values('02' , '03' , 80) insert into SC values('03' , '01' , 80) insert into SC values('03' , '02' , 80) insert into SC values('03' , '03' , 80) insert into SC values('04' , '01' , 50) insert into SC values('04' , '02' , 30) insert into SC values('04' , '03' , 20) insert into SC values('05' , '01' , 76) insert into SC values('05' , '02' , 87)