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