Oracle-参数学习_no_or_expansion
Oracle-参数学习_no_or_expansion
一、需求，某客户问_no_or_expansion=TRUE 这个参数干啥用的？是否建议在11.2.0.4环境上设置？
需求如上，那么我们首先了解一下这个参数是干啥的？ 另外就是探索为什么客户以前的DBA设置这个参数？ 非默认隐含参数
二、参数说明
2.1 参数含义
参考一个网站的论坛 https:
说的很清楚！
Or expansion * emp id= OR name= OR condition , SQL into two pieces * emp id= * emp name=
2.2 参数测试对比
未创建索引前，观察设置参数后，执行计划和参数默认false一样，没啥子区别！
--------------------------------------------------------------------------------_no_or_expansion
FALSE
OR expansion during optimization disabled
alter session set "_no_or_expansion"=true;set linesize 500set 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 500set 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 500set 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 : ALL_SDO_GEOM_METADATA QUERIES RESULTS IN ORA-. - ORA- error on query with join on UNION ALL view (Doc ID - Enterprise Edition - Version .--Expansion allows the query to execute then you may have encountered Oracle Spatial Systems : WRONG RESULT WITH OR-EXPANSION CAUSED BY FIX TO BUG . RDBMS . QRY OPTIMIZER PRODID- PORTID--EXPANSION CAUSED BY FIX TO BUG . Product Version - Oracle Solaris on SPARC (-bit) 客户新的机器DB版本11.2.0.4，结论是可以不设置这个参数，如果DB <=11.2.0.2的情况下，如果有补丁也行，否则这个参数设置可以不用取消，放着就行！