一般来说，对于一个SQL文,Oracle的解析器先是进行解析验证，比如对于查询SQL文：FROM子句的表名是否正确，各个字段名分别对应哪个表，拼写及语法是否正确等等，然后确立实行计划，选择基础表(Driving Table)，按照WHERE子句中的条件进行连表，最后按照实行计划进行查询。基础表是指被最先访问的表(通常以全表扫描的方式被访问)。根据优化器的不同，SQL语句中基础表的选择是不一样的。现在一般使用的都是CBO(COST BASED OPTIMIZER)，Oracle优化器会检查SQL语句中的每个表的物理大小，索引的状态，然后选用花费最低的执行路径。
当然，通常情况下SQL文的优化跟它的可读性是有矛盾的，一句优化的很好的SQL文可能它的可读性会比较差，而一句可读性好的SQL文它的效率可能并不怎么样，所以经常是需要编程人员根据实际情况，对两者作出最佳的调整，比如表的数据不是很多的时候，着重考虑它的可读性，而当表数据量非常多时，着重要考虑的是SQL文的效率。还有一点，对于有些优化规则，根据表的数据量的不同，有时候反而会降低SQL文的实行效率（下文会详细说明），也需要编程人员根据实际情况作出对应，对下文的优化规则进行灵活应用，完了还需要实际的测试验证其实行速度是否可以。在这里，对每个SQL文，先查看它的实行计划，是一种良好的习惯。现在很多Oracle的工具都提供了实行计划的查看，如Object Browser，cse等，在SQL*PLUS中，输入SET AUTOTRACE，将会得到AUTOTRACE的用法如下：
SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]，
然后输入SET AUTOT TRACE EXP后再实行SQL文，便可以看到其实行计划了。
为了优化SQL文以提高Oracle的实行速度，优化方法如下。 1、SQL文全部用大写，并且尽量简洁。
Oracle对于大小写混合的SQL文，会加大SQL文实行前的验证分析时间，虽然影响甚微，但出于最优化考虑，全部用大写，即统一书写规则，又可节省开销。
有时候对于一个复杂的SQL文，为了可读性，在编程的时候可能会加上大量的回车和空格，也会稍微的影响SQL文的処理時間，请根据实际情况跟可读性作出适当的调整。 2、使用含绑定变量(bind variables)的SQL文，利用Oracle的高速缓冲(cache buffering)功能来提高效率。
为了不重复解析相同的SQL语句，在第一次解析之后，Oracle将SQL语句存放在内存中，这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。因此，当执行一个SQL语句时，如果它和之前的执行过的语句完全相同，Oracle就能很快获得已经被解析的语句以及最好的执行路径。Oracle的这个高速缓冲(cache buffering)功能大大地提高了SQL的执行性能并节省了内存的使用。
可惜的是Oracle只对简单的表提供高速缓冲，这个功能有时候并不适用于多表连接查询。DBA需要在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。
当你向Oracle 提交一个SQL语句，Oracle会首先在这块内存中查找相同的语句。这里需要注明的是，Oracle对两者采取的是一种严格匹配。要达成共享，a.SQL语句必须完全相同(包括空格、换行等)，字符大小写也需一致；b.两个语句所指的对象必须完全相同，如果同一表名对不同DB用户来说代表的不是同一张表，则不能匹配；c.在使用绑定变量的SQL文时，绑定变量名也必须完全相同。 3、减少访问数据库的次数。
当执行每条SQL语句时，Oracle在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据库的次数，就能实际上减少Oracle的工作量。 4、尽量多使用COMMIT。
只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少。COMMIT所释放的资源：
a. 回滚段上用于恢复数据的信息
b. 被程序语句获得的锁
c. redo log buffer 中的空间
d. Oracle为管理上述3种资源中的内部花费
但是，在使用COMMIT时必须要注意到事务的完整性，一般来说COMMIT的使用更多的是从实际的业务逻辑出发来考虑的。 5、SELECT子句中避免使用"*"，只将需要的字段名进行列举。
对于"*"，Oracle在解析的过程中通过查询数据字典（user_tab_columns）将"*"转换为所有的列名，不可取。
只将所需要的字段名进行选取，如果是统计count(*)，则可用Primary Key代替。添加没有必要的字段，会加大Oracle的检索量，从而降低效率。总体来说，使用"*"所带来的影响不是很大，但从最优化角度考虑，此规则还是必须的。 6、对于所用到的表，一定要加上别名。
使用别名可降低Oracle解析处理的开销，提高效率，同时对于可读性也是有利的。如：
Select Employee_Id FROM employees, cct_odr ==> 不妥
SELECT A.EMPLOYEE_ID FROM EMPLOYEES A, CCT_ODR B ==> 正确 7、WHERE子句中的连接顺序。
Oracle采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾(越有效地缩小范围的条件越放后)。但是在效率与可读性之间需做出一定的调整，而且在使用HINT文(提示处理)后能主动指定需要索引的字段来调整收缩顺序，因此建议对于简单的或者数据量小的SQL文可以重点考虑其可读性，而对于复杂的或者数据量大的SQL文则考虑使用HINT文（下文将详细介绍）。
SELECT ... FROM EMP A
WHERE A.SAL > 50000 AND A.JOB = 'MANAGER'
AND 25 < (SELECT COUNT(B.EMPNO) FROM EMP B WHERE A.MGR = B.EMPNO) ==> 不妥
SELECT ... FROM EMP A
WHERE 25 < (SELECT COUNT(B.EMPNO) FROM EMP B WHERE A.MGR = B.EMPNO)
AND A.SAL > 50000 AND A.JOB = 'MANAGER' ==> 正确 8、WHERE子句中的条件的指定。
两表连接的时候，对于收缩条件，记录数少的优先考虑。如下面两表A、B，A.X_VALUE有1万件，而B.X_VALUE有5万件，则：
SELECT ... FROM TABLE_A A, TABLE_B B WHERE A.X_VALUE = B.X_VALUE AND B.X_VALUE = 10 ==> 不妥
SELECT ... FROM TABLE_A A, TABLE_B B WHERE A.X_VALUE = B.X_VALUE AND A.X_VALUE = 10 ==> 正确 9、控制使用GROUP BY、ORDER BY和HAVING。
使用GROUP BY、ORDER BY和HAVING时会发生磁盘I/O操作，不是必要的话，需谨慎使用。 10、对操作符的优化：使用DECODE函数，或者CASE来减少处理时间，避免重复扫描相同记录或重复连接相同的表。
CASE的语法：CASE WHEN [条件式1] THEN [值1] WHEN [条件式2] THEN [值2] ... ELSE [值] END
如：
SELECT A.SAL*10 AS SAL FROM EMP A WHERE A.DEPT_NO = 0020;
SELECT A.SAL*50 AS SAL FROM EMP A WHERE A.DEPT_NO = 0030; ==> 不妥:重复扫描字段DEPT_NO，多次进出数据库
SELECT DECODE(A.DEPT_NO, 0020, A.SAL*10, 0030 A.SAL*50, 0)AS SAL FROM EMP A WHERE SAL > 0; ==> 正确
或者：
SELECT CASE WHEN A.DEPT_NO=0020 THEN A.SAL*10 WHEN A.DEPT_NO=0030 THEN A.SAL*50 ELSE 0 END AS SAL
FROM EMP A WHERE SAL > 0; ==> 正确 11、对操作符的优化：避免使用IN、NOT IN操作符，用多表连接或者EXISTS、NOT EXISTS来代替。
用IN写出来的SQL的优点是比较容易写及清晰易懂，但性能总是比较低的。从Oracle执行的步骤来分析，Oracle试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。
SELECT DEPARTMENT_ID FROM DEPARTMENTS D WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES)
==>不妥：IN的使用，需避免
SELECT DEPARTMENT_ID FROM DEPARTMENTS D WHERE EXISTS (SELECT 1 FROM EMPLOYEES E WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID)
==>正确：用EXISTS进行了代替 12、对操作符的优化：避免使用DISTINCT操作符，有时候可用EXISTS来代替。
如果可以的话，一定要避免DISTINCT的使用。有些DISTINCT是可以用EXISTS来进行代替的，如：
SELECT DISTINCT A.ID1, A.NAME1 FROM TABLE1 A, TABLE2 B WHERE A.ID1 = B.ID2 ==> 不妥
SELECT A.ID1, A.NAME1 FROM TABLE1 A WHERE EXISTS (SELECT '1' FROM TABLE2 B WHERE A.ID1 = B.ID2) ==> 正确 13、对操作符的优化：避免在索引列上使用<>操作符(不等于)，用> or 不等于操作符是不能利用索引（INDEX）的，因此对它的处理只会产生全表扫描，需避免。如ID<>0 可改为 ID>0 or ID<0。 14、对操作符的优化：在索引列上使用NOT，需谨慎。
NOT会产生在和在索引列上使用函数相同的影响，当Oracle遇到NOT时，它就会停止使用索引转而执行全表扫描。然而在某些时候，Oracle优化器会自动将NOT转化成相对应的关系操作符，如：NOT > 转化成： <=，NOT >= 转化成： SELECT A.NAME FROM EMP A WHERE NOT A.EMPNO > 1;
SELECT A.NAME FROM EMP A WHERE A.EMPNO <= 1; 15、对操作符的优化：在索引列上使用>、=、<=操作符（大于、小于及大于等于、小于等于）时，需考虑实际情况。
对于>=，Oracle其实做的是> r =的操作，所以对于一个数值型字段VAL_NUM来说，VAL_NUM>=3的效率不如VAL_NUM>2来的好。当然也有特殊情况，比如30万记录中VAL_NUM=2的有29万，而1万记录是VAL_NUM>=3的，那么执行VAL_NUM>2时Oracle会找出=2的记录索引再进行比较，而VAL_NUM>=3时Oracle则直接找>=3的记录索引，所以这时候VAL_NUM>2的效率不如VAL_NUM>=3来的好。
一般情况下，着重考虑的是实际情况和可读性，但是当速度差生比较大的影响时，需要考虑一下此规则的应用。 16、对操作符的优化：避免在索引列上使用IS NULL或IS NOT NULL操作（判断字段是否为空），但根据实际情况变动。
判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。
推荐方案是不允许字段为空，而用一个缺省值代替空值。然后VAL IS NOT NULL改为 VAL>0 或者 VAL>''等。也可以建立位图索引，但有分区的表不能建，而且位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象。
总之此优化规则需要考虑实际情况，来决定是否应用，如何应用。 17、对操作符的优化：LIKE操作符。
LIKE操作符可以应用通配符（%、_）查询，其中%接受任意个数的字符，_接受一个字符。LIKE如果用得不好也会产生性能上的问题，如LIKE '%5400%'这种查询不会引用索引，而LIKE 'X5400%'则会引用范围索引。一个实际例子：VAL LIKE '%5400%'这个条件会产生全表扫描，如果VAL值第一位是'B'或者'X'，则改成VAL LIKE 'B5400%' OR VAL LIKE 'X5400%'则会利用VAL的索引进行两个范围的查询，性能要好很多。而后方一致，如LIKE '%5400'也会导致全表检索。 18、对操作符的优化：对于UNION操作符，避免使用UNION，能够的话用UNION ALL。
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。如：
SELECT A.ID1, A.NAME1 FROM TABLE1 A
UNION
SELECT B.ID1, B.NAME1 FROM TABLE2 B
这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话还可能会用磁盘来进行排序。而采用UNION ALL的话，只是简单的将两个结果合并后就返回。
SELECT A.ID1, A.NAME1 FROM TABLE1 A
UNION ALL
SELECT B.ID1, B.NAME1 FROM TABLE2 B
一般来说如果对于两个不同的表的话，经常是不用考虑其结果是否重复的，即使需要考虑的话，有时候也可以通过程序中来进行控制。
此规则请根据实际情况来进行调整。 19、使用索引。
一般使用了索引的话，会大大的加快SQL文的实行速度，Oracle的实行计划，也会自动使用索引（能够使用的话）来加快速度。
当然使用索引也会增大开销，一般来说是利大于弊的，特别是对于索引唯一扫描 ( INDEX UNIQUE SCAN)，但是对于有些索引范围查询(INDEX RANGE SCAN)而言，则需视情况而定。这个一般是WHERE子句中指定了一部分多列索引的字段条件而导致的，这种场合下有时反而会是全表检索的效率好，需要对实际情况进行考虑，并进行两者的测试。
比如此索引值的纪录非常多，跟全表纪录差不多，则可能是用全表检索开销会小一点，这种情况下即使Oracle自动应用了此索引，也要用HINT文，或WHERE子句中修改相应条件来强制排除此一索引。
索引有很多种，传统的索引--B树（B-Tree）索引，位图（bitmap)索引，哈希（hash)索引，基于原始表的索引，基于函数的索引和域（Domain）索引。实际应用中主要是B-Tree索引和位图索引，所以简单介绍这两种索引类型。
B-Tree索引是最普通的索引，缺省条件下建立的索引就是这种类型的索引，对于包含大量变化数据的列是非常有用的，主键和唯一约束就会隐式的建立这种索引；B-Tree索引可以是唯一或非唯一的，可以是单一的（基于一列）或连接的（多列）。B-Tree索引在检索高基数数据列（该列有很多不同的值）时提供了最好的性能。对于取出较少的数据B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。正如名字所暗示的那样，B-Tree索引是基于二元树的，由枝干块（branch block)和树叶块（leaf block)组成，枝干块包含了索引列（关键字）和另一索引的地址。树叶块包含了关键字和给表中每个匹配行的ROWID。
位图（bitmap）索引，对于不同值很少的字段，例如区分等比较合适。主要用于决策支持系统或静态数据，不支持行级锁定。位图索引可以是简单的（单列）也可以是连接的（多列），但在实践中绝大多数是简单的。位图索引最好用于低到中群集（cardinality)列，在这些列上多位图索引可以与AND或OR操作符结合使用。位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE（1）、FALSE（0）、或NULL值。位图索引的位图存放在B-Tree结构的页节点中。B-Tree结构使查找位图非常方便和快速。另外，位图以一种压缩格式存放，因此占用的磁盘空间比B-Tree索引要小得多。
此外使用了函数的字段是没法应用索引的，但是可以建立基于函数的索引，则使用相应函数时，照样可以进行索引。
还有一点，对于一个关联多列的索引，如表TABLE_A的索引DEPT_IDX由多列字段DE_NO，PT_DIV（按先后顺序）组成，则要利用此索引的话，WHERE子句中必须要包含有此索引第一个列DE_NO的条件。
在索引带来便利的同时，也要花费一定的开销。索引需要空间来存储，也需要定期维护。每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT、DELETE、UPDATE将为此多付出4、5次的磁盘I/O。因为索引需要额外的存储空间和处理，所以在大型表中正确的使用索引是特别有效的，但索引使用错误，或使用了效率不好的索引，或者对很小的表建立索引，则会适得其反。 20、索引的利用：习惯将需要利用索引（INDEX）的字段名放在WHERE子句中条件的左边，并且避免对其使用函数。
如果对需要索引的字段使用了函数，则Oracle将无法对此字段进行索引，需要避免，用其他方式来等价替换。如：
SUBSTR(VAL, 1, 4) = '5400' ==> 不妥
VAL LIKE '5400%' ==> 正确
当然可以省略的函数也应该去掉，如：
TO_NUMBER(A.ORDER_NO) = TO_NUMBER(B.ORDER_NO) ==> 不妥
A.ORDER_NO = B.ORDER_NO ==> 正确 21、索引的利用：WHERE子句中条件的两边类型不一致的情况需要禁止，对类型转换函数TO_NUMBER、TO_CHAR的使用也需要注意。
当条件两边数据类型不一致时，Oracle会自动进行类型转换，如：CHAR_VAL为VARCHAR2型
CHAR_VAL = 1 ==> 不妥：Oracle自动进行TO_NUMBER(CHAR_VAL) = 1 和 CHAR_VAL = TO_CHAR(1)的转换，再继续操作
CHAR_VAL = '1' ==> 正确
还有就是对于TO_NUMBER、TO_CHAR的使用，如果想要对CHAR_VAL进行索引，则
TO_NUMBER(CHAR_VAL) = NUM_VAL ==> 不妥
CHAR_VAL = TO_CHAR(NUM_VAL) ==> 正确 22、索引的利用：进行了显式或隐式的运算的字段不能进行索引，需要避免。
当条件两边数据类型不一致时，Oracle会隐式的进行类型转换，如上一规则所言。再比如：
NUM_VAL+20 > 50， 优化处理成：NUM_VAL > 30，
NUM_VAL+20 > NUMM 优化处理成：NUM_VAL > NUMM-20，
'X' || CHAR_VAL > 'X5400'，优化处理成：CHAR_VAL > '5400' 23、索引的利用：条件内包括了多个本表的字段运算时不能进行索引，需要避免。
如同一表的两字段：CHAR_VAL1 > CHAR_VAL2，则无法进行优化。
再如CHAR_VAL1 || CHAR_VAL2 = '5400NAME'，可以的话，优化处理成：CHAR_VAL1 = '5400' AND CHAR_VAL2 = 'NAME' 24、索引的利用：复合式的使用需要禁止。
复合式的应用如：NUM_VAL = NVL(NUM2, NUM_VAL)，NVL(NUM_VAL, 0) = ...等，使得Oracle对其实行计划无法进行最优化，结果导致对实行计划以及表的结合方法产生恶劣影响，需要禁止这种用法。如：
NVL(NUM_VAL, 0) = 0 ==> 错误
NUM_VAL = 0 OR NUM_VAL IS NULL ==> 正确 25、索引的利用：多个索引的利用情况，以及>、 在一个SQL文中可能会分布有多个索引。Oracle选择执行路径时，唯一性索引的等级高于非唯一性索引。如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性。在这种情况下，Oracle将使用唯一性索引而完全忽略非唯一性索引。然而这个规则只有当WHERE子句中索引列和常量比较才有效。如果索引列和其他表的索引类相比较,这种子句在优化器中的等级是非常低的。如果是等式比较和范围比较的组合，两者都有索引，则只对等号的字段使用索引，即范围比较的唯一性索引，等级要比等式比较的非唯一性索引低。如：
SELECT A.NAME FROM CUSTOMERS A
WHERE A.SAL > 1000 ==> INDEX不使用，即使SAL为主键也不索引此字段
AND A.JOB = 'MANAGER' ==> INDEX使用
如果不同表中两个相同等级的索引被引用，FROM子句中表的顺序将决定哪个会被率先使用。FROM子句中最后的表的索引将有最高的优先级。如果相同表中两个想同等级的索引被引用，WHERE子句中最先被引用的索引将有最高的优先级。如：
SELECT A.NAME FROM CUSTOMERS A
WHERE A.TAX > 100 ==> INDEX使用
AND A.SAL > 1000 ==> INDEX不使用
如DEPT_NO为EMP的主键，而CUST_NO为CUSTOMERS的主键，则：
SELECT A.NAME FROM CUSTOMERS A, EMP B
WHERE A.CUST_NO = B.CUST_NO
AND A.DEPT_NO = '19' ==> INDEX不使用
AND B.CUST_NO = '76' ==> INDEX使用 26、WITH A AS()的用法。
复杂的子查询，或者被多次引用到的子查询，可以在SQL文开始处用WITH A AS()建立一个类似临时的视图，然后在SQL文中就可以用A来引用这个查询了，增加可读性，同时在多次引用到的场合下也能提高效率。 综上所述，各种优化规则往往对应其实际情况，并且还伴随着可读性这一很重要的编程因素在里面。这里对于索引是一个很重的方面，很多的考虑都是为了使用索引，若是索引带来便利要小于其开销时，便需要反向来应用这一规则，所以需要编程人员好好的理解，并结合实际情况，灵活的应用各种优化方案，更考虑到程序的可读性，分析各方面的利弊得失，并且反复通过实行计划和实际运行的效果，最终得出一个比较理想的方案。有关HINT文方面的，请见HINT文的简介。
当然，通常情况下SQL文的优化跟它的可读性是有矛盾的，一句优化的很好的SQL文可能它的可读性会比较差，而一句可读性好的SQL文它的效率可能并不怎么样，所以经常是需要编程人员根据实际情况，对两者作出最佳的调整，比如表的数据不是很多的时候，着重考虑它的可读性，而当表数据量非常多时，着重要考虑的是SQL文的效率。还有一点，对于有些优化规则，根据表的数据量的不同，有时候反而会降低SQL文的实行效率（下文会详细说明），也需要编程人员根据实际情况作出对应，对下文的优化规则进行灵活应用，完了还需要实际的测试验证其实行速度是否可以。在这里，对每个SQL文，先查看它的实行计划，是一种良好的习惯。现在很多Oracle的工具都提供了实行计划的查看，如Object Browser，cse等，在SQL*PLUS中，输入SET AUTOTRACE，将会得到AUTOTRACE的用法如下：
SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]，
然后输入SET AUTOT TRACE EXP后再实行SQL文，便可以看到其实行计划了。
为了优化SQL文以提高Oracle的实行速度，优化方法如下。 1、SQL文全部用大写，并且尽量简洁。
Oracle对于大小写混合的SQL文，会加大SQL文实行前的验证分析时间，虽然影响甚微，但出于最优化考虑，全部用大写，即统一书写规则，又可节省开销。
有时候对于一个复杂的SQL文，为了可读性，在编程的时候可能会加上大量的回车和空格，也会稍微的影响SQL文的処理時間，请根据实际情况跟可读性作出适当的调整。 2、使用含绑定变量(bind variables)的SQL文，利用Oracle的高速缓冲(cache buffering)功能来提高效率。
为了不重复解析相同的SQL语句，在第一次解析之后，Oracle将SQL语句存放在内存中，这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。因此，当执行一个SQL语句时，如果它和之前的执行过的语句完全相同，Oracle就能很快获得已经被解析的语句以及最好的执行路径。Oracle的这个高速缓冲(cache buffering)功能大大地提高了SQL的执行性能并节省了内存的使用。
可惜的是Oracle只对简单的表提供高速缓冲，这个功能有时候并不适用于多表连接查询。DBA需要在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。
当你向Oracle 提交一个SQL语句，Oracle会首先在这块内存中查找相同的语句。这里需要注明的是，Oracle对两者采取的是一种严格匹配。要达成共享，a.SQL语句必须完全相同(包括空格、换行等)，字符大小写也需一致；b.两个语句所指的对象必须完全相同，如果同一表名对不同DB用户来说代表的不是同一张表，则不能匹配；c.在使用绑定变量的SQL文时，绑定变量名也必须完全相同。 3、减少访问数据库的次数。
当执行每条SQL语句时，Oracle在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据库的次数，就能实际上减少Oracle的工作量。 4、尽量多使用COMMIT。
只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少。COMMIT所释放的资源：
a. 回滚段上用于恢复数据的信息
b. 被程序语句获得的锁
c. redo log buffer 中的空间
d. Oracle为管理上述3种资源中的内部花费
但是，在使用COMMIT时必须要注意到事务的完整性，一般来说COMMIT的使用更多的是从实际的业务逻辑出发来考虑的。 5、SELECT子句中避免使用"*"，只将需要的字段名进行列举。
对于"*"，Oracle在解析的过程中通过查询数据字典（user_tab_columns）将"*"转换为所有的列名，不可取。
只将所需要的字段名进行选取，如果是统计count(*)，则可用Primary Key代替。添加没有必要的字段，会加大Oracle的检索量，从而降低效率。总体来说，使用"*"所带来的影响不是很大，但从最优化角度考虑，此规则还是必须的。 6、对于所用到的表，一定要加上别名。
使用别名可降低Oracle解析处理的开销，提高效率，同时对于可读性也是有利的。如：
Select Employee_Id FROM employees, cct_odr ==> 不妥
SELECT A.EMPLOYEE_ID FROM EMPLOYEES A, CCT_ODR B ==> 正确 7、WHERE子句中的连接顺序。
Oracle采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾(越有效地缩小范围的条件越放后)。但是在效率与可读性之间需做出一定的调整，而且在使用HINT文(提示处理)后能主动指定需要索引的字段来调整收缩顺序，因此建议对于简单的或者数据量小的SQL文可以重点考虑其可读性，而对于复杂的或者数据量大的SQL文则考虑使用HINT文（下文将详细介绍）。
SELECT ... FROM EMP A
WHERE A.SAL > 50000 AND A.JOB = 'MANAGER'
AND 25 < (SELECT COUNT(B.EMPNO) FROM EMP B WHERE A.MGR = B.EMPNO) ==> 不妥
SELECT ... FROM EMP A
WHERE 25 < (SELECT COUNT(B.EMPNO) FROM EMP B WHERE A.MGR = B.EMPNO)
AND A.SAL > 50000 AND A.JOB = 'MANAGER' ==> 正确 8、WHERE子句中的条件的指定。
两表连接的时候，对于收缩条件，记录数少的优先考虑。如下面两表A、B，A.X_VALUE有1万件，而B.X_VALUE有5万件，则：
SELECT ... FROM TABLE_A A, TABLE_B B WHERE A.X_VALUE = B.X_VALUE AND B.X_VALUE = 10 ==> 不妥
SELECT ... FROM TABLE_A A, TABLE_B B WHERE A.X_VALUE = B.X_VALUE AND A.X_VALUE = 10 ==> 正确 9、控制使用GROUP BY、ORDER BY和HAVING。
使用GROUP BY、ORDER BY和HAVING时会发生磁盘I/O操作，不是必要的话，需谨慎使用。 10、对操作符的优化：使用DECODE函数，或者CASE来减少处理时间，避免重复扫描相同记录或重复连接相同的表。
CASE的语法：CASE WHEN [条件式1] THEN [值1] WHEN [条件式2] THEN [值2] ... ELSE [值] END
如：
SELECT A.SAL*10 AS SAL FROM EMP A WHERE A.DEPT_NO = 0020;
SELECT A.SAL*50 AS SAL FROM EMP A WHERE A.DEPT_NO = 0030; ==> 不妥:重复扫描字段DEPT_NO，多次进出数据库
SELECT DECODE(A.DEPT_NO, 0020, A.SAL*10, 0030 A.SAL*50, 0)AS SAL FROM EMP A WHERE SAL > 0; ==> 正确
或者：
SELECT CASE WHEN A.DEPT_NO=0020 THEN A.SAL*10 WHEN A.DEPT_NO=0030 THEN A.SAL*50 ELSE 0 END AS SAL
FROM EMP A WHERE SAL > 0; ==> 正确 11、对操作符的优化：避免使用IN、NOT IN操作符，用多表连接或者EXISTS、NOT EXISTS来代替。
用IN写出来的SQL的优点是比较容易写及清晰易懂，但性能总是比较低的。从Oracle执行的步骤来分析，Oracle试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。
SELECT DEPARTMENT_ID FROM DEPARTMENTS D WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES)
==>不妥：IN的使用，需避免
SELECT DEPARTMENT_ID FROM DEPARTMENTS D WHERE EXISTS (SELECT 1 FROM EMPLOYEES E WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID)
==>正确：用EXISTS进行了代替 12、对操作符的优化：避免使用DISTINCT操作符，有时候可用EXISTS来代替。
如果可以的话，一定要避免DISTINCT的使用。有些DISTINCT是可以用EXISTS来进行代替的，如：
SELECT DISTINCT A.ID1, A.NAME1 FROM TABLE1 A, TABLE2 B WHERE A.ID1 = B.ID2 ==> 不妥
SELECT A.ID1, A.NAME1 FROM TABLE1 A WHERE EXISTS (SELECT '1' FROM TABLE2 B WHERE A.ID1 = B.ID2) ==> 正确 13、对操作符的优化：避免在索引列上使用<>操作符(不等于)，用> or 不等于操作符是不能利用索引（INDEX）的，因此对它的处理只会产生全表扫描，需避免。如ID<>0 可改为 ID>0 or ID<0。 14、对操作符的优化：在索引列上使用NOT，需谨慎。
NOT会产生在和在索引列上使用函数相同的影响，当Oracle遇到NOT时，它就会停止使用索引转而执行全表扫描。然而在某些时候，Oracle优化器会自动将NOT转化成相对应的关系操作符，如：NOT > 转化成： <=，NOT >= 转化成： SELECT A.NAME FROM EMP A WHERE NOT A.EMPNO > 1;
SELECT A.NAME FROM EMP A WHERE A.EMPNO <= 1; 15、对操作符的优化：在索引列上使用>、=、<=操作符（大于、小于及大于等于、小于等于）时，需考虑实际情况。
对于>=，Oracle其实做的是> r =的操作，所以对于一个数值型字段VAL_NUM来说，VAL_NUM>=3的效率不如VAL_NUM>2来的好。当然也有特殊情况，比如30万记录中VAL_NUM=2的有29万，而1万记录是VAL_NUM>=3的，那么执行VAL_NUM>2时Oracle会找出=2的记录索引再进行比较，而VAL_NUM>=3时Oracle则直接找>=3的记录索引，所以这时候VAL_NUM>2的效率不如VAL_NUM>=3来的好。
一般情况下，着重考虑的是实际情况和可读性，但是当速度差生比较大的影响时，需要考虑一下此规则的应用。 16、对操作符的优化：避免在索引列上使用IS NULL或IS NOT NULL操作（判断字段是否为空），但根据实际情况变动。
判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。
推荐方案是不允许字段为空，而用一个缺省值代替空值。然后VAL IS NOT NULL改为 VAL>0 或者 VAL>''等。也可以建立位图索引，但有分区的表不能建，而且位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象。
总之此优化规则需要考虑实际情况，来决定是否应用，如何应用。 17、对操作符的优化：LIKE操作符。
LIKE操作符可以应用通配符（%、_）查询，其中%接受任意个数的字符，_接受一个字符。LIKE如果用得不好也会产生性能上的问题，如LIKE '%5400%'这种查询不会引用索引，而LIKE 'X5400%'则会引用范围索引。一个实际例子：VAL LIKE '%5400%'这个条件会产生全表扫描，如果VAL值第一位是'B'或者'X'，则改成VAL LIKE 'B5400%' OR VAL LIKE 'X5400%'则会利用VAL的索引进行两个范围的查询，性能要好很多。而后方一致，如LIKE '%5400'也会导致全表检索。 18、对操作符的优化：对于UNION操作符，避免使用UNION，能够的话用UNION ALL。
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。如：
SELECT A.ID1, A.NAME1 FROM TABLE1 A
UNION
SELECT B.ID1, B.NAME1 FROM TABLE2 B
这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话还可能会用磁盘来进行排序。而采用UNION ALL的话，只是简单的将两个结果合并后就返回。
SELECT A.ID1, A.NAME1 FROM TABLE1 A
UNION ALL
SELECT B.ID1, B.NAME1 FROM TABLE2 B
一般来说如果对于两个不同的表的话，经常是不用考虑其结果是否重复的，即使需要考虑的话，有时候也可以通过程序中来进行控制。
此规则请根据实际情况来进行调整。 19、使用索引。
一般使用了索引的话，会大大的加快SQL文的实行速度，Oracle的实行计划，也会自动使用索引（能够使用的话）来加快速度。
当然使用索引也会增大开销，一般来说是利大于弊的，特别是对于索引唯一扫描 ( INDEX UNIQUE SCAN)，但是对于有些索引范围查询(INDEX RANGE SCAN)而言，则需视情况而定。这个一般是WHERE子句中指定了一部分多列索引的字段条件而导致的，这种场合下有时反而会是全表检索的效率好，需要对实际情况进行考虑，并进行两者的测试。
比如此索引值的纪录非常多，跟全表纪录差不多，则可能是用全表检索开销会小一点，这种情况下即使Oracle自动应用了此索引，也要用HINT文，或WHERE子句中修改相应条件来强制排除此一索引。
索引有很多种，传统的索引--B树（B-Tree）索引，位图（bitmap)索引，哈希（hash)索引，基于原始表的索引，基于函数的索引和域（Domain）索引。实际应用中主要是B-Tree索引和位图索引，所以简单介绍这两种索引类型。
B-Tree索引是最普通的索引，缺省条件下建立的索引就是这种类型的索引，对于包含大量变化数据的列是非常有用的，主键和唯一约束就会隐式的建立这种索引；B-Tree索引可以是唯一或非唯一的，可以是单一的（基于一列）或连接的（多列）。B-Tree索引在检索高基数数据列（该列有很多不同的值）时提供了最好的性能。对于取出较少的数据B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。正如名字所暗示的那样，B-Tree索引是基于二元树的，由枝干块（branch block)和树叶块（leaf block)组成，枝干块包含了索引列（关键字）和另一索引的地址。树叶块包含了关键字和给表中每个匹配行的ROWID。
位图（bitmap）索引，对于不同值很少的字段，例如区分等比较合适。主要用于决策支持系统或静态数据，不支持行级锁定。位图索引可以是简单的（单列）也可以是连接的（多列），但在实践中绝大多数是简单的。位图索引最好用于低到中群集（cardinality)列，在这些列上多位图索引可以与AND或OR操作符结合使用。位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE（1）、FALSE（0）、或NULL值。位图索引的位图存放在B-Tree结构的页节点中。B-Tree结构使查找位图非常方便和快速。另外，位图以一种压缩格式存放，因此占用的磁盘空间比B-Tree索引要小得多。
此外使用了函数的字段是没法应用索引的，但是可以建立基于函数的索引，则使用相应函数时，照样可以进行索引。
还有一点，对于一个关联多列的索引，如表TABLE_A的索引DEPT_IDX由多列字段DE_NO，PT_DIV（按先后顺序）组成，则要利用此索引的话，WHERE子句中必须要包含有此索引第一个列DE_NO的条件。
在索引带来便利的同时，也要花费一定的开销。索引需要空间来存储，也需要定期维护。每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT、DELETE、UPDATE将为此多付出4、5次的磁盘I/O。因为索引需要额外的存储空间和处理，所以在大型表中正确的使用索引是特别有效的，但索引使用错误，或使用了效率不好的索引，或者对很小的表建立索引，则会适得其反。 20、索引的利用：习惯将需要利用索引（INDEX）的字段名放在WHERE子句中条件的左边，并且避免对其使用函数。
如果对需要索引的字段使用了函数，则Oracle将无法对此字段进行索引，需要避免，用其他方式来等价替换。如：
SUBSTR(VAL, 1, 4) = '5400' ==> 不妥
VAL LIKE '5400%' ==> 正确
当然可以省略的函数也应该去掉，如：
TO_NUMBER(A.ORDER_NO) = TO_NUMBER(B.ORDER_NO) ==> 不妥
A.ORDER_NO = B.ORDER_NO ==> 正确 21、索引的利用：WHERE子句中条件的两边类型不一致的情况需要禁止，对类型转换函数TO_NUMBER、TO_CHAR的使用也需要注意。
当条件两边数据类型不一致时，Oracle会自动进行类型转换，如：CHAR_VAL为VARCHAR2型
CHAR_VAL = 1 ==> 不妥：Oracle自动进行TO_NUMBER(CHAR_VAL) = 1 和 CHAR_VAL = TO_CHAR(1)的转换，再继续操作
CHAR_VAL = '1' ==> 正确
还有就是对于TO_NUMBER、TO_CHAR的使用，如果想要对CHAR_VAL进行索引，则
TO_NUMBER(CHAR_VAL) = NUM_VAL ==> 不妥
CHAR_VAL = TO_CHAR(NUM_VAL) ==> 正确 22、索引的利用：进行了显式或隐式的运算的字段不能进行索引，需要避免。
当条件两边数据类型不一致时，Oracle会隐式的进行类型转换，如上一规则所言。再比如：
NUM_VAL+20 > 50， 优化处理成：NUM_VAL > 30，
NUM_VAL+20 > NUMM 优化处理成：NUM_VAL > NUMM-20，
'X' || CHAR_VAL > 'X5400'，优化处理成：CHAR_VAL > '5400' 23、索引的利用：条件内包括了多个本表的字段运算时不能进行索引，需要避免。
如同一表的两字段：CHAR_VAL1 > CHAR_VAL2，则无法进行优化。
再如CHAR_VAL1 || CHAR_VAL2 = '5400NAME'，可以的话，优化处理成：CHAR_VAL1 = '5400' AND CHAR_VAL2 = 'NAME' 24、索引的利用：复合式的使用需要禁止。
复合式的应用如：NUM_VAL = NVL(NUM2, NUM_VAL)，NVL(NUM_VAL, 0) = ...等，使得Oracle对其实行计划无法进行最优化，结果导致对实行计划以及表的结合方法产生恶劣影响，需要禁止这种用法。如：
NVL(NUM_VAL, 0) = 0 ==> 错误
NUM_VAL = 0 OR NUM_VAL IS NULL ==> 正确 25、索引的利用：多个索引的利用情况，以及>、 在一个SQL文中可能会分布有多个索引。Oracle选择执行路径时，唯一性索引的等级高于非唯一性索引。如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性。在这种情况下，Oracle将使用唯一性索引而完全忽略非唯一性索引。然而这个规则只有当WHERE子句中索引列和常量比较才有效。如果索引列和其他表的索引类相比较,这种子句在优化器中的等级是非常低的。如果是等式比较和范围比较的组合，两者都有索引，则只对等号的字段使用索引，即范围比较的唯一性索引，等级要比等式比较的非唯一性索引低。如：
SELECT A.NAME FROM CUSTOMERS A
WHERE A.SAL > 1000 ==> INDEX不使用，即使SAL为主键也不索引此字段
AND A.JOB = 'MANAGER' ==> INDEX使用
如果不同表中两个相同等级的索引被引用，FROM子句中表的顺序将决定哪个会被率先使用。FROM子句中最后的表的索引将有最高的优先级。如果相同表中两个想同等级的索引被引用，WHERE子句中最先被引用的索引将有最高的优先级。如：
SELECT A.NAME FROM CUSTOMERS A
WHERE A.TAX > 100 ==> INDEX使用
AND A.SAL > 1000 ==> INDEX不使用
如DEPT_NO为EMP的主键，而CUST_NO为CUSTOMERS的主键，则：
SELECT A.NAME FROM CUSTOMERS A, EMP B
WHERE A.CUST_NO = B.CUST_NO
AND A.DEPT_NO = '19' ==> INDEX不使用
AND B.CUST_NO = '76' ==> INDEX使用 26、WITH A AS()的用法。
复杂的子查询，或者被多次引用到的子查询，可以在SQL文开始处用WITH A AS()建立一个类似临时的视图，然后在SQL文中就可以用A来引用这个查询了，增加可读性，同时在多次引用到的场合下也能提高效率。 综上所述，各种优化规则往往对应其实际情况，并且还伴随着可读性这一很重要的编程因素在里面。这里对于索引是一个很重的方面，很多的考虑都是为了使用索引，若是索引带来便利要小于其开销时，便需要反向来应用这一规则，所以需要编程人员好好的理解，并结合实际情况，灵活的应用各种优化方案，更考虑到程序的可读性，分析各方面的利弊得失，并且反复通过实行计划和实际运行的效果，最终得出一个比较理想的方案。有关HINT文方面的，请见HINT文的简介。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13956325/viewspace-598286/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/13956325/viewspace-598286/
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
