Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, SHANKAR.

Asked: November 24, 2009 - 11:42 pm UTC

Last updated: October 24, 2011 - 5:17 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Regarding the topic “Views and Merging/Pushing”, I am not able to
understand the point no.3 “Code Listing 3: Query against V3”, when we
use order by clause, why the view is nonmergeable.

Could you please explain me what is the purpose of View merging for
Oracle, what is the advantage for doing so by oracle.

I am able to understand the predicate pushing.

and Tom said...

I suspect you are referring to this article:

https://www.oracle.com/technetwork/issue-archive/2005/05-mar/o25asktom-089621.html

you don't say, but I'll guess... (references to the original source are always nice...)



In the example, we have a view with an order by. something like:


ops$tkyte%ORA11GR2> create table t1 as
  2  select * from all_users;

Table created.

ops$tkyte%ORA11GR2> create table t2 as
  2  select * from all_users;

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> 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 =
  6           t2.user_id )
  7   order by t1.user_id,
  8            t2.username;

View created.



Now, if we execute a query:

ops$tkyte%ORA11GR2> select * from v3 where username like '%X%' and rownum = 1;

   USER_ID USERNAME
---------- ------------------------------
        42 EXFSYS


Execution Plan
----------------------------------------------------------
Plan hash value: 779243896

--------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |    30 |     8  (25)| 00:00:01 |
|*  1 |  COUNT STOPKEY          |      |       |       |            |          |
|   2 |   VIEW                  | V3   |     2 |    60 |     8  (25)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY|      |     2 |    86 |     8  (25)| 00:00:01 |
|*  4 |     HASH JOIN           |      |     2 |    86 |     7  (15)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL  | T2   |     2 |    60 |     3   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL  | T1   |    40 |   520 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM=1)
   3 - filter(ROWNUM=1)
   4 - access("T1"."USER_ID"="T2"."USER_ID")
   5 - filter("T2"."USERNAME" LIKE '%X%')



we fully expect (because of the order by in the view) that the tables will be joined, filtered (the t2.username like is applied, PUSHED into the view) - but the view step must remain (we cannot just rewrite the query with the text of the view 'inline' - because the order by is there. We need to ORDER BY before applying the rownum predicate.

The semantics of our query against the view is to join, find all of the %X% rows, sort, AND THEN get the first row.

Hence the view can be partially pushed into (where username like) but not merged away - out of existence, the boundary of the view must be there to preserve the sort.


If we did merge the view, like this (manually merged)

ops$tkyte%ORA11GR2> select t1.user_id, t2.username
  2    from t1 inner join t2
  3      on ( t1.user_id =
  4           t2.user_id )
  5   where t2.username like '%X%' and rownum = 1
  6   order by t1.user_id,
  7            t2.username;

   USER_ID USERNAME
---------- ------------------------------
        87 IX


Execution Plan
----------------------------------------------------------
Plan hash value: 1960641982

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    43 |     8  (25)| 00:00:01 |
|   1 |  SORT ORDER BY       |      |     1 |    43 |     8  (25)| 00:00:01 |
|*  2 |   COUNT STOPKEY      |      |       |       |            |          |
|*  3 |    HASH JOIN         |      |     2 |    86 |     7  (15)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| T2   |     2 |    60 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T1   |    40 |   520 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=1)
   3 - access("T1"."USER_ID"="T2"."USER_ID")
   4 - filter("T2"."USERNAME" LIKE '%X%')



You can see the answer is different. Because the order by happened AFTER the rownum filter.


That is why the view with an order by is in general "not mergable", we need that view boundary to ensure that the sort happens before other operations - think of it like a "line in the sand", some things can be pushed over that line, others cannot - but the line must persist (no merging) to ensure correct results.

Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Views and Merging/Pushing

SHANKAR AVUDAINAYAGAM, November 26, 2009 - 11:23 am UTC

