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