[20140823]12c join convert连接转换.txt

最新推荐文章于 2022-05-26 09:43:53 发布
最新推荐文章于 2022-05-26 09:43:53 发布
阅读量70 收藏
点赞数

[20140823]12c join convert连接转换.txt

--前面提高12c执行计划的Partial Join Evaluation.现在看看12c join convert.
--链接:

1.建立测试环境:

SCOTT@test01p> @ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

create table t1 as select rownum id ,cast('testtest' as varchar2(10)) name from dual connect by level<=1e5;
create table t2 as select rownum id ,cast('testtest' as varchar2(10)) name from dual connect by level<=10;
create unique index pk_t1 on t1 (id);
alter table t1 add constraint pk_t1 primary key (id);
create unique index pk_t2 on t2 (id);
alter table t2 add constraint pk_t2 primary key (id);
execute dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);
execute dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);

SYS@test01p> @hide _convert_set_to_join
NAME                  DESCRIPTION                                 DEFAULT_VALUE  SESSION_VALUE  SYSTEM_VALUE
--------------------- ------------------------------------------- -------------- -------------- ------------
_convert_set_to_join  enables conversion of set operator to join  TRUE           FALSE          FALSE
--12.1.0.1版本_convert_set_to_join=false.

2.测试:
SCOTT@test01p> alter session set statistics_level=all;
Session altered.
SCOTT@test01p> select * from t1 intersect select * from t2;
ID NAME
--- ---------
  1 testtest
  2 testtest
  3 testtest
  4 testtest
  5 testtest
  6 testtest
  7 testtest
  8 testtest
  9 testtest
10 testtest
10 rows selected.

