一些sql语句的优化心得

chao_ping

本文观点错误，删除，呵呵。

ygy

关于exists与in的优化，不能一概而论，有三种情况：

1、查询表与子查询表的数据量不多，两者的性能差异不大；

2、查询表数据量少，子查询数据量大，利用索引情况下，则采用exists一定较in性能好；

3、查询表数据量大，子查询数据量小（10万以内），采用in效果会更佳；

4、两者数据量都较大，采用exists会更稳健一些（保证你的性能不会随着数据量增加而发生突变）。

   以上均建立在能充分使用索引的情况。数据量大的含义应为10万、100万级之上。

ncgxq

1、not in和用minus的查询速度相差很多，我原来的一个同事就用过not in来做查询，关联的两个表的记录数均在17万条以上，用not in做了20分钟还出不来，后我改用minus以后3分钟不到就出了结果。

2、oracle是把from子句中最后一个表做为驱动表的，如果相关联的表的记录数相差很大的话，把小表放在from列表的最后一个。关于这方面的东西还请参考oracle文档光盘中的内容，或参考一下机工的《oracle参考大全》一书，不过这本书中是以oracle 8为例的。此外请注意一下merge join、hash join等join之间的区别及适用的情况。此外，优化器方式对sql语句的工作路径也有影响，如果要强制使用哪种优化器的话，用hint指定。

3、慎用嵌套查询，这通常会使性能下降，如果可能的话，把此查询分拆开来，做成plsql块更好，我做过这方面的试验，一个两层嵌套的查询耗时48分钟（还有一次把50M的temp表空间撑满了），在未对表做修改的情况下，改用plsql执行，2分18秒搞定。

4、ligengocp所说的组合索引也不是不能用，如果是(a,b,c)组成的索引，那么用a或ab或abc均可用到此索引，这是因为oracle的索引是前缀式索引，这与sybase是不同的。

5、查询的字段加函数也不是不能用到索引，但此类查询要用索引的前提是已创建了function-base  index。

6、关于临时表是在oracle 8i在引入的，其大致语法为：
create global temporary table tablename(...) storage-clause
on commit delete rows|reserve rows。
有on commit delete rows子句的临时表为事务型的临时表，在每次提交后，临时表中的行会自动删除（此用户的），带on commit reserve rows的临时表为会话型临时表，在会话期间数据不会删除，当用户logout后，数据会自动清除。但请注意，临时表中数据为私有的，只有当前会话可看到本会话插入的数据，即使是同一用户的不同会话也不能查看。

7、临时表在使用之中的问题：临时表在使用中有诸多限制，特别是在constraint上，在oracle的help中说可在临时表上建primary key，但在实际工作中却发现不能。此外，尽量把临时表放在单独的表空间中，且要保持有足够的空间，如果没有足够的空间时，permanent table是会报cant't extentd的，但临时表报的可就ORA-00600错误了，这也是我的同事误打误撞试出来的。

ligengocp

最初由 ncgxq 发布
[B]1、not in和用minus的查询速度相差很多，我原来的一个同事就用过not in来做查询，关联的两个表的记录数均在17万条以上，用not in做了20分钟还出不来，后我改用minus以后3分钟不到就出了结果。

2、oracle是把from子句中最后一个表做为驱动表的，如果相关联的表的记录数相差很大的话，把小表放在from列表的最后一个。关于这方面的东西还请参考oracle文档光盘中的内容，或参考一下机工的《oracle参考大全》一书，不过这本书中是以oracle 8为例的。此外请注意一下merge join、hash join等join之间的区别及适用的情况。此外，优化器方式对sql语句的工作路径也有影响，如果要强制使用哪种优化器的话，用hint指定。

3、慎用嵌套查询，这通常会使性能下降，如果可能的话，把此查询分拆开来，做成plsql块更好，我做过这方面的试验，一个两层嵌套的查询耗时48分钟（还有一次把50M的temp表空间撑满了），在未对表做修改的情况下，改用plsql执行，2分18秒搞定。

4、ligengocp所说的组合索引也不是不能用，如果是(a,b,c)组成的索引，那么用a或ab或abc均可用到此索引，这是因为oracle的索引是前缀式索引，这与sybase是不同的。

5、查询的字段加函数也不是不能用到索引，但此类查询要用索引的前提是已创建了function-base  index。

