oracle索引没有去掉并行,Oracle 建立索引及SQL优化

数据库索引：

索引有单列索引，复合索引之说。

如某表的某个字段有主键约束和唯一性约束，则oracle会自动在相应的约束列上建议问一索引。数据库索引主要进行提高访问速度。

(如楼层索引，进来先看索引能快速定位到要找的楼层房间)

建设原则:

1、索引应该常建在where子句经常用到的列上。如果某个大表经常使用某个字段进行查询，并且检索行数小于总表行数的5%，则应该考虑。

2、对于两表链接的字段，应该建立索引。如果经常在某表的一个字段进行order by  则也经过进行索引。

3、不应该在小表上建设索引。

优缺点：

1、索引主要进行提高数据的查询速度。当进行DML时，会更新索引。因此索引越多，则DML越慢，其需要维护索引。因此在创建索引及DML时需要权衡。

创建索引：

单一索引：create Index on (column_name)。

复合索引：create Index cbs_Index on thr_cbsglb(ssxm,ywzgbm)。

select * from thr_cbsglb where ssxm= 66 and ywzgbm= ywzgbm-->走索引

select * from thr_cbsglb where CBS = 66 or ywzgbm= 45 -->不走索引

select * from thr_cbsglb where ssxm= 66 -->走索引

select * from thr_cbsglb where ywzgbm= 45-->不走索引

如果where子句中有or操作符或单独引用ywzgbm列(索引列的后面列)则将不会走索引，将会进行全盘扫描。

Sql优化：

当oracle数据库拿到sql语句时，其会根据查询优化器分析改语句，并根据分析结果生成查询执行计划。也就是说数据库时执行的查询计划，而不时sql语句。

查询优化器有rule-based-optimizer(基于规则的查询优化器)和Cost-Based-optimizer(基于成本的查询优化器)。

其中基于规则的查询优化器在10g版本中消失。对于规则查询，其最后查询的是全表扫描。而CBO则会根据统计信息进行最后的选择。

1、先执行from -> where -> group by -> order by

2、执行form字句是从右往左执行。因此必须选择记录条数最少的表放在右边。

3、对于where子句其执行顺序是从后往前执行、因此可以过滤大数量记录的条件必须写在where子句的末尾。而对于多表之间的连接，则写在之前，因为这样连接时，

可以去掉大多不重复的项。

4、select子句中避免使用(*)，oracle在解析的过程中，会将(*)依次转换成所有的列名，这个工作时通过数据字典完成的，这意味着将耗费更多的时间。

5、索引失效情况：

① not null/null 如果某列建立索引，当进行select * from thr_cbsglb where ssxm is not null/ is null 则会索引失效。

② 索引列上不要使用函数，select ssgs from thr_cbsglb where substr(cbsmc,1,3) = '12队'  或则 select ssgs from thr_cbsglb where cbsmc like '%12队%'，前者失效后者会使用。

③ 索引上不能进行计算， select ssgs from thr_cbsglb where ssxm / 10 > 10 则会使索引失效，应改成select  ssgs from thr_cbsglb where ssxm > 10 * 10。

④ 索引列上不要使用not(!=、<>)如：select ssgs from thr_cbsglb where ssxm != 10，应改成select ssgs from thr_cbsglb where ssxm > 10 or ssxm < 10。

6、用union替换or(适用于索引列)

union：是将使将两个查询的结果集并行到一起，它不会引起列的变化。由于是追加操作，需要两个结果集的列数应该是相关的，并且相应列的数据类型也因该相同的。union返回

两个结果集，同时将两个结果集的重复项进行取消，如果不取消则用union all。

通常情况下，用union替换where子句中的or将会起到较好的效果，队索引列使用or将造成全表扫描。注意：以上规则只针对多个索引列有效。

如果有column没有被索引，查询效率可能会因为没有选择or而降低。(thr_cbsglb_id、cbsmc都建有索引)

高效：

select thr_cbsglb_id,ssxm,cbsmc from thr_cbsglb where thr_cbsglb_id = 10

union

select thr_cbsglb_id,ssxm,cbsmc from thr_cbsglb where cbsmc = '12队'

低效：

select thr_cbsglb_id,ssxm,cbsmc from thr_cbsglb where thr_cbsglb_id = 10 or cbsmc = '12队'

如果坚持要用or,那就需要将返回记录最少的索引列写在最前面。

7、用exists代替 in、用not exists代替 not in

在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表惊醒连接，在这种情况下使用exists或者not exists通常将提高查询的效率。

在子查询中，not in 子句将执行一个内部的排序和合并，无论在哪种情况下，not in都是最低效的(因为它对子查询中的表执行了一个全表遍历)。

为了避免使用not in,可以把它改写为外连接(outer joins)或not exists。

高效：

select * from THR_AQJCBZK where exists(select 'x'  from thr_yjfl where thr_yjfl.yjfl_id = thr_aqjcbzk.yjfl_id and zjid like '12%')

低效：

select * from THR_AQJCBZK where yjfl_id in(select yjfl_id  from thr_yjfl where zjid like '12%')