[20190430]注意sql hint寫法.txt

[20190430]注意sql hint寫法.txt

--//連結:

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 cache

old   1: select * from V$SQL_HINT where name like upper('%&1%')

new   1: select * from V$SQL_HINT where name like upper('%cache%')

NAME             SQL_FEATURE     CLASS        INVERSE         TARGET_LEVEL   PROPERTY VERSION  VERSION_OUTLINE

---------------- --------------- ------------ --------------- ------------ ---------- -------- ----------------

CACHE_CB         QKSFM_CBO       CACHE_CB     NOCACHE                    4        256 8.1.5

CACHE            QKSFM_EXECUTION CACHE        NOCACHE                    4        256 8.1.0

NOCACHE          QKSFM_EXECUTION CACHE        CACHE                      4        256 8.1.0

CACHE_TEMP_TABLE QKSFM_ALL       CACHE        NOCACHE                    4        256 8.1.5

RESULT_CACHE     QKSFM_EXECUTION RESULT_CACHE NO_RESULT_CACHE            2          0 11.1.0.6

NO_RESULT_CACHE  QKSFM_EXECUTION RESULT_CACHE RESULT_CACHE               2          0 11.1.0.6

6 rows selected.

SCOTT@book> select /*+ result cache */ * from dept ;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

--//注意中間沒有"_".

SCOTT@book> @ dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  5sm6uuf1wtunm, child number 0

-------------------------------------

select /*+ result cache */ * from dept

Plan hash value: 3383998547

---------------------------------------------------------------------------

| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          |

|   1 |  TABLE ACCESS FULL| DEPT |      4 |    80 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$1 / DEPT@SEL$1

SCOTT@book> select /*+ result_cache */ * from dept ;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

SCOTT@book> @ dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  dh09kah6tkdjy, child number 0

-------------------------------------

select /*+ result_cache */ * from dept

Plan hash value: 3383998547

--------------------------------------------------------------------------------------------------

| Id  | Operation          | Name                       | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                            |        |       |     3 (100)|          |

|   1 |  RESULT CACHE      | gsg6g7y8rvxaydjyjh2g2yr21r |        |       |            |          |

|   2 |   TABLE ACCESS FULL| DEPT                       |      4 |    80 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$1

   2 - SEL$1 / DEPT@SEL$1

--//連結:也提到一種情況:

--//我沒有19c.

SCOTT@book> select /*+ use_nl(emp dept) */ * from dept ,emp where dept.deptno=emp.deptno;

...

SCOTT@book> @ dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  gk5d852xxj4b5, child number 0

-------------------------------------

select /*+ use_nl(emp dept) */ * from dept ,emp where

dept.deptno=emp.deptno

Plan hash value: 4192419542

----------------------------------------------------------------------------

| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |        |       |    10 (100)|          |

|   1 |  NESTED LOOPS      |      |     14 |   812 |    10   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| DEPT |      4 |    80 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| EMP  |      4 |   152 |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$1

   2 - SEL$1 / DEPT@SEL$1

   3 - SEL$1 / EMP@SEL$1

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("DEPT"."DEPTNO"="EMP"."DEPTNO")

--//注意看執行計劃,實際上主驅動dept表.只有寫成如下:

SCOTT@book> select /*+ use_nl(emp ) */ * from dept ,emp where dept.deptno=emp.deptno;

..

Plan hash value: 1123238657

-------------------------------------------------------------------------------------------------------

| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |        |       |     6 (100)|          |       |       |          |

|*  1 |  HASH JOIN         |      |     14 |   812 |     6   (0)| 00:00:01 |  1048K|  1048K|  662K (0)|

|   2 |   TABLE ACCESS FULL| EMP  |     14 |   532 |     3   (0)| 00:00:01 |       |       |          |

|   3 |   TABLE ACCESS FULL| DEPT |      4 |    80 |     3   (0)| 00:00:01 |       |       |          |

-------------------------------------------------------------------------------------------------------

--//這樣emp才能作為驅動表.

--//最後一種情況是我經常犯的錯誤..