今天同事发给我一个sql,说查询不到结果,sql本身没有错误。
而且在其他服务器上执行可以得到结果。
表结构:
SQL> desc plt_plat
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NOT NULL CHAR(24)
PLAT_FATHER CHAR(24)
PLAT_CLASS CHAR(1)
PLAT_GRADE NUMBER(2)
PLAT_NAME NOT NULL VARCHAR2(30)
PLAT_LEVEL VARCHAR2(10)
PLAT_DESC VARCHAR2(500)
PLAT_ROOT CHAR(24)
PLAT_CODE VARCHAR2(30)
查询语句
select pp.id,pp.plat_name,pp.plat_class,city.id,city.plat_name,city.plat_class
,province.id,province.plat_name,province.plat_class
from plt_plat pp,
plt_plat city,
plt_plat province
where pp.plat_father=city.id
and city.plat_father=province.id
and pp.plat_class=4
由于这张表是物化视图复制生成的,首先我检查了对象的状态,然后检查了物化视图的脚本,并重新刷新了这张物化视图。错误依旧。
和其他服务器上的表进行表结构的对比。没有发现错。
检查表中的数据,发现和其他服务器上的完全一致。
analyze table plt_plat validate structure cascade
未发现异常。
怀疑是否是错误的统计信息造成的
analyze table plt_plat delete statistics;
结果发现得到了正确的结果。
然后重新analyze table plt_plat compute statistics
错误又出现了。
怀疑和执行路径有关
set autot on
SQL> select pp.id,pp.plat_name,pp.plat_class,city.id,city.plat_name,city.plat_class
2 ,province.id,province.plat_name,province.plat_class
3 from plt_plat pp,
4 plt_plat city,
5 plt_plat province
6 where pp.plat_father=city.id
7 and city.plat_father=province.id
8 and pp.plat_class=4
9 ;
no rows selected
Elapsed: 00:00:00.32
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=132)
1 0 HASH JOIN (Cost=7 Card=1 Bytes=132)
2 1 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=1 Bytes=72)
3 1 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=330 Bytes=19800)
奇怪,怎么三张表的连接,执行计划里面只有两张表。
SQL> select pp.id,pp.plat_name,pp.plat_class,city.id,city.plat_name,city.plat_class
2 ,province.id,province.plat_name,province.plat_class
3 from plt_plat pp,
4 plt_plat city,
5 plt_plat province
6 where pp.plat_father=city.id
7 and city.plat_father=province.id
8 and pp.plat_class=4
9 ;
149 rows selected.
Elapsed: 00:00:00.32
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'PLT_PLAT'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT'
5 4 INDEX (UNIQUE SCAN) OF 'PK_PLT_PLAT' (UNIQUE)
6 1 TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT'
7 6 INDEX (UNIQUE SCAN) OF 'PK_PLT_PLAT' (UNIQUE)
而用rule模式,执行计划是正确的。
下面是在其他服务器上相同表(也是通过物化视图复制生成的)上执行查询的结果。
SQL> select pp.id,pp.plat_name,pp.plat_class,city.id,city.plat_name,city.plat_class
2 ,province.id,province.plat_name,province.plat_class
3 from plt_plat pp,
4 plt_plat city,
5 plt_plat province
6 where pp.plat_father=city.id
7 and city.plat_father=province.id
8 and pp.plat_class=4
9 ;
149 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=76 Bytes=12692)
1 0 HASH JOIN (Cost=8 Card=76 Bytes=12692)
2 1 HASH JOIN (Cost=5 Card=76 Bytes=9728)
3 2 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=2 Card=76 Bytes=4864)
4 2 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=2 Card=304 Bytes=19456)
5 1 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=2 Card=304 Bytes=11856)
错误已经很明显了,oracle的执行计划出错。我估计删除这张表后,通过物化视图重建,错误就应该解决了,但是错误的产生原因还不清楚。