Hi All,
I have a little difficult question about Query Transformation and FPD.
FPD(Filter Push Down).
※Create sample record(ver11.2.0.1.0)
drop table t1 purge;
create table t1
as
select mod(level, 4) as c1,
level as c2,
chr(ascii('A')+level-1) as c3
from dual
connect by level<=5;
create index t1_x01 on t1(c2, c1);
exec dbms_stats.gather_table_stats(user,'t1');
explain plan for
select *
from
(
select /*+ no_merge */
*
from t1
where c2>=3
) v1
where v1.c1=v1.c2;
select * from table(dbms_xplan.display);
-----------------------------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | VIEW | |
| 2 | TABLE ACCESS BY INDEX ROWID | T1 |
|* 3 | INDEX RANGE SCAN | T1_X01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C2">=3 AND "T1"."C1">=3 AND "C2" IS NOT NULL)
filter("T1"."C1">=3 AND "T1"."C1"="T1"."C2")
Predicate like this,
Id3 c2>=3
c1=v1.c2
to be same step.
I want to control execution plan to be diffent step.
for example,
Id1 c1=v1.c2
Id3 c2>=3
I checked that FPD was working in 10053 trace file.
alter session set tracefile_identifier='SQL1_53';
alter session set events '10053 trace name context forever, level 1';
select *
from
(
select /*+ no_merge */
*
from t1
where c2>=3
) v1
where v1.c1=v1.c2;
alter session set events '10053 trace name context off';
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "V1"."C1"="V1"."C2"
JPPD: JPPD bypassed: View not on right-side of outer-join.
FPD: Following are pushed to where clause of query block SEL$2 (#0)
"T1"."C1"="T1"."C2"
FPD: Considering simple filter push in query block SEL$2 (#0)
"T1"."C2">=3 AND "T1"."C1">=3 AND "T1"."C1"="T1"."C2"
try to generate transitive predicate from check constraints for query block SEL$2 (#0)
finally: "T1"."C2">=3 AND "T1"."C1">=3 AND "T1"."C1"="T1"."C2"
I can prevent FPD'working like this,
and rownum>=0
I added useless condition rownum>=0 in the view V1.
explain plan for
select *
from
(
select /*+ no_merge */
*
from t1
where c2>=3
and rownum>=0
) v1
where v1.c1=v1.c2;
select * from table(dbms_xplan.display);
--------------------------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | VIEW | |
| 2 | COUNT | |
|* 3 | FILTER | |
| 4 | TABLE ACCESS BY INDEX ROWID | T1 |
|* 5 | INDEX RANGE SCAN | T1_X01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V1"."C1"="V1"."C2")
3 - filter(ROWNUM>=0)
5 - access("C2">=3 AND "C2" IS NOT NULL)
Execution plan was showed predicates different step.
Id1 c2>=3
Id5 c1=v1.c2
I want to control that FPD is working with only hints.
Every below hints was failed prventing that FPD is working.
explain plan for
select *
from
(
select /*+
NO_MERGE no_push_pred
OPT_PARAM('_optimizer_cost_based_transformation' 'off')
OPT_PARAM('_push_join_predicate' 'false')
OPT_PARAM('_pred_move_around' 'false')
OPT_PARAM('_optimizer_push_pred_cost_based' 'false')
OPT_PARAM('_simple_view_merging' 'false')
OPT_PARAM('_optimizer_enhanced_filter_push' 'false' )
OPT_PARAM('_optimizer_filter_pred_pullup' 'false')
*/
*
from t1
where c2>=3
) v1
where v1.c1=v1.c2;
select * from table(dbms_xplan.display(null,null,'advanced -projection'));
------------------------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------------------------
0 | SELECT STATEMENT | |
| 1 | VIEW | |
| 2 | TABLE ACCESS BY INDEX ROWID | T1 |
|* 3 | INDEX RANGE SCAN | T1_X01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2 / V1@SEL$1
2 - SEL$2 / T1@SEL$2
3 - SEL$2 / T1@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$2" "T1"@"SEL$2" ("T1"."C2" "T1"."C1"))
NO_ACCESS(@"SEL$1" "V1"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
ALL_ROWS
OPT_PARAM('_optimizer_filter_pred_pullup' 'false')
OPT_PARAM('_optimizer_enhanced_filter_push' 'false')
OPT_PARAM('_optimizer_push_pred_cost_based' 'false')
OPT_PARAM('_optimizer_cost_based_transformation' 'off')
OPT_PARAM('_push_join_predicate' 'false')
OPT_PARAM('_pred_move_around' 'false')
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C2">=3 AND "T1"."C1">=3 AND "C2" IS NOT NULL)
filter("T1"."C1"="T1"."C2" AND "T1"."C1">=3)
Regards Eqon