为什么加上rownum就会变得无限慢？

hebin_25

数据库版本11.2.0.4
rac环境
linux 64位os

select /*+full(csm) full(cle1) full(cle2)*/
csm.office_id,
sss.itemid,
decode(sss.REG_INS_FLAG,'N','平常','R','挂号','V','保价') reg_ins_flag,decode(sss.TRANSTYPE,'A','航空','B','S.A.L','C','水陆路') transtype,
to_char(sss.INS_TIME,'yyyy-mm-dd') item_date,
cle1.DISP_DEST_NATION supply_le,
cle2.DISP_DEST_NATION discharge_le
from ST_SORTER_SUPPLY sss,ST_SORTER_DISCHARGE ssd,CFG_LOGICAL_EXPORT cle1,CFG_LOGICAL_EXPORT cle2,CFG_SORTING_MACHINE csm
where csm.SM_SERNO=sss.SORTER_CODE and sss.itemid=ssd.itemid and
sss.LE_SERNO!=ssd.LE_SERNO and sss.INS_TIME>=to_date('2014-11-17','yyyy-mm-dd') and  sss.INS_TIME<to_date('2014-11-18','yyyy-mm-dd') and
to_char(cle1.LE_SERNO)=sss.LE_SERNO and to_char(cle2.LE_SERNO)=ssd.LE_SERNO
;




19 rows selected.

Elapsed: 00:00:05.14

Execution Plan
----------------------------------------------------------

-------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                | Rows        | Bytes | Cost        | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                        |  1318K|   106M| 88239 |        |        |
|   1 |  HASH JOIN                  |                        |  1318K|   106M| 88239 |        |        |
|   2 |   TABLE ACCESS FULL          | CFG_SORTING_MACHINE |     1 |    14 |     3 |        |        |
|   3 |   HASH JOIN                  |                        |  1318K|    89M| 88233 |        |        |
|   4 |    TABLE ACCESS FULL          | CFG_LOGICAL_EXPORT        |   670 |  5360 |    16 |        |        |
|   5 |    HASH JOIN                  |                        |  1318K|    79M| 88213 |        |        |
|   6 |     PARTITION RANGE SINGLE|                        |   657K|    23M|  8850 |    12 |    12 |
|   7 |      TABLE ACCESS FULL          | ST_SORTER_SUPPLY        |   657K|    23M|  8850 |    12 |    12 |
|   8 |     HASH JOIN                  |                        |  4086K|    97M| 70636 |        |        |
|   9 |      TABLE ACCESS FULL          | CFG_LOGICAL_EXPORT        |   670 |  5360 |    16 |        |        |
|  10 |      PARTITION RANGE ALL  |                        |    12M|   204M| 70586 |     1 |1048575|
|  11 |       TABLE ACCESS FULL   | ST_SORTER_DISCHARGE |    12M|   204M| 70586 |     1 |1048575|
-------------------------------------------------------------------------------------------------


19条结果，5秒出来了

但

select /*+full(csm) full(cle1) full(cle2)*/
csm.office_id,
sss.itemid,
decode(sss.REG_INS_FLAG,'N','平常','R','挂号','V','保价') reg_ins_flag,decode(sss.TRANSTYPE,'A','航空','B','S.A.L','C','水陆路') transtype,
to_char(sss.INS_TIME,'yyyy-mm-dd') item_date,
cle1.DISP_DEST_NATION supply_le,
cle2.DISP_DEST_NATION discharge_le
from ST_SORTER_SUPPLY sss,ST_SORTER_DISCHARGE ssd,CFG_LOGICAL_EXPORT cle1,CFG_LOGICAL_EXPORT cle2,CFG_SORTING_MACHINE csm
where csm.SM_SERNO=sss.SORTER_CODE and sss.itemid=ssd.itemid and
sss.LE_SERNO!=ssd.LE_SERNO and sss.INS_TIME>=to_date('2014-11-17','yyyy-mm-dd') and  sss.INS_TIME<to_date('2014-11-18','yyyy-mm-dd') and
to_char(cle1.LE_SERNO)=sss.LE_SERNO and to_char(cle2.LE_SERNO)=ssd.LE_SERNO and rownum<=10
;

就很久很久出不来结果


看v$session_wait 全是 gc cr request等待       

OWNER                               SEGMENT_NAME                                                                         SEGMENT_TYPE
------------------------------ --------------------------------------------------------------------------------- ------------------
SYS                               _SYSSMU21_3274734285$                                                                 TYPE2 UNDO



OWNER                               SEGMENT_NAME                                                                         SEGMENT_TYPE
------------------------------ --------------------------------------------------------------------------------- ------------------
IPWS3                               ST_SORTER_SUPPLY                                                                  TABLE PARTITION

SQL> /

OWNER                               SEGMENT_NAME                                                                         SEGMENT_TYPE
------------------------------ --------------------------------------------------------------------------------- ------------------
IPWS3                               ST_SORTER_SUPPLY                                                                  TABLE PARTITION


二十分钟也出不来结果
就只是做了下rownum 为啥是这样呢？

不了峰

把rownum写在最外层试试看

hebin_25

解决了

改成

select   /*+full(csm) full(cle1) full(cle2) ALL_ROWS*/
csm.office_id,
sss.itemid,
decode(sss.REG_INS_FLAG,'N','平常','R','挂号','V','保价') reg_ins_flag,decode(sss.TRANSTYPE,'A','航空','B','S.A.L','C','水陆路') transtype,
to_char(sss.INS_TIME,'yyyy-mm-dd') item_date,
cle1.DISP_DEST_NATION supply_le,
cle2.DISP_DEST_NATION discharge_le
from ST_SORTER_SUPPLY sss,ST_SORTER_DISCHARGE ssd,CFG_LOGICAL_EXPORT cle1,CFG_LOGICAL_EXPORT cle2,CFG_SORTING_MACHINE csm
where csm.SM_SERNO=sss.SORTER_CODE and sss.itemid=ssd.itemid and
sss.LE_SERNO!=ssd.LE_SERNO and sss.INS_TIME>=to_date('2014-11-17','yyyy-mm-dd') and  sss.INS_TIME<to_date('2014-11-18','yyyy-mm-dd') and
to_char(cle1.LE_SERNO)=sss.LE_SERNO and to_char(cle2.LE_SERNO)=ssd.LE_SERNO  and rownum<10
;
就好了
因为加了rownum后执行计划变了 变成nl连接了

可能是toad 吧plan table 给弄得有问题了导致的

统计信息都是挺对的 但执行计划计算的不对