定义：

OBYE - order by elimination
The purpose of order-by elimination is to remove superfluous ORDER BY clauses from subqueries, inline views, and regular views. Top-level SELECT clauses are obviously not considered by this heuristic-based query transformation. ORDER BY clauses can be considered unnecessary when an ORDER BY is followed by an operation that doesn’t guarantee that it will return the rows ordered, or that it will return the rows in a different order

CBO在进行转换前先将sql中一些没有必要的排序操作去掉，防止一些不必要的排序操作。

参数：(默认为true)

KSPPINM KSPPST KSPPDESC
---------------------------------------- ------ -----------------------------------------------------------------
_optimizer_order_by_elimination_enabled TRUE Eliminates order bys from views before query transformation

也可由hint ELIMINATE_OBY和NO_ELIMINATE_OBY控制

例子：

sql文本:

SQL> select /*+qb_name(nb)*/ count(*) from (select /*+qb_name(sb)*/ * from lxy order by a);
COUNT(*)
----------
1000000

执行计划:

Plan hash value: 488019508
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 418 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| LXY | 1000K| 418 (2)| 00:00:06 |
-------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$22AD7931
2 - SEL$22AD7931 / LXY@SB
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$22AD7931")
MERGE(@"SEL$F096B05D")
OUTLINE(@"NB")
OUTLINE(@"SEL$F096B05D")
ELIMINATE_OBY(@"SB")--消除了查询块SB里的排序
OUTLINE(@"SB")
FULL(@"SEL$22AD7931" "LXY"@"SB")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22] --#keys=0说明operation id=1的操作并没有执行过排序，SORT AGGREGATE是聚合函数的操作

10053：

***************************

Order-by elimination (OBYE)

***************************
OBYE: Considering Order-by Elimination from view SB (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE: Removing order by from query block SB (#0) (order not used)

从10053中也可以发现CBO对消除了查询块SB里的排序操作

ITPub博客

Order By Elimination

定义：