谓词推入导致的数据不一致案例
程序员文章站
2022-12-21 18:15:34
现象 下面的语句, 能查到deviceid 为DEV005M0的记录, 但是加上deviceid = 'DEV005M0'这个条件后, 查询语句查不出结果了。 语句如下: 当注释掉deviceid = 'DEV005M0', 查询结果如下: 当增加deviceid = 'DEV005M0',查询结果 ......
现象
下面的语句, 能查到deviceid 为dev005m0的记录, 但是加上deviceid = 'dev005m0'这个条件后, 查询语句查不出结果了。
语句如下:
select * from ( select deviceid deviceid, port, cvlan, status, funcswitch, decode(funcswitch, 3, pvlan, 1, pvlan) svlan, decode(funcswitch, 3, pvlan, 1, lead(pvlan)over(partition by deviceid, port, cvlan, status order by pvlan asc)) evlan from vlanstatus_pre2bak ) where funcswitch <> 2 and evlan is null -- and deviceid = 'dev005m0';
当注释掉deviceid = 'dev005m0', 查询结果如下:
当增加deviceid = 'dev005m0',查询结果没有记录:
deviceid的数据类型为char(8), vlanstatus_pre2bak的deviceid数据也没有空格等特殊字符, 非常诡异。下面来具体分析。
分析如下
1. 查询语句, 查询没有记录
select * from ( select deviceid deviceid, port, cvlan, status, funcswitch, decode(funcswitch, 3, pvlan, 1, pvlan) svlan, decode(funcswitch, 3, pvlan, 1, lead(pvlan)over(partition by deviceid, port, cvlan, status order by pvlan asc)) evlan from vlanstatus_pre2bak ) where funcswitch <> 2 and evlan is null and deviceid = 'dev005m0' ;
或者:
with tmptab as ( select deviceid deviceid, port, cvlan, status, funcswitch, decode(funcswitch, 3, pvlan, 1, pvlan) svlan, decode(funcswitch, 3, pvlan, 1, lead(pvlan)over(partition by deviceid, port, cvlan, status order by pvlan asc)) evlan from vlanstatus_pre2bak ) select * from tmptab where funcswitch <> 2 and evlan is null and deviceid = 'dev005m0' ;
查看执行计划:
sql> set lines 200 sql> set pages 200 sql> explain plan for 2 select * from 3 ( select deviceid deviceid, port, cvlan, status, funcswitch, 4 decode(funcswitch, 3, pvlan, 1, pvlan) svlan, 5 decode(funcswitch, 3, pvlan, 1, 6 lead(pvlan)over(partition by deviceid, port, cvlan, status order by pvlan asc)) evlan 7 from vlanstatus_pre2bak 8 ) 9 where funcswitch <> 2 and evlan is null and deviceid = 'dev005m0' ; 已解释。 sql> select * from table(dbms_xplan.display); plan_table_output ---------------------------------------------------------------------------------------------------- plan hash value: 2175325539 ------------------------------------------------------------------------------------------ | id | operation | name | rows | bytes | cost (%cpu)| time | ------------------------------------------------------------------------------------------ | 0 | select statement | | 70 | 49350 | 692 (1)| 00:00:09 | |* 1 | view | | 70 | 49350 | 692 (1)| 00:00:09 | | 2 | window sort | | 70 | 3430 | 692 (1)| 00:00:09 | |* 3 | table access full| vlanstatus_pre2bak | 70 | 3430 | 691 (1)| 00:00:09 | ------------------------------------------------------------------------------------------ predicate information (identified by operation id): --------------------------------------------------- 1 - filter("funcswitch"<>2 and "evlan" is null) 3 - filter("deviceid"='dev005m0') 已选择16行。 sql>
查看id为3的谓词过滤(3 - filter("deviceid"='dev005m0')), 说明先在表vlanstatus_pre2bak执行了deviceid = 'dev005m0'操作。id为1的谓词过滤只有两个(1 - filter("funcswitch"<>2 and "evlan" is null)),说明这个查询语句先在子查询里面过滤了deviceid = 'dev005m0'的记录,然后在整个查询视图执行过滤条件funcswitch<>2 and evlan is null。这个现象就是谓词推入。
2. 使用materialize的hint避免谓词推入
with tmptab as ( select /*+materialize*/ deviceid deviceid, port, cvlan, status, funcswitch, decode(funcswitch, 3, pvlan, 1, pvlan) svlan, decode(funcswitch, 3, pvlan, 1, lead(pvlan)over(partition by deviceid, port, cvlan, status order by pvlan asc)) evlan from vlanstatus_pre2bak ) select * from tmptab where funcswitch <> 2 and evlan is null and deviceid = 'dev005m0' ;
sql> set lines 200 sql> set pages 200 sql> explain plan for 2 with tmptab as 3 ( 4 select /*+materialize*/ deviceid deviceid, port, cvlan, status, funcswitch, 5 decode(funcswitch, 3, pvlan, 1, pvlan) svlan, 6 decode(funcswitch, 3, pvlan, 1, 7 lead(pvlan)over(partition by deviceid, port, cvlan, status order by pvlan asc)) evlan 8 from vlanstatus_pre2bak 9 ) 10 select * from tmptab 11 where funcswitch <> 2 and evlan is null and deviceid = 'dev005m0' ; 已解释。 sql> select * from table(dbms_xplan.display); plan_table_output ---------------------------------------------------------------------------------------------------- plan hash value: 1444871666 ---------------------------------------------------------------------------------------------------- | id | operation | name | rows | bytes |tempspc| cost (%cpu)| time | ---------------------------------------------------------------------------------------------------- | 0 | select statement | | 313k| 210m| | 5062 (1)| 00:01:01 | | 1 | temp table transformation | | | | | | | | 2 | load as select | sys_temp_0fd9d66ce_df9dbbfb | | | | | | | 3 | window sort | | 313k| 14m| 20m| 4492 (1)| 00:00:54 | | 4 | table access full | vlanstatus_pre2bak | 313k| 14m| | 690 (1)| 00:00:09 | |* 5 | view | | 313k| 210m| | 570 (1)| 00:00:07 | | 6 | table access full | sys_temp_0fd9d66ce_df9dbbfb | 313k| 14m| | 570 (1)| 00:0 ---------------------------------------------------------------------------------------------------- predicate information (identified by operation id): --------------------------------------------------- 5 - filter("funcswitch"<>2 and "evlan" is null and "deviceid"='dev005m0') 已选择18行。 sql>
查看id为5的谓词过滤条件, 三个条件都是发生在视图view上面。
3. 使用trim或者拼接一个空的字符
select * from ( select trim(deviceid) deviceid, port, cvlan, status, funcswitch, decode(funcswitch, 3, pvlan, 1, pvlan) svlan, decode(funcswitch, 3, pvlan, 1, lead(pvlan)over(partition by deviceid, port, cvlan, status order by pvlan asc)) evlan from vlanstatus_pre2bak ) where funcswitch <> 2 and evlan is null and deviceid = 'dev005m0' ;
查看执行计划:
sql> set lines 200 sql> set lines 300 sql> set pages 200 sql> explain plan for 2 select * from 3 ( select trim(deviceid) deviceid, port, cvlan, status, funcswitch, 4 decode(funcswitch, 3, pvlan, 1, pvlan) svlan, 5 decode(funcswitch, 3, pvlan, 1, 6 lead(pvlan)over(partition by deviceid, port, cvlan, status order by pvlan asc)) evlan 7 from vlanstatus_pre2bak 8 ) 9 where funcswitch <> 2 and evlan is null and deviceid = 'dev005m0'; 已解释。 sql> select * from table(dbms_xplan.display); plan_table_output ---------------------------------------------------------------------------------------------------- plan hash value: 2175325539 -------------------------------------------------------------------------------------------------- | id | operation | name | rows | bytes |tempspc| cost (%cpu)| time | -------------------------------------------------------------------------------------------------- | 0 | select statement | | 313k| 209m| | 4492 (1)| 00:00:54 | |* 1 | view | | 313k| 209m| | 4492 (1)| 00:00:54 | | 2 | window sort | | 313k| 14m| 20m| 4492 (1)| 00:00:54 | | 3 | table access full| vlanstatus_pre2bak | 313k| 14m| | 690 (1)| 00:00:09 | -------------------------------------------------------------------------------------------------- predicate information (identified by operation id): --------------------------------------------------- 1 - filter("funcswitch"<>2 and "evlan" is null and "deviceid"='dev005m0') 已选择15行。 sql>
查看谓词过滤, 三个过滤条件都发生在id为1的view上面。
小海蟹出品
推荐阅读