Thanks for the question, Sakthivel.

Asked: February 23, 2017 - 5:22 pm UTC

Last updated: February 26, 2017 - 1:57 am UTC

Version: 11.2.0.3.0

Viewed 10K+ times! This question is

You Asked

====================================================
-- v_product view definition
create or replace view v_product
select <<column_names>>
from t_product
order by col_1, col_2;

--Below is the exiting code running since many years in production.
--This query is used to get the results and write it in external csv file.
SELECT << column_names >>
FROM v_product --> This is DB view shown above. Inside this view already having Order by Clause.
WHERE active_prod = 'Y'
AND << other column filters >>;
=======================================================

Now the requirement is to sort the result set with two new columns before writing into csv file.
Question is:
where is the right place to put Order by clause?

1). As per your some of other thread, i had put 'Order by' in the outermost query.

-- Final Query used to write it in csv file.
SELECT << column_names >>
FROM v_product --> Inside this view already having Order by Clause.
WHERE active_prod = 'Y'
AND << other column filters >>
Order by new_column_3, new_column_4 DESC; --> Added order by here.

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 65M| 9325M| | 2288K (1)| 00:04:36 |
| 1 | SORT ORDER BY | | 65M| 9325M| 12G| 2288K (1)| 00:04:36 |
| 2 | TABLE ACCESS FULL| WC_I110 | 65M| 9325M| | 127K (2)| 00:00:16 |
--------------------------------------------------------------------------------------

2). I have added these two news columns in the view itself (which is not the outermost query), Still gives the same execution plan and results.

--View definition modified with new order by clause.
create or replace view v_product
select <<column_names>>
from t_product
order by col_1, col_2
new_column_3, new_column_4 DESC; --> this part is added newly.

--Without modifying the Final query. still gives the same result.
SELECT << column_names >>
FROM v_product --> This is DB view. Inside this view already having Order by Clause.
where active_prod = 'Y'
and << other column filters >>;

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 65M| 9325M| | 2288K (1)| 00:04:36 |
| 1 | SORT ORDER BY | | 65M| 9325M| 12G| 2288K (1)| 00:04:36 |
| 2 | TABLE ACCESS FULL| WC_I110 | 65M| 9325M| | 127K (2)| 00:00:16 |
--------------------------------------------------------------------------------------

==> Could you please explain how is it possible to have same execution plan and results by putting Order by inside or outside view?

Thanks in advance for your help.

and Connor said...

It's because we're smart :-) Well...not so much me, but the optimizer is.

Before a query is optimized, it goes through a phase called "transformation" where we look for ways of re-writing the query which would potentially give more options for optimization *without* changing the intent of the SQL.

Here's an example

SQL> create table t as select * From dba_objects;

Table created.

SQL>
SQL> create or replace view v as
  2  select * from t
  3  order by object_name, object_id, created;

View created.

SQL>
SQL> alter session set events = '10053 trace name context forever, level 1';

Session altered.

SQL> explain plan for select * from v;

Explained.

SQL> explain plan for select * from v order by owner;

Explained.

The '10053' instructs the database to dump out optimizer information. You can see our view V with an order-by in the defintion, and then I looked at two potential queries on it.

