[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增加的条件更加复杂.参考链接

--//.

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):内容.