Are materialize hint and predicate pushing mutually exclusive?
1. Example with clause where predicate is pushed all the way down, but employees table is accessed twice:
select * from (
with q as (select * from employees )
select * from q
union all
select * from q
)
where department_id=10
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1200 | 2 (0)| 00:00:01 |
| 1 | VIEW | | 6 | 1200 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 65 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 65 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEES"."DEPARTMENT_ID"=10)
6 - access("EMPLOYEES"."DEPARTMENT_ID"=10)
19 rows selected
2. Example with clause where predicate is not pushed, but employees table is accessed once:
select * from (
with q as (select /*+materialize*/ * from employees )
select * from q
union all
select * from q
)
where department_id=10
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 42 | 11004 | 4 (0)| 00:00:01 |
| 1 | VIEW | | 42 | 11004 | 4 (0)| 00:00:01 |
| 2 | TEMP TABLE TRANSFORMATION | | | | | |
| 3 | LOAD AS SELECT | | | | | |
| 4 | TABLE ACCESS FULL | EMPLOYEES | 21 | 1365 | 3 (0)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
|* 6 | VIEW | | 21 | 5502 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9E6735_58A979A | 21 | 1365 | 2 (0)| 00:00:01 |
|* 8 | VIEW | | 21 | 5502 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9E6735_58A979A | 21 | 1365 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("Q"."DEPARTMENT_ID"=10)
8 - filter("Q"."DEPARTMENT_ID"=10)
3. Desired execution: predicate is pushed and employees table is accessed once.
Since in my situation this whole query is in a view, i can't put where inside with clause. Is there any other way to push this predicate?
select * from (
with q as (select /*+materialize*/ * from employees where department_id=10 )
select * from q
union all
select * from q
)
where department_id=10
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 524 | 4 (0)| 00:00:01 |
| 1 | VIEW | | 2 | 524 | 4 (0)| 00:00:01 |
| 2 | TEMP TABLE TRANSFORMATION | | | | | |
| 3 | LOAD AS SELECT | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 65 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 1 (0)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
|* 7 | VIEW | | 1 | 262 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9E6737_58A979A | 1 | 65 | 2 (0)| 00:00:01 |
|* 9 | VIEW | | 1 | 262 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9E6737_58A979A | 1 | 65 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPARTMENT_ID"=10)
7 - filter("Q"."DEPARTMENT_ID"=10)
9 - filter("Q"."DEPARTMENT_ID"=10)