时间:2021-07-01 10:21:17 帮助过:34人阅读
求助,还能这么优化?这是一个视图的查询,每次要10秒左右。 我移到存储过程中,居然报ORA-01747:user.table.column,table.column或列说明无效 无语了,放在视图中没问题的, dblink是不是有什么限制,还是啥的。 Oracle insert into MR_MSG_XZBKMX_RES_BKXX(
求助,还能这么优化?这是一个视图的查询,每次要10秒左右。insert into MR_MSG_XZBKMX_RES_BKXX(ID,MSGID,HH,HM, DZ,BW,BCH, BWH,KJ,BSH, SQXZ,SQCBR,HBSL, ZDDS,RJSL,LSSL, QSL1,QSL2,QSL3, DJ,HHYS,YSXZMS, RKS,CBZQ,SFLX, LXDH,ZBRQ,QFSL, QFJE,QFBS,DKZHYE, SQDBDS,YSKYE,TSXX, MS1,MS2,MS3, MS4,MS5,CBSXH, BKSXH) select SEQ_MR_MSG_XZBKMX_RES_BKXX.nextval, pinmsgid,x.HH,x.HM, x.DZ,x.BW,x.BCH, x.BWH,x.KJ,x.BSH, x.SQXZ,x.SQCBR,x.HBSL, decode(x.ZDDS,0,v_zdds,x.ZDDS),x.RJSL,case when v_lssllx=1 then x.qsl1 else x.pjsl end, x.QSL1,x.QSL2,x.QSL3, x.DJ,x.HHYS,x.YSXZMS, x.RKS,x.CBZQ,x.SFLX, x.LXDH,x.ZBRQ,x.QFSL, x.QFJE,x.QFBS,x.DKZHYE, x.SQDBDS,x.YSKYE,x.TSXX, x.MS1,x.MS2,x.MS3, x.MS4,x.MS5,x.CBSXH, rownum from (select a.hh, a.bzmc HM, a.yhdz DZ, a.zbwz BW, a.bch, a.bwh, f.kj, f.bsh, NVL(g.xbqd,d.sqxz) SQXZ, d.sqcbr, 0 hbsl, f.zdds zdds, -1 rjsl, d.pjsl, d.qsl1,d.qsl2,d.qsl3, a.zysxz, h.dj, case when a.ysxzsl=1 then 0 else 1 end hhys, h.xzms YSXZMS, a.ysrk RKS, a.cbzq, i.sflx, i.lxdh, k.zbrq, 0 QFSL, 0 QFJE, 0 QFBS, 0 dkzhye, 0 sqdbds, NVL(i.yskye,0) yskye, null tsxx, null ms1, null ms2, null ms3, null ms4, null ms5, d.cbsxh from da_yhbk@o3mislink a full join da_cbxx@o3mislink d on a.hh = d.hh full join sb_sbda@o3mislink f on f.sbid = a.sbid full join da_khxx@o3mislink i on i.khh = a.khh full join da_lhxx@o3mislink k on k.hh = a.hh full join da_dm_yhzt@o3mislink c on a.yhzt = c.ztid and c.sfcb = 1 left join mr_gps n on a.hh=n.hh left join (select x.hh,x.xbqd from cb_chrw@o3mislink x, (select a.hh,max(a.sgrq) sgrq from cb_chrw@o3mislink a,da_yhbk@o3mislink c where (v_bchs like '%'||c.bch||',%') and a.hh=c.hh and a.jsbz=1 group by a.hh) y where x.hh = y.hh and x.sgrq = y.sgrq) g on g.hh = a.hh full join (select ysxz,dj,xzms from fy_ysxz@o3mislink where v_sfyf between sxrq and nvl(jsrq,v_sfyf) ) h on a.zysxz = h.ysxz left join cb_slb@o3mislink e on a.hh = e.hh and e.jgbz in (0,1,2,4,6,7) and e.sfyf = v_sfyf where v_bchs like '%'||a.bch||',%' and MOD(EXTRACT(month from trunc(v_sfyf,'MM')),a.CBZQ)=MOD(a.QCY,a.CBZQ) and e.hh is null order by a.bch,d.cbsxh ) x;
SELECT STATEMENT, GOAL = ALL_ROWS 216384 148833 69058512 SORT ORDER BY 216384 148833 69058512 FILTER NESTED LOOPS OUTER 201685 148833 69058512 HASH JOIN RIGHT SEMI 168940 148833 68165514 TABLE ACCESS FULL NNMIS DA_DM_YHZT 2 5 30 VIEW SYS 168919 1041831 470907612 UNION-ALL HASH JOIN RIGHT OUTER 123752 1041825 468821250 TABLE ACCESS BY INDEX ROWID NNMIS FY_YSXZ 3 6 186 INDEX RANGE SCAN NNMIS IDX_FY_YSXZ_SXRQ 2 21 HASH JOIN RIGHT OUTER 123731 1041825 436524675 VIEW NNMIS 1052 1 26 TABLE ACCESS BY INDEX ROWID NNMIS CB_CHRW 2 1 14 NESTED LOOPS 1052 1 36 VIEW NNMIS 1050 1 22 HASH GROUP BY 1050 1 33 HASH JOIN 1047 17643 582219 INDEX FAST FULL SCAN NNMIS IX_DA_YHBK_BCH 431 17341 329479 TABLE ACCESS FULL NNMIS CB_CHRW 614 33941 475174 INDEX RANGE SCAN NNMIS IDX_CB_CHRW_HH 1 1 HASH JOIN RIGHT OUTER 122661 1041825 409437225 REMOTE MR_GPS 0 1 9 VIEW SYS 122642 1041825 400060800 UNION-ALL HASH JOIN RIGHT OUTER 49369 710090 274804830 TABLE ACCESS FULL NNMIS DA_LHXX 879 331736 3980832 VIEW SYS 35071 710090 266283750 UNION-ALL HASH JOIN RIGHT OUTER 16953 374282 127255880 TABLE ACCESS FULL NNMIS DA_KHXX 1631 335809 5372944 VIEW SYS 8900 374282 121267368 UNION-ALL NESTED LOOPS OUTER 4073 174 54462 VIEW SYS 3724 174 52026 UNION-ALL NESTED LOOPS OUTER 2685 173 41174 TABLE ACCESS FULL NNMIS DA_YHBK 2346 173 36676 TABLE ACCESS BY INDEX ROWID NNMIS DA_CBXX 2 1 26 INDEX UNIQUE SCAN NNMIS PK_DA_CBXX 1 1 FILTER NESTED LOOPS ANTI 1040 1 39 TABLE ACCESS FULL NNMIS DA_CBXX 931 331736 8625136 INDEX UNIQUE SCAN NNMIS PK_DA_YHBK 0 346828 4508764 TABLE ACCESS BY INDEX ROWID NNMIS SB_SBDA 2 1 14 INDEX UNIQUE SCAN NNMIS PK_CB_SBDA 1 1 FILTER HASH JOIN RIGHT ANTI 4827 374108 10100916 VIEW NNMIS 3050 346829 4508777 UNION-ALL MERGE JOIN OUTER 2771 346828 10751668 TABLE ACCESS BY INDEX ROWID NNMIS DA_YHBK 1546 346828 9017528 INDEX FULL SCAN NNMIS PK_DA_YHBK 744 346828 SORT JOIN 1226 331736 1658680 INDEX FAST FULL SCAN NNMIS PK_DA_CBXX 171 331736 1658680 NESTED LOOPS ANTI 279 1 18 INDEX FAST FULL SCAN NNMIS PK_DA_CBXX 171 331736 1658680 INDEX UNIQUE SCAN NNMIS PK_DA_YHBK 0 346828 4508764 TABLE ACCESS FULL NNMIS SB_SBDA 883 374109 5237526 FILTER HASH JOIN ANTI 18117 335808 9738432 TABLE ACCESS FULL NNMIS DA_KHXX 1631 335809 5372944 VIEW NNMIS 15153 720937 9372181 UNION-ALL HASH JOIN RIGHT OUTER 11205 346829 10404870 INDEX FAST FULL SCAN NNMIS PK_CB_SBDA 181 374109 1496436 VIEW NNMIS 10094 346829 9017554 UNION-ALL MERGE JOIN OUTER 9055 346828 621168948 TABLE ACCESS BY INDEX ROWID NNMIS DA_YHBK 1546 346828 591688568 INDEX FULL SCAN NNMIS PK_DA_YHBK 744 346828 SORT JOIN 7509 331736 28197560 TABLE ACCESS FULL NNMIS DA_CBXX 931 331736 28197560 NESTED LOOPS ANTI 1040 1 98 TABLE ACCESS FULL NNMIS DA_CBXX 931 331736 28197560 INDEX UNIQUE SCAN NNMIS PK_DA_YHBK 0 346828 4508764 HASH JOIN ANTI 3948 374108 6359836 INDEX FAST FULL SCAN NNMIS PK_CB_SBDA 181 374109 1496436 VIEW NNMIS 3050 346829 4508777 UNION-ALL MERGE JOIN OUTER 2771 346828 10751668 TABLE ACCESS BY INDEX ROWID NNMIS DA_YHBK 1546 346828 9017528 INDEX FULL SCAN NNMIS PK_DA_YHBK 744 346828 SORT JOIN 1226 331736 1658680 INDEX FAST FULL SCAN NNMIS PK_DA_CBXX 171 331736 1658680 NESTED LOOPS ANTI 279 1 18 INDEX FAST FULL SCAN NNMIS PK_DA_CBXX 171 331736 1658680 INDEX UNIQUE SCAN NNMIS PK_DA_YHBK 0 346828 4508764 FILTER HASH JOIN ANTI 73273 331735 8293375 TABLE ACCESS FULL NNMIS DA_LHXX 879 331736 3980832 VIEW NNMIS 70721 1056745 13737685 UNION-ALL HASH JOIN RIGHT OUTER 54237 720937 22349047 INDEX FAST FULL SCAN NNMIS SYS_C0057272 173 335809 1679045 VIEW NNMIS 52462 720937 18744362 UNION-ALL HASH JOIN RIGHT OUTER 46713 346829 743948205 TABLE ACCESS FULL NNMIS SB_SBDA 883 374109 24691194 VIEW NNMIS 10094 346829 721057491 UNION-ALL MERGE JOIN OUTER 9055 346828 621168948 TABLE ACCESS BY INDEX ROWID NNMIS DA_YHBK 1546 346828 591688568 INDEX FULL SCAN NNMIS PK_DA_YHBK 744 346828 SORT JOIN 7509 331736 28197560 TABLE ACCESS FULL NNMIS DA_CBXX 931 331736 28197560 NESTED LOOPS ANTI 1040 1 98 TABLE ACCESS FULL NNMIS DA_CBXX 931 331736 28197560 INDEX UNIQUE SCAN NNMIS PK_DA_YHBK 0 346828 4508764 HASH JOIN RIGHT ANTI 5749 374108 29554532 VIEW NNMIS 3050 346829 4508777 UNION-ALL MERGE JOIN OUTER 2771 346828 10751668 TABLE ACCESS BY INDEX ROWID NNMIS DA_YHBK 1546 346828 9017528 INDEX FULL SCAN NNMIS PK_DA_YHBK 744 346828 SORT JOIN 1226 331736 1658680 INDEX FAST FULL SCAN NNMIS PK_DA_CBXX 171 331736 1658680 NESTED LOOPS ANTI 279 1 18 INDEX FAST FULL SCAN NNMIS PK_DA_CBXX 171 331736 1658680 INDEX UNIQUE SCAN NNMIS PK_DA_YHBK 0 346828 4508764 TABLE ACCESS FULL NNMIS SB_SBDA 883 374109 24691194 HASH JOIN ANTI 16484 335808 6044544 INDEX FAST FULL SCAN NNMIS SYS_C0057272 173 335809 1679045 VIEW NNMIS 15153 720937 9372181 UNION-ALL HASH JOIN RIGHT OUTER 11205 346829 10404870 INDEX FAST FULL SCAN NNMIS PK_CB_SBDA 181 374109 1496436 VIEW NNMIS 10094 346829 9017554 UNION-ALL MERGE JOIN OUTER 9055 346828 621168948 TABLE ACCESS BY INDEX ROWID NNMIS DA_YHBK 1546 346828 591688568 INDEX FULL SCAN NNMIS PK_DA_YHBK 744 346828 SORT JOIN 7509 331736 28197560 TABLE ACCESS FULL NNMIS DA_CBXX 931 331736 28197560 NESTED LOOPS ANTI 1040 1 98 TABLE ACCESS FULL NNMIS DA_CBXX 931 331736 28197560 INDEX UNIQUE SCAN NNMIS PK_DA_YHBK 0 346828 4508764 HASH JOIN ANTI 3948 374108 6359836 INDEX FAST FULL SCAN NNMIS PK_CB_SBDA 181 374109 1496436 VIEW NNMIS 3050 346829 4508777 UNION-ALL MERGE JOIN OUTER 2771 346828 10751668 TABLE ACCESS BY INDEX ROWID NNMIS DA_YHBK 1546 346828 9017528 INDEX FULL SCAN NNMIS PK_DA_YHBK 744 346828 SORT JOIN 1226 331736 1658680 INDEX FAST FULL SCAN NNMIS PK_DA_CBXX 171 331736 1658680 NESTED LOOPS ANTI 279 1 18 INDEX FAST FULL SCAN NNMIS PK_DA_CBXX 171 331736 1658680 INDEX UNIQUE SCAN NNMIS PK_DA_YHBK 0 346828 4508764 FILTER FILTER TABLE ACCESS BY INDEX ROWID NNMIS FY_YSXZ 3 6 186 INDEX RANGE SCAN NNMIS IDX_FY_YSXZ_SXRQ 2 21 HASH JOIN RIGHT OUTER 16080 3472 187488 VIEW NNMIS 1052 1 13 TABLE ACCESS BY INDEX ROWID NNMIS CB_CHRW 2 1 14 NESTED LOOPS 1052 1 36 VIEW NNMIS 1050 1 22 SORT GROUP BY 1050 1 33 HASH JOIN 1047 17643 582219 INDEX FAST FULL SCAN NNMIS IX_DA_YHBK_BCH 431 17341 329479 TABLE ACCESS FULL NNMIS CB_CHRW 614 33941 475174 INDEX RANGE SCAN NNMIS IDX_CB_CHRW_HH 1 1 NESTED LOOPS OUTER 15029 3472 142352 VIEW NNMIS 15027 3472 111104 UNION-ALL HASH JOIN OUTER 15027 3471 10805223 VIEW NNMIS 12159 3471 10520601 UNION-ALL HASH JOIN OUTER 12159 3470 8904020 VIEW NNMIS 7509 3470 8376580 UNION-ALL HASH JOIN OUTER 7509 3469 7441005 VIEW NNMIS 4886 3469 7212051 UNION-ALL HASH JOIN OUTER 4886 3468 6211188 TABLE ACCESS FULL NNMIS DA_YHBK 2137 3468 5916408 TABLE ACCESS FULL NNMIS DA_CBXX 931 331736 28197560 FILTER NESTED LOOPS ANTI 1040 1 98 TABLE ACCESS FULL NNMIS DA_CBXX 931 331736 28197560 INDEX UNIQUE SCAN NNMIS PK_DA_YHBK 0 346828 4508764 TABLE ACCESS FULL NNMIS SB_SBDA 883 374109 24691194 FILTER HASH JOIN RIGHT ANTI 5749 374108 29554532 VIEW NNMIS 3050 346829 4508777 UNION-ALL MERGE JOIN OUTER 2771 346828 10751668 TABLE ACCESS BY INDEX ROWID NNMIS DA_YHBK 1546 346828 9017528 INDEX FULL SCAN NNMIS PK_DA_YHBK 744 346828 SORT JOIN 1226 331736 1658680 INDEX FAST FULL SCAN NNMIS PK_DA_CBXX 171 331736 1658680 NESTED LOOPS ANTI 279 1 18 INDEX FAST FULL SCAN NNMIS PK_DA_CBXX 171 331736 1658680 INDEX UNIQUE SCAN NNMIS PK_DA_YHBK 0 346828 4508764 TABLE ACCESS FULL NNMIS SB_SBDA 883 374109 24691194 TABLE ACCESS FULL NNMIS DA_KHXX 1631 335809 51042968 FILTER HASH JOIN RIGHT ANTI 20276 335808 55408320 VIEW NNMIS 15153 720937 9372181 UNION-ALL HASH JOIN RIGHT OUTER 11205 346829 10404870 INDEX FAST FULL SCAN NNMIS PK_CB_SBDA 181 374109 1496436 VIEW NNMIS 10094 346829 9017554 UNION-ALL MERGE JOIN OUTER 9055 346828 621168948 TABLE ACCESS BY INDEX ROWID NNMIS DA_YHBK 1546 346828 591688568 INDEX FULL SCAN NNMIS PK_DA_YHBK 744 346828 SORT JOIN 7509 331736 28197560 TABLE ACCESS FULL NNMIS DA_CBXX 931 331736 28197560 NESTED LOOPS ANTI 1040 1 98 TABLE ACCESS FULL NNMIS DA_CBXX 931 331736 28197560 INDEX UNIQUE SCAN NNMIS PK_DA_YHBK 0 346828 4508764 HASH JOIN ANTI 3948 374108 6359836 INDEX FAST FULL SCAN NNMIS PK_CB_SBDA 181 374109 1496436 VIEW NNMIS 3050 346829 4508777 UNION-ALL MERGE JOIN OUTER 2771 346828 10751668 TABLE ACCESS BY INDEX ROWID NNMIS DA_YHBK 1546 346828 9017528 INDEX FULL SCAN NNMIS PK_DA_YHBK 744 346828 SORT JOIN 1226 331736 1658680 INDEX FAST FULL SCAN NNMIS PK_DA_CBXX 171 331736 1658680 NESTED LOOPS ANTI 279 1 18 INDEX FAST FULL SCAN NNMIS PK_DA_CBXX 171 331736 1658680 INDEX UNIQUE SCAN NNMIS PK_DA_YHBK 0 346828 4508764 TABLE ACCESS FULL NNMIS DA_KHXX 1631 335809 51042968 TABLE ACCESS FULL NNMIS DA_LHXX 879 331736 27202352 FILTER HASH JOIN RIGHT ANTI 74367 331735 31514825 VIEW NNMIS 70721 1056745 13737685 UNION-ALL HASH JOIN RIGHT OUTER 54237 720937 22349047 INDEX FAST FULL SCAN NNMIS SYS_C0057272 173 335809 1679045 VIEW NNMIS 52462 720937 18744362 UNION-ALL HASH JOIN RIGHT OUTER 46713 346829 743948205 TABLE ACCESS FULL NNMIS SB_SBDA 883 374109 24691194 VIEW NNMIS 10094 346829 721057491 UNION-ALL MERGE JOIN OUTER 9055 346828 621168948 TABLE ACCESS BY INDEX ROWID NNMIS DA_YHBK 1546 346828 591688568 INDEX FULL SCAN NNMIS PK_DA_YHBK 744 346828 SORT JOIN 7509 331736 28197560 TABLE ACCESS FULL NNMIS DA_CBXX 931 331736 28197560 NESTED LOOPS ANTI 1040 1 98 TABLE ACCESS FULL NNMIS DA_CBXX 931 331736 28197560 INDEX UNIQUE SCAN NNMIS PK_DA_YHBK 0 346828 4508764 HASH JOIN RIGHT ANTI 5749 374108 29554532 VIEW NNMIS 3050 346829 4508777 UNION-ALL MERGE JOIN OUTER 2771 346828 10751668 TABLE ACCESS BY INDEX ROWID NNMIS DA_YHBK 1546 346828 9017528 INDEX FULL SCAN NNMIS PK_DA_YHBK 744 346828 SORT JOIN 1226 331736 1658680 INDEX FAST FULL SCAN NNMIS PK_DA_CBXX 171 331736 1658680 NESTED LOOPS ANTI 279 1 18 INDEX FAST FULL SCAN NNMIS PK_DA_CBXX 171 331736 1658680 INDEX UNIQUE SCAN NNMIS PK_DA_YHBK 0 346828 4508764 TABLE ACCESS FULL NNMIS SB_SBDA 883 374109 24691194 HASH JOIN ANTI 16484 335808 6044544 INDEX FAST FULL SCAN NNMIS SYS_C0057272 173 335809 1679045 VIEW NNMIS 15153 720937 9372181 UNION-ALL HASH JOIN RIGHT OUTER 11205 346829 10404870 INDEX FAST FULL SCAN NNMIS PK_CB_SBDA 181 374109 1496436 VIEW NNMIS 10094 346829 9017554 UNION-ALL MERGE JOIN OUTER 9055 346828 621168948 TABLE ACCESS BY INDEX ROWID NNMIS DA_YHBK 1546 346828 591688568 INDEX FULL SCAN NNMIS PK_DA_YHBK 744 346828 SORT JOIN 7509 331736 28197560 TABLE ACCESS FULL NNMIS DA_CBXX 931 331736 28197560 NESTED LOOPS ANTI 1040 1 98 TABLE ACCESS FULL NNMIS DA_CBXX 931 331736 28197560 INDEX UNIQUE SCAN NNMIS PK_DA_YHBK 0 346828 4508764 HASH JOIN ANTI 3948 374108 6359836 INDEX FAST FULL SCAN NNMIS PK_CB_SBDA 181 374109 1496436 VIEW NNMIS 3050 346829 4508777 UNION-ALL MERGE JOIN OUTER 2771 346828 10751668 TABLE ACCESS BY INDEX ROWID NNMIS DA_YHBK 1546 346828 9017528 INDEX FULL SCAN NNMIS PK_DA_YHBK 744 346828 SORT JOIN 1226 331736 1658680 INDEX FAST FULL SCAN NNMIS PK_DA_CBXX 171 331736 1658680 NESTED LOOPS ANTI 279 1 18 INDEX FAST FULL SCAN NNMIS PK_DA_CBXX 171 331736 1658680 INDEX UNIQUE SCAN NNMIS PK_DA_YHBK 0 346828 4508764 TABLE ACCESS FULL NNMIS DA_LHXX 879 331736 27202352 REMOTE MR_GPS 0 1 9 VIEW SYS 0 1 6 TABLE ACCESS BY INDEX ROWID NNMIS CB_SLB 4 1 17 INDEX RANGE SCAN NNMIS IX_CB_SLB_HH_SFYF 3 1