Oracle语法效率优化应注意的若干细节.docxVIP

Oracle语法效率优化应注意的若干细节 一、 尽量避免对列的操作 任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据 库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边， 甚至去掉函数。 例：下列SQL条件语句中的列都建有恰当的索引，但30万行数据情 况下执行速度却非常慢： select * from record where substrb(CardNo, 1, 4)=，5378， select * from record where amount/30 1000 select * from record where to_char(ActionTime, yyyymmdd，)二1999120T 于where子句中对列的任何操作结果都是在SQL运行时逐行计算 得到的，因此它不得不进行表扫描，而没有使用该列上面的索引；如 果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化， 使用索引，避免表扫描，因此可将SQL修改如下： select * from record where CardNo like 5378% select * from record where amount 1000*30 select * from record where ActionTime= to date ‘yyyymmdd) 二、 避免不必要的类型转换 尽量避免潜在的数据类烈转换。如将字符烈数据与数值型数据比 较，ORACLE会自动将字符型用to_number ()函数进行转换，从而导致 全表扫描。 例：表tabl中的列coll是字符型(char),则以下语旬存在类型转 换： select col 1,col2 from tabl where coll10 应该写为： select coll, col2 from tabl where coll〉10‘ 三、增加查询的范围限制 增加查询的范围限制，避免全范围的搜索。 例：以卜查询表record中时间ActionTime小于2015年5月1日的数据： select * from record where ActionTime to date ‘yyyymm) 查询计划表明，上面的查询对表进行全表扫描，如果我们知道表 屮的最早的数据为2011年1月1日，那么，可以增加一个最小时间，使 查询在一个完整的范围之内。 修改女口下： select * from record where ActionTime to date ‘yyyymm) and ActionTime to_date ‘yyyymm) 此SQL语句将利用ActionTime字段上的索引，从而提高查询效率。 同理，对于大于某个值的查询，如果知道当前可能的最大值，也可以 在Where子句中加上“AND列名〈MAX(最大值)”。 四、尽量去掉〃IN〃、〃0R〃 含有〃IN〃、〃0R〃的Where子句常会使用工作表，使索引失效；如果 不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索 引。 例4： select count (*) from stuff where i d_no (23 秒) 可以考虑将or子句分开： select count (*) from stuff where id_no二O select count (*) from stuff where id_no二T 然后再做一个简单的加法，与原来的SQL语句相比，查询速度更快。 五、尽量去掉 尽量去掉〃?〃，避免全表扫描，如果数据是枚举值，且取值范围 固定，则修改为〃0R〃方式。 例： UPDATE SERVTCETNFO SET STATE^O WHERE STATEOO; 以上语句由于其中包含了执行计划中用了全表扫描(TABLE ACCESS FULL),没有用到state字段上的索引。实际应用中，市于业 务逻辑的限制，字段state为枚举值，只能等于0, 1或2,而且，值等 于二1, 2的很少，因此可以去掉〃?勺利用索引来提高效率。 修改为：UPDATE SERVTCETNFO SET STATEN WHERE STATE 二 1 OR STATE二2 o进一步的修改可以参考第4种方法。 六、 去掉Where了句中的IS NULL和IS NOT NULL Where字句中的IS NULL和IS NOT NULL将不会使用索引而是进行全 表搜索，因此需要通过改变查询方式，分情况讨论等方法，去掉Where 子句中的IS NULL和IS NOT NULL。 七、 索引提高数据分布不均匀时查询效率 索引的选择性低，但数据的值分布差异很大时，仍然可以利用索 引提