Oracle SQL性能优化

（1）??????选择最有效率的表名顺序(只在基于规则的优化器中有效)：

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果, 交叉表是指那个被其他表所引用的表.

--在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表--假设a表中有数据120W，b表中有数据20WSELECT count(a.a1) FROM a,b WHERE a.a1 = b.b1--有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表SELECT count(a.a1) FROM a,b,c WHERE a.a1 = c.c1 and b.b1 = c.c1

?

（2）??????WHERE子句中的连接顺序：
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

SELECT count(a.a1) FROM a,b WHERE a.a1 = b.b1 AND a.a1 > 100000

（3）??????SELECT子句中避免使用 ‘ * ‘：
ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

--效率不高，SELECT count(*) FROM a;SELECT count(a.a1) FROM a;

（4）??????减少访问数据库的次数：能TM的一次检索出来的话就别分多次
ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等；

--当执行每条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：(次低效)declarecursor c1(e_no number) isselect emp_name, salary, grade from emp where emp_no = e_no;beginopen c1(342);fetch c1into .., .., ..;open c1(291);fetch c1into .., .., ..;close c1;end;--方法3：(高效)select  a.emp_name, a.salary, a.grade, b.emp_name, b.salary, b.gradefrom  emp a, emp bwhere  a.emp_no = 342and  b.emp_no = 291;

（5）??????在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200不是说这个数越多越好啊，很占逻辑I/O，具体问题具体分析，如果想深入研究的话，请参考Arraysize的设置以及对查询性能的影响。

--Arraysize是sql*plus中可以设置的一个参数，这个参数设置的意思表--示，sql*plus一次可以从数据库服务器端获取的记录行数。show arraysizearraysize 15--可以看到，在SQL*plus中，默认设置是15。--有效值是1-5000。我们把以下语句运行看看效果set arraysize 15select * from colocated a15 where x between 20000 and 30000;set arraysize 100select * from colocated a100 where x between 20000 and 30000;tkprof报告显示：（a15）Rows     Row Source Operation-------  ---------------------------------------------------  10001  TABLE ACCESS BY INDEX ROWID COLOCATED (cr=1452 pr=0 pw=0 time=100109 us)  10001   INDEX RANGE SCAN COLOCATED_PK (cr=689 pr=0 pw=0 time=40047 us)(object id 53215)（a100）Rows     Row Source Operation-------  ---------------------------------------------------  10001  TABLE ACCESS BY INDEX ROWID COLOCATED (cr=344 pr=0 pw=0 time=90081 us)  10001   INDEX RANGE SCAN COLOCATED_PK (cr=124 pr=0 pw=0 time=30043 us)(object id 53215)--Arraysize为15时，对索引执行了689个逻辑I/O，--对表执行了763（1452-689）个逻辑I/O，arraysize为100时，--对索引执行了124个逻辑I/O，--对表执行了220个逻辑I/O。--这说明因为表有序，--所以在一个块中有较多需要的记录，增加arraysize可以获得良好效果。

（6）??????使用DECODE函数来减少处理时间：
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.

--从网上找到了下面的例子，要计算两个SUM的话一般都会这样去写　　 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%’;--说白了还是跟上面我说的第4个优化原理是一样的

（7）??????整合简单,无关联的数据库访问：
如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)

--下面有三条比较有代表性的语句SELECT a.a1 FROM a WHERE a.id = 1;SELECT b.b1 FROM b WHERE b.editdate = sysdate;SELECT c.c1 FROM c WHERE c.id='c';--我们一起来把他们混搭一起SELECT a.a1,  b.b1, c.c1 FROM a,b,c,DUAL XWHERE NVL(‘X’,X.DUMMY) = NVL(‘X’,a.ROWID(+))AND NVL(‘X’,X.DUMMY) = NVL(‘X’,b.ROWID(+))AND NVL(‘X’,X.DUMMY) = NVL(‘X’,c.ROWID(+))AND a.id (+) = 1AND b.editdate (+) = sysdateAND c.id(+) = ‘c’; --看起来挺乱的，所以最好是能够将分开的写法作为注释，便于日后进行修改升级

（8）??????删除重复记录：
最高效的删除重复记录方法 ( 因为使用了ROWID)例子：

DELETE  FROM  EMP E  WHERE  E.ROWID > (SELECT MIN(X.ROWID)FROM  EMP X  WHERE  X.EMP_NO = E.EMP_NO);

（9）??????用TRUNCATE替代DELETE：
当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.但是truncate的应用还有很多地方需要我们仔细研究，请参考[ORACLE]浅谈truncate的使用

--表中插入1000万条数据insert into test_table select level from dual connect by level<=10000000;10000000 rows created.commit;Commit complete.Elapsed: 00:00:00.02truncate table test_table;Table truncated.Elapsed: 00:00:29.52set autotrace traceonly statistics;select * from test_table;no rows selectedElapsed: 00:00:00.00Statistics----------------------          1  recursive calls          1  db block gets          6  consistent gets          0  physical reads         96  redo size        318  bytes sent via SQL*Net to client        453  bytes received via SQL*Net from client          1  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          0  rows processed--truncate后再查询只做了7个逻辑读，也就是读了7个内存块。

（10） 尽量多使用COMMIT：
只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
a. 回滚段上用于恢复数据的信息.
b. 被程序语句获得的锁
c. redo log buffer 中的空间
d. ORACLE为管理上述3种资源中的内部花费