SCOTT@test01p> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  g5jv20wwdh1zz, child number 0
-------------------------------------
select * from t1 intersect select * from t2
Plan hash value: 1917753433
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |   576 (100)|     10 |00:00:00.08 |     269 |       |       |          |
|   1 |  INTERSECTION       |      |      1 |        |            |     10 |00:00:00.08 |     269 |       |       |          |
|   2 |   SORT UNIQUE       |      |      1 |    100K|   573   (1)|    100K|00:00:00.07 |     266 |  5510K|   963K| 4897K (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |    100K|    77   (0)|    100K|00:00:00.01 |     266 |       |       |          |
|   4 |   SORT UNIQUE       |      |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       3 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------
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$2")
      OUTLINE_LEAF(@"SET$1")
      FULL(@"SEL$2" "T2"@"SEL$2")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

--可以发现执行出现INTERSECTION,与一些11g版本一样.Buffers=269.

SCOTT@test01p> select /*+ SET_TO_JOIN(@"SET$1") */ * from t1 intersect select * from t2;
ID NAME
-- --------------------
7 testtest
8 testtest
2 testtest
5 testtest
1 testtest
3 testtest
6 testtest
10 testtest
4 testtest
9 testtest

--注意输出的顺序发生了一些"混乱",对比前面的输出.

SCOTT@test01p> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7y6uhq1r2bhnr, child number 0
-------------------------------------
select /*+ SET_TO_JOIN(@"SET$1") */ * from t1 intersect select * from t2
Plan hash value: 847386728
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |    13 (100)|     10 |00:00:00.01 |      13 |       |       |          |
|   1 |  HASH UNIQUE                  |       |      1 |     10 |    13   (0)|     10 |00:00:00.01 |      13 |  1600K|  1600K|  979K (0)|
|   2 |   NESTED LOOPS                |       |      1 |        |            |     10 |00:00:00.01 |      13 |       |       |          |
|   3 |    NESTED LOOPS               |       |      1 |     10 |    13   (0)|     10 |00:00:00.01 |      12 |       |       |          |
|   4 |     TABLE ACCESS FULL         | T2    |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       3 |       |       |          |
|*  5 |     INDEX UNIQUE SCAN         | PK_T1 |     10 |      1 |     0   (0)|     10 |00:00:00.01 |       9 |       |       |          |
|*  6 |    TABLE ACCESS BY INDEX ROWID| T1    |     10 |      1 |     1   (0)|     10 |00:00:00.01 |       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$02B15F54")
      MERGE(@"SEL$1")
      MERGE(@"SEL$2")
      OUTLINE(@"SET$09AAA538")
      SET_TO_JOIN(@"SET$1")
      ~~~~~~~~~~~~~~~~~~~~~
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SET$1")
      FULL(@"SEL$02B15F54" "T2"@"SEL$2")
      INDEX(@"SEL$02B15F54" "T1"@"SEL$1" ("T1"."ID"))
      LEADING(@"SEL$02B15F54" "T2"@"SEL$2" "T1"@"SEL$1")
      USE_NL(@"SEL$02B15F54" "T1"@"SEL$1")
      NLJ_BATCHING(@"SEL$02B15F54" "T1"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$02B15F54")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."ID"="T2"."ID")
   6 - filter(SYS_OP_MAP_NONNULL("T1"."NAME")=SYS_OP_MAP_NONNULL("T2"."NAME"))
Note
-----
   - this is an adaptive plan

--可以发现几点变化:
1.执行计划没有出现INTERSECTION,而是转化为join操作.
2.另外一个副作用这个执行计划adaptive plan,后面再看看为什么?
3.buffer=13,比原来大大减少.操作先扫描T2小表.在连接T1,逻辑读自然大大减少.
4.看不明白,为什么输出的顺序会发生变化.....?????也许出在执行计划的HASH UNIQUE上.

3.使用提示看看:
SCOTT@test01p> select /*+ OPT_PARAM('_convert_set_to_join' 'true') */ * from t1 intersect select * from t2;
ID NAME
-- --------------------
7 testtest
8 testtest
2 testtest
5 testtest
1 testtest
3 testtest
6 testtest
10 testtest
4 testtest
9 testtest
10 rows selected.
--执行计划与select /*+ SET_TO_JOIN(@"SET$1") */ * from t1 intersect select * from t2一样,不再贴出.

4.修改参数测试:
--我使用的12.1.0.1.0版本._convert_set_to_join=false.设置为true看看.
SCOTT@test01p> alter session set "_convert_set_to_join"=true ;
Session altered.

SCOTT@test01p> select * from t1 intersect select * from t2;
ID NAME
-- --------------------
7 testtest
8 testtest
2 testtest
5 testtest
1 testtest
3 testtest
6 testtest
10 testtest
4 testtest
9 testtest
10 rows selected.

--执行计划与select /*+ SET_TO_JOIN(@"SET$1") */ * from t1 intersect select * from t2一样,不在贴出.

5.看看是否顺序可以改变:
SCOTT@test01p> select * from V$SQL_HINT where name like '%USE%AGGREGATION%';
NAME                    SQL_FEATURE          CLASS                 INVERSE                  TARGET_LEVEL   PROPERTY VERSION    VERSION_OUTLINE CON_ID
----------------------- -------------------- --------------------- ------------------------ ------------ ---------- ---------- --------------- ------
USE_HASH_AGGREGATION    QKSFM_ALL            USE_HASH_AGGREGATION  NO_USE_HASH_AGGREGATION             2          0 10.2.0.1   10.2.0.5             0
NO_USE_HASH_AGGREGATION QKSFM_ALL            USE_HASH_AGGREGATION  USE_HASH_AGGREGATION                2          0 10.2.0.1   10.2.0.5             0
--猜测换成NO_USE_HASH_AGGREGATION看看.

select
  /*+
      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$02B15F54")
      MERGE(@"SEL$1")
      MERGE(@"SEL$2")
      OUTLINE(@"SET$09AAA538")
      SET_TO_JOIN(@"SET$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SET$1")
      FULL(@"SEL$02B15F54" "T2"@"SEL$2")
      INDEX(@"SEL$02B15F54" "T1"@"SEL$1" ("T1"."ID"))
      LEADING(@"SEL$02B15F54" "T2"@"SEL$2" "T1"@"SEL$1")
      USE_NL(@"SEL$02B15F54" "T1"@"SEL$1")
      NLJ_BATCHING(@"SEL$02B15F54" "T1"@"SEL$1")
      NO_USE_HASH_AGGREGATION(@"SEL$02B15F54")
      END_OUTLINE_DATA
  */ * from t1 intersect select * from t2;

ID NAME
-- --------------------
1 testtest
2 testtest
3 testtest
4 testtest
5 testtest
6 testtest
7 testtest
8 testtest
9 testtest
10 testtest
10 rows selected.

SCOTT@test01p> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  3frmt98v3kjsp, child number 0
-------------------------------------
select   /*+       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$02B15F54")       MERGE(@"SEL$1")
MERGE(@"SEL$2")       OUTLINE(@"SET$09AAA538")
SET_TO_JOIN(@"SET$1")       OUTLINE(@"SEL$1")       OUTLINE(@"SEL$2")
    OUTLINE(@"SET$1")       FULL(@"SEL$02B15F54" "T2"@"SEL$2")
INDEX(@"SEL$02B15F54" "T1"@"SEL$1" ("T1"."ID"))
LEADING(@"SEL$02B15F54" "T2"@"SEL$2" "T1"@"SEL$1")
USE_NL(@"SEL$02B15F54" "T1"@"SEL$1")       NLJ_BATCHING(@"SEL$02B15F54"
"T1"@"SEL$1")       NO_USE_HASH_AGGREGATION(@"SEL$02B15F54")
END_OUTLINE_DATA   */ * from t1 intersect select * from t2

Plan hash value: 1438968633

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |    13 (100)|     10 |00:00:00.01 |      13 |       |       |          |
|   1 |  SORT UNIQUE                  |       |      1 |      8 |    13   (0)|     10 |00:00:00.01 |      13 |  2048 |  2048 | 2048  (0)|
|   2 |   NESTED LOOPS                |       |      1 |        |            |     10 |00:00:00.01 |      13 |       |       |          |
|   3 |    NESTED LOOPS               |       |      1 |     10 |    13   (0)|     10 |00:00:00.01 |      12 |       |       |          |
|   4 |     TABLE ACCESS FULL         | T2    |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       3 |       |       |          |
|*  5 |     INDEX UNIQUE SCAN         | PK_T1 |     10 |      1 |     0   (0)|     10 |00:00:00.01 |       9 |       |       |          |
|*  6 |    TABLE ACCESS BY INDEX ROWID| T1    |     10 |      1 |     1   (0)|     10 |00:00:00.01 |       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$02B15F54")
      MERGE(@"SEL$1")
      MERGE(@"SEL$2")
      OUTLINE(@"SET$09AAA538")
      SET_TO_JOIN(@"SET$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SET$1")
      FULL(@"SEL$02B15F54" "T2"@"SEL$2")
      INDEX(@"SEL$02B15F54" "T1"@"SEL$1" ("T1"."ID"))
      LEADING(@"SEL$02B15F54" "T2"@"SEL$2" "T1"@"SEL$1")
      USE_NL(@"SEL$02B15F54" "T1"@"SEL$1")
      NLJ_BATCHING(@"SEL$02B15F54" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T1"."ID"="T2"."ID")
   6 - filter(SYS_OP_MAP_NONNULL("T1"."NAME")=SYS_OP_MAP_NONNULL("T2"."NAME"))
60 rows selected.

6.最后观察为什么出现adaptive plan.

--重新进入:
SCOTT@test01p> alter session set statistics_level=all;
Session altered.

select /*+ SET_TO_JOIN(@"SET$1") */ * from t1 intersect select * from t2;

SCOTT@test01p> @dpc '' outline,adaptive
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7y6uhq1r2bhnr, child number 2
-------------------------------------
select /*+ SET_TO_JOIN(@"SET$1") */ * from t1 intersect select * from t2

Plan hash value: 847386728

--------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                      | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT               |       |      1 |        |    13 (100)|     10 |00:00:00.01 |      13 |       |       |          |
|     1 |  HASH UNIQUE                   |       |      1 |      1 |    13   (0)|     10 |00:00:00.01 |      13 |  1600K|  1600K|  985K (0)|
|- *  2 |   HASH JOIN                    |       |      1 |      1 |    13   (0)|     10 |00:00:00.01 |      13 |  1079K|  1079K|          |
|     3 |    NESTED LOOPS                |       |      1 |        |            |     10 |00:00:00.01 |      13 |       |       |          |
|     4 |     NESTED LOOPS               |       |      1 |      1 |    13   (0)|     10 |00:00:00.01 |      12 |       |       |          |
|-    5 |      STATISTICS COLLECTOR      |       |      1 |        |            |     10 |00:00:00.01 |       3 |       |       |          |
|     6 |       TABLE ACCESS FULL        | T2    |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       3 |       |       |          |
|  *  7 |      INDEX UNIQUE SCAN         | PK_T1 |     10 |      1 |     0   (0)|     10 |00:00:00.01 |       9 |       |       |          |
|  *  8 |     TABLE ACCESS BY INDEX ROWID| T1    |     10 |      1 |     1   (0)|     10 |00:00:00.01 |       1 |       |       |          |
|-    9 |    TABLE ACCESS FULL           | T1    |      0 |      1 |     1   (0)|      0 |00:00:00.01 |       0 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------
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$02B15F54")
      MERGE(@"SEL$1")
      MERGE(@"SEL$2")
      OUTLINE(@"SET$09AAA538")
      SET_TO_JOIN(@"SET$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SET$1")
      FULL(@"SEL$02B15F54" "T2"@"SEL$2")
      INDEX(@"SEL$02B15F54" "T1"@"SEL$1" ("T1"."ID"))
      LEADING(@"SEL$02B15F54" "T2"@"SEL$2" "T1"@"SEL$1")
      USE_NL(@"SEL$02B15F54" "T1"@"SEL$1")
      NLJ_BATCHING(@"SEL$02B15F54" "T1"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$02B15F54")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."ID"="T2"."ID" AND SYS_OP_MAP_NONNULL("T1"."NAME")=SYS_OP_MAP_NONNULL("T2"."NAME"))
   7 - access("T1"."ID"="T2"."ID")
Note
-----
   - statistics feedback used for this statement
   - this is an adaptive plan (rows marked '-' are inactive)
59 rows selected.
--对adaptive plan不是很熟悉,可以理解marked '-' are inactive.或者先生成的是这些带-,实际执行时发生了改变.


7.再回头看看原始的执行计划:
SCOTT@test01p> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  g5jv20wwdh1zz, child number 0
-------------------------------------
select * from t1 intersect select * from t2
Plan hash value: 1917753433
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |   576 (100)|     10 |00:00:00.08 |     269 |       |       |          |
|   1 |  INTERSECTION       |      |      1 |        |            |     10 |00:00:00.08 |     269 |       |       |          |
|   2 |   SORT UNIQUE       |      |      1 |    100K|   573   (1)|    100K|00:00:00.07 |     266 |  5510K|   963K| 4897K (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |    100K|    77   (0)|    100K|00:00:00.01 |     266 |       |       |          |
|   4 |   SORT UNIQUE       |      |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       3 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------

--即使我们建立每个表上都建立了主键(字段id),而老的方法选择的仍然是全表扫描,每条记录肯定唯一,而在选择sort unique有点多余.
--即使写成如下:
select id from t1 intersect select id from t2;
--执行计划如下,依旧出现SORT UNIQUE.buffers=218 , 也不小.
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |      1 |        |   365 (100)|     10 |00:00:00.10 |     218 |      2 |       |       |          |
|   1 |  INTERSECTION          |       |      1 |        |            |     10 |00:00:00.10 |     218 |      2 |       |       |          |
|   2 |   SORT UNIQUE          |       |      1 |    100K|   364   (1)|    100K|00:00:00.09 |     216 |      1 |  4588K|   893K| 4078K (0)|
|   3 |    INDEX FAST FULL SCAN| PK_T1 |      1 |    100K|    58   (0)|    100K|00:00:00.04 |     216 |      1 |       |       |          |
|   4 |   SORT UNIQUE NOSORT   |       |      1 |     10 |     1   (0)|     10 |00:00:00.01 |       2 |      1 |       |       |          |
|   5 |    INDEX FULL SCAN     | PK_T2 |      1 |     10 |     1   (0)|     10 |00:00:00.01 |       2 |      1 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------

--实际上写成这样,输出结果一样.

select id,name from t2 where (id,name) in ( select id,name from t1);
select id,name from t1 where (id,name) in ( select id,name from t2);

--仅仅贴出第二个执行计划.实际上如果看Plan hash value是一样的.
SCOTT@test01p> select id,name from t1 where (id,name) in ( select id,name from t2);
        ID NAME
---------- --------------------
         1 testtest
         2 testtest
         3 testtest
         4 testtest
         5 testtest
         6 testtest
         7 testtest
         8 testtest
         9 testtest
        10 testtest
10 rows selected.

SCOTT@test01p> @dpc '' outline,adaptive
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  dnk2d8zxsk4gf, child number 0
-------------------------------------
select id,name from t1 where (id,name) in ( select id,name from t2)
Plan hash value: 4001747048
----------------------------------------------------------------------------------------------------------------
|   Id  | Operation                     | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT              |       |      1 |        |    13 (100)|     10 |00:00:00.01 |      23 |
|- *  1 |  HASH JOIN                    |       |      1 |     10 |    13   (0)|     10 |00:00:00.01 |      23 |
|     2 |   NESTED LOOPS                |       |      1 |        |            |     10 |00:00:00.01 |      23 |
|     3 |    NESTED LOOPS               |       |      1 |     10 |    13   (0)|     10 |00:00:00.01 |      13 |
|-    4 |     STATISTICS COLLECTOR      |       |      1 |        |            |     10 |00:00:00.01 |       4 |
|     5 |      TABLE ACCESS FULL        | T2    |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       4 |
|  *  6 |     INDEX UNIQUE SCAN         | PK_T1 |     10 |      1 |     0   (0)|     10 |00:00:00.01 |       9 |
|  *  7 |    TABLE ACCESS BY INDEX ROWID| T1    |     10 |      1 |     1   (0)|     10 |00:00:00.01 |      10 |
|-    8 |   TABLE ACCESS FULL           | T1    |      0 |      1 |     1   (0)|      0 |00:00:00.01 |       0 |
----------------------------------------------------------------------------------------------------------------
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$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3" "T2"@"SEL$2")
      INDEX(@"SEL$5DA710D3" "T1"@"SEL$1" ("T1"."ID"))
      LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
      USE_NL(@"SEL$5DA710D3" "T1"@"SEL$1")
      NLJ_BATCHING(@"SEL$5DA710D3" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"="ID" AND "NAME"="NAME")
   6 - access("ID"="ID")
   7 - filter("NAME"="NAME")
Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)
52 rows selected.

8.换成exists看看.

select id,name from t2 where exists ( select null from t1 where t1.id=t2.id and t1.name =t2.name);
select id,name from t1 where exists ( select null from t2 where t2.id=t1.id and t2.name =t1.name);

SCOTT@test01p> @dpc '' outline,adaptive
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  by9m9cvtgybq8, child number 0
-------------------------------------
select id,name from t1 where exists ( select null from t2 where t2.id=t1.id and t2.name =t1.name)
Plan hash value: 1238133714
-------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                     | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT              |       |      1 |        |     8 (100)|     10 |00:00:00.01 |      22 |       |       |          |
|- *  1 |  HASH JOIN                    |       |      1 |     10 |     8   (0)|     10 |00:00:00.01 |      22 |  1096K|  1096K|          |
|     2 |   NESTED LOOPS                |       |      1 |        |            |     10 |00:00:00.01 |      22 |       |       |          |
|     3 |    NESTED LOOPS               |       |      1 |     10 |     8   (0)|     10 |00:00:00.01 |      12 |       |       |          |
|-    4 |     STATISTICS COLLECTOR      |       |      1 |        |            |     10 |00:00:00.01 |       3 |       |       |          |
|     5 |      SORT UNIQUE              |       |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|     6 |       TABLE ACCESS FULL       | T2    |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       3 |       |       |          |
|  *  7 |     INDEX UNIQUE SCAN         | PK_T1 |     10 |      1 |     0   (0)|     10 |00:00:00.01 |       9 |       |       |          |
|  *  8 |    TABLE ACCESS BY INDEX ROWID| T1    |     10 |      1 |     1   (0)|     10 |00:00:00.01 |      10 |       |       |          |
|-    9 |   TABLE ACCESS FULL           | T1    |      0 |      1 |     1   (0)|      0 |00:00:00.01 |       0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------
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$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3" "T2"@"SEL$2")
      INDEX(@"SEL$5DA710D3" "T1"@"SEL$1" ("T1"."ID"))
      LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
      USE_NL(@"SEL$5DA710D3" "T1"@"SEL$1")
      NLJ_BATCHING(@"SEL$5DA710D3" "T1"@"SEL$1")
      SEMI_TO_INNER(@"SEL$5DA710D3" "T2"@"SEL$2")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T2"."ID"="T1"."ID" AND "T2"."NAME"="T1"."NAME")
   7 - access("T2"."ID"="T1"."ID")
   8 - filter("T2"."NAME"="T1"."NAME")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

9. in ,exists后一点点小问题会漏掉id相等,name为NULL的情况,继续测试:
insert into t2 values (11,null);
update t1 set name=null where id=11;
commt;

SCOTT@test01p> select /*+ OPT_PARAM('_convert_set_to_join' 'true') */ * from t1 intersect select * from t2;
        ID NAME
---------- --------------------
         7 testtest
         8 testtest
         2 testtest
         5 testtest
         1 testtest
         3 testtest
         6 testtest
        10 testtest
         4 testtest
         9 testtest
        11
11 rows selected.
--实际上看前面的执行计划,就很容易理解filter(SYS_OP_MAP_NONNULL("T1"."NAME")=SYS_OP_MAP_NONNULL("T2"."NAME"))的含义.


SCOTT@test01p> select id,name from t1 where exists ( select null from t2 where t2.id=t1.id and t2.name =t1.name);
        ID NAME
---------- --------------------
         1 testtest
         2 testtest
         3 testtest
         4 testtest
         5 testtest
         6 testtest
         7 testtest
         8 testtest
         9 testtest
        10 testtest

10 rows selected.

SCOTT@test01p> select id,name from t1 where (id,name) in ( select id,name from t2);
        ID NAME
---------- --------------------
         1 testtest
         2 testtest
         3 testtest
         4 testtest
         5 testtest
         6 testtest
         7 testtest
         8 testtest
         9 testtest
        10 testtest
10 rows selected.

--如果使用in,exists,写成这样.
select id,name from t1 where (id,name) in ( select id,name from t2)
union all
select id,name from t1 where (id) in ( select id from t2 where t2.name is null) and  t1.name is null ;

select id,name from t2 where (id,name) in ( select id,name from t2)
union all
select id,name from t2 where (id) in ( select id from t1 where t1.name is null) and  t2.name is null ;

select id,name from t1 where exists ( select null from t2 where t2.id=t1.id and (t2.name =t1.name or (t1.name is null and t2.name is null)));

select id,name from t2 where exists ( select null from t1 where t1.id=t2.id and (t1.name =t2.name or (t2.name is null and t1.name is null)));

12.猜测函数SYS_OP_MAP_NONNULL的意义:
SCOTT@test01p> select SYS_OP_MAP_NONNULL(id) c10 ,SYS_OP_MAP_NONNULL(name) ,t2.*,dump(id,16) c20,dump(name,16) c40 from t2;
C10        SYS_OP_MAP_NONNULL_NAM         ID NAME                 C20                  C40
---------- ---------------------- ---------- -------------------- -------------------- ----------------------------------------
C10200     746573747465737400              1 testtest             Typ=2 Len=2: c1,2    Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10300     746573747465737400              2 testtest             Typ=2 Len=2: c1,3    Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10400     746573747465737400              3 testtest             Typ=2 Len=2: c1,4    Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10500     746573747465737400              4 testtest             Typ=2 Len=2: c1,5    Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10600     746573747465737400              5 testtest             Typ=2 Len=2: c1,6    Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10700     746573747465737400              6 testtest             Typ=2 Len=2: c1,7    Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10800     746573747465737400              7 testtest             Typ=2 Len=2: c1,8    Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10900     746573747465737400              8 testtest             Typ=2 Len=2: c1,9    Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10A00     746573747465737400              9 testtest             Typ=2 Len=2: c1,a    Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10B00     746573747465737400             10 testtest             Typ=2 Len=2: c1,b    Typ=1 Len=8: 74,65,73,74,74,65,73,74
C10C00     FF                             11                      Typ=2 Len=2: c1,c    NULL

11 rows selected.

--从输出结果应该猜到大概.

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

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

cuixun7780
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle 12c 11g下载地址.txt
05-29
oracle 11g、12c在官网已经不支持下载，该文档是oracle 11g、12c及其他老版本的真实官网下载地址。亲测有效。
STC12C5A60s的AD转换.zip
04-01
它通常具有多个输入通道，可以连接不同的模拟传感器，如血压计中的压力传感器，将血压值转换为电信号。 STC12C5A60S2的AD转换特性包括： 1. **分辨率**：单片机的AD转换器可能有8位、10位、12位等不同分辨率，决定...
mysql 转 numeric_MySQL – JOIN基于CONVERT列到NUMERIC
weixin_32058991的博客
01-27 416
基本上,我正在尝试在进行连接时转换表字段.字段中的数字以0009897896,000239472938,00032423等开头.我想做一个基于连接的转换,所以当我查询列时,它将显示没有前导0的数字,因此它们将显示为9897896,239472938,32423等等.有人能帮助我做到这一点吗？我一直坚持这个问题:(这是我到目前为止所做的…从另一个数据库加入：SELECT l.loannumber A...
hive的MapJoin机制
Inner Peace
09-12 8904
什么是MapJoin? MapJoin顾名思义，就是在Map阶段进行表之间的连接。而不需要进入到Reduce阶段才进行连接。这样就节省了在Shuffle阶段时要进行的大量数据传输。从而起到了优化作业的作用。 MapJoin的原理： 即在map 端进行join，其原理是broadcast join，即把小表作为一个完整的驱动表来进行join操作。通常情况下，要连接的各个表里面的数据会分布在不同
oracle 11204 和oracle 18.1.0.1 优化器参数变化对比
Lixora's DB Home
02-20 640
PARAMETER                                 '11.2.0.4'                     '18.1.0.1'                     DESCRIPTION                                                                                  ---...
[20151217]12c标量子查询.txt
weixin_34097242的博客
01-20 80
[20151217]12c标量子查询.txt --我曾经写过blog，提到许多开发没有根据情况滥用子查询。 --而在12c下呢？ So starting with Oracle 12c, the CBO transformation engine can unnest some types of scala...
Oracle转换Postgres
帅的数说
10-16 6426
Oracle转换Postgres 1、前提 首先需要对Oracle和PostgreSQL的SQL都比较熟悉。对其理解的越详细就越具有优势，本文帮助读者迅速理解这两类SQL的区别是什么。 如果因ACS/pg而需要将Oracle移植到PG，那么就需要熟悉AOLserver Tcl，尤其是SOLserver的API。本文，主要讨论： Oracle 10g到11g（大多数可以适用到8i） Ora...
OCA/OCP Oracle 数据库12c考试指南读书笔记：第27章: Duplicating a Database
In-Memory Computing Technology
12-24 453
可传输表空间可将表空间从一个数据库拷贝到另一数据库，而无需耗时的export/import。 可传输数据库是可传输表空间的扩展，可创建新数据库，并将非系统表空间拷贝到新数据库。 还可以复制整个数据库，源数据库可以不停机，无需RMAN中间文件，因此可节省时间和空间。 描述和使用可传输表空间和可传输数据库 数据在数据库间的传递可使用Database Link，Data Pump和可传输表空间。数据量大...
誉天OCP 12C培训课程大纲
10-25 365
第01天 环境搭建 课程及考试介绍 讲师简介及资质证明 课程具体时间安排 课程内容介绍 考试内容、形式、注意事项 虚拟环境搭建 虚拟机搭建及配置 RHEL配置及安装、系统设置 Oracle 12c数据库软件安装 Oracle 12c建库（CDB容器数据库） 12c云库基本操作 SQLPLUS工具使用 CDB与PDB关系 如何连接CDB和PDB 第02天 SQL基础 SQL基本语句 SQL简介及分类 基本SELECT语句功能 单列、多列、列标属性、算...
flowable 6.7.2 工作流扩展连接达梦数据库
热门推荐
qq_37829708的博客
05-26 1万+
一、 修改jdbc连接达梦数据库 1.修改pom文件引入达梦驱动包和方言包（可在达梦安装目录中找到） 2.修改连接jdbc相关配置文件（此处略） 二、修改flowable-engine-common-6.7.2.jar中源码 1.上面设置完成后尝试启动项目，报错信息如下： Caused by: org.flowable.common.engine.api.FlowableException: couldn’t deduct database type from database product name ‘
sql server2014切换到oracle12c
baidu_36415076的博客
12-11 924
安装oracle12c略创建用户--------创建kdbase------------------------------------------ sqlplus / as sysdba; create pluggable database PDBORCL admin user user1 identified by user1 roles=(connect) file_name_conver
Navicat 连接数据库Oracle 12c ocr.dll连接资源包
12-25
标题中的“Navicat 连接数据库Oracle 12c OCR.dll连接资源包”指的是使用Navicat这款数据库管理工具连接Oracle 12c数据库时，可能会遇到的问题以及解决这个问题所需的资源。Navicat是一款流行的数据库管理和开发工具...
oracle12c中英文考题.txt
12-07
12月考完，没有什么用处，想考12c ocp的同学可以拿来看看。
Oracle RAC 12c运维最佳实践指南.pdf
10-14
Oracle RAC 12c 运维最佳实践指南 Oracle RAC 12c 是一种高可用、高性能的集群解决方案，旨在实现数据库的高可用性和可扩展性。本文将对 Oracle RAC 12c 运维最佳实践指南进行解读，总结出相关的知识点。 一、 ...
财务报表，项目费用支出明细.xlsx
08-09
工资表，财务报表，对账表，付款申请，财务报告，费用支出表 适用人群：学习不同技术领域的小白或进阶学习者；可作为毕设项目、课程设计、大作业、工程实训或初期项目立项。
ppap-第四版.pdf
08-09
ppap-第四版
铁路转辙机 ZD6 使用说明书
最新发布
08-09
铁路转辙机 ZD6 使用说明书
redhat7.0 oracle 12c rac完整部署手册.pdf
07-09
《REDHAT7.0 ORACLE 12C RAC完整部署手册.pdf》是一本关于在 Redhat 7.0 系统上部署 Oracle 12c RAC（Real Application Clusters）的完整手册。RAC是一种基于集群的数据库架构，可以为企业提供高可用性和可扩展性。...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值