Version 11.2.0.3
I am attempting to create a view which refers to two identical tables. The results should only ever be presented from one of the tables based on a SYS_CONTEXT variable.
The issue I am seeing however is that the execution plan differs when I use literals as opposed to a SYS_CONTEXT variable. The plan with literals is what I expect to see when I use SYS_CONTEXT as well. The difference between the 2 plans is that the filter(NULL IS NOT NULL) filter appears for the SELECT which is not run. The following illustrates the problem:
create table tbl1(id number,date_created date);
create table tbl2(id number,date_created date);
insert into tbl1 values (1, to_date('20140114','yyyymmdd'));
insert into tbl2 values (2, to_date('20140115','yyyymmdd'));
insert into tbl2 values (2, to_date('20140115','yyyymmdd'));
COMMIT;
create or replace context tbl_switch using set_tbl_context;
create or replace procedure Set_Tbl_Context (p_Tbl_No in varchar2) as
begin
dbms_session.set_context('TBL_SWITCH', 'SWITCH_VALUE', p_Tbl_No);
end Set_Tbl_Context;
/
SQL> exec set_tbl_context('2');
PL/SQL procedure successfully completed.
SQL> select sys_context('TBL_SWITCH','SWITCH_VALUE') FROM DUAL;
SYS_CONTEXT('TBL_SWITCH','SWITCH_VALUE')
----------------------------------------
2
SQL> set autotrace on
SQL> select * from tbl1 where 1 = 2
2 union all
3 select * from tbl2 where 2 = 2
4 /
ID DATE_CREA
---------- ---------
2 15-JAN-14
2 15-JAN-14
Execution Plan
--------------
Plan hash value: 6123662
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 66 | 3 (100)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL| TBL1 | 1 | 22 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TBL2 | 2 | 44 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NULL IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------
16 recursive calls
0 db block gets
36 consistent gets
0 physical reads
0 redo size
436 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> select * from tbl1
2 where '1'=sys_context('TBL_SWITCH','SWITCH_VALUE')
3 union all
4 select * from tbl2
5 where '2'=sys_context('TBL_SWITCH','SWITCH_VALUE')
6 /
ID DATE_CREA
---------- ---------
2 15-JAN-14
2 15-JAN-14
Execution Plan
--------------
Plan hash value: 687287648
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 66 | 6 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL| TBL1 | 1 | 22 | 3 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS FULL| TBL2 | 2 | 44 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SYS_CONTEXT('TBL_SWITCH','SWITCH_VALUE')='1')
4 - filter(SYS_CONTEXT('TBL_SWITCH','SWITCH_VALUE')='2')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------
7 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
436 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
Can anybody suggest why this is ? Furthermore, does the query with the SYS_CONTEXT variable execute both SELECT's ?
Thanks