Thanks, now able to understand the concept with clarity. Also the last phrase "think of it like a "line in the sand", some things can be pushed over that line, others cannot - but the line must persist (no merging) to ensure correct results" is too good.

Good Work!!!

Order by clause in a view definition

Tony Andrews, February 19, 2010 - 8:55 am UTC

Is there ever a good reason to put an ORDER BY clause in a view (a "real" view, not an inline view)? It just seems wrong to me. It encourages lazy developers to put an ORDER BY in the view definition to "save" having to put one in each query that uses the view. Then they complain when they don;t get the ordering they expected in a query that joins the view to another table (for example). Just wondering if this is another feature like triggers and "when others" that you would prefer never to have been added?
Tom Kyte
February 25, 2010 - 12:26 am UTC

... It just seems wrong to me. ...


I agree personally, it is a scary thing since a join of that view with anything else would obviate the order by - and people might well not realize that.

... Just wondering if this is another feature like triggers and "when others" that
you would prefer never to have been added? ...

A little bit, but not as strongly as "when others", triggers and auotnomous transactions - only because those first three are the cause of most of the developer inflicted bugs I see - whereas the order by in the view hasn't been so universally abused yet :)

Order by in view

Dana, February 25, 2010 - 12:02 pm UTC

I use order by views, but very sparingly, and never grant anyone access.
There are times a simple select first_col_row from ordered_view is easier to look at than a much larger inline query.

Tom Kyte
March 01, 2010 - 11:07 am UTC

but, you can only compare:


select first_col_row from ordered_view

with

select first_col_row from unordered_view order by <whatever>;


Not sure what your comment on inline views was about?

view merging and predicate pushing

choche, October 18, 2010 - 11:02 am UTC

Tom,
Many thanks for all your valuable tips and education to the Oracle community. Anyway, on the subject of view merging and predicate pushing you have been discussing in this thread, I am still not very clear as to what these features are and I will appreciate if you could provide me some clear and precise definitions of what they are.
Tom Kyte
October 25, 2010 - 9:09 am UTC

it is an optimization to have work performed as early as possible - to avoid having to do a TON of work.

For example, suppose you have a view:

create view v as select * from emp;


and you issue a query:

select * from v where empno = 1234;


and assume empno is a number and a primary key (hence there exists an index on it)

Would you like us to:

a) full scan emp, returning the data as if you issued "select * from emp" and then apply the filter "empno = 1234" to each row

or

b) pretend you really issued "select * from emp where empno = 1234" (that is, MERGE your predicate into the view itself) so we could optimize using an index range scan.


In simplest terms - that is what it is. It is rewriting your query to merge referenced views into the outer query (as if the views were not even there) and pushing predicates down as far as we can into views/subqueries/whatever to have them evaluated sooner rather than later.

Effective Oracle by design.

A reader, October 30, 2010 - 5:21 am UTC

Hi Tom,

in your book page 490 you described the rownum effect on query. then on page 491 in heading Tuning with RowNum you describe why query will not perform merge. there you use the word materialized. So my understand become when a query is being materialize due to the reasons you have described the materialize part of the query gets stored in temporary tablespace or memory, is my understanding is righ?

For example,

Select a.ename
from (select rownum, ename from emp e, dept d where d.deptno=e.deptno)
where rownum<10

the inlineview
select rownum, ename from emp e, dept d where d.deptno=e.deptno

will be stored in memory or temporary tablespace.



Tom Kyte
November 01, 2010 - 6:59 am UTC

yes, an intermediate result set that is 'materialzed' would be materialized into temp (memory or on disk)



..Predicate pushing

Manoj Kaparwan, November 29, 2010 - 12:58 am UTC

Hi Tom,

Thanks for your time.


SQL> set pagesize 123
SQL>
SQL>
SQL>
SQL> create table T as select rownum id, object_type name, object_id value, mod(rownum,10000) col2id from all_objects
where Object_type in ('INDEX','TABLE','VIEW','SYNONYM')   2
  3  ;

