[20170606]11G _optimizer_null_aware_antijoin.txt

[20170606]11G _optimizer_null_aware_antijoin.txt

–//上午测试_optimizer_null_aware_antijoin,发现自己不通过意间还要作了一个低等错误,做1单记录.

1.环境:
SYS@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

SYS@book> @ &r/hide _optimizer_null_aware_antijoin
NAME                           DESCRIPTION                  
DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE



_optimizer_null_aware_antijoin null-aware antijoin parameter
TRUE          TRUE          TRUE

2.举行一个测试例子:
SCOTT@book> create table t1 as select * from dba_objects;
Table created.

SCOTT@book> create table t2 as select * from dba_objects;
Table created.

SCOTT@book> select count(*) from t2;

  COUNT(*)

     86996

SCOTT@book> select count(*) from t1;

  COUNT(*)

     86995
–//分析表略.
–//因为T1,T2建立有先后,这样T2的记录数据比T1笔录之数多1.瞩目1只特性允许NULL(这样树立的申),如果您以all_objects来起就情况不同了:
–//你可查定义desc all_objects, desc dba_objects.
SCOTT@book> @ &r/desc t1
   Name            Null? Type
   ————— —– ————–
 1 OWNER                 VARCHAR2(30)
 2 OBJECT_NAME           VARCHAR2(128)
 3 SUBOBJECT_NAME        VARCHAR2(30)
 4 OBJECT_ID             NUMBER
 5 DATA_OBJECT_ID        NUMBER
 6 OBJECT_TYPE           VARCHAR2(19)
 7 CREATED               DATE
 8 LAST_DDL_TIME         DATE
 9 TIMESTAMP             VARCHAR2(19)
10 STATUS                VARCHAR2(7)
11 TEMPORARY             VARCHAR2(1)
12 GENERATED             VARCHAR2(1)
13 SECONDARY             VARCHAR2(1)
14 NAMESPACE             NUMBER
15 EDITION_NAME          VARCHAR2(30)

SCOTT@book> select * from t1 where object_name=’T2′;
no rows selected

SCOTT@book> select * from t2 where object_name=’T2′;
OWNER  OBJECT_NAME SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID
OBJECT_TYPE CREATED             LAST_DDL_TIME      
TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME




SCOTT  T2                          90863          90863 TABLE      
2017-06-06 16:12:27 2017-06-06 16:12:27 2017-06-06:16:12:27 VALID   N N
N          1

SCOTT@book> select  * from t1 where OBJECT_ID=90863;
no rows selected

3.测试:
SCOTT@book> alter session set statistics_level=all;
Session altered.

SCOTT@book> select count(*) from t2 where object_id not in(select
object_id from t1);

  COUNT(*)

         0

SCOTT@book> select * from t2 where object_id not in(select
object_id from t1);
no rows selected
–//why? 理论应当回到1行啊.

SCOTT@book> @ &r/dpc ” ”

PLAN_TABLE_OUTPUT

SQL_ID  45ppus55apd1z, child number 1

select * from t2 where object_id not in(select object_id from t1)

Plan hash value: 35395643

| Id  | Operation               | Name | Starts | E-Rows |E-Bytes|E-Temp
| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem

|  1Mem | Used-Mem |

|   0 | SELECT STATEMENT        |      |      1 |        |       |      
|  1218 (100)|          |      0 |00:00:00.06 |    1244 |   1242 |      
|       |          |
|*  1 |  HASH JOIN RIGHT ANTI NA|      |      1 |    870 | 89610 | 
1448K|  1218   (1)| 00:00:15 |      0 |00:00:00.06 |    1244 |   1242 | 
1696K|  1696K| 2503K (0)|
|   2 |   TABLE ACCESS FULL     | T1   |      1 |  86995 |   424K|      
|   347   (1)| 00:00:05 |  86994 |00:00:00.02 |    1244 |   1242 |      
|       |          |
|   3 |   TABLE ACCESS FULL     | T2   |      0 |  86996 |  8325K|      
|   347   (1)| 00:00:05 |      0 |00:00:00.01 |       0 |      0 |      

|       |          |

Query Block Name / Object Alias (identified by operation id):

   1 – SEL$5DA710D3
   2 – SEL$5DA710D3 / T1@SEL$2
   3 – SEL$5DA710D3 / T2@SEL$1

Predicate Information (identified by operation id):

   1 – access(“OBJECT_ID”=”OBJECT_ID”)
27 rows selected.

–//这才想起来以前的一个不当,不克这么查询,因为IN中之笔录是NULL,这样NULL<>NULL.应该改写如下:

SCOTT@book> select * from t2 where object_id not in(select
object_id from t1 where t1.object_id is not null );
OWNER  OBJECT_NAME          SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID
OBJECT_TYPE         CREATED             LAST_DDL_TIME      
TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME




SCOTT  T2                                   90863          90863
TABLE               2017-06-06 16:12:27 2017-06-06 16:12:27
2017-06-06:16:12:27 VALID   N N N          1

