Yesterday, I came across an extremely odd problem with a view that I had created. The problem I had was that I was joining the view to another table, based on the primary key of the main table in the view, yet it was doing a FTS of that table and then doing the filtering.
This made the query take ~18 minutes, yet I was convinced that it ought to be able to push the filtering inside the view. Nothing I tried would get the filtering to take place in the view, not push_pred or no_merge or no_unnest hint; nothing.
It was actually a conversation with Doug Burns about the trace file I generated that spat out a clue – thanks Doug!
I’ve managed to recreate the issue, so here goes:
create table t1 as
select level col1,
dbms_random.string('A', 5) col2,
mod(level, 20) col3
from dual
connect by level <= 10000;
alter table t1 add constraint t1_pk primary key (col1);
create table t2 as
select level col1,
ceil(level/2) col2,
dbms_random.string('A', 5) col3
from dual
connect by level <= 20000;
alter table t2 add constraint t2_pk primary key (col1);
alter table t2 add constraint t2_t1_fk foreign key (col2) references t1 (col1);
create table t3 as
select level col1,
level * 2 col2
from dual
connect by level <= 19;
alter table t3 add constraint t3_pk primary key (col1);
begin
dbms_stats.gather_table_stats(user, 'T1');
dbms_stats.gather_table_stats(user, 'T2');
dbms_stats.gather_table_stats(user, 'T3');
end;
create view t1_t3_view
as
with t3a as (select col1,
decode(col2, 4, 90, 8, 45, col2) col2
from t3)
select t1.col1,
t1.col2,
t1.col3,
t3a.col2 t3_col2
from t1,
t3a
where t1.col3 = t3a.col1 (+);
The following query does push the filtering into the view (see the use of the T1_PK index):
select *
from t1_t3_view
where col1 in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID ca9pw03r3tua1, child number 0
-------------------------------------
select * from t1_t3_view where col1 in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
Plan hash value: 1448310819
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | VIEW | T1_T3_VIEW | 10 | 20410 | 10 (0)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 10 | 180 | 10 (0)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 120 | 7 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | T1_PK | 10 | | 6 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T3 | 19 | 114 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
2 - access("T1"."COL3"="COL1")
5 - access(("T1"."COL1"=1 OR "T1"."COL1"=2 OR "T1"."COL1"=3 OR "T1"."COL1"=4 OR
"T1"."COL1"=5 OR "T1"."COL1"=6 OR "T1"."COL1"=7 OR "T1"."COL1"=8 OR "T1"."COL1"=9 OR
"T1"."COL1"=10)
whereas the following query does not (note the FTS on T1):
select *
from t1_t3_view
where col1 in (select col2 from t2
where col1 <= 10);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID 95rxxkfy26rg3, child number 0
-------------------------------------
select * from t1_t3_view where col1 in (select col2 from t2
where col1 <= 10)
Plan hash value: 637444478
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15 (100)| |
|* 1 | HASH JOIN RIGHT SEMI | | 10 | 20500 | 15 (7)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 90 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T2_PK | 10 | | 2 (0)| 00:00:01 |
| 4 | VIEW | T1_T3_VIEW | 10000 | 19M| 12 (9)| 00:00:01 |
|* 5 | HASH JOIN RIGHT OUTER | | 10000 | 175K| 12 (9)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T3 | 19 | 114 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T1 | 10000 | 117K| 8 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
1 - access("COL1"="COL2")
3 - access("COL1"<=10)
5 - access("T1"."COL3"="COL1")
After much scratching of my head (and not a small amount of swearing!) I eventually worked out how to do a 10053 trace on the query. The following is what Doug picked up on (and to be fair, so had I, only I hadn't got round to doing anything about it aside from making a mental note of it):
************************** Predicate Move-Around (PM) ************************** PM: Considering predicate move-around in SEL$1 (#0). PM: Checking validity of predicate move-around in SEL$1 (#0). CBQT: copy not possible because linked to with clause in SEL$3 (#0) CBQT: copy not possible because view in SEL$1 (#0) CBQT bypassed for SEL$1 (#0): Cannot copy query block. CBQT: Validity checks failed for 95rxxkfy26rg3.
If I change the view so that the subquery is an in-line view, bingo!:
create view t1_t3_view2
as
select t1.col1,
t1.col2,
t1.col3,
t3a.col2 t3_col2
from t1,
(select col1,
decode(col2, 4, 90, 8, 45, col2) col2
from t3) t3a
where t1.col3 = t3a.col1 (+);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID 9jj0atfw7050d, child number 0
-------------------------------------
select * from t1_t3_view2 where col1 in (select col2 from t2
where col1 <= 10)
Plan hash value: 2918082440
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 13 (100)| |
|* 1 | HASH JOIN OUTER | | 10 | 270 | 13 (16)| 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 210 | 9 (12)| 00:00:01 |
| 3 | SORT UNIQUE | | 10 | 90 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 90 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_PK | 10 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 12 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | T1_PK | 1 | | 0 (0)| |
| 8 | TABLE ACCESS FULL | T3 | 19 | 114 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL3"="COL1")
5 - access("COL1"<=10)
7 - access("T1"."COL1"="COL2")
So one place where a WITH clause may cause problems and, much as I love it, I will now steer clear of WITH clauses in views until I know the issue has been fixed! (Not that I work with views that often; I'm only doing so here due to the fact we're on 10g, there's a join I need to add into a dynamic sql and the 32767 character limit EXECUTE IMMEDIATE has. I can't wait for 11g where EXECUTE IMMEDIATE works with CLOBs!)
Leave a Comment