AI 助理
备案控制台
开发者社区 数据库 文章 正文

[20150705]使用use_concat提示.txt

2015-07-06 840
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： [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
目录
相关文章
爱你三千遍斯塔克
|
4月前
rep-wc-管道符 grep [-n ] 关键字 文件路径,grep -n “code“ test.txt,grep -n “code“ test.txt 显示行号的写法，wc test.txt全
rep-wc-管道符 grep [-n ] 关键字 文件路径,grep -n “code“ test.txt,grep -n “code“ test.txt 显示行号的写法，wc test.txt全
爱你三千遍斯塔克
28 0
lfreeali
|
SQL Oracle 关系型数据库
[20180310]12c exp 无法dirct的情况.txt
[20180310]12c exp 无法dirct的情况.txt --//前一阵子测试.exp 无法dirct的情况的链接: http://blog.itpub.net/267265/viewspace-2151290/ --//12c 改进增加字段与缺省值的情况,允许不要加not null修改表块.
lfreeali
1485 0
lfreeali
|
SQL Oracle 关系型数据库
[20180224]exp参数RECORDLENGTH.txt
[20180224]exp参数RECORDLENGTH.txt --//虽然已经很少使用exp导致,如果加入direct=y参数,设置RECORDLENGTH参数能加快数据导出.
lfreeali
1585 0
lfreeali
|
SQL Oracle 关系型数据库
[20180226]exp 无法dirct的情况.txt
[20180226]exp 无法dirct的情况.txt http://blog.csdn.net/leshami/article/details/9146023 传统路径导出 VS 直接路径导出(oracle exp direct=y) 1、两者的差异 a、 Conventional path Export     传统路径模式使用SQL SELECT语句抽取表数据。
lfreeali
1226 0
lfreeali
|
关系型数据库 数据库管理 Oracle
[20180208]ezconnect语法.txt
[20180208]ezconnect语法.txt --昨天看书Oracle Database11g DBA Handbook.pdf,Using Easy Connect Naming P561.
lfreeali
1072 0
lfreeali
|
SQL Oracle 关系型数据库
[20180102]11g的V$SORT_USAGE视图.txt
[20180102]11g的V$SORT_USAGE视图.txt --//当出现排序使用临时表空间的情况时,查询V$SORT_USAGE那个会话使用,但是执行的sql语句不一定是问题语句,通过例子说明.
lfreeali
1583 0
lfreeali
|
Oracle 关系型数据库 OLAP
[20170315]11.2.0.4 exp可以导出空表.txt
[20170315]11.2.0.4 exp可以导出空表.txt --链接http://www.itpub.net/thread-2084282-1-1.html,11.
lfreeali
1188 0
lfreeali
|
SQL Oracle 关系型数据库
[20170112]为什么提示不一样.txt
[20170112]为什么提示不一样.txt --问题链接:http://www.itpub.net/thread-2074643-1-1.html --自己也测试看看: 1.
lfreeali
860 0
lfreeali
|
Oracle 关系型数据库 索引
[20160118]提示index_join.txt
[20160118]提示index_join.txt --生产系统优化，遇到1例使用index_join提示的异常情况，通过例子来说明： 1.环境： 1.环境： SCOTT@book> @ &r/ver1 PORT_STRING               ...
lfreeali
925 0
lfreeali
|
索引
[20160112]提示NUM_INDEX_KEY.txt
[20160112]提示NUM_INDEX_KEY.txt --如果我们查询,假设建立的索引是id1,id2的复合索引. select * from t where id1=:x and id2 in(1,100); --一般执行计划通过索引access id1=:X,然后再过滤id2等于1和100的值.
lfreeali
994 0