oracledistinct分页优化_OracleSQL性能优化最常⽤的40条建
议
关于Oracle SQL优化的内容，这⼀篇应该能满⾜常规⼤部分的应⽤优化需求，整整40条优化建议，⼲货满满。
1. SQL语句执⾏步骤
语法分析> 语义分析> 视图转换 >表达式转换> 选择优化器 >选择连接⽅式 >选择连接顺序 >选择数据的搜索路径 >运⾏“执⾏计划”。
2. 选⽤适合的Oracle优化器
RULE(基于规则)、 COST(基于成本) 、CHOOSE(选择性)。
3. 访问Table的⽅式全表扫描
1)全表扫描
全表扫描就是顺序地访问表中每条记录，ORACLE采⽤⼀次读⼊多个数据块(database block)的⽅式优化全表扫描。
2)通过ROWID访问表
ROWID包含了表中记录的物理位置信息，ORACLE采⽤索引实现了数据和存放数据的物理位置(ROWID)之间的联系，通常索引提供了快速访问ROWID的⽅法，因此那些基于索引列的查询就可以得到性能上的提⾼。
4. 共享SQL语句
Oracle提供对执⾏过的SQL语句进⾏⾼速缓冲的机制。被解析过并且确定了执⾏路径的SQL语句存放在SGA的共享池中。
Oracle执⾏⼀个SQL语句之前每次先从SGA共享池中查是否有缓冲的SQL语句，如果有则直接执⾏该SQL语句。
可以通过适当调整SGA共享池⼤⼩来达到提⾼Oracle执⾏性能的⽬的。
5. 选择最有效率的表名顺序
ORACLE的解析器按照从右到左的顺序处理FROM⼦句中的表名，因此FROM⼦句中写在最后的表(基础表 driving table)将被最先处理。
当ORACLE处理多个表时，会运⽤排序及合并的⽅式连接它们，并且是从右往左的顺序处理FROM⼦句。⾸先，扫描第⼀个表(FROM ⼦句中最后的那个表)并对记录进⾏排序，然后扫描第⼆个表(FROM⼦句中倒数第⼆个表)，最后将所有从第⼆个表中检索出的记录与第⼀个表中合适记录进⾏合并。
只在基于规则的优化器中有效。
举例：
表 TAB1 16,384 条记录
表 TAB2 1 条记录
/*选择TAB2作为基础表 (最好的⽅法)*/
SELECT COUNT(*) FROM TAB1,TAB2
/
*执⾏时间0.96秒*/
/*选择TAB1作为基础表 (不佳的⽅法)*/
SELECT COUNT(*) FROM TAB2,TAB1
/*执⾏时间26.09秒*/
如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引⽤的表。
/*⾼效的SQL*/
SELECT * FROM LOCATION L, CATEGORY C, EMP E
WHERE E.EMP_NO BETWEEN1000AND2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
将⽐下列SQL更有效率：
/*低效的SQL*/SELECT * FROM EMP E, LOCATION L, CATEGORY CWHERE E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND E.EMP_NO BETWEEN 1000
6. Where⼦句中的连接顺序
Oracle采⽤⾃下⽽上或⾃右向左的顺序解析WHERE⼦句。根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最
⼤数量记录的条件必须写在WHERE⼦句的末尾。
/*低效，执⾏时间156.3秒*/
SELECT Column1,Column2
FROM EMP EWHERE E.SAL > 50000
AND E.JOB = 'MANAGER'AND 25 <
(SELECT COUNT(*) FROM EMP
WHERE MGR = E.EMPNO)
/*⾼效，执⾏时间10.6秒*/
SELECT Column1,Column2FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND E.SAL > 50000
AND E.JOB = 'MANAGER'
7. SELECT⼦句中避免使⽤“*”
Oracle在解析SQL语句的时候，对于“*”将通过查询数据库字典来将其转换成对应的列名。
如果在Select⼦句中需要列出所有的Column时，建议列出所有的Column名称，⽽不是简单的⽤“*”来替代，这样可以减少多于的数据库查询开销。
8. 减少访问数据库的次数
当执⾏每条SQL语句时, ORACLE在内部执⾏了许多⼯作：解析SQL语句 > 估算索引的利⽤率 > 绑定变量 > 读数据块等等由此可见, 减少访
问数据库的次数 , 就能实际上减少ORACLE的⼯作量。
9. 整个简单⽆关联的数据库访问
如果有⼏个简单的数据库查询语句，你可以把它们整合到⼀个查询中(即使它们之间没有关系)，以减少多于的数据库IO开销。虽然采取这种⽅法，效率得到提⾼，但是程序的可读性⼤⼤降低，所以还是要权衡之间的利弊。
10. 使⽤Truncate⽽⾮Delete
Delete表中记录的时候，Oracle会在Rollback段中保存删除信息以备恢复。Truncate删除表中记录的时候不保存删除信息，不能恢复。因此Truncate删除记录⽐Delete快，⽽且占⽤资源少。
删除表中记录的时候，如果不需要恢复的情况之下应该尽量使⽤Truncate⽽不是Delete。sql语句优化方式
Truncate仅适⽤于删除全表的记录。
11. 尽量多使⽤COMMIT
只要有可能，在程序中尽量多使⽤COMMIT, 这样程序的性能得到提⾼，需求也会因为COMMIT所释放的资源⽽减少。
COMMIT所释放的资源：
回滚段上⽤于恢复数据的信息。
被程序语句获得的锁。
redo log buffer 中的空间。
ORACLE为管理上述3种资源中的内部花费。
12. 计算记录条数
Select count(*) from tablename;
Select count(1) from tablename;
Select count(column) from tablename;
⼀般认为，在没有主键索引的情况之下，第⼆种COUNT(1)⽅式最快。如果只有⼀列且⽆索引COUNT(*)反⽽⽐较快， 如果有索引列，当然是使⽤索引列COUNT(column)最快。
13. ⽤Where⼦句替换Having⼦句
避免使⽤HAVING⼦句，HAVING 只会在检索出所有记录之后才对结果集进⾏过滤。这个处理需要排序、总计等操作。如果能通过WHERE ⼦句限制记录的数⽬，就能减少这⽅⾯的开销。
14. 减少对表的查询操作
在含有⼦查询的SQL语句中，要注意减少对表的查询操作。
/*低效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)
/*⾼效SQL*/
SELECT TAB_NAME FROM TABLES
WHERE (TAB_NAME，DB_VER)=(
SELECT TAB_NAME，DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
15. 使⽤表的别名(Alias)
当在SQL语句中连接多个表时, 请使⽤表的别名并把别名前缀于每个Column上.这样⼀来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
Column歧义指的是由于SQL中不同的表具有相同的Column名，当SQL语句中出现这个Column时,SQL解析器⽆法判断这个Column的归属。
16. ⽤EXISTS替代IN
在许多基于基础表的查询中，为了满⾜⼀个条件 ，往往需要对另⼀个表进⾏联接。在这种情况下，使⽤EXISTS(或NOT EXISTS)通常将提⾼查询的效率。
/*低效SQL*/
SELECT * FROM EMP
WHERE EMPNO > 0
AND DEPTNO IN (
SELECT DEPTNO FROM DEPT
WHERE LOC = 'MELB')
/*⾼效SQL*/
SELECT * FROM EMP
WHERE EMPNO > 0
AND EXISTS (SELECT1
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = 'MELB')
17. ⽤NOT EXISTS替代NOT IN
在⼦查询中，NOT IN⼦句将执⾏⼀个内部的排序和合并，对⼦查询中的表执⾏⼀个全表遍历，因此是⾮常低效的。为了避免使⽤NOT IN，可以把它改写成外连接(Outer Joins)或者NOT EXISTS。
/*低效SQL*/
SELECT * FROM EMP
WHERE DEPT_NO NOT IN (
SELECT DEPT_NO FROM DEPT
WHERE DEPT_CAT='A')
/*⾼效SQL*/
SELECT * FROM EMP E
WHERE NOT EXISTS (SELECT 1
FROM DEPT D
WHERE D.DEPT_NO = E.DEPT_NO
AND DEPT_CAT ='A')
18. ⽤表连接替换EXISTS
通常来说 ，采⽤表连接的⽅式⽐EXISTS更有效率 。
/*低效SQL*/
SELECT ENAME
FROM EMP E
WHERE EXISTS (SELECT 1
FROM DEPT
WHERE DEPT_NO = E.DEPT_NO
AND DEPT_CAT = 'A')
/*⾼效SQL*/
SELECT ENAME
FROM DEPT D，EMP E
WHERE E.DEPT_NO = D.DEPT_NO
AND D.DEPT_CAT = 'A'
19. ⽤EXISTS替换DISTINCT
当提交⼀个包含对多表信息(⽐如部门表和雇员表)的查询时，避免在SELECT⼦句中使⽤DISTINCT。⼀般可以考虑⽤EXIST替换。EXISTS 使查询更为迅速，因为RDBMS核⼼模块将在⼦查询的条件⼀旦满⾜后，⽴刻返回结果。
/*低效SQL*/
SELECT DISTINCT D.DEPT_NO，D.DEPT_NAME
FROM DEPT D，EMP E

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。