构造数据

drop table tab1;
drop table tab2;
create  table tab1(v1 int,v2 int,v3 varchar(30000));
create  table tab2(v1 int,v2 int,v3 varchar(30000));
create index idx_tab1_v1 on tab1(v2);
create index idx_tab2_v1 on tab2(v1);

亲密接触的语句和它的计划

SQL语句：

select
        *
from
        (
                select
                        row_.*,
                        rownum rownum_
                from
                        (
                                select
                                        /*+TOP_ORDER_OPT_FLAG(0) stat(a 100M),stat(b 100M)*/  
                                        a.*,b.v3 v33
                                from
                                        tab1  a,
                                        tab2 b
                                where
                                        a.v1 = b.v1
                                    and a.v2 =?
                                order by
                                        a.v1 desc
                        )
                        row_
        )
where
        rownum_  < 1000
    and rownum_ >= 1

执行计划：

1   #NSET2: [682, 50, 108] 
2     #PRJT2: [682, 50, 108]; exp_num(5), is_atom(FALSE) 
3       #SLCT2: [682, 50, 108]; DMTEMPVIEW_16781200.ROWNUM_ >= var3
4         #PRJT2: [682, 1000, 108]; exp_num(5), is_atom(FALSE) 
5           #RNSK: [682, 1000, 108]; rownum < exp_cast(1000)
6             #PRJT2: [682, 1000, 108]; exp_num(4), is_atom(FALSE) 
7               #SORT3: [682, 1000, 108]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
8                 #NEST LOOP INDEX JOIN2: [490, 2500000, 108] 
9                   #BLKUP2: [2524, 2500000, 56]; IDX_TAB1_V1(A)
10                    #SSEK2: [2524, 2500000, 56]; scan_type(ASC), IDX_TAB1_V1(TAB1 as A), scan_range[exp_param(no:0),exp_param(no:0)]
11                  #BLKUP2: [16304, 1, 0]; IDX_TAB2_V1(B)
12                    #SSEK2: [16304, 1, 0]; scan_type(ASC), IDX_TAB2_V1(TAB2 as B), scan_range[A.V1,A.V1]

问题场景1的说明

create index idx_tab1_v1_modify on tab1(v2,v1); 

问题1：在上面的数据基础上，新增这个索引，发现执行计划如下，还是有SORT操作符，不能消除排序。
说明：优化器还不够智能，在通过V2定位后，不能从“尾巴”往前扫。

执行计划：

1   #NSET2: [682, 50, 108] 
2     #PRJT2: [682, 50, 108]; exp_num(5), is_atom(FALSE) 
3       #SLCT2: [682, 50, 108]; DMTEMPVIEW_16781243.ROWNUM_ >= var3
4         #PRJT2: [682, 1000, 108]; exp_num(5), is_atom(FALSE) 
5           #RNSK: [682, 1000, 108]; rownum < exp_cast(1000)
6             #PRJT2: [682, 1000, 108]; exp_num(4), is_atom(FALSE) 
7               #TOPN2: [682, 1000, 108]; top_num(1000)
8                 #NEST LOOP INDEX JOIN2: [490, 2500000, 108] 
9                   #BLKUP2: [2527, 2500000, 56]; IDX_TAB1_V1_MODIFY(A)
10                    #SSEK2: [2527, 2500000, 56]; scan_type(ASC), IDX_TAB1_V1_MODIFY(TAB1 as A), scan_range[(exp_param(no:0),min),(exp_param(no:0),max))
11                  #BLKUP2: [16304, 1, 0]; IDX_TAB2_V1(B)
12                    #SSEK2: [16304, 1, 0]; scan_type(ASC), IDX_TAB2_V1(TAB2 as B), scan_range[A.V1,A.V1]

问题场景2的说明

create or replace index idx_tab1_v1_modify on tab1(v2,v1 desc);

问题2：在上面的数据基础上，做了这个索引更改后，还必须通过no_index(a idx_tab1_v1)屏蔽掉原先的v1的索引，才能够消除排序。
说明：sort操作符在优化器的脑回路里，是不会提升代价评估的；它只考虑了 IDX_TAB1_V1_MODIFY 2527 IDX_TAB1_V1 2524 的扫描代价，如果不进行屏蔽索引的干预，优化器自己会继续坚持选择 2524 代价的计划（需要排序的计划）。优化器“根本不考虑消除排序带来的收益”。

SQL语句：

select
        *
from
        (
                select
                        row_.*,
                        rownum rownum_
                from
                        (
                                select
                                        /*+TOP_ORDER_OPT_FLAG(0) no_index(a IDX_TAB1_V1) stat(a 100M),stat(b 100M)*/  
                                        a.*,b.v3 v33
                                from
                                        tab1  a,
                                        tab2 b
                                where
                                        a.v1 = b.v1
                                    and a.v2 =?
                                order by
                                        a.v1 desc
                        )
                        row_
        )
where
        rownum_  < 1000
    and rownum_ >= 1

执行计划：

1   #NSET2: [682, 50, 108] 
2     #PRJT2: [682, 50, 108]; exp_num(5), is_atom(FALSE) 
3       #SLCT2: [682, 50, 108]; DMTEMPVIEW_16781250.ROWNUM_ >= var3
4         #PRJT2: [682, 1000, 108]; exp_num(5), is_atom(FALSE) 
5           #RNSK: [682, 1000, 108]; rownum < exp_cast(1000)
6             #PRJT2: [682, 1000, 108]; exp_num(4), is_atom(FALSE) 
7               #TOPN2: [682, 1000, 108]; top_num(1000)
8                 #NEST LOOP INDEX JOIN2: [490, 2500000, 108] 
9                   #BLKUP2: [2527, 2500000, 56]; IDX_TAB1_V1_MODIFY(A)
10                    #SSEK2: [2527, 2500000, 56]; scan_type(ASC), IDX_TAB1_V1_MODIFY(TAB1 as A), scan_range[(exp_param(no:0),min),(exp_param(no:0),max))
11                  #BLKUP2: [16304, 1, 0]; IDX_TAB2_V1(B)
12                    #SSEK2: [16304, 1, 0]; scan_type(ASC), IDX_TAB2_V1(TAB2 as B), scan_range[A.V1,A.V1]