6、关于临时表是在oracle 8i在引入的，其大致语法为：
create global temporary table tablename(...) storage-clause
on commit delete rows|reserve rows。
有on commit delete rows子句的临时表为事务型的临时表，在每次提交后，临时表中的行会自动删除（此用户的），带on commit reserve rows的临时表为会话型临时表，在会话期间数据不会删除，当用户logout后，数据会自动清除。但请注意，临时表中数据为私有的，只有当前会话可看到本会话插入的数据，即使是同一用户的不同会话也不能查看。

7、临时表在使用之中的问题：临时表在使用中有诸多限制，特别是在constraint上，在oracle的help中说可在临时表上建primary key，但在实际工作中却发现不能。此外，尽量把临时表放在单独的表空间中，且要保持有足够的空间，如果没有足够的空间时，permanent table是会报cant't extentd的，但临时表报的可就ORA-00600错误了，这也是我的同事误打误撞试出来的。 [/B]

1）你说的嵌套查询指的什么，能否举一两例？？
2）-字段加函数可以考虑使用Function Based Index来代替普通索引
这个我听说过，但不知道哪里有中文文档，我想速成。还有，如果这个函数是我自定义的，且比较复杂，包括了从多个表中的计算。Function Based Index是否还有效。
3）组合索引是可以使用，但查询的字段如你所写，必须有一定的顺序。试想想如果是(a,b,c)组成的索引，那么用ba或bc,ac是否会用到此索引

谢谢各位的回答！！

toadnet

ncgxg兄提出：ligengocp所说的组合索引也不是不能用，如果是(a,b,c)组成的索引，那么用a或ab或abc均可用到此索引，这是因为oracle的索引是前缀式索引，这与sybase是不同的。

   我有个疑问：好象在SYBASE中组合索引的用法也是这样的，如果不是，请指教

白鹭

我想，大家的讨论忽略了一个前提，就是你是采用RBO还是采用CBO得出的结论。
在RBO和CBO下,执行路径通常都是不同的，CBO下一般情况下能够选择最小开销。
1.关于TABLE JOIN
  RBO:
  -->ligengocp:3，两个表进行JION时，大表放在前面，JION字段建索引
  -->chao_ping:两表进行Join，应该是小的表放在前面效率更加好一点？
  -->我的看法:很明确，小表放后头，RBO下，驱动表在后面，也即总是对后面的表全扫描。
  CBO:
    如果优化器选择NESTED LOOP的话,无所谓前后，Oracle会自动选择小表全扫描。
2.关于COUNT(*)(前提是没有加where语句，where语句又导致不同的执行路径)
   RBO:
   --> ligengocp:7，如要统计大表的记录条件，用COUNT（*）优于COUNT（某列）
   -->chao_ping:关于count，应该是没有差别的。你对你的执行结果的explain plan看看，应该是一样的。
     时间不一样，可能是第一次有物理读，第二次已经在内存里面读取造成的影响。
   -->我的看法:
      chao_ping对的,count(*),count(column)都是导致全表扫描。
   CBO:
     -->chao_ping:如果有索引的话，都会用Fast Full Index Scan的
     如果表上有INDEX,Oracle会选择开销最小的index进行INDEX (FULL SCAN),
     单不会进行Fast Full Index Scan,就算count(column)也不会。
3.关于bitmap index
-->ligengocp:9，对于字段取值单一（如性别字段只有男与女），而经常在性别上做查询，则建立位图索引。
BITMAP INDEX通常用于DSS,如果你的系统是OLTP,DML操作将LOCK整个BITMAP SEGMENT,因此只在DSS下
考虑BITMAP INDEX。

gangang

关于JOIN中表的顺序问题，如果使用CBO，那么就和的顺序无关了。

bodyguard

a,b表同结构，数据量很大，则
1:select a.* from a, b where a.c = b.c + and b.c is null
要比
2:select * from a where a.c not in (select c from b )
快很多
1和2的结果集不一定等价.
如果b.c列全部not null,1会返回空.

sunbird

大家好 我给大家拜个早年 受到了吗

violet

select a.* from a, b where a.c = b.c + and b.c is null

我的测试结果怎么是：依次为10s,160s,0s
而且第三个没结果，不知为何？