Exclusion of Unioned SQL in Views – Followup August 20, 2012
Posted by mwidlake in database design, performance, SQL.Tags: design, performance, SQL
trackback
Last week I put up a post about how Oracle can filter out sections of a union view..
Within the comments I put up another example where the CBO did not filter out all but one of the Union views despite my replicating the exact WHERE clause of one of the unioned statements. Jonathan Lewis posted a followup to say “Oracle can be very touchy about how it plays this game” and made a prediction of how the CBO would handle a slightly different scenario.
This was the extra scenario and I include brief details on creating the unioned view too. NB all on Oracle 11.2.0.2. {non-Exadata 🙂 }
1 create table mdw_source 2 tablespace users 3 as select * from dba_objects 1 create or replace view mdw_v1 as 2 select 3 '1' src_type 4 ,ms.OWNER ,ms.OBJECT_NAME,ms.SUBOBJECT_NAME ,ms.CREATED 5 from mdw_source ms 6 where ms.object_name like 'A%' 7 union all 8 select 9 '2' src_type 10 ,ms.OWNER ,ms.OBJECT_NAME,ms.SUBOBJECT_NAME ,ms.CREATED 11 from mdw_source ms 12 where ms.object_name like 'B%' 13 UNION ALL 14 select 15 '3' src_type 16 ,ms.OWNER ,ms.OBJECT_NAME,ms.SUBOBJECT_NAME ,ms.CREATED 17 from mdw_source ms 18 where ms.object_name like 'C%' 1 select count(created) 2 from mdw_v1 mv 3 where mv.object_name like ‘C%’ Execution Plan ———————————————————- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ———————————————————————————– | 0 | SELECT STATEMENT | | 1 | 75 | 551 (1)| 00:00:07 | | 1 | SORT AGGREGATE | | 1 | 75 | | | | 2 | VIEW | MDW_V1 | 824 | 61800 | 551 (1)| 00:00:07 | | 3 | UNION-ALL | | | | | | |* 4 | TABLE ACCESS FULL| MDW_SOURCE | 1 | 34 | 184 (1)| 00:00:03 | |* 5 | TABLE ACCESS FULL| MDW_SOURCE | 2 | 68 | 184 (1)| 00:00:03 | |* 6 | TABLE ACCESS FULL| MDW_SOURCE | 821 | 27914 | 184 (1)| 00:00:03 | ———————————————————————————– Predicate Information (identified by operation id): ————————————————— 4 – filter(“MS”.”OBJECT_NAME” LIKE ‘A%’ AND “MS”.”OBJECT_NAME” LIKE ‘C%’) 5 – filter(“MS”.”OBJECT_NAME” LIKE ‘B%’ AND “MS”.”OBJECT_NAME” LIKE ‘C%’) 6 – filter(“MS”.”OBJECT_NAME” LIKE ‘C%’) Statistics ———————————————————- 8 recursive calls 0 db block gets 2417 consistent gets
So, as you can see, despite me providing the WHERE clause in my final select as “where mv.object_name like ‘C%’” and the WHERE clauses of the unioned view are all similar and one is “ms.object_name like ‘C%’” the CBO fails to realise it can exclude all but one of the unioned SQL statements – despite the mutally exclusive filter predicates:
4 – filter(“MS”.”OBJECT_NAME” LIKE ‘A%’ AND “MS”.”OBJECT_NAME” LIKE ‘C%’)
5 – filter(“MS”.”OBJECT_NAME” LIKE ‘B%’ AND “MS”.”OBJECT_NAME” LIKE ‘C%’)
I have to confess, in light of recent experience I would have wondered if these filter predicates would prevent the other two SQL statements being fired at execution time but the number of consistent gets confirms that it is not – 2417 compared to 805 or so for when the query exclusion occurs. You can check back to the prior post to verify that.
So, let’s do the tests Jonathan suggested. He suggested altering the view to use equality predicates so I have recreated the view as such:
TEST_DB> create or replace view mdw_v1 as
2 select
3 '1' src_type
4 ,ms.OWNER ,ms.OBJECT_NAME ,ms.SUBOBJECT_NAME ,ms.CREATED
5 from mdw_source ms
6 where substr(ms.object_name,1,1) ='A'
7 union all
8 select
9 '2' src_type
10 ,ms.OWNER ,ms.OBJECT_NAME ,ms.SUBOBJECT_NAME ,ms.CREATED
11 from mdw_source ms
12 where substr(ms.object_name,1,1) ='B'
13 UNION ALL
14 select
15 '3' src_type
16 ,ms.OWNER ,ms.OBJECT_NAME ,ms.SUBOBJECT_NAME ,ms.CREATED
17 from mdw_source ms
18 where substr(ms.object_name,1,1) ='C'
View created.
TEST_DB> set autotrace on
TEST_DB> --
TEST_DB> -- below will do a select of a non-indexed column across the view
TEST_DB> select count(created) from mdw_v1
COUNT(CREATED)
--------------
1288
Execution Plan
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 552 (1)| 00:00:07 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | VIEW | MDW_V1 | 1677 | 15093 | 552 (1)| 00:00:07 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL| MDW_SOURCE | 559 | 19006 | 184 (1)| 00:00:03 |
|* 5 | TABLE ACCESS FULL| MDW_SOURCE | 559 | 19006 | 184 (1)| 00:00:03 |
|* 6 | TABLE ACCESS FULL| MDW_SOURCE | 559 | 19006 | 184 (1)| 00:00:03 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='A')
5 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='B')
6 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='C')
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
2417 consistent gets
TEST_DB> -- below will do a select of a non-indexed column
TEST_DB> -- specific to one of the Union's in the view via the src_type
TEST_DB> select count(created)
2 from mdw_v1
3 where src_type='3'
COUNT(CREATED)
--------------
129
Execution Plan
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 184 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
| 2 | VIEW | MDW_V1 | 561 | 6732 | 184 (1)| 00:00:03 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| MDW_SOURCE | 559 | 19006 | 184 (1)| 00:00:03 |
|* 6 | FILTER | | | | | |
|* 7 | TABLE ACCESS FULL| MDW_SOURCE | 559 | 19006 | 184 (1)| 00:00:03 |
|* 8 | TABLE ACCESS FULL | MDW_SOURCE | 559 | 19006 | 184 (1)| 00:00:03 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(NULL IS NOT NULL)
5 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='A')
6 - filter(NULL IS NOT NULL)
7 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='B')
8 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='C')
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
807 consistent gets
TEST_DB> -- below will do a select of a non-indexed column
TEST_DB> -- specific to one of the Union's in the view via matching the where clause
TEST_DB> select count(created)
2 from mdw_v1
3 where substr(object_name,1,1) ='C'
COUNT(CREATED)
--------------
129
Execution Plan
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 75 | 184 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 75 | | |
| 2 | VIEW | MDW_V1 | 561 | 42075 | 184 (1)| 00:00:03 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| MDW_SOURCE | 559 | 19006 | 184 (1)| 00:00:03 |
|* 6 | FILTER | | | | | |
|* 7 | TABLE ACCESS FULL| MDW_SOURCE | 559 | 19006 | 184 (1)| 00:00:03 |
|* 8 | TABLE ACCESS FULL | MDW_SOURCE | 559 | 19006 | 184 (1)| 00:00:03 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(NULL IS NOT NULL)
5 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='A')
6 - filter(NULL IS NOT NULL)
7 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='B')
8 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='C')
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
807 consistent gets
TEST_DB> -- below will do a select of a non-indexed column
TEST_DB> -- specific to one of the Union's in the view via NOT matching the where clause
TEST_DB> -- but maybe logical exclusion can be managed
TEST_DB> select count(created)
2 from mdw_v1
3 where object_name ='C'
COUNT(CREATED)
--------------
0
Execution Plan
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 75 | 551 (1)| 00:00:07 |
| 1 | SORT AGGREGATE | | 1 | 75 | | |
| 2 | VIEW | MDW_V1 | 3 | 225 | 551 (1)| 00:00:07 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL| MDW_SOURCE | 1 | 34 | 184 (1)| 00:00:03 |
|* 5 | TABLE ACCESS FULL| MDW_SOURCE | 1 | 34 | 184 (1)| 00:00:03 |
|* 6 | TABLE ACCESS FULL| MDW_SOURCE | 1 | 34 | 184 (1)| 00:00:03 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("MS"."OBJECT_NAME"='C' AND
SUBSTR("MS"."OBJECT_NAME",1,1)='A')
5 - filter("MS"."OBJECT_NAME"='C' AND
SUBSTR("MS"."OBJECT_NAME",1,1)='B')
6 - filter("MS"."OBJECT_NAME"='C' AND
SUBSTR("MS"."OBJECT_NAME",1,1)='C')
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
2417 consistent gets
TEST_DB> -- below will do a select of a non-indexed column
TEST_DB> -- specific to one of the Union's in the view via NOT matching the where clause
TEST_DB> -- but logically it is the same.
TEST_DB> select count(created)
2 from mdw_v1
3 where object_name like 'C%'
COUNT(CREATED)
--------------
129
Execution Plan
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 75 | 551 (1)| 00:00:07 |
| 1 | SORT AGGREGATE | | 1 | 75 | | |
| 2 | VIEW | MDW_V1 | 24 | 1800 | 551 (1)| 00:00:07 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL| MDW_SOURCE | 8 | 272 | 184 (1)| 00:00:03 |
|* 5 | TABLE ACCESS FULL| MDW_SOURCE | 8 | 272 | 184 (1)| 00:00:03 |
|* 6 | TABLE ACCESS FULL| MDW_SOURCE | 8 | 272 | 184 (1)| 00:00:03 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("MS"."OBJECT_NAME" LIKE 'C%' AND
SUBSTR("MS"."OBJECT_NAME",1,1)='A')
5 - filter("MS"."OBJECT_NAME" LIKE 'C%' AND
SUBSTR("MS"."OBJECT_NAME",1,1)='B')
6 - filter("MS"."OBJECT_NAME" LIKE 'C%' AND
SUBSTR("MS"."OBJECT_NAME",1,1)='C')
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
2417 consistent gets
Above, I create the view, do a quick select count(*) to cause a full scan of all three section in the union view, then do selects using various WHERE predicates to see what sections of the unioned SQl statements are excluded.
The results are very intersting. The filtering on sub_type still works fine, but now the WHERE predicate “where substr(object_name,1,1) =’C’” allows the CBO to exclude sections of the union, the “Null is not null” filters appear and the consistent gets is 807. The WHERE predicate “where object_name =’C'” which at first glance you might think to be logically the same but is not (we selected object_names where only the first character is checked but the whole object_name is selected) acts does not do any exclusion and I thought it might. There are no OBJECT_NAMES of ‘C’ but logically as a human you can see that such an object name could not be found by the first two unioned SQL statements in any case.
The last test is most interesting. The WHERE clause of the SQL select over the view is “where object_name like ‘C%’” and is logically the same as the one WHERE clause in the unioned view “where substr(object_name,1,1) =’C’“. So no exclusion occurs and again we see 2417 consistent gets.
It would seem the CBO Pixies either have not coded for that or, as Jonathan says Oracle can be “touchy” about this feature. Bottom line, if you are expecting this sort of unioned view SQL exclusion to occur – TEST!
Martin Widlake's Yet Another Oracle Blog 
Comments»
No comments yet — be the first.