关于分区表上面没有建立分区索引导致排序效率低下

sqysl

rgshh1990 发表于 2017-9-13 11:24
分区的还是按照那种式样来的从整个主键索引进行扫描然后找出来数据然后进行过滤，效率并不高，我觉得 ...

你把不带rownum<50条件的执行计划贴下，你确定结果集只有2000多条？另外，两张表分别有多少数据量？

哦，又仔细看了下，大概明白了。
你不带rownum<50条件，应该是对特定子分区走了FTS，这样结果集2000多很有可能；你加上rownum<50，对主键索引走了INDEX FULL SCAN，主键索引是global的。
如果我上面说的是对的，你可以试试，通过hint让计划走FTS试试，类似这样：
select * from (
   select /*+ full(a) */ *
             from bdwsps.jk_history_ss1 a
             where a.main_prod_id = '10501110'
               and a.main_sub_prod_id = '0'
               and a.sts = 'C'
               AND A.USER_TYPE =99
             order by a.jk_history_id) where rownum<50;

如果我上面的推测是对的，加hint后也变快,并且子分区的数据量远大于2000行，那么，你可以考虑把主键索引改为local index（main_prod_id,main_sub_prod_id，jk_history_id），或者另建个local index(sts,user_type)。具体你自己试着看吧，如果觉得加hint和性能可以接受，不改造主键或不新建local index也可以。

newkid

rgshh1990 发表于 2017-9-13 11:24
分区的还是按照那种式样来的从整个主键索引进行扫描然后找出来数据然后进行过滤，效率并不高，我觉得 ...

这个WITH被合并了，并没有先执行。在 table1 里面加上ROWNUM看看。

newkid

rgshh1990 发表于 2017-9-13 14:42
就如同我说的一样，利用hint会有一个再之前回复版主当中提到的一个问题，处理和未处理的此消彼长，死板的 ...

你说的这种“此消彼长”，会不会导致在两种情况下要用两种执行计划？
如果一种计划搞不定你就得写成两个SQL。执行之前先写个简化的COUNT判断要用哪个SQL。

rgshh1990

sqysl 发表于 2017-9-13 19:40
你把不带rownum

嗯  分区表的问题解决了，确实应该使用全表扫描才会提高效率，而且非常智能的只选了那一个分区，如果不想用hint必须要弄索引分区不然CBO会走索引全扫描先排序然后再过滤，这种模式效率过低。
之前您回复的问题确实是电信系统内部而且表没有做分区，月末大数据要处理的时候出现的问题，我的解决思路是首先把历史不用处理的让程序弄出在途表，然后使用hint来实现在大数据量未处理的，看了版主的建议我觉得是要分count一下判断在变化之中用哪个hint效率高