博客
关于我
强烈建议你试试无所不能的chatGPT，快点击我
[20160318]push_pred hint使用疑惑.txt
阅读量：6526 次
发布时间：2019-06-24

本文共 16896 字，大约阅读时间需要 56 分钟。

[20160318]push_pred hint使用疑惑.txt

--前几天看的帖子,链接如下:

--当时的第一感觉,就是闭包传递的问题,做1个记录:

1.环境:

book> @ &r/ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table T_SMALL_TABLE as select * from dba_objects;

create index I_T_SMALL_TABLE_OWNER on T_SMALL_TABLE (OWNER);

create table T_HUGE_TABLE as select * from dba_objects;

create index I_T_HUGE_TABLE_object_id on T_HUGE_TABLE (OBJECT_ID);

create table T_OTHER_TABLE as select * from dba_objects;
create index I_T_OTHER_TABLE_object_id on T_OTHER_TABLE (OBJECT_ID);

2.测试:

WITH vm
     AS (SELECT *
           FROM (SELECT t.*
                       ,ROW_NUMBER ()
                        OVER
                        (
                           PARTITION BY t.object_id
                           ORDER BY t.CREATED DESC
                        )
                           rn
                   FROM t_huge_table t)
          WHERE rn = 1)
SELECT *
  FROM t_small_table a
       JOIN vm ON a.object_id = vm.object_id
       LEFT JOIN t_other_table c ON vm.object_id = c.object_id
WHERE a.owner = 'kudfweu';

Plan hash value: 1484884627

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

| Id  | Operation                       | Name                      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                           |      1 |        |       |     6 (100)|          |      0 |00:00:00.01 |       2 |       |       |          |
|   1 |  NESTED LOOPS OUTER             |                           |      1 |      1 |   456 |     6  (17)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|   2 |   NESTED LOOPS                  |                           |      1 |      1 |   338 |     4  (25)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID  | T_SMALL_TABLE             |      1 |      1 |   118 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|*  4 |     INDEX RANGE SCAN            | I_T_SMALL_TABLE_OWNER     |      1 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|*  5 |    VIEW PUSHED PREDICATE        |                           |      0 |      1 |   220 |     3  (34)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|*  6 |     WINDOW SORT PUSHED RANK     |                           |      0 |      1 |   118 |     3  (34)| 00:00:01 |      0 |00:00:00.01 |       0 | 73728 | 73728 |          |
|   7 |      TABLE ACCESS BY INDEX ROWID| T_HUGE_TABLE              |      0 |      1 |   118 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|*  8 |       INDEX RANGE SCAN          | I_T_HUGE_TABLE_OBJECT_ID  |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|   9 |   TABLE ACCESS BY INDEX ROWID   | T_OTHER_TABLE             |      0 |      1 |   118 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|* 10 |    INDEX RANGE SCAN             | I_T_OTHER_TABLE_OBJECT_ID |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- id=5 VIEW PUSHED PREDICATE  ,很好地使用push_pred.但是当把left删除.看看执行计划:

WITH vm

     AS (SELECT *
           FROM (SELECT t.*
                       ,ROW_NUMBER ()
                        OVER
                        (
                           PARTITION BY t.object_id
                           ORDER BY t.CREATED DESC
                        )
                           rn
                   FROM t_huge_table t)
          WHERE rn = 1)
SELECT *
  FROM t_small_table a
       JOIN vm ON a.object_id = vm.object_id
       JOIN t_other_table c ON vm.object_id = c.object_id
WHERE a.owner = 'kudfweu';

