Outer Joins
A reader, November 23, 2004 - 1:08 pm UTC
Tom,
Version 9i release 2, CBO
I have a view like
VW1:
select p.*,q.name srch_name, q.name0 srch_number,z.name, z.name0, other columns
from (select table1.companyid, ....table1.srch_number
from table1, table2, table3
where table1.s_col = table2.s_col
)P,
(select table1.companyid, ....table1.srch_number
from table1, table4, table5, table6
where table6.l_col = table5.l_col(+)
and table6.sl_col = table5.sl_col(+)) Q,
(select table1.companyid, other columns
from table1, table 5, table 7
<where clause> ) R , S, T
WHERE P.COMPANYID = Q.COMPANYID (+)
AND P.COMPANYID = R.COMPANYID (+)
and p.companyid = S.companyid (+)
Now if i query
select * from vw where srch_number = 1234
The view is not getting merged. Should it be merged in this case of view definition? If so how should/what should i specify
decode( OUTER_JOINED_COLUMN, NULL, NULL, LASTNAME || ', ' || FIRSTNAME )
I can provide the complete view def and plan also.
Thanks,
November 23, 2004 - 2:23 pm UTC
well, do you really mean to outer join to Q
where q.column = <value> and p.column = q.some_column(+)
doesn't make sense. if you outer join, then q.column would be NULL.
an outer join has to have a predicate applied to it, you cannot apply the predicate to Q and then outer join -- it would be "different"
ops$tkyte@ORA9IR2> create table p ( x int );
Table created.
ops$tkyte@ORA9IR2> create table q ( y int );
Table created.
ops$tkyte@ORA9IR2> insert into p values ( 1 );
1 row created.
ops$tkyte@ORA9IR2> create or replace view v
2 as
3 select * from p, q where p.x = q.y(+);
View created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from v where y = 5;
no rows selected
ops$tkyte@ORA9IR2> select * from p, (select * from q where y = 5 ) q
2 where p.x = q.y(+);
X Y
---------- ----------
1
so, it cannot push the predicate down into "q" that would change the answer.
Actual Query
A reader, November 23, 2004 - 2:41 pm UTC
Tom,
Here is the view query:
CREATE OR REPLACE FORCE VIEW VWGS_MAIN
(sid, COMPANYID, BNAME, BNAME_AC, CUST_STATUS,
BNAME_SRCH, BO_ID, MBRID, TAXPAYER_ID, MIND
)
AS
SELECT X.sid , X.COMPANYID , X.BNAME , X.BNAME_AC , X.CUST_STATUS , x.BNAME_UPPER
, X.BO_ID ,
x.MBRID ,
X.TAXPAYER_ID ,
X.MIND
FROM( SELECT A.sid , A.COMPANYID , B.BNAME , B.BNAME_AC , C.CUST_STATUS , B.BNAME_UPPER , A.BO_ID , A.MBRID , A.TAXPAYER_ID , A.MIND , A.PTSUBMITTERIND , A.PTMERCHANTGROUP , A.PTFINANCIALRPTCYC , A.BO_ID_PARENT , A.PTBUSINESSPROGRAM
FROM COMPANY_TBL A , NAME_TBL B , INDICATOR_TBL C
WHERE A.BO_ID = B.BO_ID
AND A.BO_ID = C.BO_ID
AND A.MARKET = 'CON'
) X,
( SELECT pur_typ.descr ,cm.PHONE ,A.COMPANYID , A.MBRID
FROM COMPANY_TBL A , cmb_tbl bcm , cmsp_tbl cm , cmsp_tbl_purp_type pur_typ
WHERE A.BO_ID = bcm.BO_ID
AND cm.CM_ID = bcm.CM_ID
AND cm.CM_TYPE_ID = bcm.CM_TYPE_ID
AND pur_typ.cm_type_id = bcm.CM_TYPE_ID(+)
AND pur_typ.cm_purpose_type_id = bcm.CM_PURPOSE_TYPE_ID(+)
AND pur_typ.descr = 'Business'
) Y,
( SELECT A.COMPANYID ,A.BO_ID_PARENT ,B.BNAME , A.MBRID
FROM COMPANY_TBL A , NAME_TBL B
WHERE A.BO_ID_PARENT = B.BO_ID (+)
) Z
WHERE X.COMPANYID = Y.COMPANYID (+)
AND X.COMPANYID = Z.COMPANYID (+)
Queries:
select * from VWGS_MAIN where mbrid = 1234;
select * from vwgs_main where bname_upper like 'PCAT%'
View is not getting merged
for
select * from VWGS_MAIN where companyid = 9999
view gets merged
thanks in advance
November 23, 2004 - 3:00 pm UTC
for exactly why i said.
"where of an outer join"
is not the same as
"outer join to a where'ed result set"
P in my example -- predicates can be pushed into it.
Q -- no, because outer joining to Q *after* a predicate is applied to it changes the answer (as demonstrated)
where'ing an outer join is not the same as outer joining to a "where'd" set.
Excellent information to know about 9i and below!!
A reader, November 23, 2004 - 3:35 pm UTC
A reader, November 23, 2004 - 3:37 pm UTC
Tom,
select * from VWGS_MAIN where mbrid = 1234;
should'nt the predicate "mbrid = 1234" pushed across all the subqueries and then outer joined? Because in my view this predicate can be pushed all across and the result would be the same.
Thanks for your Answer
November 23, 2004 - 7:24 pm UTC
you outer join to X
you outer join to Y
mbrid is an attribute of X
mbrid is an attribute of Y
mbrid is an attribute of Z
it could at most be "pushed" into X (since that is whence it comes).
it cannot possibly be "pushed" into Y or Z, that would not even make sense (so no, NOT across "all" subqueries).
how do you know it is not being pushed into X.
from previous post
A reader, November 23, 2004 - 4:10 pm UTC
Tom,
What i meant is:
since i have COMPANY_TBL every where in the subquery should'nt the predicate being pushed? Its not two different tables(p,q) but the same table
Thanks,
November 23, 2004 - 7:26 pm UTC
so? you have the same table? so?
that doesn't mean the rows joined together from each are even remotely similar.
your predicate is only on the mbrid in X, it cannot be on any of the others -- who is to say that x.mbrid = y.mbrid = z.mbrid or even that it makes sense to consider it to be that way? y and z's mbrid are totally different beasts from x's
cont.. of previous post
A reader, November 23, 2004 - 8:54 pm UTC
Tom,
Excellent way of putting things across....still one more question.....
Agreed that since my view column is selected from X, hence
it got pushed into 'X' and rest of them are views and hash joined(outer). The question now is should'nt that same query plan apply when i am selecting
select * from my_vw where company_id = 1234.
The view gets merged here. Here I do not see "VIEW" in the explain plan.... what makes it different here?
Thanks,
November 23, 2004 - 9:15 pm UTC
totally different concept -- the understanding is there that company id is actually supposed to be "the same" in all.
mbrid in x, y, z - there is NOTHING saying "they will be the same value"
company_id -- in x, y, z -- they will either be the same or null -- but since X is driving this car and you predicate on the join column -- they will all be the same.
A reader, November 23, 2004 - 9:43 pm UTC
Thank you very much Tom for clearly explaining the concept.
Thanks again
View Merging and Predicate Pushing
Vivek Sharma, December 01, 2004 - 5:55 am UTC
Hi Tom,
Thanks for your brilliant explanation in this forum.
Tom, I was going through the Oracle 9i Performance Tuning Docs.
"
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#721"
In Chapter 1, about Query Transformer, I was reading about View Merging and Predicate Pushing. But, I could not understand it since the examples are not given in these docs. Now for the example query shown by you in this forum, I have created a view V. Please see below :
SQL> create table p ( x int );
Table created.
SQL> create table q ( y int );
Table created.
SQL> insert into p values ( 1 );
1 row created.
SQL> create or replace view v
2 as
3 select * from p, q where p.x = q.y(+);
View created.
SQL> select * from v where y=5;
no rows selected
SQL> select * from v;
X Y
---------- ----------
1
SQL> select * from p, (select * from q where y = 5 ) q
2 where p.x = q.y(+);
X Y
---------- ----------
1
SQL> explain plan for
2 select * from v where y=5;
Explained.
SQL> @utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | FILTER | | | | |
| 2 | MERGE JOIN OUTER | | | | |
| 3 | SORT JOIN | | | | |
| 4 | TABLE ACCESS FULL| P | | | |
|* 5 | SORT JOIN | | | | |
| 6 | TABLE ACCESS FULL| Q | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("Q"."Y"=5)
5 - access("P"."X"="Q"."Y"(+))
filter("P"."X"="Q"."Y"(+))
Note: rule based optimization
21 rows selected.
I have seen the explain plan for both the queries. My query is :
1. Can you explain the concept of View Merging and Predicate Pushing with some brilliant examples, as you normally do.
2. How do I check whether the Optimizer is doing View Merging or Predicate Pushing for my query.
3. In the above queries, what is it ? Whether is it View Merging or Predicate Pushing ?
Awaiting for your answer.
Thanks and Regards
Vivek
SQL> explain plan for
2 select * from v where x=1;
Explained.
SQL> @utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | MERGE JOIN OUTER | | | | |
| 2 | SORT JOIN | | | | |
|* 3 | TABLE ACCESS FULL | P | | | |
|* 4 | SORT JOIN | | | | |
| 5 | TABLE ACCESS FULL | Q | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("P"."X"=1)
4 - access("P"."X"="Q"."Y"(+))
filter("P"."X"="Q"."Y"(+))
Note: rule based optimization
20 rows selected.
December 01, 2004 - 9:40 am UTC
view merging simply means the query was rewritten without the "view" at all -- it was as if instead of:
select * from VIEW;
you actually typed in the view text. Predicate pushing is when a predicate is pushed down as far into the plan as possible (evaluated as soon as it can be). I'll use three views to show:
a) a non-mergable view, non-pushable view
b) a mergable view
c) a non-mergable view that does predicate pushing.
ops$tkyte@ORA9IR2> create table t1 as select * from all_users;
Table created.
ops$tkyte@ORA9IR2> create table t2 as select * from all_users;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view v1
2 as
3 select t1.user_id, t2.username , rownum r
4 from t1 inner join t2
5 on ( t1.user_id = t2.user_id );
View created.
<b>this will be non-mergable and we won't be able to push predicates. that is because of ROWNUM in this case (analytics would typically have the same effect). The reason: "r" would change if we did</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view v2
2 as
3 select t1.user_id, t2.username
4 from t1 inner join t2
5 on ( t1.user_id = t2.user_id );
View created.
<b>this will be totally mergable</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view v3
2 as
3 select t1.user_id, t2.username
4 from t1 inner join t2
5 on ( t1.user_id = t2.user_id )
6 order by t1.user_id, t2.username;
View created.
<b>the order by will prevent merging -- but NOT predicate pushing</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
7 rows deleted.
ops$tkyte@ORA9IR2> explain plan for
2 select * from v1 where username = 'FRED';
Explained.
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | VIEW | V1 | | | |
| 2 | COUNT | | | | |
| 3 | MERGE JOIN | | | | |
| 4 | SORT JOIN | | | | |
| 5 | TABLE ACCESS FULL| T2 | | | |
|* 6 | SORT JOIN | | | | |
| 7 | TABLE ACCESS FULL| T1 | | | |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V1"."USERNAME"='FRED')
6 - access("T1"."USER_ID"="T2"."USER_ID")
filter("T1"."USER_ID"="T2"."USER_ID")
Note: rule based optimization
22 rows selected.
<b>view step shows view was not merged and the filter being way up there shows the predicate was not pushed into the view</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
8 rows deleted.
ops$tkyte@ORA9IR2> explain plan for
2 select * from v2 where username = 'FRED';
Explained.
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | MERGE JOIN | | | | |
| 2 | SORT JOIN | | | | |
|* 3 | TABLE ACCESS FULL | T2 | | | |
|* 4 | SORT JOIN | | | | |
| 5 | TABLE ACCESS FULL | T1 | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."USERNAME"='FRED')
4 - access("T1"."USER_ID"="T2"."USER_ID")
filter("T1"."USER_ID"="T2"."USER_ID")
Note: rule based optimization
20 rows selected.
<b>Here the lack of the view step shows the view was merged. it is as if we queried:
select t1.user_id, t2.username
from t1 inner join t2
on ( t1.user_id = t2.user_id )
where t2.username = 'FRED'
instead of:
select * from v2 where username = 'FRED';
</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
6 rows deleted.
ops$tkyte@ORA9IR2> explain plan for
2 select * from v3 where username = 'FRED';
Explained.
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | VIEW | V3 | | | |
| 2 | SORT ORDER BY | | | | |
| 3 | MERGE JOIN | | | | |
| 4 | SORT JOIN | | | | |
|* 5 | TABLE ACCESS FULL| T2 | | | |
|* 6 | SORT JOIN | | | | |
| 7 | TABLE ACCESS FULL| T1 | | | |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("T2"."USERNAME"='FRED')
6 - access("T1"."USER_ID"="T2"."USER_ID")
filter("T1"."USER_ID"="T2"."USER_ID")
Note: rule based optimization
22 rows selected.
<b>and here we see the view step -- the view was not merged -- however, the predicates applied to the view were pushed down as far as possible.</b>
Excellent. But Differentiating Non-Mergeable Plan and Predicate Pushing Plan is difficult
Vivek Sharma, December 02, 2004 - 1:22 am UTC
Hi Tom,
Thanks for your excellent answer. You are really brilliant when it comes to explaining with examples.
From the above plans, I could understand the difference between the three views. Thanks for it.
But when looking to the plan of v1 and v3, it is not possible to differentiate whether the view was not merged or a predicate pushing was used. Both the plans appears to be same except the COUNT and SORT ORDER BY in the 2 plans.
Plan for Non-Mergeable view
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | VIEW | V1 | | | |
| 2 | COUNT | | | | |
| 3 | MERGE JOIN | | | | |
| 4 | SORT JOIN | | | | |
| 5 | TABLE ACCESS FULL| T2 | | | |
|* 6 | SORT JOIN | | | | |
| 7 | TABLE ACCESS FULL| T1 | | | |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V1"."USERNAME"='FRED')
6 - access("T1"."USER_ID"="T2"."USER_ID")
filter("T1"."USER_ID"="T2"."USER_ID")
Note: rule based optimization
Plan for Predicate Pushing
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | VIEW | V3 | | | |
| 2 | SORT ORDER BY | | | | |
| 3 | MERGE JOIN | | | | |
| 4 | SORT JOIN | | | | |
|* 5 | TABLE ACCESS FULL| T2 | | | |
|* 6 | SORT JOIN | | | | |
| 7 | TABLE ACCESS FULL| T1 | | | |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("T2"."USERNAME"='FRED')
6 - access("T1"."USER_ID"="T2"."USER_ID")
filter("T1"."USER_ID"="T2"."USER_ID")
Note: rule based optimization
22 rows selected.
If I see the text of the view, I can figure out whether it is a non-mergeable view. But is there any way to figure it out from the Plan Table ?
Thanks and Regards
Vivek
December 02, 2004 - 7:39 am UTC
?
there is a VIEW step -- so the view was not merged.
But the filter on username was obviously not pushed in the first case and was pushed in the second.
view step -- view was not merged
filter placement -- shows if predicate was pushed into the view.
see how the second query didn't have a view step at all -- it was perfectly mergable.
Bookmarks -> Bookmark this page...
A reader, December 02, 2004 - 10:46 am UTC
Wow... this one's going on my list of favorites on this site.
Tom... you couldn't have demonstrated this more clearly and thoroughly.
Merge view and push predicate
karma, February 17, 2005 - 9:42 am UTC
Woderful example and learn new things everyday. I understand that if i have a view that does UNION ALL and if this view is used with other layer of views then the innermost view ( with all UNION ALL ) will be non-meargeable and will not be able to push predicate. Is this correct ?
If so, is there any workaround, hints etc to at least push predicate and/or make it view meargeable?
February 17, 2005 - 10:52 am UTC
that is not true at all (where did you get that from on this page? I'll update it to make it more clear, I don't see that here.. the first appearance of the word UNION on this page is yours in fact)
ops$tkyte@ORA9IR2> create table t1 as select * from all_objects;
Table created.
ops$tkyte@ORA9IR2> create index t1_idx on t1(owner,object_type,object_name);
Index created.
ops$tkyte@ORA9IR2> create table t2 as select * from all_objects;
Table created.
ops$tkyte@ORA9IR2> create index t2_idx on t2(owner,object_type,object_name);
Index created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T1', cascade=>true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T2', cascade=>true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view v
2 as
3 select * from t1
4 union all
5 select * from t2;
View created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
14 rows deleted.
ops$tkyte@ORA9IR2> explain plan for
2 select *
3 from v
4 where owner = 'SCOTT'
5 and object_type = 'TABLE'
6 and object_name = 'EMP';
Explained.
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 |
| 1 | VIEW | V | 1 | 98 | 2 |
| 2 | UNION-ALL PARTITION | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 93 | 2 |
|* 4 | INDEX RANGE SCAN | T1_IDX | 1 | | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 93 | 2 |
|* 6 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."OWNER"='SCOTT' AND "T1"."OBJECT_TYPE"='TABLE' AND
"T1"."OBJECT_NAME"='EMP')
6 - access("T2"."OWNER"='SCOTT' AND "T2"."OBJECT_TYPE"='TABLE' AND
"T2"."OBJECT_NAME"='EMP')
Note: cpu costing is off
22 rows selected.
predicates totally pushed.
Views and predicate push
karma, February 17, 2005 - 11:46 am UTC
Yes, you never mentioned UINION in this thread but i read about this on your previous thread.
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1455263156298 <code>
May be i misunderstood since you proved that this is not the case.
I have layer of views and predicate are not pushed. Lowest level has union all. Then i have 2 other views say viewx and viewy where viewx has login_id and list of accounts that this login_id can access. viewy has all accounts for all users with other account info. Frontend tool joins this 3 views and says where user_id = viewx.login_id.
Now in innermost view ( with all UNION all) do not get list of accounts based on this login_id. Is there a way to push?
February 17, 2005 - 1:49 pm UTC
if the view CAN allow the predicate to be pushed safely (that is without changing the ANSWERS THAT THE VIEW would otherwise return) it'll try to do so.
I cannot answer your question without a simple example. viewx, viewy must be doing something that prevents predicate pushing -- doing something that if we did push the predicate it would affect the answer (change it)
A reader, February 17, 2005 - 1:12 pm UTC
Hi Tom,
I am not getting the predicate information when I do
select * from table(dbms_xplan.display)
--------------------------------------------------------------------------------
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | VIEW | V3 | | | |
| 2 | SORT ORDER BY | | | | |
| 3 | MERGE JOIN | | | | |
| 4 | SORT JOIN | | | | |
| 5 | TABLE ACCESS FULL| TT2 | | | |
| 6 | SORT JOIN | | | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 7 | TABLE ACCESS FULL| TT1 | | | |
---------------------------------------------------------------------
Note: rule based optimization, PLAN_TABLE' is old version
15 rows selected.
Is there anything else that needs to be set for getting this predicate information given below
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("T2"."USERNAME"='FRED')
6 - access("T1"."USER_ID"="T2"."USER_ID")
filter("T1"."USER_ID"="T2"."USER_ID")
I am using oracle client SQL*Plus: Release 9.2.0.1.0 and connecting to 9204 database
February 17, 2005 - 2:08 pm UTC
PLAN_TABLE' is old version
hmmm, plan_table is old version.........
create the new plan table ($oracle_home/rdbms/admin/utlxplan.sql)
View merging and predicate pushing
Jay, February 17, 2005 - 2:41 pm UTC
Hi Tom,
A few posts above you have illustrated a case of view merging but NOT predicate pushing. The view used for the example was
-----------------------
ops$tkyte@ORA9IR2> create or replace view v3
2 as
3 select t1.user_id, t2.username
4 from t1 inner join t2
5 on ( t1.user_id = t2.user_id )
6 order by t1.user_id, t2.username;
View created.
the order by will prevent merging -- but NOT predicate pushing
--------------------
and the following select was used to demostrate the point:
ops$tkyte@ORA9IR2> explain plan for
2 select * from v3 where username = 'FRED';
Questions:
1. Why is this view not mergable? Infact the only thing required to make this happen is to push the predicate, which is happening in the plan.
2. The reason the view is not mergable is said to be due to the View step in the plan (which is really not required). Does the SQL engine actually perform anything in the View step or is it just that the explain plan is displayed that way (i.e. totally cosmetic)?
February 17, 2005 - 3:04 pm UTC
1) because there could be other stuff happening in a larger, less trivial query than I used -- the ordering would have to be "preserved", hence the VIEW step here -- but it was really a "no operation" sort of step in this case.
2) the view step is what we see to tell that the view was not mergable (it is not the reason, it is a side effect, the REASON is the order by! we cannot merge another section of a larger query into this one as that would tend to break the order by)
View merging and predicate pushing
Jay, February 17, 2005 - 2:44 pm UTC
The first sentence of my post above should read:
'A few posts above you have illustrated a case of view not merging but predicate pushing occurs'.
Sorry about that.
UNION-ALL PARTITION
AB, February 18, 2005 - 11:49 am UTC
Tom,
I have a similar scenario to the UNION-ALL view example you showed a couple of replies back. I have two tables of similar structure, each with a PK column of the same type and scale. There is a view over these that simply "UNION ALLs" them. The PK values are completely unique across the view and they don't overlap at all between the two tables.
Please don't ask me why there are two tables as I have no idea ;o)
When I do a single key lookup against the view, I get a UNION ALL (PARTITION) plan with two index unique scans. As expected.
However, when I try to join another very small resultset to the view ( hoping for a small number of NL key probes ), I get a full scan of the two underlying tables. Index hints only give me two index full scans. I'm looking up just 400 unique values against 1 million rows, so I'd expect a NL and an index unique lookup.
I've tried all afternoon to reproduce this problem so it makes sense but cannot. It's an 8.1.7 database and everything is analyzed and the clustering factors are low. OICA is 5 and OIC is 25 ( again don't ask me why ). I've tried 10-90 and 0-100 and also tried a RULE hint.
I just cannot get a query of this type to use the unique indexes:-
SELECT t1.*
FROM t1
, v1 --<--- t2 union all t3
WHERE t1.grandparent_id = (
SELECT grandparent_id
FROM t1
WHERE id = 'a literal'
)
AND t1.id = v1.id;
I appreciate this isn't enough for a definitive answer, but any pointers would be very gratefully received. Else I'm going to have to outer join to both and DECODE everything, which means exposing the business rules from the view...
February 18, 2005 - 2:34 pm UTC
set up the example tables and such to reproduce with...
ops$tkyte@ORA817DEV> /*
DOC>
DOC>drop table t1;
DOC>drop table t2;
DOC>drop table t3;
DOC>set echo on
DOC>set linesize 121
DOC>
DOC>
DOC>create table t2 as select rownum id, a.* from all_objects a;
DOC>create table t3 as select -rownum id, a.* from all_objects a;
DOC>alter table t2 add constraint t2_pk primary key(id);
DOC>alter table t3 add constraint t3_pk primary key(id);
DOC>
DOC>exec dbms_stats.gather_table_stats( user, 'T2', cascade=>true );
DOC>exec dbms_stats.gather_table_stats( user, 'T3', cascade=>true );
DOC>
DOC>create table t1 ( id int, grandparent_id int );
DOC>insert into t1 select rownum, rownum+1 from all_objects;
DOC>insert into t1 select -rownum, -rownum+1 from all_objects;
DOC>create index t1_idx1 on t1(grandparent_id);
DOC>create index t1_idx2 on t1(id);
DOC>
DOC>exec dbms_stats.gather_table_stats( user, 'T1', cascade=>true );
DOC>
DOC>create or replace view v1
DOC>as
DOC>select * from t2
DOC>union all
DOC>select * from t3;
DOC>*/
ops$tkyte@ORA817DEV> set autotrace traceonly explain
ops$tkyte@ORA817DEV> select t1.*
2 from t1, v1
3 where t1.grandparent_id = ( select grandparent_id from t1 where id = 55 )
4 and t1.id = v1.id
5 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=16)
1 0 NESTED LOOPS (Cost=2 Card=1 Bytes=16)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=10)
3 2 INDEX (RANGE SCAN) OF 'T1_IDX1' (NON-UNIQUE) (Cost=1 Card=1)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=10)
5 4 INDEX (RANGE SCAN) OF 'T1_IDX2' (NON-UNIQUE) (Cost=1 Card=1)
6 1 VIEW OF 'V1'
7 6 UNION-ALL (PARTITION)
8 7 INDEX (UNIQUE SCAN) OF 'T2_PK' (UNIQUE) (Cost=1 Card=1 Bytes=5)
9 7 INDEX (UNIQUE SCAN) OF 'T3_PK' (UNIQUE) (Cost=1 Card=1 Bytes=6)
ops$tkyte@ORA817DEV> set autotrace off
ops$tkyte@ORA817DEV> select count(*) from t1;
COUNT(*)
----------
51768
Bit off the main topic ...
Gabe, February 18, 2005 - 3:22 pm UTC
<quote>
where t1.grandparent_id = ( select grandparent_id from t1 where id = 55 )
</quote>
why not "where t1.id = 55 and t1.grandparent_id is not null"?
Seems rather cryptic ... am I missing something?
February 18, 2005 - 3:38 pm UTC
they are not semantically equivalent.
the first one says "give me all of the rows in T1 who's grandparent is the same as the grandparent of id=55"
sort of like saying 'show me all of the grandchildren of the grandparent of Mary'
the second says "show me all of the rows in T1 whose id is 55 and has a grandparent"
sort of like saying 'show me Mary if she has a grandparent'
ops$tkyte@ORA9IR2> create table t1 ( id int, grandparent_id int );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t1 values ( 42, 1 );
1 row created.
ops$tkyte@ORA9IR2> insert into t1 values ( 55, 1 );
1 row created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t1 where t1.grandparent_id = ( select grandparent_id from t1 where id = 55 );
ID GRANDPARENT_ID
---------- --------------
42 1
55 1
ops$tkyte@ORA9IR2> select * from t1 where t1.id = 55 and t1.grandparent_id is not null;
ID GRANDPARENT_ID
---------- --------------
55 1
thanks
Gabe, February 18, 2005 - 3:48 pm UTC
Sometimes having a _slow_ day is the best one can hope for ... hmm.
Can't get the testcase to differ...
AB, February 20, 2005 - 3:02 pm UTC
As I mentioned in my first post above, I tried all afternoon on Friday to get a representative test case which in the end looked very similar to your's. And it was "behaving" itself in the same manner as your example.
I have no idea what is different about the live data and my test case. My testcase was conceived to have the same proportions of data. Looks like I'll have to keep digging, as I've tried all the obvious tests.
Unfortunately I can't post the real query here due to commercial sensitivity.
Thanks anyway...
Scalar subquery the only way...
AB, February 21, 2005 - 4:22 am UTC
While the simple testcase observes the correct behaviour, I just cannot get the same behaviour from the "live" query. I have stripped it down to its simplest form. I've taken the results of the self-join on grandparent_id and stored them in a new table with a PK, containing just 459 rows. I've then joined this table to the UNION-ALL view and still get index full scan. The UNION-ALL view has 850,000 on the "top" table and just 1,000 on the "bottom" table. I've tried to fake the bottom stats to look large but to no avail. Even when the CBO knows there are just 459 rows to NL to the view, it still ignores the unique index.
The only thing I can get to work is a scalar subquery ( select a select ). The CBO is happy taking a single row and looking up the unique index across the UNION-ALL view...
Any ideas on how I can tell Oracle in 8i to use the two unique indexes "inside" the UNION-ALL view ?
Thanks
AB
February 21, 2005 - 10:17 am UTC
it will if the stats say "it makes sense". show us the autotrace traceonly and the tkprof of the real query with the real data (we don't need to see the data values, just the query and the stats)
Underscore...
AB, February 21, 2005 - 8:50 am UTC
Tom,
Sorry to keep banging on about this one, but thought you might be interested to know that I've discovered the cause of my problem ( and why our test cases couldn't reproduce it ).
The two tables that are "union-alled" are of a different structure and therefore setting the _PUSH_JOIN_UNION_VIEW hidden parameter to TRUE solves the problem. ( But it might also generate some problems, so I'll have to investigate a bit ).
So the testcase becomes a lot easier to force now ( following is representative of the data proportions in my system )...
817> CREATE TABLE t1
2 AS
3 SELECT TO_CHAR(object_id) AS id
4 , MOD(ROWNUM,400) AS parent_id
5 , MOD(ROWNUM,100) AS grandparent_id
6 FROM all_objects;
Table created.
817>
817> CREATE TABLE t2
2 AS
3 SELECT id
4 , 'Name for ' || id AS some_name
5 FROM t1
6 WHERE ROWNUM <= ( SELECT TRUNC(COUNT(*) * 0.9) FROM t1 );
Table created.
817>
817> CREATE TABLE t3
2 AS
3 SELECT id
4 , 'Name for ' || id AS some_name
5 , SYSDATE AS some_date
6 FROM (
7 SELECT id FROM t1
8 MINUS
9 SELECT id FROM t2
10 );
Table created.
817>
817> ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY ( id );
Table altered.
817> ALTER TABLE t2 ADD CONSTRAINT pk_t2 PRIMARY KEY ( id );
Table altered.
817> ALTER TABLE t3 ADD CONSTRAINT pk_t3 PRIMARY KEY ( id );
Table altered.
817>
817> BEGIN
2 DBMS_STATS.SET_TABLE_STATS(USER,'t1',numrows=>860000,numblks=>13500,avgrlen=>104);
3 DBMS_STATS.SET_TABLE_STATS(USER,'t2',numrows=>820000,numblks=>28000,avgrlen=>249);
4 DBMS_STATS.SET_TABLE_STATS(USER,'t3',numrows=>960,numblks=>25,avgrlen=>158);
5 DBMS_STATS.SET_INDEX_STATS(USER,'pk_t1',numrows=>850000,numlblks=>6800,numdist=>850000,avglblk=>1,avgdblk=>1,clstfct=>750000,indlevel=>2 );
6 DBMS_STATS.SET_INDEX_STATS(USER,'pk_t2',numrows=>820000,numlblks=>2600,numdist=>820000,avglblk=>1,avgdblk=>1,clstfct=>270000,indlevel=>2 );
7 DBMS_STATS.SET_INDEX_STATS(USER,'pk_t3',numrows=>960,numlblks=>5,numdist=>960,avglblk=>1,avgdblk=>1,clstfct=>20,indlevel=>1 );
8 END;
9 /
PL/SQL procedure successfully completed.
817>
817> CREATE OR REPLACE VIEW v_t2_t3
2 AS
3 SELECT id, some_name, TO_DATE(NULL) AS some_date FROM t2
4 UNION ALL
5 SELECT id, some_name, some_date FROM t3;
View created.
817>
817> var bv varchar2(30);
817>
817> set autotrace traceonly explain
817>
817>
817> SELECT t1.*
2 FROM t1
3 , v_t2_t3
4 WHERE t1.grandparent_id = (
5 SELECT grandparent_id
6 FROM t1
7 WHERE id = :bv
8 )
9 AND t1.id = v_t2_t3.id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3358 Card=70602560 Bytes=4942179200)
1 0 FILTER
2 1 HASH JOIN (Cost=3358 Card=70602560 Bytes=4942179200)
3 2 TABLE ACCESS (FULL) OF 'T1' (Cost=1299 Card=8600 Bytes=412800)
4 2 VIEW OF 'V_T2_T3' (Cost=132 Card=820960 Bytes=18061120)
5 4 UNION-ALL
6 5 INDEX (FULL SCAN) OF 'PK_T2' (UNIQUE) (Cost=2602 Card=820000 Bytes=18040000)
7 5 INDEX (FULL SCAN) OF 'PK_T3' (UNIQUE) (Cost=6 Card=960 Bytes=21120)
8 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=1 Card=1 Bytes=35)
9 8 INDEX (UNIQUE SCAN) OF 'PK_T1' (UNIQUE) (Cost=2 Card=8600)
817> ALTER SESSION SET "_PUSH_JOIN_UNION_VIEW" = TRUE;
Session altered.
817>
817> SELECT t1.*
2 FROM t1
3 , v_t2_t3
4 WHERE t1.grandparent_id = (
5 SELECT grandparent_id
6 FROM t1
7 WHERE id = :bv
8 )
9 AND t1.id = v_t2_t3.id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1300 Card=70602560 Bytes=4942179200)
1 0 FILTER
2 1 NESTED LOOPS (Cost=1300 Card=70602560 Bytes=4942179200)
3 2 TABLE ACCESS (FULL) OF 'T1' (Cost=1299 Card=8600 Bytes=412800)
4 2 VIEW OF 'V_T2_T3'
5 4 UNION-ALL (PARTITION)
6 5 INDEX (UNIQUE SCAN) OF 'PK_T2' (UNIQUE) (Cost=2 Card=1 Bytes=22)
7 5 INDEX (UNIQUE SCAN) OF 'PK_T3' (UNIQUE) (Cost=1 Card=1 Bytes=22)
8 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=1 Card=1 Bytes=35)
9 8 INDEX (UNIQUE SCAN) OF 'PK_T1' (UNIQUE) (Cost=2 Card=8600)
So, I do have a question out of all of this...
This is 8.1.7 - do you know what the default for this parameter is in later versions or whether the default behaviour changes ?
Thanks
AB
February 21, 2005 - 11:43 am UTC
in 9i, the example as above chooses:
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8600 | 587K| 4878 |
|* 1 | HASH JOIN | | 8600 | 587K| 4878 |
|* 2 | TABLE ACCESS FULL | T1 | 8600 | 403K| 2051 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 35 | 3 |
|* 4 | INDEX UNIQUE SCAN | PK_T1 | 1 | | 2 |
| 5 | VIEW | V_T2_T3 | 820K| 17M| 2607 |
| 6 | UNION-ALL | | | | |
| 7 | INDEX FAST FULL SCAN | PK_T2 | 820K| 17M| 396 |
| 8 | INDEX FAST FULL SCAN | PK_T3 | 960 | 21120 | 2 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="V_T2_T3"."ID")
2 - filter("T1"."GRANDPARENT_ID"= (SELECT /*+ */ "T1"."GRANDPARENT_ID"
FROM "T1" "T1" WHERE "T1"."ID"=:Z))
4 - access("T1"."ID"=:Z)
Note: cpu costing is off
however, first rows has it:
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8600 | 587K| 10651 |
| 1 | NESTED LOOPS | | 8600 | 587K| 10651 |
|* 2 | TABLE ACCESS FULL | T1 | 8600 | 403K| 2051 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 35 | 3 |
|* 4 | INDEX UNIQUE SCAN | PK_T1 | 1 | | 2 |
| 5 | VIEW | V_T2_T3 | 1 | 22 | 1 |
| 6 | UNION-ALL PARTITION | | | | |
|* 7 | INDEX UNIQUE SCAN | PK_T2 | 1 | 22 | 2 |
|* 8 | INDEX UNIQUE SCAN | PK_T3 | 1 | 22 | 1 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."GRANDPARENT_ID"= (SELECT /*+ */ "T1"."GRANDPARENT_ID"
FROM "T1" "T1" WHERE "T1"."ID"=:Z))
4 - access("T1"."ID"=:Z)
7 - access("T2"."ID"="T1"."ID")
8 - access("T3"."ID"="T1"."ID")
Note: cpu costing is off
25 rows selected.
given the card=, this looks appropriate (but that setting is no long needed)
under what conditions does view materialize first
A reader, October 26, 2005 - 3:17 pm UTC
Hi
I am puzzled when are the views materialized first in 8.1.7.4. I have some complex views and I have _COMPLEX_VIEW_MERGING set to TRUE
I have this query where table_x_workgroup_case is another complex view however the view just contains simple table joins (no rownum, no union, no roup etc) but the plan shows view o2 is resolved first and then joined with table_site e
How so? Under what conditions cannot complex view merging happen?
SELECT o2.elm_objid AS objid, '1' AS wipbin_type, ' ' AS age_str, -1 AS dev,
o2.wip_objid, 2 AS elm_objid, o2.age,
o2.creation_time AS creation_date, o2.condition, o2.status, o2.title,
o2.priority, o2.id_number AS idnum, -1 AS struct_type,
owner AS x_owner, x_owner_name, x_owner_group, x_owner_group_name,
o2.x_tipo_caso AS TYPE, o2.objid, o2.NAME, o2.x_prioridad_nodo,
o2.x_proveedor_nodo, o2.x_tipo_nodo, e.objid, e.NAME,
e.x_prioridad_nodo
FROM (SELECT n.x_nodo2emplazamiento, o.elm_objid, o.wip_objid, o.age,
c.creation_time, o.condition, o.status, o.title, o.priority,
o.id_number, owner, x_owner_name, x_owner_group,
x_owner_group_name, o.x_tipo_caso, n.objid, n.NAME,
n.x_prioridad_nodo, n.x_proveedor_nodo, n.x_tipo_nodo
FROM table_x_workgroup_case o,
table_case c,
table_x_case_in ci,
table_site n
WHERE c.objid = o.elm_objid
AND c.objid = ci.x_case_in2case
AND n.objid = c.case_reporter2site) o2,
table_site e
WHERE o2.x_nodo2emplazamiento(+) = e.objid
AND o2.x_owner_name = 'ADMIN'
October 27, 2005 - 4:44 am UTC
and if you remove the outer join in the outer part of the query ... what then?
if I remove the outer join
A reader, October 31, 2005 - 3:54 am UTC
hi
the view disappears, complex view happens.
so outer join avoids complex view merging?
October 31, 2005 - 5:43 am UTC
an outer join and some other set operations can prevent view merging and/or predicate pushing.
CBO, Views union all
Amit, July 24, 2007 - 6:45 am UTC
Hi Tom,
Thanks for all your help to oracle community and in fact many Business across globe.
Here, I have a Scenerio incuding CBO, Views, views on Views and hints etc
Oracle 9.2
Happy days- before change
1) we have view A on Table T1 (group by ) and Table T2(group by ).
2) View B on View A and table T3,t4 and T5 (group by)
hints used on view B no_merge ordered
3) Query
select * from view B,table T6,T7 (group by)
Query 3 is returning rows in 1 minutes and using index (range scan) on tables T1 and T2
BUT after we put the following change in Defination of View A-
view A now consists of
Table T1 (group by used) and Table T2(group by used)-- same as above
UNION ALL
Table T9 (group by used) and Table T2(group by used)
and when we run query 3 now it took 4 hours.
Plan now shows that it no longer uses index (range scan) rather
it is doing full table scan on all the above tables(T1, T2 and T9, T2)
and if we hint it to use index it start using it but worsen performance.
all the above tables are very big(around 10m ) and stats are fine
I can't post the whole queries because of business material
and can't reproduce the scenerio because of many dependant tables and large data involved.
What I need is your help if you can give some pointers-
*why using union all causes it to do full table scan and no indexes
*I have tried union instead of union all but no luck
*any other way to rewrite the view query
Thanks
view merging & predicate pushing
amit, July 24, 2007 - 9:15 am UTC
Continue above posting-
In your other thread I read about 'view merging' and 'predicate pushing'
It looks like in my case above, neither 'view merging' nor 'predicate pushing' is happening.
(ignore this addional post it you think is not the case and please answer my original query above..thanks)
Q1) Is there any way we can change the view defination below so that it start
view merging ?
Q2) Also Can We change the view A so that we can achieve predicate pushing !
Here is modified View A-
CREATE OR REPLACE VIEW A
( a_cust_id
, a_seq
, a_bill_date
, a_bill_total
, a_total_rc
, a_total_uc
, a_total_oc )
AS
( SELECT t1.cust_id
, NVL(bd.bill_seq, CEIL(MONTHS_BETWEEN(bd.bill_date, t1.conn_date)))
, bd.bill_date
, SUM(bd.amount)
, SUM(DECODE(bd.charge_type, 'M', bd.amount, 0))
, SUM(DECODE(bd.charge_type, 'T', bd.amount, 0))
, SUM(DECODE(bd.charge_type, 'Y', bd.amount, 0))
FROM TABLE_T1 t1
, bill bd
WHERE t1.ban = bd.ban
AND t1.ctn = bd.ctn
GROUP BY t1.cust_id
, NVL(bd.bill_seq, CEIL(MONTHS_BETWEEN(bd.bill_date, t1.conn_date)))
, bd.bill_date
)
UNION ALL
( SELECT t1.cust_id
, NVL(bd.bill_seq, CEIL(MONTHS_BETWEEN(bd.bill_date, t1.conn_date)))
, bd.bill_date
, SUM(bd.amount)
, SUM(DECODE(bd.charge_type, 'M', bd.amount, 0))
, SUM(DECODE(bd.charge_type, 'T', bd.amount, 0))
, SUM(DECODE(bd.charge_type, 'Y', bd.amount, 0))
FROM TABLE_T1 t1
, bill_new bd
WHERE t1.ban = bd.ban
AND t1.ctn = bd.ctn
GROUP BY t1.cust_id
, NVL(bd.bill_seq, CEIL(MONTHS_BETWEEN(bd.bill_date, t1.conn_date)))
, bd.bill_date
)
ORIGINAL VIEW DEF BEFORE CHANGE:
CREATE OR REPLACE VIEW A
( a_cust_id
, a_seq
, a_bill_date
, a_bill_total
, a_total_rc
, a_total_uc
, a_total_oc )
AS
( SELECT t1.cust_id
, NVL(bd.bill_seq, CEIL(MONTHS_BETWEEN(bd.bill_date, t1.conn_date)))
, bd.bill_date
, SUM(bd.amount)
, SUM(DECODE(bd.charge_type, 'M', bd.amount, 0))
, SUM(DECODE(bd.charge_type, 'T', bd.amount, 0))
, SUM(DECODE(bd.charge_type, 'Y', bd.amount, 0))
FROM TABLE_T1 t1
, bill bd
WHERE t1.ban = bd.ban
AND t1.ctn = bd.ctn
GROUP BY t1.cust_id
, NVL(bd.bill_seq, CEIL(MONTHS_BETWEEN(bd.bill_date, t1.conn_date)))
, bd.bill_date
)