[20170703]11g增加欄位與查詢條件的變化

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