SCOTT@book> @ &r/dpc ” ”

PLAN_TABLE_OUTPUT

SQL_ID  9cn6amuwk12r4, child number 0

select * from t2 where object_id not in(select object_id from t1
where
t1.object_id is not null )

Plan hash value: 2323664790

| Id  | Operation                | Name | Starts | E-Rows
|E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers

| Reads  |  OMem |  1Mem | Used-Mem |

|   0 | SELECT STATEMENT         |      |      1 |        |      
|       |  1218 (100)|          |      1 |00:00:00.15 |    2489 |   2484
|       |       |          |
|*  1 |  HASH JOIN RIGHT ANTI SNA|      |      1 |    870 | 89610 | 
1448K|  1218   (1)| 00:00:15 |      1 |00:00:00.15 |    2489 |   2484 | 
5416K|  2890K| 5574K (0)|
|*  2 |   TABLE ACCESS FULL      | T1   |      1 |  86993 |  
424K|       |   347   (1)| 00:00:05 |  86993 |00:00:00.02 |    1244 |  
1242 |       |       |          |
|   3 |   TABLE ACCESS FULL      | T2   |      1 |  86996 | 
8325K|       |   347   (1)| 00:00:05 |  86996 |00:00:00.03 |    1245 |  

1242 |       |       |          |

Query Block Name / Object Alias (identified by operation id):

   1 – SEL$5DA710D3
   2 – SEL$5DA710D3 / T1@SEL$2
   3 – SEL$5DA710D3 / T2@SEL$1

Predicate Information (identified by operation id):

   1 – access(“OBJECT_ID”=”OBJECT_ID”)
   2 – filter(“T1″.”OBJECT_ID” IS NOT NULL)
–//注意执行计划中的HASH JOIN RIGHT ANTI SNA.

4.测试”_optimizer_null_aware_antijoin”=false看看.

SCOTT@book> alter session set
“_optimizer_null_aware_antijoin”=false;
Session altered.

SCOTT@book> Select * from t2 where object_id not in(select
object_id from t1 where t1.object_id is not null );
OWNER  OBJECT_NAME          SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID
OBJECT_TYPE         CREATED             LAST_DDL_TIME      
TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME




SCOTT  T2                                   90863          90863
TABLE               2017-06-06 16:12:27 2017-06-06 16:12:27
2017-06-06:16:12:27 VALID   N N N          1
–//昏,那个慢受不了…….

SCOTT@book> @ &r/dpc ” ”

PLAN_TABLE_OUTPUT

SQL_ID  4ps64yjz4g7pu, child number 0

Select * from t2 where object_id not in(select object_id from t1
where
t1.object_id is not null )

Plan hash value: 2132554994

| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost

