现象

下面的语句， 能查到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