In the trace file from the 10053, we see the following

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "V"."OWNER" "OWNER",
"V"."OBJECT_NAME" "OBJECT_NAME",
"V"."SUBOBJECT_NAME" "SUBOBJECT_NAME",
"V"."OBJECT_ID" "OBJECT_ID",
"V"."DATA_OBJECT_ID" 
"DATA_OBJECT_ID",
"V"."OBJECT_TYPE" "OBJECT_TYPE",
"V"."CREATED" "CREATED",
"V"."LAST_DDL_TIME" "LAST_DDL_TIME",
"V"."TIMESTAMP" "TIMESTAMP",
"V"."STATUS" "STATUS",
"V"."TEMPORARY" "TEMPORARY",
"V"."GENERATED" "GENERATED",
"V"."SECONDARY" "SECONDARY",
"V"."NAMESPACE" "NAMESPACE",
"V"."EDITION_NAME" "EDITION_NAME",
"V"."SHARING" "SHARING",
"V"."EDITIONABLE" "EDITIONABLE",
"V"."ORACLE_MAINTAINED" "ORACLE_MAINTAINED" 
FROM  (SELECT * /* MSQ_NON_UNPARSED_SELECT_LIST_FOR_VPD_VIEW */ 
       FROM MCDONAC."T" "T" WHERE 1=1 ORDER BY "T"."OBJECT_NAME","T"."OBJECT_ID", "T"."CREATED") "V"
       
       
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T"."OWNER" "OWNER",
"T"."OBJECT_NAME" "OBJECT_NAME",
"T"."SUBOBJECT_NAME" "SUBOBJECT_NAME",
"T"."OBJECT_ID" "OBJECT_ID",
"T"."DATA_OBJECT_ID" "DATA_OBJECT_ID",
"T"."OBJECT_TYPE" "OBJECT_TYPE",
"T"."CREATED" "CREATED",
"T"."LAST_DDL_TIME" "LAST_DDL_TIME",
"T"."TIMESTAMP" "TIMESTAMP",
"T"."STATUS" "STATUS",
"T"."TEMPORARY" "TEMPORARY",
"T"."GENERATED" "GENERATED",
"T"."SECONDARY" "SECONDARY",
"T"."NAMESPACE" "NAMESPACE",
"T"."EDITION_NAME" "EDITION_NAME",
"T"."SHARING" "SHARING",
"T"."EDITIONABLE" "EDITIONABLE",
"T"."ORACLE_MAINTAINED" "ORACLE_MAINTAINED" 
FROM MCDONAC."T" "T" 
WHERE 1=1 ORDER BY "T"."OWNER"

In the first example, we simply ran the view with its order by as per the defintion. With the second example, the optimizer detected that there is no benefit in ordering based on the view definition, because we're about to override it in the outer query. So the query was transformed to reflect that.

Rating

(4 ratings)

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

Comments

Surprised

A reader, February 24, 2017 - 7:55 am UTC

Hum? Are you really sure that your first sort on 3 cols give same results?

Order by c1.c2.c3

Order by c0

Order by c0, c1.c2.c3

Order by c1.c2.c3 + order by c0

February 24, 2017 - 10:37 am UTC

Not sure what you're getting at here. As Connor showed, if you have an order by inside a view, then use a different order by when selecting from it, the outer order by replaces the one in the view.

Sakthivel, February 24, 2017 - 11:16 am UTC

From your second example trace file 10053 shows, the optimizer detected that there is no benefit in ordering based on the view definition.
1). first question is:
So, what you would say is, Instead of putting Order by in View, have to put Order by only in the Final Query. So, it will guarantee that it will be sorted as expected. Please confirm if my understanding is correct.

2). second question is:
In that case, why the execution plan is same for both the cases.

Or its totally upto Optimizer to decide. Can't we conclude where to put order by? in this particular case.

February 25, 2017 - 1:10 am UTC

1) Yes, its rare for a view to have an ORDER BY unless you're confident that it will be used without any additional ordering (for example, it might be used just in canned queries by an application).

2) If you put a different ORDER BY in the query where you select from the view, then we will always respect that. Depending on the view complexity, we might even do the order by within the view, but we will always finish up with that last order-by in the query if present

Resume

A reader, February 25, 2017 - 5:27 am UTC

So in short expression :
Most outer order by clause will override the others. Right?

February 26, 2017 - 1:57 am UTC

You could phrase it that way - I'd phrase it more like - the results of a query will obey the intent of the SQL that generated that result.

A reader, February 26, 2017 - 6:20 am UTC

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Surprised

Resume

More to Explore

Security

'Order by' within or outside View, gives same execution plan and results

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Surprised

Resume

More to Explore

Security