phenomenon
The following statement can find the record of deviceid DEV005M0, but with the condition of deviceid ='DEV005M0', the query statement can not find the result.
Statements are as follows:
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';When the deviceid ='DEV005M0'is commented out, the query results are as follows:
When deviceid = DEV005M0 is added, the query result is not recorded:
The data type of deviceid is char (8). The deviceid data of vlanstatus_pre2bak has no special characters such as spaces, which is very weird. Next comes the concrete analysis.
The analysis is as follows
1. Query statement, query not recorded
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' ;Or:
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' ;View the implementation plan:
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' ;
//It has been explained.
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')
//Sixteen rows have been selected.
SQL> Look at the predicate filter with ID 3 (3 - filter("DEVICEID"='DEV005M0'), indicating that the deviceid ='DEV005M0'operation was performed first in table VLANSTATUS_PRE2BAK. There are only two predicate filters with ID 1 (1 - filter ("FUNCSWITCH"<> 2 AND "EVLAN" IS NULL). This indicates that the query first filters the record of deviceid ='DEV005M0'in the sub-query, and then performs the filtering condition FUNCSWITCH<> 2 AND EVLAN IS NULL in the whole query view. This phenomenon is predicate pushing.
2. Use materialize hint to avoid predicate pushing
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' ;
//It has been explained.
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')
//Eighteen rows have been selected.
SQL> Look at the predicate filtering condition with ID 5, all three of which occur on the view view.
3. Use trim or stitch an empty character
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' ;
View the implementation plan:
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';
//It has been explained.
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')
//Fifteen rows have been selected.
SQL> View predicate filtering, all three filtering conditions occur on view with ID 1.
Small crab