Oracle SQL的一些优化

 

定期重构索引：

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

 

在WHERE子句中，如果索引所对应的值的第一个字符由通配符开始索引将不被采用

 

避免在索引列上使用计算

 

避免在索引列上使用NOT

 

尽量用>=和<=代替>和<。

 

对索引列使用OR会导致全表扫描，应该使用UNION替换OR

 

避免在索引列上使用IS NULL和IS NOT NULL，这需要在DB设计的时候避免默认的空值

 

如果索引是建立在多个列上时，只有第一个列被WHERE子句引用该索引才会被选择

 

避免显式或者隐式改变索引列的类型，在比较中，尽量显式转换被比较值的类型到索引的类型

 

在不是特别必要时，尽量用UNION ALL代替UNION

 

Oracle中对于From和WHERE都是采用自下而上的访问方式

 

基础表(Driving Table)是被最先访问的表，一般也就是FROM最后面的表

 

在使用GROUP BY之前尽量通过WHERE语句先将数据筛选

 

一般情况下使用WHERE代替HAVING，除非筛选条件中必须使用聚集函数

 

尽量用WHERE替代ORDER BY：

ORDER BY子句只是在两种严格的条件下使用索引；

ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序；

ORDER BY中所有的列必须定义为非空；

WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列

 

 

Oracle对null的排序：

Oracle在order by 时缺省认为null是最大值，所以如果是ASC升序则排在最后，DESC降序则排在最前

 

使用nulls first 或者nulls last 语法：
--将nulls始终放在最前select * from tb_xxx order by col_xxx nulls first
--将nulls始终放在最后select * from tb_xxx order by col_xxx desc nulls last

 

如何快速生成大量测试数据：

create table largeTestTable as
select rownum as id,
               to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,
               trunc(dbms_random.value(0, 100)) as random_id,
               dbms_random.string('x', 20) random_string
          from dual
        connect by level <= 1000000(期望生成测试数据的件数);

1、利用Oracle特有的“connect by”树形连接语法生成测试记录，“level <= 10”表示要生成10记录；
2、利用rownum虚拟列生成递增的整数数据；
3、利用sysdate函数加一些简单运算来生成日期数据，本例中是每条记录的时间加1秒；
4、利用dbms_random.value函数生成随机的数值型数据，本例中是生成0到100之间的随机整数；
5、利用dbms_random.string函数生成随机的字符型数据，本例中是生成长度为20的随机字符串，字符串中可以包括字符或数字。