Table created.

SQL> alter table t add primary key (ID);

Table altered.

SQL> create index t_idx on t ( value);

Index created.

SQL> select count(*) from t;

  COUNT(*)
----------
     22552

SQL> exec dbms_stats.gather_table_stats( user, 'T', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE  VIEW  V_T
  2      (Objid, col_ind_type, col_tab_type,col_view_type,col_synonym_typecol2id, col2id
  3  )
   AS
  4    5     SELECT ID Objid,
  6                  MAX (DECODE (name, 'INDEX', value, null)) AS col_ind_type,
  7                  MAX (DECODE (name, 'TABLE', value, null)) AS col_tab_type,
  8                  MAX (DECODE (name, 'VIEW', value, null)) AS col_view_type,
  9                 MAX (DECODE (name, 'SYNONYM', value, null)) AS col_synonym_type,
 10                  col2id
 11  FROM T
 12  where ID = col2id
 13     GROUP BY
 14  ID,col2id
 15  ;

View created.

SQL> explain plan for
  2  SELECT objid, col2id
  FROM v_t
 WHERE (col2id IS NOT NULL) AND col_ind_type = 27  3    4
  5  ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2604835923

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    20 |    23   (9)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   HASH GROUP BY     |      |     1 |    20 |    23   (9)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T    |     1 |    20 |    22   (5)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(MAX(DECODE("NAME",'INDEX',"VALUE",NULL))=27)
   3 - filter("ID"="COL2ID" AND "COL2ID" IS NOT NULL)

16 rows selected.

SQL> set autotrace     on
SQL>
SQL>
SQL> set timing on
SQL>
SQL> SELECT objid, col2id
  FROM v_t
 WHERE (col2id IS NOT NULL) AND col_ind_type = 27  2    3
  4  ;

     OBJID     COL2ID
---------- ----------
         6          6

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 2604835923

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    20 |    23   (9)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   HASH GROUP BY     |      |     1 |    20 |    23   (9)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T    |     1 |    20 |    22   (5)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(MAX(DECODE("NAME",'INDEX',"VALUE",NULL))=27)
   3 - filter("ID"="COL2ID" AND "COL2ID" IS NOT NULL)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         85  consistent gets
          0  physical reads
          0  redo size
        579  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>


Applicatoin code make use of the view v_T and  runs the query as 

SELECT objid, col2id
  FROM v_t
 WHERE (col2id IS NOT NULL) AND col_ind_type = 27


Questions:

a) How to tune it using the view? Here predicate col_ind_type = 27  is not pushing  because of aggregate, group by functions in view.
b) Making use of base table T without use of view v_T , so that we can row set is reduced in the begining itself?






Tom Kyte
November 29, 2010 - 2:39 am UTC

this will be a mess to 'tune'. the predicate cannot be pushed because you don't just want works with 'col_ind_type=27', from T - you need other related rows but that relations are not known until you actually hit the data.

A query similar to this - you will need to verify that this is semantically equivalent to your existing query - I did it rather quickly - would be better for you - it can use index access which I assume is what you want

ops$tkyte%ORA11GR2> create index t_idx2 on t(name,id,col2id);

Index created.

ops$tkyte%ORA11GR2> select id objId,
  2         max(value) col_ind_type,
  3             (select max(t2.value) from t t2 where t2.name = 'TABLE' and t2.id = t1.id and t2.col2id = t1.col2id) col_tab_type,
  4             (select max(t2.value) from t t2 where t2.name = 'VIEW' and t2.id = t1.id and t2.col2id = t1.col2id) col_view_type,
  5             (select max(t2.value) from t t2 where t2.name = 'SYNONYM' and t2.id = t1.id and t2.col2id = t1.col2id) col_synonym_type,
  6             col2id
  7    from t t1
  8   where value = 27
  9     and name = 'INDEX'
 10     and col2id is not null
 11     and (select max(t2.value) from t t2 where t2.name = 'INDEX' and t2.id = t1.id and t2.col2id = t1.col2id) = 27
 12   group by id, col2id
 13  /

     OBJID COL_IND_TYPE COL_TAB_TYPE COL_VIEW_TYPE COL_SYNONYM_TYPE     COL2ID
