I have have two complex, expensive queries that I have performed a union all to bring together for use in a report tool
I have a bind variable that, if set to one value, the union query should execute the first part of the union. If the bind variable is set to a second value, then the second part of the union query should be executed.
However, when I look at trace and/or explain plans for my query, there is a cost coming from BOTH parts of the union query.
Here is a simplified version of what I am trying to do :
create table t1
as
select 'T1' as ttype, 'Gadget 1' as descr from dual
union all
select 'T2' as ttype, 'Gadget 2' as descr from dual
union all
select 'T3' as ttype, 'Gadget 3' as descr from dual;
create table t2
as
select 'T21' as ttype, 'Widget 1' as descr from dual
union all
select 'T22' as ttype, 'Widget 2' as descr from dual
union all
select 'T23' as ttype, 'Widget 3' as descr from dual;
3' as ttype, 'Widget 3' as descr from dual;
And then my query is :
select ttype, descr from t1
where :P_Type='1'
union all
select ttype, descr from t2
where :P_Type='2';
I would expect that if I provide bind value of '1' for P_Type, then the CBO / query would only actually access table t1 (with zero cardinality / cost for table t2).
However, what I see is a cost & cardinality coming from BOTH tables, regardless of the value I assign to the bind variable
How can I force the CBO / database to "prune" those parts of the query where the condition / bind value indicates that part of the union query is irrelevant ?
I do not want to split it into two different queries, as there is a lot of formatting on the report, which I would need to replicate for the two queries.
Many thanks in advance for your feedback ...
Oracle Database Enterprise Edition 12.1.0.2 with SQL Tuning option
Are you looking at the
estimated rows in the
explain plan, or the
actual rows in an
execution plan?
Remember that explain plan costs and cardinalities are estimates. These are generated with no knowledge of what the bind values are.
Bind variable peeking allows the optimizer to adapt the plan based on the inputs. But in this case it doesn't matter. The plan is the same whatever value you assign to :P_Type.
For example, in the execution plan below, notice how starts and A-rows are both zero for T2.
The filter operations at steps 2 & 4 short-circuit processing. The database only does the full scans below them if the condition is true:
create table t1 as
select 'T1' as ttype, 'Gadget 1' as descr from dual
union all
select 'T2' as ttype, 'Gadget 2' as descr from dual
union all
select 'T3' as ttype, 'Gadget 3' as descr from dual;
create table t2 as
select 'T21' as ttype, 'Widget 1' as descr from dual
union all
select 'T22' as ttype, 'Widget 2' as descr from dual
union all
select 'T23' as ttype, 'Widget 3' as descr from dual;
set serveroutput off
var p_type varchar2(1);
exec :p_type := '1';
select /*+ gather_plan_statistics */ttype, descr from t1
where :P_Type='1'
union all
select ttype, descr from t2
where :P_Type='2';
select *
from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST +COST'));
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (100)| 3 |
| 1 | UNION-ALL | | 1 | | | 3 |
|* 2 | FILTER | | 1 | | | 3 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 3 | 2 (0)| 3 |
|* 4 | FILTER | | 1 | | | 0 |
| 5 | TABLE ACCESS FULL| T2 | 0 | 3 | 2 (0)| 0 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:P_TYPE='1')
4 - filter(:P_TYPE='2') So it has pruned the execution of the query!
Remember: to evaluate a plan, you must look at the actual work it does.