[20210120]提示加入注解.txt

[20210120]提示加入注解.txt --//经常做sql语句优化,需要手工加入各种提示,但是有时候要取消很麻烦,我个人喜欢直接加入一些11,12之类的字符在提示前. --//看崔华<基于Oracle的SQL优化>,看到许多例子自己根据工作需要自己测试看看. --//通过例子说明: 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 2.测试: select /*+  gather_plan_statistics   use_hash(emp) */ * from dept,emp where dept.deptno=emp.deptno; --//执行计划如下: Plan hash value: 615168685 ------------------------------------------------------------------------------------------------------------------------------------------------ | Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT   |      |      1 |        |       |     6 (100)|          |     14 |00:00:00.01 |      12 |       |       |          | |*  1 |  HASH JOIN         |      |      1 |     14 |   812 |     6   (0)| 00:00:01 |     14 |00:00:00.01 |      12 |  1321K|  1321K| 1041K (0)| |   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |    80 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |       |       |          | |   3 |   TABLE ACCESS FULL| EMP  |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       6 |       |       |          | ------------------------------------------------------------------------------------------------------------------------------------------------ select /*+ comment gather_plan_statistics   use_hash(emp) */ * from dept,emp where dept.deptno=emp.deptno; --//执行计划如下: ----------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ----------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |     14 |   812 |     6  (17)| 00:00:01 | |   1 |  MERGE JOIN                  |         |     14 |   812 |     6  (17)| 00:00:01 | |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      4 |    80 |     2   (0)| 00:00:01 | |   3 |    INDEX FULL SCAN           | PK_DEPT |      4 |       |     1   (0)| 00:00:01 | |*  4 |   SORT JOIN                  |         |     14 |   532 |     4  (25)| 00:00:01 | |   5 |    TABLE ACCESS FULL         | EMP     |     14 |   532 |     3   (0)| 00:00:01 | ----------------------------------------------------------------------------------------- --//加入comment,这个是oracle的关键字,而后面的use_hash(emp)也无效了.我感觉有点奇怪.也就是这样的方式取消整个提示. --//这个倒是不错取消整个提示的好方式. --//也可以简单地使用,替换comment. select /*+ , gather_plan_statistics   use_hash(emp) */ * from dept,emp where dept.deptno=emp.deptno; --//执行计划如下: Plan hash value: 844388907 -------------------------------------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |        |       |     6 (100)|          |       |       |          | |   1 |  MERGE JOIN                  |         |     14 |   812 |     6  (17)| 00:00:01 |       |       |          | |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      4 |    80 |     2   (0)| 00:00:01 |       |       |          | |   3 |    INDEX FULL SCAN           | PK_DEPT |      4 |       |     1   (0)| 00:00:01 |       |       |          | |*  4 |   SORT JOIN                  |         |     14 |   532 |     4  (25)| 00:00:01 |  2048 |  2048 | 2048  (0)| |   5 |    TABLE ACCESS FULL         | EMP     |     14 |   532 |     3   (0)| 00:00:01 |       |       |          | -------------------------------------------------------------------------------------------------------------------- --//如果想取消某个提示,可以直接在提示前加入--,修改如下: select /*+ --gather_plan_statistics   use_hash(emp) */ * from dept,emp where dept.deptno=emp.deptno; --//执行计划如下: ------------------------------------------------------------------------------------------------------- | 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 |  1321K|  1321K| 1074K (0)| |   2 |   TABLE ACCESS FULL| DEPT |      4 |    80 |     3   (0)| 00:00:01 |       |       |          | |   3 |   TABLE ACCESS FULL| EMP  |     14 |   532 |     3   (0)| 00:00:01 |       |       |          | ------------------------------------------------------------------------------------------------------- select /*+ gather_plan_statistics   --use_hash(emp) */ * from dept,emp where dept.deptno=emp.deptno; --//执行计划如下: Plan hash value: 844388907 ------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |      1 |        |       |     6 (100)|          |     14 |00:00:00.01 |       8 |       |       |          | |   1 |  MERGE JOIN                  |         |      1 |     14 |   812 |     6  (17)| 00:00:01 |     14 |00:00:00.01 |       8 |       |       |          | |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |    80 |     2   (0)| 00:00:01 |      4 |00:00:00.01 |       2 |       |       |          | |   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |       |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       1 |       |       |          | |*  4 |   SORT JOIN                  |         |      4 |     14 |   532 |     4  (25)| 00:00:01 |     14 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)| |   5 |    TABLE ACCESS FULL         | EMP     |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       6 |       |       |          | ------------------------------------------------------------------------------------------------------------------------------------------------------------- 总结: 1.取消整个提示,在最前面加入, 或者comment. 2.另外我个人主张一个提示写一行,特别在测试与优化时. 3.取消单个提示,可以在前面加入--. 4.如果大家做优化加入提示有什么好方法,欢迎加入讨论.