分享几个优化案例

以下案例是收录自 黄远邦 老师视频中：

案例一SQL:

select min(id),max(id) from table;

单独给id列加索引并不能让该SQL走上索引。
因为id列加上索引以后，走index full scan (min/max)只能针对单列优化。例如（select min(id) from table 或者 select max(id) from table ） 可以走index full scan(min/max)。
而select min(id),max(id) from table ,从索引index full scan 角度来讲，并不能实现。所以该SQL只能走全表扫描。

优化方式：
ID列加索引并且改写该SQL语句：

select
(select min(id) from table) min_id,
(select max(id) from table) max_id
from dual;
改写以后，相当于执行了两个独立的SQL，并且两个SQL都可以走index full scan 的执行计划。（这里的index full scan 计划，并不全扫索引的叶子节点，而是只扫描最左边的叶子节点和最右边的叶子节点。即只扫最小最大值。）

案例二SQL:

select id,name from table where id is null;

因为单列索引不存储空值！！所以不能在id列上直接建索引。
复合索引的两列均不为空值的话，可以存储空值！

思路：利用复合索引。
如果name 列字段有 NOT NULL 约束的话，则可以创建 id,name 的复合索引。可以走出index range scan 。 （不建议）

最好的方法：建立id和常量的复合索引。

create index idx_id_1 on table(id,1) online;
该索引创建以后，保证了复合索引，常量1永远不为空，也无需去定义not null约束。

案例三SQL:对于分页SQL的优化：

该SQL是页面展示前10条最新的新闻:

select * from
(select rownum rn,a.* from
(select new_id,new_tital,pub_time from news order by pub_date desc ) a)
where rn between 1 and 10;

以上垃圾SQL无法优化，会走全表扫，即使给 pub_date字段加上索引也不行！ 因为它先对全表进行了排序，再取的前10行，只能改写SQL，让其走index full scan desc。

改写后SQL:
在pub_date字段上加上索引的基础上改写：
写法一:

select * from
(select rownum rn, a.*
from (select new_id, new_title, pub_date from news where pub_date is not null order by pub_date desc) a where rownum <= 10) b
where rn >= 1
–如果不加 is not null 的话，依然走全表扫，不会走索引的，因为null值最大。

写法二：

select * from
(select a.*, row number() over(order by pub_date desc) rn from news a
where publish date is not null)
where rn between 1 and 10;

写法三：(oracle 12c 以后版本支持的写法)

select a.* from news a
order by pub_date desc
offset 0 rows fetch next 10 rows only;

以上三种写法都有一个弊端：
越往后翻页越慢！！因为越往后，索引扫描的叶子块越多。

最优写法：使用rowid自关联：

select /*+ use nl(result1 result2) leading(result1 result2) rowid(result2) /
a.rn, result2. from
(select * from
(select rownum rn, rowid row_id from
(select rowid from news order by pub_date desc) a
where rownum <= 10000) b where rn >= 9990) result1,news result2 where result1.row_id = result2.rowid

案例四SQL:

select area_name from ip_lib
where id_addr_start>=5
and ip_addr_end<=5

如果添加复合索引，当传入值小的时候，效率会高，当传入值大的时候，index range scan扫描的叶子块越多，性能越差！！

结合业务特点，一个IP只有一个归属地等特点。
改写一：SQL:

select * from
(select area_name from ip_lib
where id_addr_start>=5
and ip_addr_end<=5
order by ip_addr_end)
where rownum<2
缺点：该SQL如果传入值不存在的话，会索引范围扫描所有叶子块，性能差！

改写二：SQL:

select *
from (select *
from (select /*+ index_rs(ip_lib IDX_END_START) */ ip_addr_start,ip_addr_end,area_name from ip_lib where ip_addr_end >=8 Order by ip addr_end)
where rownum <2)
where ip_addr_start <= 8;
该改写是结合业务实际情况，做出的等价改写。 有时我们需要结合业务特点进行看似不等价实则等价的改写！