1 TOP_ORDER_OPT_FLAG

当语句内含有TOP + ORDER，且ORDER BY列属于索引前导列时，是否进行优化，可取值为0、1。
创建数据：
DROP TABLE IF EXISTS TEST1;
CREATE TABLE TEST1 AS
SELECT LEVEL AS A,LEVEL%2 AS B
FROM DUAL CONNECT BY LEVEL<=10000000 ;

DROP TABLE IF EXISTS TEST2;
CREATE TABLE TEST2 AS
SELECT LEVEL AS A,LEVEL%2 AS B
FROM DUAL CONNECT BY LEVEL<=10000000;
commit;
create index ix_test1_a on TEST1(a);
create index ix_test2_a on TEST2(a);
测试语句1：
TOP_ORDER_OPT_FLAG为0时：
select
/+TOP_ORDER_OPT_FLAG(0)/
*
from test1 a
left join (select bb.* from test2 bb )b on a.a=b.a
order by a.a
limit 10;
执行成功, 执行耗时16秒 299毫秒. 执行号:199348

通过HASH JOIN后，会产生一个非常大的结果集，且代价很高。
TOP_ORDER_OPT_FLAG为0，调整默认HASH桶数为1kw。
select
/+TOP_ORDER_OPT_FLAG(0) JOIN_HASH_SIZE(10000000)+/ *
from test1 a
left join (select bb.* from test2 bb )b on a.a=b.a
order by a.a
limit 10;
执行成功, 执行耗时2秒 29毫秒. 执行号:199350

虽然代价和结果集没有变化，但查询时间明显降低了。
TOP_ORDER_OPT_FLAG为1，调整默认HASH桶数为1kw。
select
/+TOP_ORDER_OPT_FLAG(1) JOIN_HASH_SIZE(10000000)+/ *
from test1 a
left join (select bb.* from test2 bb )b on a.a=b.a
order by a.a
limit 10;
执行成功, 执行耗时2毫秒. 执行号:199352

可以看到，ORDER BY列TEST1.a有索引，因此使用该索引去掉了SORT
上述SQL的另一种写法：
SELECT
/+TOP_ORDER_OPT_FLAG(1) JOIN_HASH_SIZE(10000000)+/
TOP 10 * FROM
TEST1
LEFT JOIN TEST2
ON TEST1.A = TEST2.A ORDER BY TEST1.A;

2 ENABLE_RQ_TO_NONREF_SPL

是否使用SPL2方式对相关子查询去除相关性，取值0、1、2。
ENABLE_RQ_TO_NONREF_SPL=0时，不进行特别处理；ENABLE_RQ_TO_NONREF_SPL=1时，对查询项中出现的相关子查询表达式进行优化处理；ENABLE_RQ_TO_NONREF_SPL=2时，对查询项和WHERE表达式中出现的相关子查询表达式进行优化处理。
测试语句1：
ENABLE_RQ_TO_NONREF_SPL为0时：
select /+ ENABLE_RQ_TO_NONREF_SPL(0)/ A.C1,(SELECT MIN(B.C1) FROM TEST2 B WHERE B.C1=A.C1)
FROM TEST1 A
LIMIT 10;
执行成功, 执行耗时727毫秒. 执行号:247010。

首先将TEST1和TEST2表进行关联，然后用TEST1表进行探测。
ENABLE_RQ_TO_NONREF_SPL为1时：
select /+ ENABLE_RQ_TO_NONREF_SPL(1)/ A.C1,(SELECT MIN(B.C1) FROM TEST2 B WHERE B.C1=A.C1)
FROM TEST1 A
LIMIT 10;
执行成功, 执行耗时22毫秒. 执行号:247011

当调整ENABLE_RQ_TO_NONREF_SPL为1时，将TEST1和TEST2表的连接结果转化为变量，表TEST1按行进行探测。查询时间明显优化。
测试语句2：
ENABLE_RQ_TO_NONREF_SPL为0时
select /+ ENABLE_RQ_TO_NONREF_SPL(0)/ count(0)
from (
select A.C1,(SELECT MIN(B.C1) FROM TEST2 B WHERE B.C1=A.C1) as SPL
FROM TEST1 A
)
where spl=11111 ;
执行成功, 执行耗时850毫秒. 执行号:247033

ENABLE_RQ_TO_NONREF_SPL为1时
select /+ ENABLE_RQ_TO_NONREF_SPL(1)/ count(0)
from (
select A.C1,(SELECT MIN(B.C1) FROM TEST2 B WHERE B.C1=A.C1) as SPL
FROM TEST1 A
)
where spl=11111 ;
执行成功, 执行耗时2秒 381毫秒. 执行号:247035

将表TEST2与TEST1的连接作为变量后，因为后面还有spl=11111，即还需要将TEST2的C1列作为过滤条件，因此该情况下，不使用变量优化较快。

3 VIEW_PULLUP_FLAG

VIEW_PULLUP_FLAG参数，是否视图上拉，将其转化为原始定义，取值0、1、2。
创建数据：
DROP TABLE IF EXISTS TEST1;
CREATE TABLE TEST1 AS SELECT LEVEL AS c1,LEVEL+1 AS c2,LEVEL+1 AS c3,LEVEL+1 AS c4,LEVEL+1 AS c5 FROM DUAL CONNECT BY LEVEL<=10000;
DROP TABLE IF EXISTS TEST2;
CREATE TABLE TEST2 AS SELECT LEVEL AS C1,LEVEL+1 AS C2 FROM DUAL CONNECT BY LEVEL<=10000 ;
DROP TABLE IF EXISTS TEST3;
CREATE TABLE TEST3 AS SELECT LEVEL AS C1,LEVEL+1 AS C2 FROM DUAL CONNECT BY LEVEL<=10000 ;
commit;
--视图定义脚本
create or replace view V1 as select a.* from test2 a join test3 b on a.c1=b.c2 ;
测试语句1：
不使用视图上拉VIEW_PULLUP_FLAG(0)
select /+VIEW_PULLUP_FLAG(0)/ *
from test1,v1
where test1.c1=v1.c1 and test1.c2=100;

使用视图上拉VIEW_PULLUP_FLAG(1)
select /+VIEW_PULLUP_FLAG(1)/ *
from test1,v1
where test1.c1=v1.c1 and test1.c2=100;

两次查询的执行计划是一样的，这里是因为视图中存在与外面的表相同的列名。此时不会上拉。

使用视图上拉VIEW_PULLUP_FLAG(2)

视图上拉2：对包含别名和同名列的视图也进行上拉优化。

4 VIEW_PULLUP_FLAG

谓词下推
创建数据：使用3中的数据
测试语句1：
不使词下推
select /+VIEW_PULLUP_FLAG(0) FILTER_PUSH_DOWN(0)/ *
from test1,v1
where test1.c1=v1.c1 and v1.c2=1111

测试语句2
使用下推
select /+VIEW_PULLUP_FLAG(0) FILTER_PUSH_DOWN(1)/ *
from test1,v1
where test1.c1=v1.c1 and v1.c2=1111

两次使用谓词下推的结果是相同的，即优化器认定此种情况下使用谓词下推是优于不使用谓词下推的。