[20150705]使用use_concat提示.txt

最新推荐文章于 2021-04-21 17:01:22 发布
最新推荐文章于 2021-04-21 17:01:22 发布
阅读量98 收藏
点赞数

[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个.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1724031/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1724031/

cuixun7780
关注
use_concat导致not in时临时表不动态采样进而导致的性能问题
zhaosj1726的专栏
01-21 1177
从这个案例,你可以看到无论是限制也好,还是说bug也好,我们并不是总能使用动态采样这个特性的,而且动态采样在硬分析时是有代价的,它会执行带有/* OPT_DYN_SAMP */这样注释的sql语句来执行动态采样数据动作的,而且如果它硬分析时动态采样到的刚好是非典型值的话,可能会生成糟糕的执行计划的.所以随意的使用动态采样,其实也是一种不负责任的行为,对于临时表,还是在插入代表性数据之后手工收集统计信息的好,对于新建的普通表,在加载完数据后,立即手工收集统计信息为好.
大数据数据仓库 Hive (三)
鱼的博客
11-10 895
大数据技术之Hive 一 Hive基本概念 1.1 什么是Hive Hive：由Facebook开源用于解决海量结构化日志的数据统计。 Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。 本质是：将HQL/SQL转化成MapReduce程序 1）Hive处理的数据存储在HDFS 2）Hive分析数据底层的实现是MapReduce 3）执行程序运行在Yarn上 1.2 Hive的优缺点 1.2.1 优点 1）操作接口采用类SQL语法，提供快速开发的能力（
如何使用USE_CONCAT提示
eygle's life
11-23 1819
使用USE_CONCAT提示--Use USE_CONCAT hints in Oracle Last Updated: Thursday, 2004-11-18 21:48 Eygle        USE_CONCAT提示强迫优化器扩展查询中的每一个OR谓词为独立的查询块.最后合并所有查询块的结果，返回结果集给用户。当使用多个in
[20150901]提示USE_CONCAT.txt
09-01 120
[20150901]提示USE_CONCAT.txt --最近一直在使用这个提示USE_CONCAT ，开发真的不要这样写代码，应该分开写,这样的sql技巧真的不能乱用！分开写对应的sql语句优 --化选择索引的建立更方...
use_concat
cuisi5661的博客
07-10 151
select /*+use_concat */ * from goodsbase access() filter() ...
use_concat，NO_EXPAND提示
cotchte0421的博客
12-30 156
USE_CONCAT提示强迫优化器扩展查询中的每一个OR谓词为独立的查询块.最后合并所有查询块的结果，返回结果集给用户。 但是从9.2.0后，这个提示似乎不是怎么有效了。 select /*+ use_concat opti...
Cobalt Strike Profile 学习记录
darkb1rd的博客
04-21 2844
此处以最新版 jquery-c2.4.3.profile 为例，学习记录各项配置的作用。部分配置尚未研究清楚适用场景，后续继续学习再补充。 cobalt strike 4.3 官方文档：https://cobaltstrike.com/downloads/csmanual43.pdf 提示 关于参数与值： profile 文件将参数括在双引号中，而不是单引号中。例如：正确： set useragent "SOME AGENT"; 错误： set useragent 'SOME AGENT'; 一些
python数据分析学习和建模的个人笔记（一）
OuKman的博客
12-19 2819
系列文章目录 提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加 例如：第一章 Python 机器学习入门之pandas的使用 提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录系列文章目录前言一、pandas是什么？二、使用步骤1.引入库2.读入数据总结 前言 提示：这里可以添加本文要记录的大概内容： 例如：随着人工智能的不断发展，机器学习这门技术也越来越重要，很多人都开启了学习机器学习，本文就介绍了机器学习的基础内容。 提示：以下是本篇文章正文内容，下面案例
SQL语句优化.txt
11-13
需要注意的是，在实际使用中应根据具体情况灵活选择合适的提示，避免过度依赖提示而忽略基本的SQL语句编写原则。此外，对于复杂查询，还应结合其他优化手段，如合理设计数据库表结构、建立适当的索引等，以达到最佳...
pytrorch官方教程做一个聊天机器人（代码注释）
光英的记忆博客
11-09 1061
from __future__ import absolute_import from __future__ import division from __future__ import print_function from __future__ import unicode_literals import torch from torch.jit import script, trace ...
[20190524]使用use_concat or_expand提示优化.txt
weixin_34250434的博客
05-24 146
[20190524]使用use_concat or_expand提示优化.txt--//上午看了链接https://connor-mcdonald.com/2019/05/22/being-generous-to-the-optimizer，突然想起我们生产系统类似语句。--//现在想想觉得开发的想象力太丰富，写这些语句是否考虑长期运行导致的结果。对方例子相对简单，我优化的例子简直就是变态。--/...
小议USE_CONCAT提示 (转）
congtudi4111的博客
07-07 211
原文：http://yangtingkun.itpub.net/post/468/500859 前两天写了篇文章描述在一个查询中使用两个以上索引的几种情况，有人在我的BLOG里面回复提到了USE_CONCAT提示，这里就...
"或"的扩张，use_concat以及no_expand提示使用
@DBA_白老大
02-26 2529
"或"的扩张，use_concat以及no_expand提示使用
oracle use 代表什么意思,使用USE_CONCAT提示
weixin_42610671的博客
04-03 1022
使用USE_CONCAT提示USE_CONCAT提示强迫优化器扩展查询中的每一个OR谓词为独立的查询块.最后合并所有查询块的结果，返回结果集给用户。当使用多个in-lists查询时，Oracle可能选择把单个查询扩展为多个查询块。使用USE_CONCAT提示示例:1.使用scott用户及标准表进行测试$ sqlplus scott/tigerSQL*Plus: Release 9.2.0.4.0 ...
oracle nl_sj,Oracle 学习之性能优化（九）使用hint
weixin_29074295的博客
04-03 259
基于代价的优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，减轻了DBA的负担。但有时它也聪明反被聪明误，选择了很差的执行计划，使某个语句的执行变得奇慢无比。此时就需要DBA进行人为的干预，告诉优化器使用我们指定的存取路径或连接类型生成执行计划，从 而使语句高效的运行。例如，如果我们认为对于一个特定的语句，执行全表扫描要比执行索引扫描更有效，则我们就可以指示优化器使用全表扫描。在Oracle...
Oracle之Hint使用总结
热门推荐
旺旺的博客
06-30 2万+
导读： 一、关于hint 1、引入hint的原因； 2、不能过分依赖hint； 3、hint的弊端； 4、hint和注释的关系； 5、hint的功能。 二、hint的具体用法 1、hint的语法； 2、hint的作用域； 3、hint数据字典。 三、hint的分类 1、和优化器相关的； 2、和访问路径相关的； 3、和查询转换相关的； 4、和表连接顺序相关的； 5、和表操作相关的； 6、和并行相关的； 7、其他相关的。...
oracle各模块是什么意思,执行计划中各字段各模块描述-Oracle
weixin_39918145的博客
04-04 313
在SQL语句的执行计划中，包含很多字段项和很多模块，其不同字段代表了不同的含义且在不同的情形下某些字段、模块显示或不显示，下面的描述给出了执行计划中各字段的含义以及各模块的描述。有关执行计划中各字段模块的描述请参考： 执行计划中各字段各模块描述有关由SQL语句来获取执行计划请参考：使用EXPLAIN PLAN获取SQL语句执行计划有关使用autotrace来获取执行计划请参考：启用AUTOTRAC...
小议USE_CONCAT提示
ckawt40802的专栏
06-08 131
前两天写了篇文章描述在一个查询中使用两个以上索引的几种情况，有人在我的BLOG里面回复提到了USE_CONCAT提示，这里就简单说明一下。 查询访问同一表的两个以上索引（一）：http://yangtingkun.itpub....
df_concat_x = pd.DataFrame() df_concat_y=pd.DataFrame() df_concat_x1=pd.DataFrame() df_concat_y1=pd.DataFrame()如何代码更简洁
最新发布
04-07
你可以使用列表来存储要合并的数据框，然后使用 pandas 的 concat 函数来合并这些数据框，示例代码如下： df_list = [df_concat_x, df_concat_y, df_concat_x1, df_concat_y1] df_concat = pd.concat(df_list) ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值