[20201130]11g or_expand提示.txt

[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非常相似，
--//那位能講講兩者的那些不同。