典型sql优化案例.docx
- 文档编号:25110182
- 上传时间:2023-06-05
- 格式:DOCX
- 页数:39
- 大小:25.79KB
典型sql优化案例.docx
《典型sql优化案例.docx》由会员分享,可在线阅读,更多相关《典型sql优化案例.docx(39页珍藏版)》请在冰豆网上搜索。
典型sql优化案例
GS0000719211
9zr7sgwqkdsxf
ead.automr.service.AutoMrService.getRealtimeData
UPDATER_DATAC
SET(THIS_READ,MR_STATUS_CODE,C.ACTUAL_MODE)=
(SELECTDECODE(:
B6,
…..
96,
DATA_VALUE96,
NULL),
'02',
:
B5
FROME_MPED_READ_CURVE@EPCM93A,R_COLL_OBJB
WHEREA.COLL_OBJ_ID=B.COLL_OBJ_ID
ANDB.METER_ID=C.METER_ID
ANDA.YMD=TRUNC(:
B4)
ANDCOLL_ITEM_CODE=
PKG_MR_LOCCAL.CHANGEITEMCODE(C.READ_TYPE_CODE)),
C.REMAIN_AMT=
(SELECTMAX(DECODE(:
B6,
1,
DATA_VALUE1,
……..
95,
DATA_VALUE95,
96,
DATA_VALUE96,
NULL))
FROME_MPED_READ_CURVE@EPCM93A,R_COLL_OBJB
WHEREA.COLL_OBJ_ID=B.COLL_OBJ_ID
ANDB.METER_ID=C.METER_ID
ANDA.YMD=TRUNC(:
B4)
ANDA.COLL_ITEM_CODE='8810'),
C.PURCHASE_TIMES=DECODE(C.READ_TYPE_CODE,
'11',
(SELECTMAX(DECODE(:
B6,
1,
DATA_VALUE1,
………
96,
DATA_VALUE96,
NULL))
FROME_MPED_READ_CURVE@EPCM93A,
R_COLL_OBJB
WHEREA.COLL_OBJ_ID=B.COLL_OBJ_ID
ANDB.METER_ID=C.METER_ID
ANDA.YMD=TRUNC(:
B4)
ANDA.COLL_ITEM_CODE='8820'),
C.PURCHASE_TIMES)
WHEREC.APP_NO=:
B3
ANDC.ORG_NO=:
B2
ANDC.AMT_YM=:
B1
ANDC.SRC_CODE='01'
ANDC.METER_IDIN(SELECT*FROMTABLE(:
B7))
ANDEXISTS
(SELECT1
FROME_MPED_READ_CURVE@EPCM93A,R_COLL_OBJB
WHEREA.COLL_OBJ_ID=B.COLL_OBJ_ID
ANDB.METER_ID=C.METER_ID
ANDA.YMD=TRUNC(:
B4)
ANDCOLL_ITEM_CODE=
PKG_MR_LOCCAL.CHANGEITEMCODE(C.READ_TYPE_CODE))
SX0001312245
8xmwxd4vrbc88
SELECTTO_CHAR(A.CALC_ID)CALC_ID,
A.YMAMT_YM,
A.CONS_NO,
A.ORG_NO,
(SELECTTO_CHAR(B.SP_ID)
FROMARC_E_MP_PARA_SNAPB
WHEREB.MP_ID=C.MP_ID
ANDB.APP_CODE=C.APP_NO
ANDB.ORG_NO=C.ORG_NO
ANDB.CALC_ID=A.CALC_ID
ANDB.YM=A.YM
ANDROWNUM=1)SP_ID,
C.MP_IDMP_ID,
(SELECTB.MP_LEVEL_NUM
FROMARC_E_MP_PARA_SNAPB
WHEREB.MP_ID=C.MP_ID
ANDB.APP_CODE=C.APP_NO
ANDB.ORG_NO=C.ORG_NO
ANDB.CALC_ID=A.CALC_ID
ANDB.YM=A.YM
ANDROWNUM=1)MP_LEVEL_NUM,
TO_CHAR(C.METER_ID)METER_ID,
NVL(C.ASSET_NO,
(SELECTASSET_NOFROMD_METERWHEREMETER_ID=C.METER_ID))ASSET_NO,
(SELECTP.NAME
FROMP_CODEP
WHEREP.CODE_TYPE='readTypeCode'
ANDP.VALUE=C.READ_TYPE_CODE
ANDROWNUM=1)READ_TYPE_CODE,
C.T_FACTORT_FACTOR,
TRIM(TO_CHAR(C.LAST_MR_NUM,'FM9999999990.00'))LAST_MR_NUM,
TRIM(TO_CHAR(C.THIS_READ,'FM9999999990.00'))THIS_READ,
TRIM(TO_CHAR((NVL(C.THIS_READ,0)-NVL(C.LAST_MR_NUM,0)),
'FM9999999990.00'))SUB_TRACT_PQ,
DECODE(C.SRC_CODE,'03',0,C.THIS_READ_PQ)THIS_READ_PQ,
C.THIS_READ_PQSETTLE_PQ,
DECODE(C.SRC_CODE,'03',C.THIS_READ_PQ,0)RS_PQ,
TO_CHAR(C.LAST_MR_DATE,'YYYYMMDD')LAST_MR_DATE,
TO_CHAR(C.THIS_YMD,'YYYYMMDD')THIS_YMD
FROMARC_E_CONS_SNAPA,ARC_R_DATAC
WHEREA.CALC_ID=C.CALC_ID
ANDA.APP_CODE=C.APP_NO
ANDC.ORG_NO=:
B6
ANDA.YM=C.AMT_YM
ANDA.ORG_NO=:
B6
ANDA.CONS_NO=:
B5
ANDA.SEND_DATE>=:
B4
ANDA.SEND_DATE<=:
B3
ANDA.YM>=:
B2
ANDA.YM<=:
B1
JL0000194892
SELECT*
FROM(SELECTrow_.*,rownumrownum_
FROM(SELECTa.cons_id,
a.cust_id,
a.cons_no,
a.cons_name,
a.orgn_cons_no,
a.contract_cap,
a.run_cap,
a.volt_code,
a.elec_type_code,
a.cons_sort_code,
a.elec_addr,
a.mr_sect_no,
(SELECTmr_sn
FROMc_mp
WHEREcons_id=a.cons_id
ANDorg_no=a.org_no
ANDmr_snISNOTNULL
ANDROWNUM=1)mr_sn,
a.org_no,
''org_name,
''city_org_no,
''city_org_name
FROMc_consa
WHEREa.org_noIN
(SELECTorg_no
FROMo_org
STARTWITHorg_no=:
1
CONNECTBYPRIORorg_no=p_org_no)
ANDnvl(a.status_code,'x')<>'9'
ANDEXISTS
(SELECT'CONS'
FROMc_bank_accth,c_accti,c_payment_relaj
WHEREh.c_a_acct_id=i.acct_id
ANDi.acct_id=j.acct_id
ANDh.bank_acct=:
2
ANDh.org_no=:
3
ANDi.cust_id=a.cust_id
ANDi.org_no=:
4
ANDj.cons_id=a.cons_id
ANDj.org_no=a.org_no)
ANDa.org_noIN(SELECTinst_no
FROMdyx_inst_rela
WHEREinst_type='org'
ANDbusi_type=:
5
ANDtop_inst_no=:
6
ANDtop_inst_type='dept'
ANDbusi_inst_flag='1'))row_
WHERErownum<=:
7)
WHERErownum_>:
8
TJ000090461
(SELECT"A8"."TASK_ID","A8"."RELA_ID",SUBSTR("A10"."BELONG_DEPT",1,5),"A9"."WH_ID",DECODE("A10"."ATTACHEQUIP_TYPE_CODE",NULL,'电能表',DECODE((SELECTCOUNT(*)||"A10"."SORT_CODE"||"A10"."TYPE_CODE"FROM"SGPM"."D_LC_EQUIP""A20"WHERE"A10"."BAR_CODE"="A20"."BAR_CODE"),'10324','集中器','11028','模块表')),"A10"."SORT_CODE","A10"."TYPE_CODE","A10"."WIRING_MODE","A10"."VOLT_CODE","A10"."CUR_STATUS_CODE",(SELECT"A18"."METER_ALLOC_DATE"FROM"WF_AMBER"."INDYWF_WORKLIST_CUR""A19","SGPM"."D_METER_ALLOC""A18","WF_AMBER"."INDYWF_WORKLIST_HIS""A17"WHERE"A17"."ITEM_KEY"(+)="A18"."APP_NO"AND"A18"."APP_NO"="A19"."ITEM_KEY"(+)AND"A10"."METER_ID"="A18"."EQUIP_ID"AND"A18".ROWID=(SELECTMAX("A21".ROWID)FROM"SGPM"."D_METER_ALLOC""A21"WHERE"A21"."EQUIP_ID"="A10"."METER_ID")ANDROWNUM=1),(SELECTNVL("A16"."PRO_TITLE","A14"."PRO_TITLE")FROM"WF_AMBER"."INDYWF_WORKLIST_CUR""A16","SGPM"."D_METER_ALLOC""A15","WF_AMBER"."INDYWF_WORKLIST_HIS""A14"WHERE"A14"."ITEM_KEY"(+)="A15"."APP_NO"AND"A15"."APP_NO"="A16"."ITEM_KEY"(+)AND"A10"."METER_ID"="A15"."EQUIP_ID"AND"A15".ROWID=(SELECTMAX("A22".ROWID)FROM"SGPM"."D_METER_ALLOC""A22"WHERE"A22"."EQUIP_ID"="A10"."METER_ID")ANDROWNUM=1),(SELECTNVL("A13"."ITEM_KEY","A11"."ITEM_KEY")FROM"WF_AMBER"."INDYWF_WORKLIST_CUR""A13","SGPM"."D_METER_ALLOC""A12","WF_AMBER"."INDYWF_WORKLIST_HIS""A11"WHERE"A11"."ITEM_KEY"(+)="A12"."APP_NO"AND"A12"."APP_NO"="A13"."ITEM_KEY"(+)AND"A10"."METER_ID"="A12"."EQUIP_ID"AND"A12".ROWID=(SELECTMAX("A23".ROWID)FROM"SGPM"."D_METER_ALLOC""A23"WHERE"A23"."EQUIP_ID"="A10"."METER_ID")ANDROWNUM=1),"A10"."BAR_CODE","A10"."CHIP_MANUFACTURER","A10"."RATED_CURRENT"FROM"SGPM"."D_METER""A10","SGPM"."D_IO_DET""A9","SGPM"."D_IO""A8"WHERE"A10"."METER_ID"="A9"."EQUIP_ID"AND"A9"."IO_WH_ID"="A8"."IO_WH_ID"AND"A8"."IO_FLAG"='02')UNION(SELECT"A5"."TASK_ID","A5"."RELA_ID",SUBSTR("A7"."DEPT_NO",1,5),"A6"."WH_ID",'负控设备',"A7"."TYPE_CODE","A7"."COLL_MODE","A7"."WIRING_MODE",'',"A7"."CUR_STATUS_CODE",(SELECT"A31"."METER_ALLOC_DATE"FROM"WF_AMBER"."INDYWF_WORKLIST_CUR""A32","SGPM"."D_METER_ALLOC""A31","WF_AMBER"."INDYWF_WORKLIST_HIS""A30"WHERE"A30"."ITEM_KEY"(+)="A31"."APP_NO"AND"A31"."APP_NO"="A32"."ITEM_KEY"(+)AND"A7"."ID"="A31"."EQUIP_ID"AND"A31".ROWID=(SELECTMAX("A33".ROWID)FROM"SGPM"."D_METER_ALLOC""A33"WHERE"A33"."EQUIP_ID"="A7"."ID")ANDROWNUM=1),(SELECTNVL("A29"."PRO_TITLE","A27"."PRO_TITLE")FROM"WF_AMBER"."INDYWF_WORKLIST_CUR""A29","SGPM"."D_METER_ALLOC""A28","WF_AMBER"."INDYWF_WORKLIST_HIS""A27"WHERE"A27"."ITEM_KEY"(+)="A28"."APP_NO"AND"A28"."APP_NO"="A29"."ITEM_KEY"(+)AND"A7"."ID"="A28"."EQUIP_ID"AND"A28".ROWID=(SELECTMAX("A34".ROWID)FROM"SGPM"."D_METER_ALLOC""A34"WHERE"A34"."EQUIP_ID"="A7"."ID")ANDROWNUM=1),(SELECTNVL("A26"."ITEM_KEY","A24"."ITEM_KEY")FROM"WF_AMBER"."INDYWF_WORKLIST_CUR""A26","SGPM"."D_METER_ALLOC""A25","WF_AMBER"."INDYWF_WORKLIST_HIS""A24"WHERE"A24"."ITEM_KEY"(+)="A25"."APP_NO"AND"A25"."APP_NO"="A26"."ITEM_KEY"(+)AND"A7"."ID"="A25"."EQUIP_ID"AND"A25".ROWID=(SELECTMAX("A35".ROWID)FROM"SGPM"."D_METER_ALLOC""A35"WHERE"A35"."EQUIP_ID"="A7"."ID")ANDROWNUM=1),"A7"."BAR_CODE",'',''FROM"SGPM"."D_LC_EQUIP""A7","SGPM"."D_IO_DET""A6","SGPM"."D_IO""A5"WHERE"A7"."ID"="A6"."EQUIP_ID"AND"A6"."IO_WH_ID"="A5"."IO_WH_ID"AND"A5"."IO_FLAG"='02'AND("A7"."ATTACH_METER_FLAG"ISNULLOR"A7"."ATTACH_METER_FLAG"='0'))UNION(SELECT"A2"."TASK_ID","A2"."RELA_ID",SUBSTR("A4"."BELONG_DEPT",1,5),"A3"."WH_ID",'互感器',"A4"."SORT_CODE","A4"."TYPE_CODE",'',"A4"."RV_CODE","A4"."CUR_STATUS_CODE",(SELECT"A43"."METER_ALLOC_DATE"FROM"WF_AMBER"."INDYWF_WORKLIST_CUR""A44","SGPM"."D_METER_ALLOC""A43","WF_AMBER"."INDYWF_WORKLIST_HIS""A42"WHERE"A42"."ITEM_KEY"(+)="A43"."APP_NO"AND"A43"."APP_NO"="A44"."ITEM_KEY"(+)AND"A4"."IT_ID"="A43"."EQUIP_ID"AND"A43".ROWID=(SELECTMAX("A45".ROWID)FROM"SGPM"."D_METER_ALLOC""A45"WHERE"A45"."EQUIP_ID"="A4"."IT_ID")ANDROWNUM=1),(SELECTNVL("A41"."PRO_TITLE","A39"."PRO_TITLE")FROM"WF_AMBER"."INDYWF_WORKLIST_CUR""A41","SGPM"."D_METER_ALLOC""A40","WF_AMBER"."INDYWF_WORKLIST_HIS""A39"WHERE"A39"."ITEM_KEY"(+)="A40"."APP_NO"AND"A40"."APP_NO"="A41"."ITEM_KEY"(+)AND"A4"."IT_ID"="A40"."EQUIP_ID"AND"A40".ROWID=(SELECTMAX("A46".ROWID)FROM"SGPM"."D_METER_ALLOC""A46"WHERE"A46"."EQUIP_ID"="A4"."IT_ID")ANDROWNUM=1),(SELECTNVL("A38"."ITEM_KEY","A36"."ITEM_KEY")FROM"WF_AMBER"."INDYWF_WORKLIST_CUR""A38","SGPM"."D_METER_ALLOC""A37","WF_AMBER"."INDYWF_WORKLIST_HIS""A36"WHERE"A36"."ITEM_KEY"(+)="A37"."APP_NO"AND"A37"."APP_NO"="A38"."ITEM_KEY"(+)AND"A4"."IT_ID"="A37"."EQUIP_ID"AND"A37".ROWID=(SELECTMAX("A47".ROWID)FROM"SGPM"."D_METER_ALLOC""A47"WHERE"A47"."EQUIP_ID"="A4"."IT_ID")ANDROWNUM=1),"A4"."BAR_CODE",'',CASEWHEN"A4"."SORT_CODE"='02'THEN"A4"."VOLT_RATIO_CODE"ELSE"A4"."RC_RATIO_CODE"ENDFROM"SGPM"."D_IT""A4","SGPM"."D_IO_DET""A3","SGPM"."D_IO""A2"WHERE"A4"."IT_ID"="A3"."EQUIP_ID"AND"A3"."IO_WH_ID"="A2"."IO_WH_ID"AND"A2"."IO_FLAG"='02')
ZJ0000275851
epm.mr.inf.service.QueryPlanService.getQueryPlan(1tk83zu1tp283)
SELECT*
FROM(SELECTrow_.*,rownumrownum_
FROM(SELECTA.MR_PLAN_NO,
A.ORG_NO,
A.APP_NO,
A.EVENT_TYPE,
A.MR_SECT_NO,
A.PLAN_TYPE_CODE,
A.PLAN_MR_DATE,
A.READER_NO,
A.MR_MODE_CODE,
(selectname
fromr_sectb
wherea.MR_SECT_NO=b.MR_SECT_NO)MR_SECT_NAME,
A.ACTUAL_DATE,
(DECODE(SUBSTR(A.PLAN_STATUS,1,1),
'0',
(SELECTCOUNT(DISTINCTCONS_NO)
FROMV_E_CONS_SNAP
WHEREMR_SECT_NO=A.MR_SECT_NO
ANDAPP_CODE=A.APP_NO
ANDNVL(STATUS_CODE,'0')<>'9'
ANDORG_NO=A.ORG_NO),
(SELECTCOUNT(DISTINCTCONS_NO)
FROMV_ARC_E_CONS_SNAP
WHEREMR_SECT_NO=A
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 典型 sql 优化 案例