语法分析> 语义分析> 视图转换 >表达式转换> 选择优化器 >选择连接方式 >选择连接顺序 >选择数据的搜索路径 >运行“执行计划”
RULE（基于规则） COST（基于成本） CHOOSE（选择性）
全表扫描
全表扫描就是顺序地访问表中每条记录，ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。
通过ROWID访问表
ROWID包含了表中记录的物理位置信息，ORACLE采用索引实现了数据和存放数据的物理位置(ROWID)之间的联系，通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。
Oracle提供对执行过的SQL语句进行高速缓冲的机制。被解析过并且确定了执行路径的SQL语句存放在SGA的共享池中。
Oracle执行一个SQL语句之前每次先从SGA共享池中查找是否有缓冲的SQL语句，如果有则直接执行该SQL语句。
可以通过适当调整SGA共享池大小来达到提高Oracle执行性能的目的。
OLTP 联机事务处理,适合使用共享SQL语句来达到优化的目的,eg : Java 中的PrepareStatement, Oracle中的变量绑定.SELECT 1 FROM EMP WHERE NAME=:x USING :VNAME;
?? - dynamic sampling used for this statement
23 rows selected.
1、根据tabs表的where条件，查找出符合条件的结果集。
2、以全表扫描的方式，扫描出cols表中的结果集。
3、按照table_name列对tabs表进行排序
4、按照table_name列对cols表进行排序
5、对两个有序的结果集进行合并，并返回最终记录集。
Sort Merge主要的开销在于对两表连接的排序，已经在连接关联列上已经进行了排序，则该连接操作就不需要再进行 sort 操作，这样可以大大提高这种连接操作的连接速度，特别是对于较大的表。
?? - dynamic sampling used for this statement
22 rows selected.
举例，从tabs的查询结果集中(owner like ‘SC%‘)里取出table_name=‘A‘，那么对于colsb表来说即select cols.table_name from cols where cols.table_name=‘A‘,如果条件满足，则将tabs表中，tabs.table_name=‘A‘和table_name=‘A‘的结果集存储在hash table里。然后接着从tabs取出table_name=‘B‘，如果子查询依旧条件满足，那么将tabs.table_name=‘B‘和table_name=‘B‘的结果集存储在hash table里。接着假设tabs里有重复的table_name，例如我们第三次从取出tabs.table_name=‘B‘，那么由于我们对于子查询来说，已经有输入输出对tabs.table_name=‘B‘在hash table里了，ORACLE会非常智能的将tabs.table_name=‘B‘的查询结果作为结果集，而不再对cols表进行全表扫描。这样，filter和neested loop相比,省去了一次全表扫描cols。这个hash table是有大小限制的，当被占满的时候，后续新的tabs.table_name的FILTER就类似neested loop了。
由此可见，当连接字段的distinct value值较小时，FILTER性能优于nested loop。
?? - dynamic sampling used for this statement
20 rows selected.
?1、计算小表的分区（bucket）数
决 定hash join的一个重要因素是小表的分区（bucket）数。这个数字由hash_area_size、hash_multiblock_io_count和 db_block_size参数共同决定。Oracle会保留hash area的20%来存储分区的头信息、hash位图信息和hash表。因此，这个数字的计算公式是：
Bucket数=0.8hash_area_size/(hash_multiblock_io_countdb_block_size)
2、Hash计算???
根据结果集读取小表tabs数据（简称为R），并对每一条数据根据hash算法进行计算。Oracle采用两种hash算法进行计算，计算出能达到最快速度的hash值
（第一hash值和第二hash值）。而关于这些分区的全部hash值（第一hash值）就成为hash表。
3、存放数据到hash内存中
将经过hash算法计算的数据，根据各个bucket的hash值（第一hash值）分别放入相应的bucket中。第二hash值就存放在各条记录中。
4、创建hash位图
与此同时，也创建了一个关于这两个hash值映射关系的hash位图。
5、超出内存大小部分被移到磁盘
如果hash area被占满，那最大一个分区就会被写到磁盘（临时表空间）上去。任何需要写入到磁盘分区上的记录都会导致磁盘分区被更新。这样的话，就会严重影响性能，因此一定要尽量避免这种情况。
持续2～5步骤，直到整个表的数据读取完毕。
6、对分区排序
为了能充分利用内存，尽量存储更多的分区，Oracle会按照各个分区的大小将他们在内存中排序。
7、读取大表数据，进行hash匹配
接 下来就开始读取大表cols表（简称S）中的数据。按顺序每读取一条记录，计算它的hash值，并检查是否与内存中的分区的hash值一致。如果是，返回 join数据。如果内存中的分区没有符合的，就将S中的数据写入到一个新的分区中，这个分区也采用与计算R一样的算法计算出hash值。也就是说这些S中 的数据产生的新的分区数应该和R的分区集的分区数一样。这些新的分区被存储在磁盘（临时表空间）上。
8、完成大表全部数据的读取
一直按照7进行，直到大表中的所有数据的读取完毕。
9、处理没有join的数据
这个时候就产生了一大堆join好的数据和从R和S中计算存储在磁盘上的分区。
10、二次hash计算
从R和S的分区集中抽取出最小的一个分区，使用第二种hash函数计算出并在内存中创建hash表。采用第二种hash函数的原因是为了使数据分布性更好。
11、二次hash匹配
在从另一个数据源（与hash在内存的那个分区所属数据源不同的）中读取分区数据，与内存中的新hash表进行匹配。返回join数据。
12、完成全部hash join
继续按照9-11处理剩余分区，直到全部处理完毕。
注 意:hash算法中的位图包含了每个hash分区是否有值的信息。它记录了有数据的分区的hash值。这个位图的最大作用就是，如果S表中的数据没有与内 存中的hash表匹配上，先查看这个位图，已决定是否将没有匹配的数据写入磁盘。那些不可能匹配到的数据（即位图上对应的分区没有数据）就不再写入磁盘。
Hash Join的相关参数
HASH_JOIN_ENABLED
这个参数是控制查询计划是否采用hash join的“总开关”。它可以在会话级和实例级被修改。默认为TRUE，既可以（不是一定，要看优化器计算出来的代价）使用。如果设为FALSE，则禁止使用hash join。
HASH_AREA_SIZE
这 个参数控制每个会话的hash内存空间有多大。它也可以在会话级和实例级被修改。默认（也是推荐）值是sort area空间大小的两倍（2SORT_AREA_SIZE）。要提高hash join的效率，就一定尽量保证sort area足够大，能容纳下整个小表的数据。但是因为每个会话都会开辟一个这么大的内存空间作为hash内存，所以不能过大（一般不建议超过2M）。在 Oracle9i及以后版本中，Oracle不推荐在dedicated server中使用这个参数来设置hash内存，而是推荐通过设置PGA_AGGRATE_TARGET参数来自动管理PGA内存。保留 HASH_AREA_SIZE只是为了向后兼容。在dedicated server中，hash area是从PGA中分配的，而在MTS(Multi-Threaded Server)中，hash area是从UGA中分配的。另外，还要注意的是，每个会话并不一定只打开一个hash area，因为一个查询中可能不止一个hash join，这是就会相应同时打开多个hash area。
HAHS_MULTIBLOCK_IO_COUNT
这 个参数决定每次读入hash area的数据块数量。因此它会对IO性能产生影响。他只能在init.ora或spfile中修改。在8.0及之前版本，它的默认值是1，在8i及以后 版本，默认值是0。一般设置为1-(65536/DB_BLOCK_SIZE)。在9i中，这个参数是一个隐藏参 数：_HASH_MULTIBLOCK_IO_COUNT，可以通过表x$ksppi查询和修改。另外，在MTS中，这个参数将不起作用（只会使用1）。 它的最大值受到OS的IO带宽和DB_BLOCK_SIZE的影响。既不能大于MAX_IO_SIZE/DB_BLOCK_SIZE。在8i及以后版本， 如果这个值设置为0，则表示在每次查询时，Oracle自己自动计算这个值。这个值对IO性能影响非常大，因此，建议不要修改这个参数，使用默认值0，让 Oracle自己去计算这个值。
如果一定要设置这个值，要保证以下不等式能成立：
???? R/M < Po2(M/C)
其中，R表示小表的大小；M=HASH_AREA_SIZE0.9；Po2(n)为n的2的次方C=HASH_MULTIBLOCK_IO_COUNT*DB_BLOCK_SIZE。
总结
1、Filter
原理上和Nest Loop Join类似，不同点在于维护hash table，总体上性能优于Nest Loop Join。
2、嵌套循环(Nest Loop Join):
对 于被连接的数据子集较小的情况，嵌套循环连接是较好的选择。在嵌套循环中，外表驱动内表，外表返回的每一行都要在内表中检索找到它匹配的行，因此整个查询 返回的结果集不能太大（大于10000不合适），要把返回子集较小的表作为外表（驱动表），而且在内表的连接字段上建议有索引。
3、排序合并连接（Sort Merge Join ）
通常情况下哈希连接的效果都比排序合并连接要好。然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序归并连接的性能会忧于哈希连接。
4、哈希连接(hash join):
哈希连接是大数据集连接时常用的方式，优化器使用两个表中较小的表，利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。
这种方式适用于较小的表完全可以放入内存的情况，这样成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段。
本段引用: 
SQL语句全部使用大写, 因为Oracle解析时会把小写全部换成大写再解析
Oracle在解析SQL语句的时候，对于“”将通过查询数据库字典来将其转换成对应的列名。
如果在Select子句中需要列出所有的Column时，建议列出所有的Column名称，而不是简单的用“”来替代，这样可以减少多于的数据库查询开销。
当执行每条SQL语句时, ORACLE在内部执行了许多工作： 解析SQL语句 > 估算索引的利用率 > 绑定变量 > 读数据块等等
由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量。
如果有几个简单的数据库查询语句，你可以把它们整合到一个查询中（即使它们之间没有关系），以减少多于的数据库IO开销。
虽然采取这种方法，效率得到提高，但是程序的可读性大大降低，所以还是要权衡之间的利弊。
Delete表中记录的时候，Oracle会在Rollback段中保存删除信息以备恢复。Truncate删除表中记录的时候不保存删除信息，不能恢复。因此Truncate删除记录比Delete快，而且占用资源少。
删除表中记录的时候，如果不需要恢复的情况之下应该尽量使用Truncate而不是Delete。
Truncate仅适用于删除全表的记录。
oracle-2中commit 详解
博文转自：http://blog.csdn.net/hzhsan/article/details/9719307
它执行的时候，你不会有什么感觉。commit在数据库编程的时候很常用，当你执行DML操作时，数据库并不会立刻修改表中数据，这时你需要commit，数据库中的数据就立刻修改了，如果在没有commit之前，就算你把整个表中数据都删了，如果rollback的话，数据依然能够还原。听 我这么说，你或许感觉commit没什么用，其实不然。当你同时执行两条或两条以上的sql语句时，问题就出现了。举一个例子，你去银行转账，你转的时候 银行的数据库会update你银行账户里面的数据，同时对另一个人得账户也进行update操作。这两个程序都必须全部正确执行，才能commit，否则 rollback。如果只是完成一条，要么你郁闷，要么银行郁闷，第一种情况是，你的账户的钱没少，转账人得账户上的钱多了，银行郁闷了。第二种情况你的 银行账户的钱少了，他的却没多，你就好郁闷了。Oracle好好学吧！sql不难，plsql努努力也能熬过去，等到优化那，哎！DBA不是那么好当的。 还有就是commit算是显式提交，还有隐式提交，并不是，不commit的话，你的全部努力就都白费了。
这个命令是将数据写到数据库中。如果不执行COMMIT这个命令，那么在你这个session之外的其他session查询的数据是你修改数据之前的数据。而COMMIT之后人家查询的是你修改的数据。你可以打开两个sqlplus比较做一下测试。一目了然。
commit的提交针对的是:DML
Data Manipulation Language(DML) 需要提交，这部分是对数据管理操作，比如Insert（插入）、Update（修改）、Delete（删除），
Data Definition Language(DDL) 不需要提交，这部分是对数据结构定义，比如 Create（创建）、Alter（修改)、Drop（删除）
oracle的commit就是提交数据（这里是释放锁不是锁表），在未提交前你前面的操作更新的都是内存，没有更新到物理文件中。执行commit从用户角度讲就是更新到物理文件了，事实上commit时还没有写date file，而是记录了redo log file，要从内存写到data物理文件，需要触发检查点，由DBWR这个后台进程来写，这里内容有点多的，如果不深究的话你就理解成commit即为从内存更新到物理文件。锁有很多种，一般我们关注的都是DML操作产生的，比如insert，delete，update，select...for update都会同时触发表级锁和行级锁
补充：对的，insert以后commit之前是锁表的状态，其他事务无法对该表进行操作。
如果不提交的话，那么这个表就被锁了
COMMIT通常是一个非常快的操作，而不论事务大小如何。你可能认为，一个事务越大(换句话说，它影响的数据越多)，COMMIT需要的时间就越长。不是这样的。不论事务有多大，COMMIT的响应时间一般都很“平”(flat，可以理解为无高低变化)。这是因为COMMIT并没有太多的工作去做，不过它所做的确实至关重要。
这一点很重要，之所以要了解并掌握这个事实，原因之一是：这样你就能心无芥蒂地让事务有足够的大小。一种错误的信念认为分批提交可以节省稀有的系统资源，而实际上这只是增加了资源的使用。如果只在必要时才提交(即逻辑工作单元结束时)，不仅能提高性能，还能减少对共享资源的竞争(日志文件、各种内部闩等)。
分批提交COMMIT的开销存在两个因素：
显然会增加与数据库的往返通信。如果每个记录都提交，生成的往返通信量就会大得多。
每次提交时，必须等待redo写至磁盘。这会导致“等待”。在这种情况下，等待称为“日志文件同步”(log file sync)。
为什么COMMIT的响应时间相当“平”，而不论事务大小呢？在数据库中执行COMMIT之前，困难的工作都已经做了。我们已经修改了数据库中的数据，所以99.9%的工作都已经完成。例如，已经发生了以下操作：
已经在SGA中生成了undo块。
已经在SGA中生成了已修改数据块。
已经在SGA中生成了对于前两项的缓存redo。
取决于前三项的大小，以及这些工作花费的时间，前面的每个数据(或某些数据)可能已经刷新输出到磁盘。
已经得到了所需的全部锁。
执行COMMIT时，余下的工作只是：
为事务生成一个SCN。如果你还不熟悉SCN，起码要知道，SCN是Oracle使用的一种简单的计时机制，用于保证事务的顺序，并支持失败恢复。SCN 还用于保证数据库中的读一致性和检查点。可以把SCN看作一个钟摆，每次有人COMMIT时，SCN都会增1.
LGWR将所有余下的缓存重做日志条目写到磁盘，并把SCN记录到在线重做日志文件中。这一步就是真正的COMMIT。如果出现了这一步，即已经提交。事务条目会从V$TRANSACTION中“删除”，这说明我们已经提交。
V$LOCK中记录这我们的会话持有的锁，这些所都将被释放，而排队等待这些锁的每一个人都会被唤醒，可以继续完成他们的工作。
如果事务修改的某些块还在缓冲区缓存中，则会以一种快速的模式访问并“清理”。块清除(Block cleanout)是指清除存储在数据库块首部的与锁相关的信息。实质上讲，我们在清除块上的事务信息，这样下一个访问这个块的人就不用再这么做了。我们采用一种无需生成重做日志信息的方式来完成块清除，这样可以省去以后的大量工作(在下面的“块清除”一节中将更全面地讨论这个问题)。
可以看到，处理COMMIT所要做的工作很少。其中耗时最长的操作要算LGWR执行的活动(一般是这样)，因为这些磁盘写是物理磁盘I/O。不过，这里LGWR花费的时间并不会太多，之所以能大幅减少这个操作的时间，原因是LGWR一直在以连续的方式刷新输出重做日志缓冲区的内容。在你工作期间，LGWR并非缓存这你做的所有工作;实际上，随着你的工作的进行，LGWR会在后台增量式地刷新输出重做日志缓冲区的内容。这样做是为了避免COMMIT等待很长时间来一次性刷新输出所有的redo。
因此，即使我们有一个长时间运行的事务，但在提交之前，它生成的许多缓存重做日志已经刷新输出到磁盘了(而不是全部等到提交时才刷新输出)。这也有不好的一面，COMMIT时，我们必须等待，直到尚未写出的所有缓存redo都已经安全写到磁盘上才行。也就是说，对LGWR的调用是一个同步(synchronous)调用。尽管LGWR本身可以使用异步I/O并行地写至日志文件，但是我们的事务会一直等待LGWR完成所有写操作，并收到数据都已在磁盘上的确认才会返回。
前面我提高过，由于某种原因，我们用的是一个Java程序而不是PL/SQL，这个原因就是 PL/SQL提供了提交时优化(commit-time optimization)。我说过，LGWR是一个同步调用，我们要等待它完成所有写操作。在Oracle 10g Release 1及以前版本中，除PL/SQL以外的所有编程语言都是如此。PL/SQL引擎不同，要认识到直到PL/SQL例程完成之前，客户并不知道这个PL /SQL例程中是否发生了COMMIT，所以PL/SQL引擎完成的是异步提交。它不会等待LGWR完成;相反，PL/SQL引擎会从COMMIT调用立即返回。不过，等到PL/SQL例程完成，我们从数据库返回客户时，PL/SQL例程则要等待LGWR完成所有尚未完成的COMMIT。因此，如果在PL /SQL中提交了100次，然后返回客户，会发现由于存在这种优化，你只会等待LGWR一次，而不是100次。这是不是说可以在PL/SQL中频繁地提交呢？这是一个很好或者不错的主意吗？不是，绝对不是，在PL/SQ;中频繁地提交与在其他语言中这样做同样糟糕。指导原则是，应该在逻辑工作单元完成时才提交，而不要在此之前草率地提交。
COMMIT是一个“响应时间很平”的操作，虽然不同的操作将生成不同大小的redo，即使大小相差很大或者说无论生成多少redo，但也并不会影响提交(COMMIT)的时间或者说提交所用的时间都基本相同。
Select count(*) from tablename;
Select count(1) from tablename;
Select max(rownum) from tablename;
一般认为，在没有索引的情况之下，第一种方式最快。 如果有索引列，使用索引列当然最快。
避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序、总计等操作。 如果能通过WHERE子句限制记录的数目，就能减少这方面的开销。
在含有子查询的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）
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属。
综合以上对IN/EXISTS的讨论，我们可以得出一个基本通用的结论：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大(内表上必须有索引)的情况。
当连接字段的distinct value值较小时，FILTER性能(EXISTS)优于nested loop(表连接)。
当提交一个包含对多表信息（比如部门表和雇员表）的查询时，避免在SELECT子句中使用DISTINCT。 一般可以考虑用EXIST替换。
EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。
低效：
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
下面的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 Trace工具来收集正在执行的SQL的性能状态数据，包括解析次数，执行次数，CPU使用时间等 。
EXPLAIN PLAN 是一个很好的分析SQL语句的工具, 它甚至可以在不执行SQL的情况下分析语句. 通过分析, 我们就可以知道ORACLE是怎么样连接表, 使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称。
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)
复制代码
（1）特点
优点： 提高效率 主键的唯一性验证
代价： 需要空间存储 定期维护
重构索引：
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
（2）Oracle对索引有两种访问模式
索引唯一扫描 (Index Unique Scan)
索引范围扫描 (Index Range Scan)
（3）基础表的选择
基础表(Driving Table)是指被最先访问的表(通常以全表扫描的方式被访问)。 根据优化器的不同，SQL语句中基础表的选择是不一样的。
如果你使用的是CBO (COST BASED OPTIMIZER)，优化器会检查SQL语句中的每个表的物理大小，索引的状态，然后选用花费最低的执行路径。
如果你用RBO (RULE BASED OPTIMIZER)， 并且所有的连接条件都有索引对应，在这种情况下，基础表就是FROM 子句中列在最后的那个表。
（4）多个平等的索引
当SQL语句的执行路径可以使用分布在多个表上的多个索引时，ORACLE会同时使用多个索引并在运行时对它们的记录进行合并，检索出仅对全部索引有效的记录。
在ORACLE选择执行路径时，唯一性索引的等级高于非唯一性索引。然而这个规则只有当WHERE子句中索引列和常量比较才有效。如果索引列和其他表的索引类相比较。这种子句在优化器中的等级是非常低的。
如果不同表中两个相同等级的索引将被引用，FROM子句中表的顺序将决定哪个会被率先使用。 FROM子句中最后的表的索引将有最高的优先级。
如果相同表中两个相同等级的索引将被引用，WHERE子句中最先被引用的索引将有最高的优先级。
（5）等式比较优先于范围比较
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
即使是唯一性索引，如果做范围比较，其优先级也低于非唯一性索引的等式比较。
（6）不明确的索引等级
当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
（7）强制索引失效
如果两个或以上索引具有相同的等级，你可以强制命令ORACLE优化器使用其中的一个(通过它,检索出的记录数量少) 。
SELECT ENAME
FROM EMP
WHERE EMPNO = 7935
AND DEPTNO + 0 = 10 /DEPTNO上的索引将失效/
AND EMP_TYPE || ‘’ = ‘A’ /EMP_TYPE上的索引将失效/
（8）避免在索引列上使用计算
WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效：
SELECT …
FROM DEPT
WHERE SAL > 25000/12;
（9）自动选择索引
如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性索引。在这种情况下，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
（10）避免在索引列上使用NOT
通常，我们要避免在索引列上使用NOT，NOT会产生在和在索引列上使用函数相同的影响。当ORACLE遇到NOT，它就会停止使用索引转而执行全表扫描。
低效: (这里，不使用索引)
SELECT …
FROM DEPT
WHERE NOT DEPT_CODE = 0
高效：(这里，使用了索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE > 0
如果DEPTNO上有一个索引
高效:
SELECT *
FROM EMP
WHERE DEPTNO >=4
低效：
SELECT *
FROM EMP
WHERE DEPTNO >3
两者的区别在于，前者DBMS将直接跳到第一个DEPT等于4的记录，而后者将首先定位到DEPTNO等于3的记录并且向前扫描到第一个DEPT大于3的记录.
通常情况下，用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。 注意，以上规则只针对多个索引列有效。
高效：
复制代码
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”
union查询时，在索引列查询速度快。
or,in采用的是全表扫描机制，更适用于非索引列查找。
union和union all的区别在于，前者在查找后，会对重复数据去重；后者则不会。
避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。
低效：（索引失效）
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL;
高效：（索引有效）
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE >=0;
如果索引是建立在多个列上， 只有在它的第一个列(leading column)被where子句引用时， 优化器才会选择使用该索引。
复制代码
SQL> create index multindex on multiindexusage(inda,indb);
Index created.
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF ‘MULTIINDEXUSAGE‘
复制代码
很明显, 当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引。
当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。如果用UNION ALL替代UNION，这样排序就不是必要了，效率就会因此得到提高。
由于UNION ALL的结果没有经过排序，而且不过滤重复的记录，因此是否进行替换需要根据业务需求而定。
由于UNION会对查询结果进行排序，而且过滤重复记录，因此其执行效率没有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%‘
当比较不同数据类型的数据时， ORACLE自动对列进行简单的类型转换。
复制代码
/假设EMP_TYPE是一个字符类型的索引列./
SELECT …
FROM EMP
WHERE EMP_TYPE = 123
/这个语句被ORACLE转换为:/
SELECT …
FROM EMP
WHERE TO_NUMBER(EMP_TYPE)=123
复制代码
因为内部发生的类型转换，这个索引将不会被用到。
几点注意：
当比较不同数据类型的数据时，ORACLE自动对列进行简单的类型转换。
如果在索引列上面进行了隐式类型转换，在查询的时候将不会用到索引。
注意当字符和数值比较时，ORACLE会优先转换数值类型到字符类型。
为了避免ORACLE对SQL进行隐式的类型转换，最好把类型转换用显式表现出来。
FULL hint 告诉ORACLE使用全表扫描的方式访问指定表。
ROWID hint 告诉ORACLE使用TABLE ACCESS BY ROWID的操作访问表。
CACHE hint 来告诉优化器把查询结果数据保留在SGA中。
INDEX Hint 告诉ORACLE使用基于索引的扫描方式。
其他的Oracle Hints
ALL_ROWS
FIRST_ROWS
RULE
USE_NL
USE_MERGE
USE_HASH 等等。
这是一个很有技巧性的工作。建议只针对特定的，少数的SQL进行hint的优化。
（1）下面的例子中，‘!=’ 将不使用索引 ，索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中。
不使用索引：
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT !=0；
使用索引：
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT > 0；
（2）下面的例子中，‘||’是字符连接函数。就象其他函数那样，停用了索引。
不使用索引：
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’；
（3）下面的例子中，‘+’是数学函数。就象其他数学函数那样，停用了索引。
不使用索引：
SELECT ACCOUNT_NAME，AMOUNT
FROM TRANSACTION
WHERE AMOUNT + 3000 >5000；
使用索引：
SELECT ACCOUNT_NAME，AMOUNT
FROM TRANSACTION
WHERE AMOUNT > 2000 ；
（4）下面的例子中，相同的索引列不能互相比较，这将会启用全表扫描。
不使用索引：
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, ’%’)
如果对一个列和一组有限的值进行比较，优化器可能执行多次扫描并对结果进行合并连接。
举例：
SELECT *
FROM LODGING
WHERE MANAGER IN (‘BILL GATES’, ’KEN MULLER’)
优化器可能将它转换成以下形式：
SELECT *
FROM LODGING
WHERE MANAGER = ‘BILL GATES’
OR MANAGER = ’KEN MULLER’基于成本的优化器（CBO，Cost-Based Optimizer）对索引的选择性进行判断来决定索引的使用是否能提高效率。
如果检索数据量超过30%的表中记录数，使用索引将没有显著的效率提高。
在特定情况下，使用索引也许会比全表扫描慢。而通常情况下，使用索引比全表扫描要块几倍乃至几千倍！
带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序（SORT）功能。DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。
通常，带有UNION，MINUS，INTERSECT的SQL语句都可以用其他方式重写。
提高GROUP BY语句的效率，可以通过将不需要的记录在GROUP BY之前过滤掉。
低效：
SELECT JOB ，AVG（SAL）
FROM EMP
GROUP BY JOB
HAVING JOB = ‘PRESIDENT’
OR JOB = ‘MANAGER’
高效：
SELECT JOB，AVG（SAL）
FROM EMP
WHERE JOB = ‘PRESIDENT’
OR JOB = ‘MANAGER’
GROUP BY JOB
当使用日期时，需要注意如果有超过5位小数加到日期上，这个日期会进到下一天!
复制代码
SELECT TO_DATE（‘01-JAN-93’+.99999）
FROM DUAL
Returns：
’01-JAN-93 23:59:59’
SELECT TO_DATE（‘01-JAN-93’+.999999）
FROM DUAL
Returns：
’02-JAN-93 00:00:00’
复制代码
使用隐式的游标，将会执行两次操作。第一次检索记录，第二次检查TOO MANY ROWS 这个exception。而显式游标不执行第二次操作。
总是将你的表和索引建立在不同的表空间内（TABLESPACES）。
决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里。
确保数据表空间和索引表空间置于不同的硬盘上。
原文：https://blog.51cto.com/jinfreaks/2545604