[20160318]push_pred hint使用疑惑.txt

[20160318]push_pred hint使用疑惑.txt

--前几天看的帖子,链接如下:
http://www.itpub.net/thread-2054898-1-1.html

--当时的第一感觉,就是闭包传递的问题,做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例子一会使用推入的关键.参考连接:http://blog.itpub.net/267265/viewspace-1991306/
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例子一会使用推入的关键.参考连接:http://blog.itpub.net/267265/viewspace-1991306/
2.要推入成功,必须是最后推入才行,也就是这个例子里面先连接a,c,在连接vm.
3.总觉的cbo还是不够智能.能力有限,也不知道如果中间就推入是否可能导致结果集合发生变化.

时间： 2024-09-20 12:39:05

[20160318]push_pred hint使用疑惑.txt的相关文章

[20131227]ORA-00600 [kkocxj : pjpCtx] error is reported when running ....txt

[20131227]PUSH_PRED?ORA-00600 [kkocxj : pjpCtx] error is reported when running a complex query.txt 前几天生产系统出现严重的性能问题,跟查询变换有关,自己做一些探究看看. SYSTEM> @verBANNER----------------------------------------------------------------Oracle Database 10g Enterprise Ed

[20160730]hint 冲突.txt

[20160730]hint 冲突.txt --昨天别人优化加提示无效,问我为什么无效?我一般认为这种情况称为hint 冲突. --通过例子来说明,我测试会使用ordered,我一般不喜欢使用ordered提示,通过例子来说明. 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                       

CBO hint:no_unnest,push_subq,push_pred的用法

文章来自:http://space.itpub.net/15415488/viewspace-663969 常常有人把这三个hint搞混,主要是因为对三种重写原理不清楚.特总结如下.(实验环境为10204)1. no_unnest, unnestunnest我们称为对子查询展开,顾名思义,就是别让子查询孤单地嵌套(nest)在里面.所以un_unnest双重否定代表肯定,即让子查询不展开,让它嵌套(nest)在里面.现做一个简单的实验:create table hao1 as select *

[20120527]视图与hint.txt

这段时间一直在看,发现yangtingkun的一篇关于视图与hint的文章.oracle的hint可以强制制定sql的执行计划.如果查询对象是视图,使用hint就很麻烦.自己重复测试了一下,实际上dbms_xplan.display_cursor的advanced或者outline参数,很容易知道如何写这个提示. 例子如下: 1.建立测试例子: $cat /home/oracle11g/sqllaji/dpc.sql select * from table(dbms_xplan.display_

20160318了解oracle版本升级的一些参数变化

[20160318]了解oracle版本升级的一些参数变化.txt --曾经写过一篇了解oracle版本升级后一些参数变化,可以通过如下连接了解: http://blog.itpub.net/267265/viewspace-1655594/ --实际上还有一个简单的方法就是提示opt_param('optimizer_features_enable' '11.2.0.1') ,然后顺便执行一条sql语句,查看执行计 --划就可以那些参数发生了变化.通过例子说明: 1.环境: SCOTT@boo

[20141014]11G长时间分析问题.txt

[20141014]11G长时间分析问题.txt http://www.itpub.net/thread-1495845-1-1.html http://space.itpub.net/267265/viewspace-723066 http://blog.itpub.net/267265/viewspace-752117/ --我以前看到这条sql语句,我做了一些简单修改. 1. 问题提出: SCOTT@test> @ver1 PORT_STRING                    VE

将SQL Server中的表变成txt文件

方法一:用BCP命令 bcp 实用工具 bcp 实用工具在 Microsoft? SQL Server? 2000 实例和数据文件之间以用户指定的格式复数据. 语法 bcp {[[database_name.][owner].]{table_name | view_name} | "query"} {in | out | queryout | format} data_file [-m max_errors] [-f format_file] [-e err_file] [-F fir

Oracle三组难缠的hint no_unnest/unnest，push_subq，push_pred--平展化(转)

经常有人把这三个hint搞混,主如果因为对三种重写道理不清楚.特总结如下.(实验景象为10204)1. no_unnest, unnestunnest我们称为对子查询展开,顾名思义,就是别让子查询孤单地嵌套(nest)在里面.所以un_unnest双重否定代表必然,即让子查询不展开,让它嵌套(nest)在里面.现做一个简单的实验:create table hao1 as * dba_objects;create table hao2 as * dba_objects;analyze table

java web-<c:import> url产生的相对路径和绝对路径的疑惑。

问题描述 <c:import> url产生的相对路径和绝对路径的疑惑. 学java web这部分,视频资料都是说 : 以"/" 的路径为 绝对路径,反之为 相对路径. 而以前提的问题 ,有人回答我: 以"/" 的路径为 相对根目录的路径,反之为 相对当前目录 的路径. 让我很混乱,, 现在学习 jstl <c:import>标签时 关于 url 属性 ,书中说:**以"/"开头的地址也是相对地址** 使我又想起了,之前学习