分区表,本地索引,分页查询,SQL效率

dla001

OS：OEL5.7 X64
DB:  11.2.0.3 X64
PSU为: 11.2.0.3.5 (14727310)

分区表按时间RANGE分区，子分区为hash分区。
代码如下：

1. create table zwh_prh1 (c1 number not null,c2 date not null,c3 varchar2(20) not
   
2.         null,c4 varchar2(120) not null)
   
3. partition by range(c2)
   
4. subpartition by  hash(c1)
   
5. subpartitions 5
   
6. (
   
7.     partition p1 values less than (to_date('2012-01-01','yyyy-mm-dd'))
   
8.     ,
   
9.     partition p2 values less than (to_date('2012-02-01','yyyy-mm-dd'))
   
10.     ,
    
11.     partition p3 values less than (to_date('2012-03-01','yyyy-mm-dd'))
    
12.     ,
    
13.     partition p4 values less than (to_date('2012-04-01','yyyy-mm-dd'))   
    
14. );
    
15. 
    
16. create sequence zwhse1 cache 2000;
    
17. 
    
18. insert into zwh_prh1 select se1.nextval,to_date('2012-04-01','yyyy-mm-dd')- se1.nextval/1440/2,lpad('A',18,'A'),lpad('B',100,'B') from dual connect by level <= 300000;
    
19. commit;
    
20. create index idx_p_03 on zwh_prh1(c2) local;
    
21. 
    
22. exec dbms_stats.gather_table_stats(user,'ZWH_PRH1',granularity=>'ALL',cascade=>true);
    
23. 
    
24. alter session set events '10046 trace name context forever, level 12';
    
25. 
    
26. select *
    
27. from
    
28. (select /*+index_desc(t IDX_P_03)*/ * from ZWH_PRH1 t where
    
29.   c2<to_date('2012-04-01','yyyy-mm-dd') order by c2 desc) where rownum<10;
    
30. select *
    
31. from
    
32. (select * from ZWH_PRH1 t where
    
33.   c2<to_date('2012-04-01','yyyy-mm-dd') order by c2 desc) where rownum<10;  
    
34. alter session set events '10046 trace name context off';

复制代码

通过10046可以看到：

使用hints的查询，走的index_desc。Rows (max)  45
不使用hints的查询，走的是全表扫描。 Rows (max) 300000
使用hints的查询效率明显比全表扫描的好。
问题是：
对于这种情况的分区表。优化器选择了不是很好的执行计划。这是优化器的问题，还是有什么地方我做错了？
除了人为干预，如果能让优化器选择正确的执行计划？

由于一些原因:不能用普通B-tree索引，不能用全局B-tree索引。

anbob

用分析函数 替换 top subquery问题 试试

26856649

create index idx_p_04 on zwh_prh1(c2 desc) local;

dla001

26856649 发表于 2013-3-28 16:34
create index idx_p_04 on zwh_prh1(c2 desc) local;

抱歉没说清楚，要能asc，desc两种排序都行。
这个索引能选择desc的情况 ，asc的情况不行。

foxhuntwang

SQL 中要先排序（order by c2） ， 由于你使用的是 局部索引（局部索引只是在一个分区中是排序的）， 那么就不能使用 index idx_p_03 索引了，需要走全表扫描 重新排序 。 如果你用 全局索引  那么  就不会出现你说的情况。

dla001

foxhuntwang 发表于 2013-3-28 16:47
SQL 中要先排序（order by c2） ， 由于你使用的是 局部索引（索引只是在分区中是排序的）， 那么就不能使 ...

使用本地索引明显是快很多。对于它为什么要走全表扫描实在想不明白。
从时间，可定位到区，这个是not null的字段，又是按时间分区的，那么数据从符合时间条件的最大的区并且从最大的值向后开始扫描，这种效率比全表的一定是好很多的。

foxhuntwang

dla001 发表于 2013-3-28 16:56
使用本地索引明显是快很多。对于它为什么要走全表扫描实在想不明白。
从时间，可定位到区，这个是not nul ...

哈哈，如果ORACLE 能做到足够 聪明，就不用 提供 HINT 这个 东西 给我们了。

dla001

foxhuntwang 发表于 2013-3-28 17:02
哈哈，如果ORACLE 能做到足够 聪明，就不用 提供 HINT 这个 东西 给我们了。

就郁闷在hint上了。开发的搞了个生成SQL的东西，一个表的SQL都用一个主体，死活不肯根据条件来写正确的SQL。看来还是要和他们沟通沟通了。多谢了。

26856649

dla001 发表于 2013-3-28 16:43
抱歉没说清楚，要能asc，desc两种排序都行。
这个索引能选择desc的情况 ，asc的情况不行。 ...

如果不想加hint
那就建俩，一个asc 一个desc
哈哈

dla001

26856649 发表于 2013-3-28 17:16
如果不想加hint
那就建俩，一个asc 一个desc
哈哈

哈哈，算了，不折腾自己了，让他们去改。不改就慢慢等。

26856649

或者试试这个

1. select *
   
2.   from zwh_prh1 t2,
   
3.        (select rid
   
4.           from (select rowid as rid
   
5.                   from zwh_prh1 t
   
6.                  where c2 < to_date('2012-04-01', 'yyyy-mm-dd')) t3
   
7.          where rownum < 10) t4
   
8. where t2.rowid = t4.rid
   

复制代码

dla001

26856649 发表于 2013-3-28 17:33
或者试试这个

这个SQL没加排序与order by c2 asc 相比，它们出来的数据是不一样的。 数据也不对。如果加了order by asc，那么执行计划还是不对的。还是要加hint。优化器对于这种复合分区表的索引选择不是那么完美。

dla001

26856649 发表于 2013-3-28 17:33
或者试试这个

发现个好玩的事情。
将上面的表，改为list类型的分区（只使用list分区）。分区字段为c3，一共8个区。
如果不加hints，这个SQL走的是：

1. NESTED LOOPS  
   
2.    VIEW  
   
3.     COUNT STOPKEY
   
4.      VIEW  
   
5.       SORT ORDER BY STOPKEY
   
6.        PARTITION LIST ALL PARTITION: 1 8
   
7.         INDEX FAST FULL SCAN IDX_NNN_0 PARTITION: 1 8
   
8.    TABLE ACCESS BY USER ROWID TMP3 PARTITION:

复制代码

如果你指定的hints，它的计划是：

1. NESTED LOOPS   
   
2.    VIEW   
   
3.     COUNT STOPKEY  
   
4.      VIEW   
   
5.       SORT ORDER BY STOPKEY  
   
6.        PARTITION LIST ALL PARTITION: 1 8  
   
7.         COUNT STOPKEY  
   
8.          INDEX RANGE SCAN IDX_NNN_0 PARTITION: 1 8  
   
9.    TABLE ACCESS BY USER ROWID TMP3 PARTITION:

复制代码

加了hints是相当于每个区取top 10，然后再sort ，最后再取top 10。
不加hints相当于，扫描所有的分区的索引，然后sort，再取top 10。这两个差距好明显。
加了hints时，优化器做出的选择真是太聪明了。。。。惊讶。