一、需求，某客户问_no_or_expansion=TRUE 这个参数干啥用的？是否建议在11.2.0.4环境上设置？
需求如上，那么我们首先了解一下这个参数是干啥的？ 另外就是探索为什么客户以前的DBA设置这个参数？ 非默认隐含参数
二、参数说明
2.1 参数含义
参考一个网站的论坛
https://community.oracle.com/mosc/discussion/3058861/no-or-expansion-parameter
说的很清楚！
Or expansion is a kind of query transformation that converts OR to UNION. Say you have a query select * from emp where id=1 OR name='Vishal' Oracle may choose to expand this OR condition , break this SQL into two pieces using a UNION clause. New transformed query would look like select * from emp where id=1 UNION select * from emp where name='Vishal'
就是Oracle内部是否会将Or 转换为union 进行展开！ 默认展开，True参数设置后，则不允许展开
2.2 参数测试对比
未创建索引前，观察设置参数后，执行计划和参数默认false一样，没啥子区别！
-------------------------------------------------------------------------------- _no_or_expansion FALSE OR expansion during optimization disabled alter session set "_no_or_expansion"=true; set linesize 500 set termout off alter session set statistics_level=all; select * from scott.emp where EMPNO=7782 OR ename='FORD'; select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS')); -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 2 |00:00:00.01 | 7 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 2 | 76 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 7 | Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / EMP@SEL$1 ----------------------------------------------------------------- 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$1") FULL(@"SEL$1" "EMP"@"SEL$1") END_OUTLINE_DATA */ 1 - filter(("EMPNO"=7782 OR "ENAME"='FORD')) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
再次观察这个参数啥子作用？
"或"的扩张。
使用"or"连接由不同字段构成的查询条件下，按照查询条件将整个查询分为多个独立的查询，为各个独立查询制定最优查询路径，然后查询完的结果再组合起来。这叫“或的扩张”。当然，只有当使用or的各个查询条件为驱动查询条件时，并且or连接的是不同的字段，才能制定这样的执行计划，否则，会走全表扫然后然后将or的查询条件当过滤用途，当然还可能选择将rowid走bitmap or，后面有介绍。
_no_or_expansion参数，默认false，true的话会禁止“或的扩张” /*+use_concat*/是想使用或扩张的hint
创建索引！！！ or两个字段都创建索引，在观察对比
SQL> create index scott.id on scott.emp(empno); create index scott.id on scott.emp(empno) * ERROR at line 1: ORA-01408: such column list already indexed SQL> create index scott.name_ind on scott.emp(ename);
参数调整为非默认true,这种情况参数如果有效果，则无法使用两个索引进行汇总聚合返回结果
alter system set "_no_or_expansion"=true; set linesize 500 set termout off alter session set statistics_level=all; select * from scott.emp where EMPNO=7782 OR ename='FORD'; select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS')); --------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 2 |00:00:00.01 | 4 | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 2 | 76 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 4 | | 2 | BITMAP CONVERSION TO ROWIDS | | 1 | | | | | 2 |00:00:00.01 | 2 | | 3 | BITMAP OR | | 1 | | | | | 1 |00:00:00.01 | 2 | | 4 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 1 |00:00:00.01 | 1 | |* 5 | INDEX RANGE SCAN | PK_EMP | 1 | | | 0 (0)| | 1 |00:00:00.01 | 1 | | 6 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 1 |00:00:00.01 | 1 | |* 7 | INDEX RANGE SCAN | NAME_IND | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | --------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("EMPNO"=7782) 7 - access("ENAME"='FORD')
参数默认不调整的情况下
alter system set "_no_or_expansion"=false; set linesize 500 set termout off alter session set statistics_level=all; select * from scott.emp where EMPNO=7782 OR ename='FORD'; select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS')); --------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 2 |00:00:00.01 | 4 | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 2 | 76 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 4 | | 2 | BITMAP CONVERSION TO ROWIDS | | 1 | | | | | 2 |00:00:00.01 | 2 | | 3 | BITMAP OR | | 1 | | | | | 1 |00:00:00.01 | 2 | | 4 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 1 |00:00:00.01 | 1 | |* 5 | INDEX RANGE SCAN | PK_EMP | 1 | | | 0 (0)| | 1 |00:00:00.01 | 1 | | 6 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 1 |00:00:00.01 | 1 | |* 7 | INDEX RANGE SCAN | NAME_IND | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | ---------------------------------------------------------------------------------------------------------------------------------------
没啥子区别，可以说明这个参数到了Oracle 11.2.0.4 版本已经被废弃了，参数没有起到效果！！！
2.3 追溯为什么要设置这个参数？
--客户历史老的DBA不可能无缘无故调整隐含参数，一般是出现了问题，随后的DB都统一调整！
那么与这个参数相关的问题，有哪些？ 请阅读如下MOS截取的信息 如果出现ORA-932 并且符合这个版本，可以考虑重点关注一下！！！
Bug 9592003 : ALL_SDO_GEOM_METADATA QUERIES RESULTS IN ORA-00932 UDT GOT MDSYS.SDO_DIM_ARRAY Product Version 10.2.0.4 Bug 9592003 - ORA-932 error on query with join on UNION ALL view (Doc ID 9592003.8) Oracle Database - Enterprise Edition - Version 10.1.0.2 and later Description ORA-932 error on query with join on union view. Rediscovery Notes: If a query with a join on a union view gets ORA-932 error and disabling or-Expansion allows the query to execute then you may have encountered this bug. This can have a major impact in Oracle Spatial Systems as it was discovered in a very common recursive query against the core ALL_SDO_GEOM_METADATA view. Workaround Use NO_EXPAND hint or set "_no_or_expansion" to TRUE. Bug 12642813 : WRONG RESULT WITH OR-EXPANSION CAUSED BY FIX TO BUG 399198 Hdr: 12642813 11.2.0.2 RDBMS 11.2.0.2 QRY OPTIMIZER PRODID-5 PORTID-23 Abstract: WRONG RESULT WITH OR-EXPANSION CAUSED BY FIX TO BUG 399198 Product Version 11.2.0.2 Fixed in Product Version 12.1 Platform 23 - Oracle Solaris on SPARC (64-bit)
客户新的机器DB版本11.2.0.4，结论是可以不设置这个参数，如果DB <=11.2.0.2的情况下，如果有补丁也行，否则这个参数设置可以不用取消，放着就行！