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.