备案控制台
探索云世界
开发者社区 数据库 文章 正文

[20150705]使用use_concat提示.txt

2015-07-06 819
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： [20150703]使用use_concat提示.txt --生产系统有一条sql语句遇到性能问题,由于生产系统语句非常复杂,我拿测试用户scott的表作为例子来说明: 1.

[20150703]使用use_concat提示.txt

--生产系统有一条sql语句遇到性能问题,由于生产系统语句非常复杂,我拿测试用户scott的表作为例子来说明:

1.问题提出:

SCOTT@test01p> @ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

语句如下:
SELECT *
  FROM dept, emp
WHERE     dept.deptno = EMP.DEPTNO
       AND (   (:a = 1 AND dept.dname = 'SALES')
            OR (:a = 2 AND dept.loc = ' ')
            OR (:a = 3 AND emp.ename = ' ')
            OR (:a = 4 AND emp.empno = 0)
            OR (:a = 5 AND emp.hiredate = '0000/01/01')
            OR (:a = 6 AND emp.sal = 0));

--我想大家看以上sql语句一定知道开发想实现的功能.我这里简化直接使用文字变量.
--实际上如果在dname输入,:a带入的参数是1.
--      如果emp.empno输入,:a 带入参数是4.
--这样情况下仅仅有1个条件执行通过.实际上写出上面语句的人真TMD变态.本来应该根据条件分别选择查询方式的,开发仅仅想通过1条
--sql语句概括全部情况

SCOTT@test01p> variable a number ;
SCOTT@test01p> exec :a :=1 ;

PL/SQL procedure successfully completed.

Plan hash value: 4009592485
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |        |       |     5 (100)|          |       |       |          |
|   1 |  MERGE JOIN                  |         |      1 |    59 |     5   (0)| 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 |   FILTER                     |         |        |       |            |          |       |       |          |
|*  5 |    SORT JOIN                 |         |     14 |   546 |     3   (0)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|   6 |     TABLE ACCESS FULL        | EMP     |     14 |   546 |     3   (0)| 00:00:01 |       |       |          |
--------------------------------------------------------------------------------------------------------------------

--可以发现emp表全表扫描.即使修改如下:
exec :a = :4 ;
SELECT *
  FROM dept, emp
WHERE     dept.deptno = EMP.DEPTNO
       AND (   (:a = 1 AND dept.dname = 'a')
            OR (:a = 2 AND dept.loc = 'a')
            OR (:a = 3 AND emp.ename = 'a')
            OR (:a = 4 AND emp.empno = 7499)
            OR (:a = 5 AND emp.hiredate = '0000/01/01')
            OR (:a = 6 AND emp.sal = 0));

