View Merge 在安全控制上的变化，是 BUG 还是增强 ？

什么是 View Merge

View Merge 是 12C 引入的新特性，也是一种优化手段。当查询中引用了 View 或 inline view 时，优化器可以将主查询中的查询条件并入视图当中去进行优化选择以获得代价最小的执行计划。而如果视图不属于当前执行语句的用户，View Merge 就可能存在潜在 OPTIMIZER_SECURE_VIEW_MERGING（默认是 TRUE）控制。当执行语句的用户缺乏对视图的 MERGE VIEW 权限，也没有 MERGE ANY VIEW 权限时，是否允许优化器进行 View Merge 优化。

View Merge 问题重现

下面是一个安全控制导致语句未能正确选择索引的演示。首先创建相应的测试用户（demo）并授予相应的权限。

SQL 代码如下：

SQL> conn / as sysdba Connected. SQL> drop user demo cascade; User dropped. SQL> create user demo identified by demo; User created. SQL> grant CREATE SYNONYM,UNLIMITED TABLESPACE to demo; Grant succeeded. SQL> grant CONNECT,PLUSTRACE,RESOURCE to demo; Grant succeeded.

创建相应的测试函数。

SQL 代码如下：

SQL> conn demo/demo Connected. SQL> create or replace FUNCTION fnCheckNumber(in_num IN VARCHAR2) RETURN NUMBER IS 2 BEGIN 3 return 1; 4 end; 5 / Function created.

创建测试用户 demo 2，授予连接与 resource 的权限。

SQL 代码如下：

SQL> conn / as sysdba Connected. SQL> drop user demo2 cascade; User dropped. SQL> create user demo2 identified by demo2; User created. SQL> grant create session, resource to demo2; Grant succeeded. SQL> alter user demo2 default tablespace lmt_data; User altered. SQL> alter user demo2 quota unlimited on lmt_data; User altered. SQL> grant create view to demo2; Grant succeeded. SQL> drop user demo2 cascade; User dropped.

创建测试表 t1，t2 以及测试视图 v1，v2。并往表中加入测试数据，同时授予 demo 访问的权限。

SQL 代码如下：

SQL>conn demo2/demo2 Connected. SQL> create table t1 as select * from all_tables; Table created. SQL> create table t2 as select * from all_objects; Table created. SQL> create unique index t2_idx1 on t2(object_id) compute statistics; Index created. SQL> create view v1 as select * from t1; View created. SQL> create view v2 as select * from t2; View created. SQL> grant select on t1 to demo; Grant succeeded. SQL> grant select on t2 to demo; Grant succeeded. SQL> grant select on v1 to demo; Grant succeeded. SQL> grant select on v2 to demo; Grant succeeded.

将 share pool 以前保存的 SQL 执行计划全部清空，释放少数的共享池资源，保证 SQL 执行计划的重新解析。

SQL 代码如下：

SQL> conn / as sysdba Connected. SQL> alter system flush shared_pool; System altered.

查看执行计划。

SQL 代码如下：

SQL> conn demo/demo Connected. SQL> set autot trace SQL> select fnCheckNumber(tablespace_name) from demo2.v1 union all select 1 from demo2.v2 where object_id = fnCheckNumber('567785951'); 106 rows selected. Execution Plan ---------------------------------------------- Plan hash value: 3515064724 ------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------- | 0 | SELECT STATEMENT | | 75158 | 953K| 40 (3)| 00:00:01 | | 1 | UNION-ALL | | | | | | | 2 | TABLE ACCESS FULL | T1 | 106 | 530 | 3 (0)| 00:00:01 | |* 3 | VIEW | V2 | 75052 | 952K| 37 (3)| 00:00:01 | | 4 | INDEX FAST FULL SCAN| T2_IDX1 | 75052 | 439K| 37 (3)| 00:00:01 | ----------------------------------------------- Predicate Information (identified by operation id): -------------------------------------- 3 - filter("OBJECT_ID"="FNCHECKNUMBER"('567785951')) Statistics ---------------------------------------- 661 recursive calls 2 db block gets 501 consistent gets 163 physical reads 0 redo size 2428 bytes sent via SQL*Net to client 629 bytes received via SQL*Net from client 9 SQL*Net roundtrips to/from client 33 sorts (memory) 0 sorts (disk) 106 rows processed

获取正确的索引访问方式

在上面的查询计划中，未能正确选择索引访问方式。当我们赋予用户 MERGE VIEW 权限（或修改 OPTIMIZER_SECURE_VIEW_MERGING 为 FALSE）后，执行计划获取到了正确的索引访问方式。

授权的 SQL 代码如下：

SQL> conn / as sysdba Connected. SQL> grant MERGE ANY VIEW to demo; Grant succeeded.

再对 share pool 中原有的执行计划进行清空一次，便于重新解析。

具体的 SQL 代码如下：

SQL> alter system flush shared_pool; System altered.

对上面语句再解析一遍，获取该语句的执行计划。

具体 SQL 代码与执行计划如下：

SQL> set autot trace SQL> select fnCheckNumber(tablespace_name) from demo2.v1 union all select 1 from demo2.v2 where object_id = fnCheckNumber('567785951'); 106 rows selected. Execution Plan ----------------------------------------------- Plan hash value: 809018835 --------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------- | 0 | SELECT STATEMENT | | 107 | 536 | 4 (0)| 00:00:01 | | 1 | UNION-ALL | | | | | | | 2 | TABLE ACCESS FULL| T1 | 106 | 530 | 3 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN| T2_IDX1 | 1 | 6 | 1 (0)| 00:00:01 | ----------------------------------------------------- Predicate Information (identified by operation id): ----------------------------------------------- 3 - access("OBJECT_ID"="FNCHECKNUMBER"('567785951')) Statistics ------------------------------------------ 680 recursive calls 2 db block gets 340 consistent gets 0 physical reads 0 redo size 2428 bytes sent via SQL*Net to client 629 bytes received via SQL*Net from client 9 SQL*Net roundtrips to/from client 33 sorts (memory) 0 sorts (disk) 106 rows processed

可以看到上面的执行计划已经正确选择索引访问方式了。

修改 optimizer_secure_view_merging 参数。

SQL 代码如下：

SQL> show parameter optimizer_secure_view_merging NAME TYPE VALUE -------------------------- ----------- ----------------------- optimizer_secure_view_merging boolean TRUE SQL> alter system set optimizer_secure_view_merging =false; System altered.

研究收获

从上面的案例可以分析出 View Merge 是 12C 的一个新特性，因为这个安全控制导致在查询计划中未能正确选择索引访问方式，只需要取消掉这个新特性或者将 MERGE ANY VIEW 授予用户后就可以得到相应正确的索引访问方式。