---------- ------------ ------------ ------------- ---------------- ----------
        53           27                                                     53

..contd..Predicate pushing ..

Manoj Kaparwan, November 29, 2010 - 1:09 am UTC

Tom,
In real scenario ( with real data which application uses)
The following results in a huge row set.

3 - filter("ID"="COL2ID" AND "COL2ID" IS NOT NULL)



i.e. there are many rows flowing out from the join condiction
"ID"="COL2ID" and COL2ID has NULLs as well.


..contd.. predicate pushing

Manoj Kaparwan, November 29, 2010 - 1:25 am UTC

Sorry Tom
extending the same question.
I chnaged the view to what application exactly uses.
and in table T creation chnaged the >>mod(rownum,100) col2id <<
and could see the  ( below) huge row set for the  query to start with.



SQL> CREATE OR REPLACE  VIEW  V_T
  2         (Objid, col_ind_type, col_tab_type,col_view_type,col_synonym_typecol2id, col2id
  3      )
  4     AS
  5        SELECT t1.ID Objid,
  6                     MAX (DECODE (t2.name, 'INDEX', t2.value, null)) AS col_ind_type,
  7                     MAX (DECODE (t2.name, 'TABLE', t2.value, null)) AS col_tab_type,
  8                     MAX (DECODE (t2.name, 'VIEW', t2.value, null)) AS col_view_type,
  9                    MAX (DECODE (t2.name, 'SYNONYM', t2.value, null)) AS col_synonym_type,
 10                    t2.col2id
 11    FROM T t1, t t2
 12     where t1.ID = t2.col2id
 13       GROUP BY
 14     t1.ID,t2.col2id
 15  ;

View created.


SQL> SELECT objid, col2id
  FROM v_t
 WHERE (col2id IS NOT NULL) AND col_ind_type = 27  2    3  ;

no rows selected

Elapsed: 00:00:00.14

Execution Plan
----------------------------------------------------------
Plan hash value: 2470956538

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |    71 |  1420 |    26  (24)| 00:00:01 |
|*  1 |  FILTER              |              |       |       |            |          |
|   2 |   HASH GROUP BY      |              |    71 |  1420 |    26  (24)| 00:00:01 |
|   3 |    NESTED LOOPS      |              | 22545 |   440K|    24  (17)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| T            | 22545 |   330K|    20   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN| SYS_C0095514 |     1 |     5 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(MAX(DECODE("T2"."NAME",'INDEX',"T2"."VALUE",NULL))=27)
   4 - filter("T2"."COL2ID" IS NOT NULL)
   5 - access("T1"."ID"="T2"."COL2ID")


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
      22637  consistent gets
          0  physical reads
          0  redo size
        386  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>

Tom Kyte
November 29, 2010 - 4:07 am UTC

see above

Hints usage

Arvind Mishra, October 24, 2011 - 12:25 am UTC

Hi Tom,

Can you please give URLS to the discussion on using following hints:

1)(NO)REWRITE
2)DRIVING_SITE
3)(NO)PARALLEL
4)(NO)APPEND ( Append is ok but no_append??)
5)CURSOR_SHARING_EXACT

Thanks,

Arvind

Tom Kyte
October 24, 2011 - 5:15 am UTC

http://www.oracle.com/pls/db112/homepage

plug them into the search.

Hint's Usage

Arvind Mishra, October 24, 2011 - 12:28 am UTC

Following link given in this page for view merging and predicate pushing is not showing the discussion intended to:

https://asktom.oracle.com/Misc/oramag/on-rollups-merges-and-moves.html

Please can you give me the correct URL?

Thanks,

Arvind

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library