一个和子查询,视图有关的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---使用子查询单独先执行不并入外部查询,从而过滤掉非法的数据。
主要还是怕文章吞了,就全部复制过来,要不然没了就.
子查询的谓词外层查询同样出现的话,就会被oracle优化掉,将查询条件全部的凭借起来,直接做一个查询,
案例,我查询一张表的某一个字段,但是这个字段中,本来应该全部是数字,但是不巧有一些中文,我就不能愉快地使用to_number 函数,所以我做了一个子查询,先过滤掉中文,然后进行to_number操作.,.但是实际上 这种才做并不起作用,因为oracle把你的子查询又花掉了,尽管我认为这种优化很蠢,但是没有办法.
上述的文章中有一种操作是使用no_merge,就是强制oracle 先进行子查询,在进行外层查询,但是我这张表具有上千万数据,这种操作会很大的影响我的效率,这里推荐使用case when .
SELECT case
when GRID.RSRP_AVG_W = '\N' then
-1000
when GRID.RSRP_AVG_W is null then
-1000
else
-900
end we
FROM NECUR_CELL_L_MR_tp NECUR
LEFT JOIN LTE_MRO_GRID_CELL_w PARTITION(P20200406) GRID
ON NECUR.OID = GRID.OID
WHERE NECUR.OID in ('LTE.734062.11')