Plan hash value: 4009592485
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |        |       |     5 (100)|          |       |       |          |
|   1 |  MERGE JOIN                  |         |      1 |    59 |     5   (0)| 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 |   FILTER                     |         |        |       |            |          |       |       |          |
|*  5 |    SORT JOIN                 |         |     14 |   546 |     3   (0)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|   6 |     TABLE ACCESS FULL        | EMP     |     14 |   546 |     3   (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 / DEPT@SEL$1
   6 - SEL$1 / EMP@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      DB_VERSION('12.1.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
      FULL(@"SEL$1" "EMP"@"SEL$1")
      LEADING(@"SEL$1" "DEPT"@"SEL$1" "EMP"@"SEL$1")
      USE_MERGE(@"SEL$1" "EMP"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(((:A=1 AND "DEPT"."DNAME"='a') OR (:A=2 AND "DEPT"."LOC"='a') OR (:A=3 AND "EMP"."ENAME"='a')
              OR (:A=4 AND "EMP"."EMPNO"=7499) OR (:A=5 AND "EMP"."HIREDATE"='0000/01/01') OR (:A=6 AND "EMP"."SAL"=0)))
   5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO")

--可以发现emp表全表扫描.

2.我这里假设走每个分支使用索引都很好,如何优化呢?oracle提供提示use_concat.

SELECT /*+ use_concat */ *
  FROM dept, emp
WHERE     dept.deptno = EMP.DEPTNO
       AND (   (:a = 1 AND dept.dname = 'SALES')
            OR (:a = 2 AND dept.loc = 'a')
            OR (:a = 3 AND emp.ename = 'a')
            OR (:a = 4 AND emp.empno = 7499)
            OR (:a = 5 AND emp.hiredate = '0000/01/01')
            OR (:a = 6 AND emp.sal = 0));

--注意最好不要使用'',这样老是不能出现我希望的执行计划.

Plan hash value: 376728639

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |              |        |       |    23 (100)|          |       |       |          |
|   1 |  CONCATENATION                          |              |        |       |            |          |       |       |          |
|*  2 |   FILTER                                |              |        |       |            |          |       |       |          |
|   3 |    NESTED LOOPS                         |              |        |       |            |          |       |       |          |
|   4 |     NESTED LOOPS                        |              |      1 |    59 |     3   (0)| 00:00:01 |       |       |          |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| EMP          |      1 |    39 |     2   (0)| 00:00:01 |       |       |          |
|*  6 |       INDEX RANGE SCAN                  | I_EMP_SAL    |      1 |       |     1   (0)| 00:00:01 |       |       |          |
|*  7 |      INDEX UNIQUE SCAN                  | PK_DEPT      |      1 |       |     0   (0)|          |       |       |          |
|   8 |     TABLE ACCESS BY INDEX ROWID         | DEPT         |      1 |    20 |     1   (0)| 00:00:01 |       |       |          |
|*  9 |   FILTER                                |              |        |       |            |          |       |       |          |
|  10 |    NESTED LOOPS                         |              |        |       |            |          |       |       |          |
|  11 |     NESTED LOOPS                        |              |      1 |    59 |     4   (0)| 00:00:01 |       |       |          |
|* 12 |      TABLE ACCESS FULL                  | EMP          |      1 |    39 |     3   (0)| 00:00:01 |       |       |          |
|* 13 |      INDEX UNIQUE SCAN                  | PK_DEPT      |      1 |       |     0   (0)|          |       |       |          |
|  14 |     TABLE ACCESS BY INDEX ROWID         | DEPT         |      1 |    20 |     1   (0)| 00:00:01 |       |       |          |
|* 15 |   FILTER                                |              |        |       |            |          |       |       |          |
|  16 |    NESTED LOOPS                         |              |      1 |    59 |     2   (0)| 00:00:01 |       |       |          |
|* 17 |     TABLE ACCESS BY INDEX ROWID         | EMP          |      1 |    39 |     1   (0)| 00:00:01 |       |       |          |
|* 18 |      INDEX UNIQUE SCAN                  | PK_EMP       |      1 |       |     0   (0)|          |       |       |          |
|  19 |     TABLE ACCESS BY INDEX ROWID         | DEPT         |      1 |    20 |     1   (0)| 00:00:01 |       |       |          |
|* 20 |      INDEX UNIQUE SCAN                  | PK_DEPT      |      1 |       |     0   (0)|          |       |       |          |
|* 21 |   FILTER                                |              |        |       |            |          |       |       |          |
|  22 |    NESTED LOOPS                         |              |        |       |            |          |       |       |          |
|  23 |     NESTED LOOPS                        |              |      1 |    59 |     4   (0)| 00:00:01 |       |       |          |
|* 24 |      TABLE ACCESS FULL                  | EMP          |      1 |    39 |     3   (0)| 00:00:01 |       |       |          |
|* 25 |      INDEX UNIQUE SCAN                  | PK_DEPT      |      1 |       |     0   (0)|          |       |       |          |
|  26 |     TABLE ACCESS BY INDEX ROWID         | DEPT         |      1 |    20 |     1   (0)| 00:00:01 |       |       |          |
|* 27 |   FILTER                                |              |        |       |            |          |       |       |          |
|  28 |    MERGE JOIN                           |              |      2 |   118 |     5   (0)| 00:00:01 |       |       |          |
|* 29 |     TABLE ACCESS BY INDEX ROWID         | DEPT         |      1 |    20 |     2   (0)| 00:00:01 |       |       |          |
|  30 |      INDEX FULL SCAN                    | PK_DEPT      |      4 |       |     1   (0)| 00:00:01 |       |       |          |
|* 31 |     SORT JOIN                           |              |     14 |   546 |     3   (0)| 00:00:01 | 73728 | 73728 |          |
|* 32 |      TABLE ACCESS FULL                  | EMP          |     14 |   546 |     3   (0)| 00:00:01 |       |       |          |
|* 33 |   FILTER                                |              |        |       |            |          |       |       |          |
|* 34 |    HASH JOIN                            |              |      5 |   295 |     5   (0)| 00:00:01 |  1172K|  1172K|  677K (0)|
|* 35 |     TABLE ACCESS BY INDEX ROWID BATCHED | DEPT         |      1 |    20 |     2   (0)| 00:00:01 |       |       |          |
|* 36 |      INDEX RANGE SCAN                   | I_DEPT_DNAME |      1 |       |     1   (0)| 00:00:01 |       |       |          |
|* 37 |     TABLE ACCESS FULL                   | EMP          |     14 |   546 |     3   (0)| 00:00:01 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------
...

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:A=6)
   6 - access("EMP"."SAL"=0)
   7 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
   9 - filter(:A=5)
  12 - filter(("EMP"."HIREDATE"='0000/01/01' AND (LNNVL(:A=6) OR LNNVL("EMP"."SAL"=0))))
  13 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
  15 - filter(:A=4)
  17 - filter(((LNNVL(:A=5) OR LNNVL("EMP"."HIREDATE"='0000/01/01')) AND (LNNVL(:A=6) OR LNNVL("EMP"."SAL"=0))))
  18 - access("EMP"."EMPNO"=7499)
  20 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
  21 - filter(:A=3)
  24 - filter(("EMP"."ENAME"='a' AND (LNNVL(:A=4) OR LNNVL("EMP"."EMPNO"=7499)) AND (LNNVL(:A=5) OR
              LNNVL("EMP"."HIREDATE"='0000/01/01')) AND (LNNVL(:A=6) OR LNNVL("EMP"."SAL"=0))))
  25 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
  27 - filter(:A=2)
  29 - filter("DEPT"."LOC"='a')
  31 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
  32 - filter(((LNNVL(:A=3) OR LNNVL("EMP"."ENAME"='a')) AND (LNNVL(:A=4) OR LNNVL("EMP"."EMPNO"=7499)) AND (LNNVL(:A=5) OR
              LNNVL("EMP"."HIREDATE"='0000/01/01')) AND (LNNVL(:A=6) OR LNNVL("EMP"."SAL"=0))))
  33 - filter(:A=1)
  34 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
  35 - filter((LNNVL(:A=2) OR LNNVL("DEPT"."LOC"='a')))
  36 - access("DEPT"."DNAME"='SALES')
  37 - filter(((LNNVL(:A=3) OR LNNVL("EMP"."ENAME"='a')) AND (LNNVL(:A=4) OR LNNVL("EMP"."EMPNO"=7499)) AND (LNNVL(:A=5) OR
              LNNVL("EMP"."HIREDATE"='0000/01/01')) AND (LNNVL(:A=6) OR LNNVL("EMP"."SAL"=0))))

--id =12 ,24,32,37 依旧出现全表扫描emp.
--id=12 确定要在emp.hiredata建立索引.
--id=24 确定要在emp.ename建立索引.
--id=32 无法确定,不过仔细看id=29,30,可以推出应该在dept.loc建立索引.
--id=37 走全表,主要问题是access("DEPT"."DNAME"='SALES')的对应emp记录很多.emp.deptno索引没有建立.

--先建立索引在看看执行计划:
create index i_dept_loc on dept(loc);
create index i_emp_hiredate on emp(hiredate);
create index i_emp_ename on emp(ename);
create index i_emp_deptno on emp(deptno);

SELECT /*+ use_concat */ *
  FROM dept, emp
WHERE     dept.deptno = EMP.DEPTNO
       AND (   (:a = 1 AND dept.dname = 'SALES')
            OR (:a = 2 AND dept.loc = 'a')
            OR (:a = 3 AND emp.ename = 'a')
            OR (:a = 4 AND emp.empno = 7499)
            OR (:a = 5 AND emp.hiredate = '0000/01/01')
            OR (:a = 6 AND emp.sal = 0));

Plan hash value: 2449074598

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                |        |       |    17 (100)|          |
|   1 |  CONCATENATION                          |                |        |       |            |          |
|*  2 |   FILTER                                |                |        |       |            |          |
|   3 |    NESTED LOOPS                         |                |        |       |            |          |
|   4 |     NESTED LOOPS                        |                |      1 |    59 |     3   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| EMP            |      1 |    39 |     2   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN                  | I_EMP_SAL      |      1 |       |     1   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN                  | PK_DEPT        |      1 |       |     0   (0)|          |
|   8 |     TABLE ACCESS BY INDEX ROWID         | DEPT           |      1 |    20 |     1   (0)| 00:00:01 |
|*  9 |   FILTER                                |                |        |       |            |          |
|  10 |    NESTED LOOPS                         |                |        |       |            |          |
|  11 |     NESTED LOOPS                        |                |      1 |    59 |     3   (0)| 00:00:01 |
|* 12 |      TABLE ACCESS BY INDEX ROWID BATCHED| EMP            |      1 |    39 |     2   (0)| 00:00:01 |
|* 13 |       INDEX RANGE SCAN                  | I_EMP_HIREDATE |      1 |       |     1   (0)| 00:00:01 |
|* 14 |      INDEX UNIQUE SCAN                  | PK_DEPT        |      1 |       |     0   (0)|          |
|  15 |     TABLE ACCESS BY INDEX ROWID         | DEPT           |      1 |    20 |     1   (0)| 00:00:01 |
|* 16 |   FILTER                                |                |        |       |            |          |
|  17 |    NESTED LOOPS                         |                |      1 |    59 |     2   (0)| 00:00:01 |
|* 18 |     TABLE ACCESS BY INDEX ROWID         | EMP            |      1 |    39 |     1   (0)| 00:00:01 |
|* 19 |      INDEX UNIQUE SCAN                  | PK_EMP         |      1 |       |     0   (0)|          |
|  20 |     TABLE ACCESS BY INDEX ROWID         | DEPT           |      1 |    20 |     1   (0)| 00:00:01 |
|* 21 |      INDEX UNIQUE SCAN                  | PK_DEPT        |      1 |       |     0   (0)|          |
|* 22 |   FILTER                                |                |        |       |            |          |
|  23 |    NESTED LOOPS                         |                |        |       |            |          |
|  24 |     NESTED LOOPS                        |                |      1 |    59 |     3   (0)| 00:00:01 |
|* 25 |      TABLE ACCESS BY INDEX ROWID BATCHED| EMP            |      1 |    39 |     2   (0)| 00:00:01 |
|* 26 |       INDEX RANGE SCAN                  | I_EMP_ENAME    |      1 |       |     1   (0)| 00:00:01 |
|* 27 |      INDEX UNIQUE SCAN                  | PK_DEPT        |      1 |       |     0   (0)|          |
|  28 |     TABLE ACCESS BY INDEX ROWID         | DEPT           |      1 |    20 |     1   (0)| 00:00:01 |
|* 29 |   FILTER                                |                |        |       |            |          |
|  30 |    NESTED LOOPS                         |                |        |       |            |          |
|  31 |     NESTED LOOPS                        |                |      2 |   118 |     3   (0)| 00:00:01 |
|  32 |      TABLE ACCESS BY INDEX ROWID BATCHED| DEPT           |      1 |    20 |     2   (0)| 00:00:01 |
|* 33 |       INDEX RANGE SCAN                  | I_DEPT_LOC     |      1 |       |     1   (0)| 00:00:01 |
|* 34 |      INDEX RANGE SCAN                   | I_EMP_DEPTNO   |      5 |       |     0   (0)|          |
|* 35 |     TABLE ACCESS BY INDEX ROWID         | EMP            |      5 |   195 |     1   (0)| 00:00:01 |
|* 36 |   FILTER                                |                |        |       |            |          |
|  37 |    NESTED LOOPS                         |                |        |       |            |          |
|  38 |     NESTED LOOPS                        |                |      5 |   295 |     3   (0)| 00:00:01 |
|* 39 |      TABLE ACCESS BY INDEX ROWID BATCHED| DEPT           |      1 |    20 |     2   (0)| 00:00:01 |
|* 40 |       INDEX RANGE SCAN                  | I_DEPT_DNAME   |      1 |       |     1   (0)| 00:00:01 |
|* 41 |      INDEX RANGE SCAN                   | I_EMP_DEPTNO   |      5 |       |     0   (0)|          |
|* 42 |     TABLE ACCESS BY INDEX ROWID         | EMP            |      5 |   195 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

--^_^,正是我需要的效果与执行计划.

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      DB_VERSION('12.1.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$1_1")
      USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(2))
      OUTLINE_LEAF(@"SEL$1_2")
      OUTLINE_LEAF(@"SEL$1_3")
      OUTLINE_LEAF(@"SEL$1_4")
      OUTLINE_LEAF(@"SEL$1_5")
      OUTLINE_LEAF(@"SEL$1_6")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1_1" "EMP"@"SEL$1" ("EMP"."SAL"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_1" "EMP"@"SEL$1")
      INDEX(@"SEL$1_1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
      INDEX_RS_ASC(@"SEL$1_2" "EMP"@"SEL$1_2" ("EMP"."HIREDATE"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_2" "EMP"@"SEL$1_2")
      INDEX(@"SEL$1_2" "DEPT"@"SEL$1_2" ("DEPT"."DEPTNO"))
      INDEX_RS_ASC(@"SEL$1_3" "EMP"@"SEL$1_3" ("EMP"."EMPNO"))
      INDEX_RS_ASC(@"SEL$1_3" "DEPT"@"SEL$1_3" ("DEPT"."DEPTNO"))
      INDEX_RS_ASC(@"SEL$1_4" "EMP"@"SEL$1_4" ("EMP"."ENAME"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_4" "EMP"@"SEL$1_4")
      INDEX(@"SEL$1_4" "DEPT"@"SEL$1_4" ("DEPT"."DEPTNO"))
      INDEX_RS_ASC(@"SEL$1_5" "DEPT"@"SEL$1_5" ("DEPT"."LOC"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_5" "DEPT"@"SEL$1_5")
      INDEX(@"SEL$1_5" "EMP"@"SEL$1_5" ("EMP"."DEPTNO"))
      INDEX_RS_ASC(@"SEL$1_6" "DEPT"@"SEL$1_6" ("DEPT"."DNAME"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_6" "DEPT"@"SEL$1_6")
      INDEX(@"SEL$1_6" "EMP"@"SEL$1_6" ("EMP"."DEPTNO"))
      LEADING(@"SEL$1_1" "EMP"@"SEL$1" "DEPT"@"SEL$1")
      LEADING(@"SEL$1_2" "EMP"@"SEL$1_2" "DEPT"@"SEL$1_2")
      LEADING(@"SEL$1_3" "EMP"@"SEL$1_3" "DEPT"@"SEL$1_3")
      LEADING(@"SEL$1_4" "EMP"@"SEL$1_4" "DEPT"@"SEL$1_4")
      LEADING(@"SEL$1_5" "DEPT"@"SEL$1_5" "EMP"@"SEL$1_5")
      LEADING(@"SEL$1_6" "DEPT"@"SEL$1_6" "EMP"@"SEL$1_6")
      USE_NL(@"SEL$1_1" "DEPT"@"SEL$1")
      NLJ_BATCHING(@"SEL$1_1" "DEPT"@"SEL$1")
      USE_NL(@"SEL$1_2" "DEPT"@"SEL$1_2")
      NLJ_BATCHING(@"SEL$1_2" "DEPT"@"SEL$1_2")
      USE_NL(@"SEL$1_3" "DEPT"@"SEL$1_3")
      USE_NL(@"SEL$1_4" "DEPT"@"SEL$1_4")
      NLJ_BATCHING(@"SEL$1_4" "DEPT"@"SEL$1_4")
      USE_NL(@"SEL$1_5" "EMP"@"SEL$1_5")
      NLJ_BATCHING(@"SEL$1_5" "EMP"@"SEL$1_5")
      USE_NL(@"SEL$1_6" "EMP"@"SEL$1_6")
      NLJ_BATCHING(@"SEL$1_6" "EMP"@"SEL$1_6")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:A=6)
   6 - access("EMP"."SAL"=0)
   7 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
   9 - filter(:A=5)
  12 - filter((LNNVL(:A=6) OR LNNVL("EMP"."SAL"=0)))
  13 - access("EMP"."HIREDATE"='0000/01/01')
  14 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
  16 - filter(:A=4)
  18 - filter(((LNNVL(:A=5) OR LNNVL("EMP"."HIREDATE"='0000/01/01')) AND (LNNVL(:A=6) OR
              LNNVL("EMP"."SAL"=0))))
  19 - access("EMP"."EMPNO"=7499)
  21 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
  22 - filter(:A=3)
  25 - filter(((LNNVL(:A=4) OR LNNVL("EMP"."EMPNO"=7499)) AND (LNNVL(:A=5) OR
              LNNVL("EMP"."HIREDATE"='0000/01/01')) AND (LNNVL(:A=6) OR LNNVL("EMP"."SAL"=0))))
  26 - access("EMP"."ENAME"='a')
  27 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
  29 - filter(:A=2)
  33 - access("DEPT"."LOC"='a')
  34 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
  35 - filter(((LNNVL(:A=3) OR LNNVL("EMP"."ENAME"='a')) AND (LNNVL(:A=4) OR
              LNNVL("EMP"."EMPNO"=7499)) AND (LNNVL(:A=5) OR LNNVL("EMP"."HIREDATE"='0000/01/01')) AND
              (LNNVL(:A=6) OR LNNVL("EMP"."SAL"=0))))
  36 - filter(:A=1)
  39 - filter((LNNVL(:A=2) OR LNNVL("DEPT"."LOC"='a')))
  40 - access("DEPT"."DNAME"='SALES')
  41 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
  42 - filter(((LNNVL(:A=3) OR LNNVL("EMP"."ENAME"='a')) AND (LNNVL(:A=4) OR
              LNNVL("EMP"."EMPNO"=7499)) AND (LNNVL(:A=5) OR LNNVL("EMP"."HIREDATE"='0000/01/01')) AND
              (LNNVL(:A=6) OR LNNVL("EMP"."SAL"=0))))

--总结:
1.先使用USE_CONCAT提示.
2.查看这个分支的filter条件,确定需要建立那些索引.
3.不明白提示里面的 USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(2)),里面的8,2表示什么...那位知道希望告诉我^_^.
4.只要每个分支都优化好,不要担心这样的执行计划不快.而且按照上面的写法,只有这种执行方式最佳.

SCOTT@test01p> alter session set statistics_level=all;
Session altered.


SCOTT@test01p> variable a number ;
SCOTT@test01p> exec :a := 4;
PL/SQL procedure successfully completed.

SELECT /*+ use_concat */ *
  FROM dept, emp
WHERE     dept.deptno = EMP.DEPTNO
       AND (   (:a = 1 AND dept.dname = 'SALES')
            OR (:a = 2 AND dept.loc = 'a')
            OR (:a = 3 AND emp.ename = 'a')
            OR (:a = 4 AND emp.empno = 7499)
            OR (:a = 5 AND emp.hiredate = '0000/01/01')
            OR (:a = 6 AND emp.sal = 0));
Plan hash value: 2449074598

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                |      1 |        |       |    17 (100)|          |      1 |00:00:00.01 |       4 |
|   1 |  CONCATENATION                          |                |      1 |        |       |            |          |      1 |00:00:00.01 |       4 |
|*  2 |   FILTER                                |                |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |
|   3 |    NESTED LOOPS                         |                |      0 |        |       |            |          |      0 |00:00:00.01 |       0 |
|   4 |     NESTED LOOPS                        |                |      0 |      1 |    59 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| EMP            |      0 |      1 |    39 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|*  6 |       INDEX RANGE SCAN                  | I_EMP_SAL      |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|*  7 |      INDEX UNIQUE SCAN                  | PK_DEPT        |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |
|   8 |     TABLE ACCESS BY INDEX ROWID         | DEPT           |      0 |      1 |    20 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|*  9 |   FILTER                                |                |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |
|  10 |    NESTED LOOPS                         |                |      0 |        |       |            |          |      0 |00:00:00.01 |       0 |
|  11 |     NESTED LOOPS                        |                |      0 |      1 |    59 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 12 |      TABLE ACCESS BY INDEX ROWID BATCHED| EMP            |      0 |      1 |    39 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 13 |       INDEX RANGE SCAN                  | I_EMP_HIREDATE |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 14 |      INDEX UNIQUE SCAN                  | PK_DEPT        |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |
|  15 |     TABLE ACCESS BY INDEX ROWID         | DEPT           |      0 |      1 |    20 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 16 |   FILTER                                |                |      1 |        |       |            |          |      1 |00:00:00.01 |       4 |
|  17 |    NESTED LOOPS                         |                |      1 |      1 |    59 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
|* 18 |     TABLE ACCESS BY INDEX ROWID         | EMP            |      1 |      1 |    39 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 19 |      INDEX UNIQUE SCAN                  | PK_EMP         |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|  20 |     TABLE ACCESS BY INDEX ROWID         | DEPT           |      1 |      1 |    20 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 21 |      INDEX UNIQUE SCAN                  | PK_DEPT        |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|* 22 |   FILTER                                |                |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |
|  23 |    NESTED LOOPS                         |                |      0 |        |       |            |          |      0 |00:00:00.01 |       0 |
|  24 |     NESTED LOOPS                        |                |      0 |      1 |    59 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 25 |      TABLE ACCESS BY INDEX ROWID BATCHED| EMP            |      0 |      1 |    39 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 26 |       INDEX RANGE SCAN                  | I_EMP_ENAME    |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 27 |      INDEX UNIQUE SCAN                  | PK_DEPT        |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |
|  28 |     TABLE ACCESS BY INDEX ROWID         | DEPT           |      0 |      1 |    20 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 29 |   FILTER                                |                |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |
|  30 |    NESTED LOOPS                         |                |      0 |        |       |            |          |      0 |00:00:00.01 |       0 |
|  31 |     NESTED LOOPS                        |                |      0 |      2 |   118 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|  32 |      TABLE ACCESS BY INDEX ROWID BATCHED| DEPT           |      0 |      1 |    20 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 33 |       INDEX RANGE SCAN                  | I_DEPT_LOC     |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 34 |      INDEX RANGE SCAN                   | I_EMP_DEPTNO   |      0 |      5 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |
|* 35 |     TABLE ACCESS BY INDEX ROWID         | EMP            |      0 |      5 |   195 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 36 |   FILTER                                |                |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |
|  37 |    NESTED LOOPS                         |                |      0 |        |       |            |          |      0 |00:00:00.01 |       0 |
|  38 |     NESTED LOOPS                        |                |      0 |      5 |   295 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 39 |      TABLE ACCESS BY INDEX ROWID BATCHED| DEPT           |      0 |      1 |    20 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 40 |       INDEX RANGE SCAN                  | I_DEPT_DNAME   |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 41 |      INDEX RANGE SCAN                   | I_EMP_DEPTNO   |      0 |      5 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |
|* 42 |     TABLE ACCESS BY INDEX ROWID         | EMP            |      0 |      5 |   195 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
----------------------------------------------------------------------------------------------------------------------------------------------------

--注意看starts列,许多都是0,也就是根本没有执行对应的指令. 逻辑读仅仅为4.

exec :a := 1;

|* 36 |   FILTER                                |                |      1 |        |       |            |          |      6 |00:00:00.01 |       6 |
|  37 |    NESTED LOOPS                         |                |      1 |        |       |            |          |      6 |00:00:00.01 |       6 |
|  38 |     NESTED LOOPS                        |                |      1 |      5 |   295 |     3   (0)| 00:00:01 |      6 |00:00:00.01 |       4 |
|* 39 |      TABLE ACCESS BY INDEX ROWID BATCHED| DEPT           |      1 |      1 |    20 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 40 |       INDEX RANGE SCAN                  | I_DEPT_DNAME   |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|* 41 |      INDEX RANGE SCAN                   | I_EMP_DEPTNO   |      1 |      5 |       |     0   (0)|          |      6 |00:00:00.01 |       2 |
|* 42 |     TABLE ACCESS BY INDEX ROWID         | EMP            |      6 |      5 |   195 |     1   (0)| 00:00:01 |      6 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------------------------------------------------
--逻辑读仅仅是6个.

文章标签：
索引
SQL
lfreeali
目录
相关文章
lfreeali
|
Oracle NoSQL 关系型数据库
[20180510]sqlplus array 和 opifch2.txt
[20180510]sqlplus array 和 opifch2.txt --//我以前通过10046跟踪发现fetchsize的大小,一般在sqlplus下第1个总是1条,然后是arraysize大小.
lfreeali
1276 0
lfreeali
|
Oracle 关系型数据库 OLAP
[20180224]expdp query 写法问题.txt
[20180224]expdp query 写法问题.txt --//如果使用expdp/impdp导入导出,如果参数复杂,最好的建议使用参数文件.避免各种问题.通过简单的例子说明问题.
lfreeali
1184 0
lfreeali
|
关系型数据库 数据库管理 Oracle
[20180208]ezconnect语法.txt
[20180208]ezconnect语法.txt --昨天看书Oracle Database11g DBA Handbook.pdf,Using Easy Connect Naming P561.
lfreeali
1055 0
lfreeali
|
存储 Oracle 关系型数据库
[20171218]varchar2(4000)如何保存.txt
varchar2(4000)如何保存
lfreeali
1256 0
技术小哥哥
date命令使用文档.txt
技术小哥哥
926 0
lfreeali
|
关系型数据库 Oracle 开发工具
[20170508]listagg拼接显示字段.txt
[20170508]listagg拼接显示字段.txt --//记得前一阵子,要给表增加一个字段,并赋值.采用表在线重定义.要使用函数dbms_redefinition.
lfreeali
865 0
lfreeali
|
SQL Oracle 关系型数据库
[20170112]为什么提示不一样.txt
[20170112]为什么提示不一样.txt --问题链接:http://www.itpub.net/thread-2074643-1-1.html --自己也测试看看: 1.
lfreeali
835 0
lfreeali
|
关系型数据库 Oracle
[20160806]12c 与 WMSYS.WM_CONCAT.txt
[20160806]12c 与 WMSYS.WM_CONCAT.txt --前几天有人问临时表空间消耗的问题,链接: http://www.itpub.net/thread-2065053-1-1.
lfreeali
942 0
lfreeali
|
Oracle 关系型数据库 索引
[20160118]提示index_join.txt
[20160118]提示index_join.txt --生产系统优化，遇到1例使用index_join提示的异常情况，通过例子来说明： 1.环境： 1.环境： SCOTT@book> @ &r/ver1 PORT_STRING               ...
lfreeali
911 0
lfreeali
[20160111]使用提示no_unnest.txt
[20160111]使用提示no_unnest.txt --今天被人问及这个提示,我记忆使用这个提示,就会出现filter过滤条件.我以前遇到这种语句,一般两者提示都使用看看那个效果好.
lfreeali
750 0

热门文章

最新文章

  • 1
    Mac安装并使用telnet命令操作
  • 2
    OSS回源的几种方式和应用场景
  • 3
    [剑指offer] 孩子们的游戏(圆圈中最后剩下的数)
  • 4
    网络安全系列之二十二 Windows用户账号加固
  • 5
    我理解的一个程序员如何学习前端开发
  • 6
    《社交网站界面设计（原书第2版）》——1.9　为设备之间的空间进行设计
  • 7
    《Microduino实战》——1.2　为什么要开源
  • 8
    .Net函数Math.Round你会用吗？
  • 9
    麻省理工大学新发明：暗黑WiFi透视技术
  • 10
    2014秋C++第19周 补充代码 哈希法的存储与查找
  • 1
    R语言关联规则模型(Apriori算法)挖掘杂货店的交易数据与交互可视化
    26
  • 2
    R语言近似贝叶斯计算MCMC（ABC-MCMC）轨迹图和边缘图可视化
    22
  • 3
    r语言中对LASSO回归，Ridge岭回归和弹性网络Elastic Net模型实现-4
    31
  • 4
    Sentieon | 每周文献-Multi-omics-第四十一期
    26
  • 5
    数据分享|R语言广义线性模型GLM：线性最小二乘、对数变换、泊松、二项式逻辑回归分析冰淇淋销售时间序列数据和模拟-2
    17
  • 6
    数据分享|R语言广义线性模型GLM：线性最小二乘、对数变换、泊松、二项式逻辑回归分析冰淇淋销售时间序列数据和模拟-1
    24
  • 7
    基于RT-Thread摄像头车牌图像采集系统
    22
  • 8
    R语言极值理论：希尔HILL统计量尾部指数参数估计可视化
    17
  • 9
    【视频】R语言中的分布滞后非线性模型（DLNM）与发病率，死亡率和空气污染示例
    23
  • 10
    sql语句创建数据库
    19

    • 相关电子书

    更多
  • 成功案例-Par...[幸运杰哥].1512720360.pdf
  • 低代码开发师（初级）实战教程
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）