[20170703]11g增加字段与查询条件的变化.txt

--//前几天给别人介绍12c新特性遇到一些问题,一直没空测试看看.有空测试看看.
--//办公环境没有12c,使用11g应该也能说明问题.

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

2.建立测试环境:
create table t (a number);
insert into t select rownum from dual connect by rownum <=10;
commit;

alter table t add ( b number default 1 not null);
--//注:11g,12c下这样也不会修改表对应的数据块.但是如果在11g这样执行会修改数据块alter table t add ( b number default 1 );
--//这样数据块中没有该字段的记录信息,而实际上查询是有值的,等于1.

--//另外12c下执行alter table t add ( b number );不会修改数据块,参考链接http://blog.itpub.net/267265/viewspace-1335561/
--//应该算12c的新特性.
--//当时的总结如下:
--//增加字段带缺省值,在12c下很快完成,不需修改表,但是以后的插入要多付出2个字节的代价.
--//它通过增加1个隐含字段里面的bit来表示这些字段从那里来读取.
--//后续的插入即使insert不带这些字段,其缺省值也插入数据块中.

insert into t select rownum+10,0  from dual connect by rownum <=1e5-10;
commit ;

--//分析表,并且在b字段建立直方图.
execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 for columns b size 254 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.

SCOTT@book> @desc t
Name  Null?    Type
----- -------- --------
A              NUMBER
B     NOT NULL NUMBER

3.测试:
select * from t where b=1;

SCOTT@book> @ &r/dpc '' projection
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2gcu8c0ucsuts, child number 0
-------------------------------------
select * from t where b=1
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |    69 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     10 |    70 |    69   (2)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NVL("B",1)=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "T"."A"[NUMBER,22], NVL("B",1)[22]

--//注意看过滤条件,可以发现过滤条件filter(NVL("B",1)=1).而12c增加的条件更加复杂.参考链接
--//http://blog.itpub.net/267265/viewspace-1335561/.

4.如果我给b增加索引,是否可以利用索引吗?这个是我当时的疑问.

SCOTT@book> create index i_t_b on t(b);
Index created.

select * from t where b=1;

--//检查执行计划:
SCOTT@book> @ &r/dpc '' projection
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2gcu8c0ucsuts, child number 0
-------------------------------------
select * from t where b=1
Plan hash value: 825900994
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |        |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     10 |    70 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T_B |     10 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(NVL("B",1)=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "T"."A"[NUMBER,22], NVL("B",1)[22]
   2 - "T".ROWID[ROWID,10], NVL("B",1)[22]

--//看来是我多虑了,oracle会使用索引.注意看Column Projection Information (identified by operation id):内容.