ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 奇怪的ora-01722错误

奇怪的ora-01722错误

原创 Linux操作系统 作者:admincy 时间:2019-06-23 19:42:04 0 删除 编辑
一个和子查询,视图有关的ora-01722错误
子查询中的ora-01722;

语句:
select *
from (select /*+full(b)*/
(substr(a034, 1, 4)) test
from sbjjcgc.datas b
where reportid = 19
and a001 is not null) a
where substr(a.test, 1, 4) = 2007




出现ora-01722错误。但子查询a中的字段test中的数据全为类似于2007-08-23格式的字符。

a034 类型为 varchar2(500),表sbjjcgc.datas做过分析。但表sbjjcgc.datas中还存在a034字段数据为中文的数据行,rowid为AAFaw9AB/AAASmOAAH
数据行a034字段值为‘成都’。

此语句的执行计划如下:
SELECT STATEMENT, GOAL = CHOOSE Cost=7380 Cardinality=31 Bytes=16027
TABLE ACCESS FULL Object owner=SBJJCGC Object name=DATAS Cost=7380 Cardinality=31 Bytes=16027

使用的是全表扫描,由于使用到了full的提示,则CBO起作用。

若将以上语句改为:

select *
from (select (substr(a034, 1, 4)) test
from sbjjcgc.datas b
where reportid = 19
and a001 is not null) a
where substr(a.test, 1, 4) = 2007

执行计划为:

SELECT STATEMENT, GOAL = CHOOSE
TABLE ACCESS FULL Object owner=SBJJCGC Object name=DATAS

此时RBO起作用,语句可成功执行。


改为:

select *
from (select /*+index(b BBB)*/
(substr(a034, 1, 4)) test
from sbjjcgc.datas b
where reportid = 19
and a001 is not null) a
where substr(a.test, 1, 4) = 2007

执行计划如下:

SELECT STATEMENT, GOAL = CHOOSE Cost=34 Cardinality=31 Bytes=16027
TABLE ACCESS BY INDEX ROWID Object owner=SBJJCGC Object name=DATAS Cost=34 Cardinality=31 Bytes=16027
INDEX FULL SCAN Object owner=SBJJCGC Object name=BBB Cost=26 Cardinality=62666


使用了索引和CBO ,索引bbb建在列SETID, REPORTID, REPORTVER, EID, DATAVER, SNUMBER上。此时可成功运行,是由于索引字段将a034中有非数字字符
的数据行过滤掉了。



将子查询得到的结果放入一中间表中
create table a_20070829 as select * from sbjjcgc.datas where reportid=19 and a001 is not null



此时表a_20070829没做分析,没有建任何索引。

执行语句:
select *
from (select substr(a034, 1, 4) test
from a_20070829
where reportid = 19
and a001 is not null) a
where a.test = 2013

时正常。

若将表a_20070829分析后,正常。


插入一条a03字段为非数字字符的数据行:

insert into a_20070829 select * from sbjjcgc.datas where rowid='AAFaw9AB/AAASmOAAH'


表a_2007089分析后,语句运行出错。 若将表分析删除,则正常。


原因:

语句 :
select *
from (select substr(a034, 1, 4) test
from a_20070829
where reportid = 19
and a001 is not null) a
where a.test = 2013
中存在一个子查询,子查询中存在谓词--用于将a034字段中的非数字字符的数据行过滤掉。同时外部查询也存在谓词:
a.test = 2013,由于test是字符类型,则运行时ORACLE自动将此谓词转换为如下的形式:

to_number(a.test)=2013;

由于查询运行时自动将子查询并入外部查询,子查询的谓词和外部查询的谓词在同一层次的查询中,变为如下的形式:

select substr(a034, 1, 4) test
from a_20070829
where reportid = 19
and a001 is not null) a
and a.test = 2013

此时做全表扫描,若先使用谓词a.test = 2013,则对于a034中的非数字字符的数据行时,会产生错误。
若先使用其它的两个谓词的话,则可将a034中的非数字字符的数据行过滤。

使用索引时,索引可将这些数据行过滤。

若使用RBO时,则据规则先使用其它两个谓词

若使用CBO时,CBO据成本来决定首先使用哪个谓词。


solution:

1.增加索引;
2.增加提示 rule;
3.删除表分析。
4.使用提示no_merge---使用子查询单独先执行不并入外部查询,从而过滤掉非法的数据。

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

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2018-09-17

  • 博文量
    18
  • 访问量
    17088