欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

谓词推入导致的数据不一致案例

程序员文章站 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上面。
小海蟹出品