SQL> SELECT

BASE_UUID,BASE_CREATEUSERID,BASE_CREATEUSERNAME,BASE_CREATEDATE,BASE_RECEIVERID,BASE_RECEIVERNAME,BASE_A

CCESSVALUE,BASE_USETIMES,BASE_HASH,BASE_KIND_UUID,BASE_BASE_UUID,BASE_VER1,BASE_VER2,BASE_VER3,BASE_LB,B

ASE_FORMAT,BASE_HEADER_GWFSXH,BASE_HEADER_MMDJ,BASE_HEADER_BMQX,BASE_HEADER_JJCD,BASE_HEADER_FWJGBS,BASE

_HEADER_FWJGDZ,BASE_HEADER_NF,BASE_HEADER_XH,BASE_HEADER_QFR,BASE_BODY_BT,BASE_BODY_ZCJG,BASE_BODY_CSDW,

BASE_BODY_CBDW,BASE_ATTAHC_NUMBER,BASE_BODY_CWRQ,BASE_BODY_YC,BASE_BODY_FZ,BASE_FOOT_ZTC,

BASE_FOOT_YFJG,BASE_FOOT_YFRQ,BASE_FOOT_SWFS,BASE_EXT_YFFS,BASE_EXT_FWDW,BASE_EXT_FWDW_ID,BASE_EXT_NGDW,

BASE_EXT_NGDW_ID,BASE_EXT_HGDW,BASE_EXT_HGDW_ID,BASE_EXT_UNION_BZ,BASE_EXT_SEQ,BASE_EXT_SWDW,BASE_EXT_SW

DW_ID,BASE_EXT_SJH,BASE_EXT_FDH,BASE_EXT_HGR,BASE_CONTEXT_UUID,BASE_XH_LEDPJ,BASE_XH_MDCLB,BASE_XH_ZQYJ,

BASE_SENDID,BASE_SEND,ACCESS_UUID,ACCESS_CREATEUSERID,ACCESS_CREATEUSERNAME,ACCESS_CREATEDATE,ACCESS_DOC

_UUID,ACCESS_RECEIVERID,ACCESS_ACCESSVALUE,ACCESS_USETIMES,ACCESS_RECEIVERNAME
3 FROM
4 (SELECT A.*,ROWNUM ROWNUM_
5 FROM
6 (SELECT

BASE_UUID,BASE_CREATEUSERID,BASE_CREATEUSERNAME,BASE_CREATEDATE,BASE_RECEIVERID,BASE_RECEIVERNAME,BASE_A

CCESSVALUE,BASE_USETIMES,BASE_HASH,BASE_KIND_UUID,BASE_BASE_UUID,BASE_VER1,BASE_VER2,BASE_VER3,BASE_LB,B

ASE_FORMAT,BASE_HEADER_GWFSXH,BASE_HEADER_MMDJ,BASE_HEADER_BMQX,BASE_HEADER_JJCD,BASE_HEADER_FWJGBS,BASE

_HEADER_FWJGDZ,BASE_HEADER_NF,BASE_HEADER_XH,BASE_HEADER_QFR,BASE_BODY_BT,BASE_BODY_ZCJG,BASE_BODY_CSDW,

BASE_BODY_CBDW,BASE_ATTAHC_NUMBER,

BASE_BODY_CWRQ,BASE_BODY_YC,BASE_BODY_FZ,BASE_FOOT_ZTC,BASE_FOOT_YFJG,BASE_FOOT_YFRQ,BASE_FOOT_SWFS,BASE

_EXT_YFFS,BASE_EXT_FWDW,BASE_EXT_FWDW_ID,BASE_EXT_NGDW,BASE_EXT_NGDW_ID,BASE_EXT_HGDW,BASE_EXT_HGDW_ID,B

ASE_EXT_UNION_BZ,BASE_EXT_SEQ,BASE_EXT_SWDW,BASE_EXT_SWDW_ID,BASE_EXT_SJH,BASE_EXT_FDH,BASE_EXT_HGR,BASE

_CONTEXT_UUID,BASE_XH_LEDPJ,BASE_XH_MDCLB,BASE_XH_ZQYJ,BASE_SENDID,BASE_SEND,ACCESS_UUID,ACCESS_CREATEUS

ERID,ACCESS_CREATEUSERNAME,ACCESS_CREATEDATE,ACCESS_DOC_UUID,ACCESS_RECEIVERID,ACCESS_ACCESSVALUE,ACCESS

_USETIMES,ACCESS_RECEIVERNAME
8 FROM T_DOC_BASE,T_DOC_DOCACCESS
9 WHERE
10 ((BASE_UUID=ACCESS_DOC_UUID) AND ((ACCESS_RECEIVERID=2810 OR ACCESS_RECEIVERID=0) AND

(BASE_KIND_UUID='KIND_GW_TZGG') )) ORDER BY BASE_CREATEDATE DESC
11 )A WHERE ROWNUM <=99999);

已选择6行。

已用时间: 00: 00: 01.07 /*时间是一样*/

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=732 Card=371 Bytes=1
708084)

1 0 VIEW (Cost=732 Card=371 Bytes=1708084)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=732 Card=371 Bytes=1708084)
4 3 SORT (ORDER BY STOPKEY) (Cost=732 Card=371 Bytes=134302)
5 4 HASH JOIN (Cost=711 Card=371 Bytes=134302)
6 5 INLIST ITERATOR
7 6 TABLE ACCESS (BY INDEX ROWID) OF'T_DOC_DOCACCESS' (Cost=325 Card=371 Bytes=44149)

8 7 INDEX (RANGE SCAN) OF 'DOCACCESS_RECEIVERID_ INDX' (NON-UNIQUE) (Cost=4 Card=371)

9 5 TABLE ACCESS (FULL) OF 'T_DOC_BASE' (Cost=383 Card=8677 Bytes=2108511)

/*Cost=732,成本是低点,所以ORACLE自动选择了这个执行计划*/

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4629 consistent gets /*但这个才是我们所关心的*/
0 physical reads
0 redo size
6539 bytes sent via SQL*Net to client
924 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed

加了

/*+index(t_doc_base BASE_COMPLEX2_INDX) use_hash(t_doc_base,t_doc_docaccess*/

使其走索引