[20201130]11g or_expand提示.txt

来源：这里教程网时间：2026-03-03 16:17:18 作者：

[20201130]11g or_expand提示.txt --//最近看了几篇使用or_expand提示的文章，我发现实际上在11g根本无法使用，做1个记录。只能使用USE_CONCAT提示。 1.环境： SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> @ sqlhint or_expand NAME SQL_FEATURE CLASS INVERSE TARGET_LEVEL PROPERTY VERSION VERSION_OUTLINE --------- --------------- --------- ------------------------------ ------------ ---------- -------------- ------------------------- OR_EXPAND QKSFM_OR_EXPAND OR_EXPAND 4 272 8.1.7 --//说明这个提示是存在的。 SCOTT@book> @ sqlhint concat NAME SQL_FEATURE CLASS INVERSE TARGET_LEVEL PROPERTY VERSION VERSION_OUTLINE ---------- ---------------- ---------- ---------- ------------ ---------- ------------------------- ------------------------- USE_CONCAT QKSFM_USE_CONCAT USE_CONCAT NO_EXPAND 2 16 8.1.0 8.1.7 --//USE_CONCAT的相反是NO_EXPAND。这个提示感觉命名不是太好。 2.测试: SCOTT@book> create index i_emp_ename on emp(ename); Index created. SCOTT@book> select /*+ or_expand */ * from emp where empno=7369 or ename like 'S%' ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 Plan hash value: 407431354 --------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | CONCATENATION | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 76 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | I_EMP_ENAME | 2 | | 1 (0)| 00:00:01 | |* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| | --------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1_1 / EMP@SEL$1 3 - SEL$1_1 / EMP@SEL$1 4 - SEL$1_2 / EMP@SEL$1_2 5 - SEL$1_2 / EMP@SEL$1_2 Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ENAME" LIKE 'S%') filter("ENAME" LIKE 'S%') 4 - filter(LNNVL("ENAME" LIKE 'S%')) 5 - access("EMPNO"=7369) --//实际上使用的USE_CONCAT。 --//换一句话讲11g根本不支持or_expand提示优化。看看no_expand提示。 SCOTT@book> select /*+ no_expand(@sel$1 ) */ * from emp where empno=7369 or ename = 'S'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 SCOTT@book> @ dpc '' advanced PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 4zgthyh2j6jya, child number 0 ------------------------------------- select /*+ no_expand(@sel$1 ) */ * from emp where empno=7369 or ename = 'S' Plan hash value: 3589351319 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 2 | 76 | 3 (0)| 00:00:01 | | 2 | BITMAP CONVERSION TO ROWIDS | | | | | | | 3 | BITMAP OR | | | | | | | 4 | BITMAP CONVERSION FROM ROWIDS| | | | | | |* 5 | INDEX RANGE SCAN | PK_EMP | | | 0 (0)| | | 6 | BITMAP CONVERSION FROM ROWIDS| | | | | | |* 7 | INDEX RANGE SCAN | I_EMP_ENAME | | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- SCOTT@book> select /*+ no_expand(@sel$1 ) */ * from emp where empno=7369 or ename like 'S%'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 Plan hash value: 3956160932 --------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / EMP@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("EMPNO"=7369 OR "ENAME" LIKE 'S%')) --//总之我在11g下无法实现or_expand.另外感觉如果支持or_expand提示的话，use_concat与or_expand非常相似， --//那位能讲讲两者的那些不同。