Plan hash value: 1375926145

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                           |      1 |        |       |       |  3091 (100)|          |      0 |00:00:00.01 |       2 |       |       |          |
|   1 |  NESTED LOOPS                  |                           |      1 |      1 |   456 |       |  3091   (1)| 00:00:38 |      0 |00:00:00.01 |       2 |       |       |          |
|   2 |   NESTED LOOPS                 |                           |      1 |      1 |   456 |       |  3091   (1)| 00:00:38 |      0 |00:00:00.01 |       2 |       |       |          |
|*  3 |    HASH JOIN                   |                           |      1 |      1 |   338 |       |  3089   (1)| 00:00:38 |      0 |00:00:00.01 |       2 |   795K|   795K|  181K (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID| T_SMALL_TABLE             |      1 |      1 |   118 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|*  5 |      INDEX RANGE SCAN          | I_T_SMALL_TABLE_OWNER     |      1 |      1 |       |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|*  6 |     VIEW                       |                           |      0 |    103K|    21M|       |  3088   (1)| 00:00:38 |      0 |00:00:00.01 |       0 |       |       |          |
|*  7 |      WINDOW SORT PUSHED RANK   |                           |      0 |    103K|    11M|    15M|  3088   (1)| 00:00:38 |      0 |00:00:00.01 |       0 |    13M|  1379K|          |
|   8 |       TABLE ACCESS FULL        | T_HUGE_TABLE              |      0 |    103K|    11M|       |   347   (1)| 00:00:05 |      0 |00:00:00.01 |       0 |       |       |          |
|*  9 |    INDEX RANGE SCAN            | I_T_OTHER_TABLE_OBJECT_ID |      0 |      1 |       |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|  10 |   TABLE ACCESS BY INDEX ROWID  | T_OTHER_TABLE             |      0 |      1 |   118 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--执行计划无法推入.实际上连接顺序并没有发生变化,为什么使用left join可以推入,而使用join不行呢?
--分析表以后看看.

Plan hash value: 974994052

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                  | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       |      1 |        |       |       |  2655 (100)|          |      0 |00:00:00.01 |       2 |       |       |          |
|*  1 |  HASH JOIN                    |                       |      1 |    492 |   199K|       |  2655   (1)| 00:00:32 |      0 |00:00:00.01 |       2 |   724K|   724K|  174K (0)|
|*  2 |   HASH JOIN                   |                       |      1 |    492 |   152K|       |  2307   (1)| 00:00:28 |      0 |00:00:00.01 |       2 |   813K|   813K|  179K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| T_SMALL_TABLE         |      1 |    492 | 48216 |       |    16   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | I_T_SMALL_TABLE_OWNER |      1 |    492 |       |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|*  5 |    VIEW                       |                       |      0 |  87032 |    18M|       |  2291   (1)| 00:00:28 |      0 |00:00:00.01 |       0 |       |       |          |
|*  6 |     WINDOW SORT PUSHED RANK   |                       |      0 |  87032 |  8329K|    11M|  2291   (1)| 00:00:28 |      0 |00:00:00.01 |       0 |  9370K|  1189K|          |
|   7 |      TABLE ACCESS FULL        | T_HUGE_TABLE          |      0 |  87032 |  8329K|       |   347   (1)| 00:00:05 |      0 |00:00:00.01 |       0 |       |       |          |
|   8 |   TABLE ACCESS FULL           | T_OTHER_TABLE         |      0 |  87034 |  8329K|       |   347   (1)| 00:00:05 |      0 |00:00:00.01 |       0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--依旧不行.

--删除  JOIN t_other_table c ON vm.object_id = c.object_id 在执行看看:

WITH vm

     AS (SELECT *
           FROM (SELECT t.*
                       ,ROW_NUMBER ()
                        OVER
                        (
                           PARTITION BY t.object_id
                           ORDER BY t.CREATED DESC
                        )
                           rn
                   FROM t_huge_table t)
          WHERE rn = 1)
SELECT *
  FROM t_small_table a
       JOIN vm ON a.object_id = vm.object_id
WHERE a.owner = 'kudfweu';

Plan hash value: 1354569996

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

| Id  | Operation                      | Name                     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                          |      1 |        |       |  1492 (100)|          |      0 |00:00:00.01 |       2 |       |       |          |
|   1 |  NESTED LOOPS                  |                          |      1 |    492 |   152K|  1492  (33)| 00:00:18 |      0 |00:00:00.01 |       2 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID  | T_SMALL_TABLE            |      1 |    492 | 48216 |    16   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|*  3 |    INDEX RANGE SCAN            | I_T_SMALL_TABLE_OWNER    |      1 |    492 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|*  4 |   VIEW PUSHED PREDICATE        |                          |      0 |      1 |   220 |     3  (34)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|*  5 |    WINDOW SORT PUSHED RANK     |                          |      0 |      1 |    98 |     3  (34)| 00:00:01 |      0 |00:00:00.01 |       0 | 73728 | 73728 |          |
|   6 |     TABLE ACCESS BY INDEX ROWID| T_HUGE_TABLE             |      0 |      1 |    98 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|*  7 |      INDEX RANGE SCAN          | I_T_HUGE_TABLE_OBJECT_ID |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$55EFA6E7
   2 - SEL$55EFA6E7 / A@SEL$1
   3 - SEL$55EFA6E7 / A@SEL$1
   4 - SEL$B01C6807 / from$_subquery$_001@SEL$2
   5 - SEL$B01C6807
   6 - SEL$B01C6807 / T@SEL$3
   7 - SEL$B01C6807 / T@SEL$3
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$B01C6807")
      PUSH_PRED(@"SEL$55EFA6E7" "from$_subquery$_001"@"SEL$2" 2)
      OUTLINE_LEAF(@"SEL$55EFA6E7")
      MERGE(@"SEL$F5BB74E1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$55EFA6E7")
      MERGE(@"SEL$F5BB74E1")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX_RS_ASC(@"SEL$55EFA6E7" "A"@"SEL$1" ("T_SMALL_TABLE"."OWNER"))
      NO_ACCESS(@"SEL$55EFA6E7" "from$_subquery$_001"@"SEL$2")
      LEADING(@"SEL$55EFA6E7" "A"@"SEL$1" "from$_subquery$_001"@"SEL$2")
      USE_NL(@"SEL$55EFA6E7" "from$_subquery$_001"@"SEL$2")
      INDEX_RS_ASC(@"SEL$B01C6807" "T"@"SEL$3" ("T_HUGE_TABLE"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

--强行推入呢?

WITH vm

     AS (SELECT *
           FROM (SELECT /*+ qb_name(x) */t.*
                       ,ROW_NUMBER ()
                        OVER
                        (
                           PARTITION BY t.object_id
                           ORDER BY t.CREATED DESC
                        )
                           rn
                   FROM t_huge_table t)
          WHERE rn = 1)
SELECT /*+ PUSH_PRED(@x) */ *
  FROM t_small_table a
       JOIN vm ON a.object_id = vm.object_id
       JOIN t_other_table c ON vm.object_id = c.object_id
WHERE a.owner = 'kudfweu';

--问题依旧.执行计划如下:

Plan hash value: 560668266

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                          |      1 |        |       |   128M(100)|          |      0 |00:00:00.01 |       2 |       |       |          |
|   1 |  NESTED LOOPS                   |                          |      1 |    492 |   199K|   128M (34)|428:37:22 |      0 |00:00:00.01 |       2 |       |       |          |
|   2 |   MERGE JOIN CARTESIAN          |                          |      1 |     42M|  8000M|   169K  (1)| 00:34:00 |      0 |00:00:00.01 |       2 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID  | T_SMALL_TABLE            |      1 |    492 | 48216 |    16   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|*  4 |     INDEX RANGE SCAN            | I_T_SMALL_TABLE_OWNER    |      1 |    492 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|   5 |    BUFFER SORT                  |                          |      0 |  87034 |  8329K|   169K  (1)| 00:34:00 |      0 |00:00:00.01 |       0 |  9370K|  1189K|          |
|   6 |     TABLE ACCESS FULL           | T_OTHER_TABLE            |      0 |  87034 |  8329K|   345   (1)| 00:00:05 |      0 |00:00:00.01 |       0 |       |       |          |
|*  7 |   VIEW PUSHED PREDICATE         |                          |      0 |      1 |   220 |     3  (34)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|*  8 |    WINDOW SORT PUSHED RANK      |                          |      0 |      1 |    98 |     3  (34)| 00:00:01 |      0 |00:00:00.01 |       0 | 73728 | 73728 |          |
|*  9 |     FILTER                      |                          |      0 |        |       |            |          |      0 |00:00:00.01 |       0 |       |       |          |
|  10 |      TABLE ACCESS BY INDEX ROWID| T_HUGE_TABLE             |      0 |      1 |    98 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|* 11 |       INDEX RANGE SCAN          | I_T_HUGE_TABLE_OBJECT_ID |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--执行连接顺序发生了变化,换一句话讲要推入成功,先要a,c连接,然后才与vm连接.也就是最后才能推入.注意这里a,c 使用MERGE JOIN CARTESIAN.

--这样就很容易明白问题在那里了:

1.在存在外连接的情况无法改变连接顺序.这也是使用left例子一会使用推入的关键.参考连接:

2.要推入成功,必须是最后推入才行,也就是这个例子里面先连接a,c,在连接vm.

--这样增加条件a.object_id=c.object_id

WITH vm

     AS (SELECT  *
           FROM (SELECT t.*
                       ,ROW_NUMBER ()
                        OVER
                        (
                           PARTITION BY t.object_id
                           ORDER BY t.CREATED DESC
                        )
                           rn
                   FROM t_huge_table t)
          WHERE rn = 1)
SELECT  *
  FROM t_small_table a
       JOIN vm ON a.object_id = vm.object_id
       JOIN t_other_table c ON vm.object_id = c.object_id
WHERE a.owner = 'kudfweu'
and a.object_id=c.object_id;

Plan hash value: 2525641501

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                          |      1 |        |       |  1840 (100)|          |      0 |00:00:00.01 |       2 |       |       |          |
|   1 |  NESTED LOOPS                   |                          |      1 |      1 |   416 |  1840  (27)| 00:00:23 |      0 |00:00:00.01 |       2 |       |       |          |
|*  2 |   HASH JOIN                     |                          |      1 |    492 | 96432 |   364   (1)| 00:00:05 |      0 |00:00:00.01 |       2 |   813K|   813K|  174K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID  | T_SMALL_TABLE            |      1 |    492 | 48216 |    16   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|*  4 |     INDEX RANGE SCAN            | I_T_SMALL_TABLE_OWNER    |      1 |    492 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|   5 |    TABLE ACCESS FULL            | T_OTHER_TABLE            |      0 |  87034 |  8329K|   347   (1)| 00:00:05 |      0 |00:00:00.01 |       0 |       |       |          |
|*  6 |   VIEW PUSHED PREDICATE         |                          |      0 |      1 |   220 |     3  (34)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|*  7 |    WINDOW SORT PUSHED RANK      |                          |      0 |      1 |    98 |     3  (34)| 00:00:01 |      0 |00:00:00.01 |       0 | 73728 | 73728 |          |
|*  8 |     FILTER                      |                          |      0 |        |       |            |          |      0 |00:00:00.01 |       0 |       |       |          |
|   9 |      TABLE ACCESS BY INDEX ROWID| T_HUGE_TABLE             |      0 |      1 |    98 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|* 10 |       INDEX RANGE SCAN          | I_T_HUGE_TABLE_OBJECT_ID |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--T_OTHER_TABLE 选择全表扫描主要是因为id=4 估计返回行数偏高492,导致选择全表扫描T_OTHER_TABLE.

WITH vm

     AS (SELECT  *
           FROM (SELECT t.*
                       ,ROW_NUMBER ()
                        OVER
                        (
                           PARTITION BY t.object_id
                           ORDER BY t.CREATED DESC
                        )
                           rn
                   FROM t_huge_table t)
          WHERE rn = 1)
SELECT /*+index(c (object_id)) */  *
  FROM t_small_table a
       JOIN vm ON a.object_id = vm.object_id
       JOIN t_other_table c ON vm.object_id = c.object_id
WHERE a.owner = 'kudfweu'
and a.object_id=c.object_id;
--或者修改如下,加入提示/*+ cardinality(a 5) */:
WITH vm
     AS (SELECT  *
           FROM (SELECT t.*
                       ,ROW_NUMBER ()
                        OVER
                        (
                           PARTITION BY t.object_id
                           ORDER BY t.CREATED DESC
                        )
                           rn
                   FROM t_huge_table t)
          WHERE rn = 1)
SELECT /*+ cardinality(a 5) */  *
  FROM t_small_table a
       JOIN vm ON a.object_id = vm.object_id
       JOIN t_other_table c ON vm.object_id = c.object_id
WHERE a.owner = 'kudfweu'
and a.object_id=c.object_id;

Plan hash value: 847857519

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                           |      1 |        |       |    41 (100)|          |      0 |00:00:00.01 |       2 |       |       |          |
|   1 |  NESTED LOOPS                   |                           |      1 |      1 |   416 |    41  (13)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|   2 |   NESTED LOOPS                  |                           |      1 |      5 |   980 |    26   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID  | T_SMALL_TABLE             |      1 |      5 |   490 |    16   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|*  4 |     INDEX RANGE SCAN            | I_T_SMALL_TABLE_OWNER     |      1 |    492 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|   5 |    TABLE ACCESS BY INDEX ROWID  | T_OTHER_TABLE             |      0 |      1 |    98 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|*  6 |     INDEX RANGE SCAN            | I_T_OTHER_TABLE_OBJECT_ID |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|*  7 |   VIEW PUSHED PREDICATE         |                           |      0 |      1 |   220 |     3  (34)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|*  8 |    WINDOW SORT PUSHED RANK      |                           |      0 |      1 |    98 |     3  (34)| 00:00:01 |      0 |00:00:00.01 |       0 | 73728 | 73728 |          |
|*  9 |     FILTER                      |                           |      0 |        |       |            |          |      0 |00:00:00.01 |       0 |       |       |          |
|  10 |      TABLE ACCESS BY INDEX ROWID| T_HUGE_TABLE              |      0 |      1 |    98 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|* 11 |       INDEX RANGE SCAN          | I_T_HUGE_TABLE_OBJECT_ID  |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

总结:

1.在存在外连接的情况无法改变连接顺序.这也是使用left例子一会使用推入的关键.参考连接:
2.要推入成功,必须是最后推入才行,也就是这个例子里面先连接a,c,在连接vm.
3.总觉的cbo还是不够智能.能力有限,也不知道如果中间就推入是否可能导致结果集合发生变化.

转载地址：http://kwibo.baihongyu.com/

你可能感兴趣的文章
JAVA多线程的问题以及处理【转】
查看>>
【Java面试题】10 abstract的method是否可同时是static,是否可同时是native，是否可同时是synchronized?...
查看>>
简单记录一次ORA-00600 kcratr_nab_less_than_odr
查看>>
【视频教程】一步步将AppBox升级到Pro版
查看>>
[原]好玩的Linux，关于时间cal命令
查看>>
[ssh]SSH反向通道端口转发，在本地调试微信支付回调
查看>>
Linux学习之CentOS(十八)--与Linux文件和目录管理相关的一些重要命令②
查看>>
vim一些挺方便的功能
查看>>
开源Java时间工具类Joda-Time体验
查看>>
如何新建UML2项目？详细操作步骤介绍
查看>>
[精讲17] 组策略
查看>>
控制流
查看>>
interlij的快捷键
查看>>
如何在Rancher上运行Elasticsearch
查看>>
shell 找出数组元素中的最大值
查看>>
Vmware虚拟机linux系统混合模式上网
查看>>
MySQL在导入的时候遇到的错误
查看>>
存储初创公司Datera带着Amazon EBS走出隐身模式
查看>>
纵观视频监控市场这几个方面值得关注
查看>>
北大访问教授吴霁虹：如何把握AI产业化机遇并建立竞争优势 | CITE 2017
查看>>
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！
当前时间: 2025-01-20 16:55:21   当前IP: 18.227.140.100   联系邮箱:javaeecc@qq.com     Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2
强烈建议你试试无所不能的CHAT-GPT，快点击我
强烈建议你试试无所不能的CHAT-GPT，快点击我