Home >Database >Mysql Tutorial >ora01747
ora01747
- WBOYOriginal
- 2016-06-07 14:57:372215browse
求助，还能这么优化？这是一个视图的查询，每次要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
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：with as 递归Next article：sqlserver中将varchar类型转换为int型