(%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |

|   0 | SELECT STATEMENT   |      |      1 |        |       | 
8847K(100)|          |      1 |00:04:06.56 |      53M|     67M|
|*  1 |  FILTER            |      |      1 |        |      
|            |          |      1 |00:04:06.56 |      53M|     67M|
|   2 |   TABLE ACCESS FULL| T2   |      1 |  86996 |  8325K|   347  
(1)| 00:00:05 |  86996 |00:00:00.05 |    1245 |   1242 |
|*  3 |   TABLE ACCESS FULL| T1   |  86995 |      2 |    10 |   116  

(0)| 00:00:02 |  86994 |00:04:05.95 |      53M|     67M|

Query Block Name / Object Alias (identified by operation id):

   1 – SEL$1
   2 – SEL$1 / T2@SEL$1
   3 – SEL$2 / T1@SEL$2

Predicate Information (identified by operation id):

   1 – filter( IS NULL)
   3 – filter((“T1″.”OBJECT_ID” IS NOT NULL AND
LNNVL(“OBJECT_ID”<>:B1)))
29 rows selected.

3.换1个方式:

SCOTT@book> create table t11 as select * from all_objects;
Table created.

SCOTT@book> create table t22 as select * from all_objects;
Table created.
–//分析表略.

SCOTT@book> @ &r/desc t11
Name           Null?    Type


OWNER          NOT NULL VARCHAR2(30)
OBJECT_NAME    NOT NULL VARCHAR2(30)
SUBOBJECT_NAME          VARCHAR2(30)
OBJECT_ID      NOT NULL NUMBER
DATA_OBJECT_ID          NUMBER
OBJECT_TYPE             VARCHAR2(19)
CREATED        NOT NULL DATE
LAST_DDL_TIME  NOT NULL DATE
TIMESTAMP               VARCHAR2(19)
STATUS                  VARCHAR2(7)
TEMPORARY               VARCHAR2(1)
GENERATED               VARCHAR2(1)
SECONDARY               VARCHAR2(1)
NAMESPACE      NOT NULL NUMBER
EDITION_NAME            VARCHAR2(30)

–// 注意这样定义之表OBJECT_ID是非空.

SCOTT@book> select * from t22 where object_id not in(select
object_id from t11);
OWNER  OBJECT_NAME          SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID
OBJECT_TYPE         CREATED             LAST_DDL_TIME      
TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME




SCOTT  T22                                  90869          90869
TABLE               2017-06-06 16:33:29 2017-06-06 16:33:29
2017-06-06:16:33:29 VALID   N N N          1

SCOTT@book> @ &r/dpc ” ”

PLAN_TABLE_OUTPUT

SQL_ID  31yq53mnd3g7k, child number 1

select * from t22 where object_id not in(select object_id from t11)

Plan hash value: 3941821364

| Id  | Operation            | Name | Starts | E-Rows |E-Bytes|E-Temp |
Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem

|  1Mem | Used-Mem |

|   0 | SELECT STATEMENT     |      |      1 |        |       |       | 
1187 (100)|          |      1 |00:00:00.15 |    2427 |   2422 |      
|       |          |
|*  1 |  HASH JOIN RIGHT ANTI|      |      1 |    848 | 87344 | 
1408K|  1187   (1)| 00:00:15 |      1 |00:00:00.15 |    2427 |   2422 | 
5236K|  2890K| 5538K (0)|
|   2 |   TABLE ACCESS FULL  | T11  |      1 |  84763 |   413K|      
|   338   (1)| 00:00:05 |  84763 |00:00:00.02 |    1213 |   1211 |      
|       |          |
|   3 |   TABLE ACCESS FULL  | T22  |      1 |  84764 |  8112K|      
|   338   (1)| 00:00:05 |  84764 |00:00:00.03 |    1214 |   1211 |      

|       |          |

Query Block Name / Object Alias (identified by operation id):

   1 – SEL$5DA710D3
   2 – SEL$5DA710D3 / T11@SEL$2
   3 – SEL$5DA710D3 / T22@SEL$1

Predicate Information (identified by operation id):

   1 – access(“OBJECT_ID”=”OBJECT_ID”)

–//注意执行计划HASH JOIN RIGHT
ANTI.我怀念起来以前10g我啊撞类型问题,将数据类型修改为NULL.

4.脱测试用not exists的场面:

SCOTT@book> select * from t2 where not exists  (select 1 from t1 
where t1.OBJECT_ID=t2.object_id);
OWNER  OBJECT_NAME SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID
OBJECT_TYPE         CREATED             LAST_DDL_TIME      
TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME




SCOTT  T2                          90863          90863
TABLE               2017-06-06 16:12:27 2017-06-06 16:12:27
2017-06-06:16:12:27 VALID   N N N          1
PUBLIC LOOPBACK                                         DATABASE
LINK       2016-12-14 15:17:00                                        
VALID   N N N
PUBLIC TEST040                                          DATABASE
LINK       2017-01-09 09:14:26                                        
VALID   N N N
–//这样返回3条.为就是这般查询注意空值的情况.也便是要是参加t2.object_id
is not null 条件,才见面回来1条.

SCOTT@book> @ &r/dpc ” ”

PLAN_TABLE_OUTPUT

SQL_ID  4jx0g5ndct9vk, child number 0

select * from t2 where not exists  (select 1 from t1  where
t1.OBJECT_ID=t2.object_id)

Plan hash value: 1142061071

| Id  | Operation            | Name | Starts | E-Rows |E-Bytes|E-Temp |
Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem

|  1Mem | Used-Mem |

|   0 | SELECT STATEMENT     |      |      1 |        |       |       | 
1218 (100)|          |      3 |00:00:00.14 |    2489 |   2484 |      
|       |          |
|*  1 |  HASH JOIN RIGHT ANTI|      |      1 |    870 | 89610 | 
1448K|  1218   (1)| 00:00:15 |      3 |00:00:00.14 |    2489 |   2484 | 
5416K|  2890K| 5759K (0)|
|   2 |   TABLE ACCESS FULL  | T1   |      1 |  86995 |   424K|      
|   347   (1)| 00:00:05 |  86995 |00:00:00.02 |    1244 |   1242 |      
|       |          |
|   3 |   TABLE ACCESS FULL  | T2   |      1 |  86996 |  8325K|      
|   347   (1)| 00:00:05 |  86996 |00:00:00.02 |    1245 |   1242 |      

|       |          |

Query Block Name / Object Alias (identified by operation id):

   1 – SEL$5DA710D3
   2 – SEL$5DA710D3 / T1@SEL$2
   3 – SEL$5DA710D3 / T2@SEL$1

Predicate Information (identified by operation id):

   1 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)

SCOTT@book> select * from t1 where object_id is null ;
OWNER  OBJECT_NAME          SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID
OBJECT_TYPE         CREATED             LAST_DDL_TIME      
TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME




PUBLIC LOOPBACK                                                 
DATABASE LINK       2016-12-14
15:17:00                                         VALID   N N N
PUBLIC TEST040                                                  
DATABASE LINK       2017-01-09
09:14:26                                         VALID   N N N
–//正好2条object_id is null.

5.总结:
1.还注意not in 与NULL的查询.
2.立良好的束缚,规避一些如此的问题.

相关文章