討論幾種資料列Column的特性（下）

上面我們主要討論了unused column的特性，我們繼續討論virtual column和其他特性。

 

3、Functional Index and hidden column

 

函式索引是會引起內部冗餘列出現的。我們對資料表t列新增一個函式索引，對last_ddl_time後一天（+1）進行處理。

 

 

SQL> create index idx_t_last_plus_one on t(last_ddl_time+1);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

 

此時，我們檢查資料字典情況。

 

 

SQL> select column_name, HIDDEN_COLUMN, VIRTUAL_COLUMN, SEGMENT_COLUMN_ID from dba_tab_cols where wner='SCOTT' and table_name='T';

 

COLUMN_NAME                    HIDDEN_COLUMN VIRTUAL_COLUMN SEGMENT_COLUMN_ID

------------------------------ ------------- -------------- -----------------

OBJECT_ID                      NO            NO                             1

OBJECT_NAME                   NO            NO                             2

LAST_DDL_TIME                  NO            NO                             3

SYS_NC00004$                   YES           YES            

 

 

新增加了一個系統列，取名為“SYS_NC00004$”，其hidden和virtual屬性設定均為Yes。

 

我們直接使用資料表的時候，該資料列預設是不現實出來的。

 

 

SQL> select * from t where rownum<5;

 

 OBJECT_ID OBJECT_NAM LAST_DDL_TIME

---------- ---------- -------------

        20 ICOL$      13-八月-09 23

        46 I_USER1    13-八月-09 23

        28 CON$       13-八月-09 23

        15 UNDO$      13-八月-09 23

 

 

但是，如果這個資料列被顯式的指明出來，我們是可以看到這個虛列的。同時，在被索引列發生DML（增加、修改、刪除）的時候，這個資料列遵循virtual column的特性連帶進行修改。

 

 

SQL> select t.*, SYS_NC00004$ from t where rownum<5;

 

 OBJECT_ID OBJECT_NAM LAST_DDL_TIME SYS_NC00004$

---------- ---------- ------------- ------------

        20 ICOL$      13-八月-09 23 14-八月-09 2

        46 I_USER1    13-八月-09 23 14-八月-09 2

        28 CON$       13-八月-09 23 14-八月-09 2

        15 UNDO$      13-八月-09 23 14-八月-09 2

 

SQL> truncate table t;

Table truncated

 

SQL> insert into t select object_id, object_name, last_ddl_time from dba_objects where rownum<5;

4 rows inserted

 

SQL> commit;

Commit complete

 

SQL> select t.*, SYS_NC00004$ from t where rownum<5;

 

 OBJECT_ID OBJECT_NAM LAST_DDL_TIME SYS_NC00004$

---------- ---------- ------------- ------------

        20 ICOL$      13-八月-09 23 14-八月-09 2

        46 I_USER1    13-八月-09 23 14-八月-09 2

        28 CON$       13-八月-09 23 14-八月-09 2

        15 UNDO$      13-八月-09 23 14-八月-09 2

 

 

函式索引對應的內部列，由Oracle進行管理。如果函式索引被刪除了，這個列自動就被刪除。

 

 

SQL> drop index idx_t_last_plus_one;

 

Index dropped

 

SQL> select column_name, HIDDEN_COLUMN, VIRTUAL_COLUMN, SEGMENT_COLUMN_ID from dba_tab_cols where wner='SCOTT' and table_name='T';

 

COLUMN_NAME                    HIDDEN_COLUMN VIRTUAL_COLUMN SEGMENT_COLUMN_ID

------------------------------ ------------- -------------- -----------------

OBJECT_ID                      NO            NO                             1

OBJECT_NAME                   NO            NO                             2

LAST_DDL_TIME                  NO            NO                             3

 

 

 

4、Virtual Column虛擬列

 

虛擬列最開始的初衷很簡單，就是將業務邏輯、計算關係繫結在資料表column的列之間。讓資料庫去負責一些資料欄位的計算。嚴格的說，這樣的設計思想和傳統的資料庫設計理論和如今的業務邏輯劃分原則是不符合的。

 

但是作為一個時代的產物，我們還是研究一下這個特性。

 

 

SQL> alter table t add mt number as (object_id+1);

 

Table altered

 

SQL> desc t

Name          Type          Nullable Default       Comments

------------- ------------- -------- ------------- --------

OBJECT_ID     NUMBER        Y                              

OBJECT_NAME   VARCHAR2(128) Y                              

LAST_DDL_TIME DATE          Y                              

MT            NUMBER        Y        "OBJECT_ID"+1          

 

 

新增virtual column之後，我們是可以透過desc檢視到資料表中增加的mt列，這個列還有一個預設值。

 

 

SQL> select column_name, HIDDEN_COLUMN, VIRTUAL_COLUMN, SEGMENT_COLUMN_ID from dba_tab_cols where wner='SCOTT' and table_name='T';

 

COLUMN_NAME                    HIDDEN_COLUMN VIRTUAL_COLUMN SEGMENT_COLUMN_ID

------------------------------ ------------- -------------- -----------------

OBJECT_ID                      NO            NO                             1

MT                             NO            YES           

OBJECT_NAME                   NO            NO                             2

LAST_DDL_TIME                  NO            NO                             3

 

 

在資料字典裡面，mt列被設定為virtual column，但是沒有column_id。在select *列表中，我們是可以看到資料mt的。

 

 

SQL> select * from t;

 

 OBJECT_ID OBJECT_NAM LAST_DDL_TIME         MT

---------- ---------- ------------- ----------

        20 ICOL$      13-八月-09 23         21

        46 I_USER1    13-八月-09 23         47

        28 CON$       13-八月-09 23         29

        15 UNDO$      13-八月-09 23         16

 

 

但是，作為virtual column，其取值和修改，是Oracle自己管理的。我們不能手工的進行干預。

 

 

SQL> insert into t (mt) values (23);

 

insert into t (mt) values (23)

 

ORA-54013: 不允許對虛擬列執行 INSERT 操作

 

SQL> update t set mt=102;

 

update t set mt=102

 

ORA-54017: 不允許對虛擬列執行 UPDATE 操作

 

 

 

5、聊聊12c裡面的visible column

 

最後我們聊聊發展趨勢。我們在11g裡面，已經有了對index的unusable和virtual index特性。

 

如果一個索引unusable了，說明這個索引在結構上已經被廢止，段結構已經失去了合法性。自然也不會參與到Oracle最佳化器決策過程中。但是，這個索引如果要重新起作用，需要進行一個複雜的rebuild過程。

 

對大表而言，rebuild索引可能是一個持續時間很長的過程，而且要消耗很多資源。於是，Oracle推出了invisible index的特性，索引沒有被設定為unusable，而且與資料之間的同步關係沒有被打散，但是不參與到CBO決策過程。

 

Invisible index主要是為了進行執行計劃的調整和實驗，這樣的策略較rebuild要好很多。

 

在12c裡面，我們發現Oracle將invisible的概念引入到column中，提出了invisible column的功能。這個特性最大的好處在於：相對於unused column而言，不可見列是可以逆轉的操作。也就是說，我們今天將資料列設定為invisible之後，過一會可以“反悔”，再加回來，資料可以找回來。

 

目前，12c剛剛推出，很多大牛們的測試文章也逐漸出水。筆者作為後輩，權當汗牛充棟而已。

 

6、結論

 

Oracle對column提供了很多的特性，但是這些特性大都帶有運維場景的背景要素。瞭解這些場景，適時的使用這些方法，對我們實際工作非常有意義。