ora01747
程序员文章站
2022-06-10 18:10:13
...
求助，还能这么优化？这是一个视图的查询，每次要10秒左右。 我移到存储过程中，居然报ORA-01747:user.table.column,table.column或列说明无效 无语了，放在视图中没问题的， dblink是不是有什么限制，还是啥的。 Oracle insert into MR_MSG_XZBKMX_RES_BKXX(
求助，还能这么优化？这是一个视图的查询，每次要10秒左右。我移到存储过程中，居然报 ORA-01747: user.table.column, table.column 或列说明无效
无语了，放在视图中没问题的，
dblink是不是有什么限制，还是啥的。 Oracle
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