ITPub博客

首页 > IT职业 > IT生活 > 优化今天在系统里捉的一条语句

优化今天在系统里捉的一条语句

原创 IT生活 作者:yuantong 时间:2006-09-14 16:09:37 0 删除 编辑

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*/

使其走索引

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

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

BASE_UUID,BASE_CREATEUSERID,BASE_CREATEUSERNAME,BASE_CREATEDATE,BASE_RECEIVERID,BASE_RECEIVERNAME,BASE

_ACCESSVALUE,BASE_USETIMES,BASE_HASH,BASE_KIND_UUID,BASE_BASE_UUID,BASE_VER1,BASE_VER2,BASE_VER3,BASE_LB

,BASE_FORMAT,BASE_HEADER_GWFSXH,BASE_HEADER_MMDJ,BASE_HEADER_BMQX,BASE_HEADER_JJCD,BASE_HEADER_FWJGBS,BA

SE_HEADER_FWJGDZ,BASE_HEADER_NF,BASE_HEADER_XH,BASE_HEADER_QFR,BASE_BODY_BT,BASE_BODY_ZCJG,BASE_BODY_CSD

W,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.04 /*时间是一样*/

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3769 Card=371 Bytes=1708084)
1 0 VIEW (Cost=3769 Card=371 Bytes=1708084)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=3769 Card=371 Bytes=1708084)
4 3 SORT (ORDER BY STOPKEY) (Cost=3769 Card=371 Bytes=134302)
5 4 HASH JOIN (Cost=3748 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 (BY INDEX ROWID) OF 'T_DOC_BASE' (Cost=3420 Card=8677 Bytes=2108511)
10 9 INDEX (RANGE SCAN) OF 'BASE_COMPLEX2_INDX' (NON-UNIQUE) (Cost=118 Card=1)

/*Cost=3769,这个成本明显高了许多,但在执行时间在一个数量

级,而一致性读又降低了一个数量级的情况下,还是这个计划较为优化*/

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
423 consistent gets /*一致性读降低了一个数量级*/
0 physical reads
0 redo size
6539 bytes sent via SQL*Net to client
1003 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed

[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12482/viewspace-864904/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-12-07

  • 博文量
    61
  • 访问量
    792786