之前筆者寫過一個系列《索引列的usable和visible》（http://space.itpub.net/17203031/viewspace-688135），詳細討論了索引列的usable和visible屬性。在11g中，Oracle推出了索引的visible和invisible屬性，用於臨時性的遮蔽索引參與最佳化器過程。

在本篇中，我們對應討論一下column的一些屬性，權作為之前系列的續篇。

1、環境介紹

我們選擇Oracle 11gR2進行試驗。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

在資料字典dba_tab_cols中，有兩個資料列HIDDEN_COLUMN和VIRTUAL_COLUMN。本篇主要圍繞兩個屬性進行研究。

SQL> desc dba_tab_cols;

Name Type Nullable Default Comments

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

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30) Table, view or cluster name

（篇幅原因，有省略……）

HIDDEN_COLUMN VARCHAR2(3) Y Is this a hidden column?

VIRTUAL_COLUMN VARCHAR2(3) Y Is this a virtual column?

2、unused column

Unused Column是Oracle為了支援快速資料列Column刪除提供的一種功能。我們可以透過設定資料列unused的狀態，很快地（7×24系統情況下）將資料表的某些列遮蔽組。這個過程中不消耗很多的資源和引起大量的阻塞。

Sys使用者下有一個資料表T，包括資料列seq_num。我們可以透過set unusable 來將資料列設定為unused。

SQL> show user;

User is "SYS"

SQL> alter table t set unused column seq_num;

alter table t set unused column seq_num

ORA-12988: 無法刪除屬於 SYS 的表中的列

Sys使用者下的資料表是不能對column進行unused操作的。我們切換到scott使用者下，建立資料表t。

SQL> conn scott/tiger@wilson;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

SQL> create table t as select object_id, owner, object_name, last_ddl_time from dba_objects;

Table created

SQL> select count(*) from t;

COUNT(*)

----------

72779

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

OWNER NO NO 2

OBJECT_NAME NO NO 3

LAST_DDL_TIME NO NO 4

此時，四個資料列都是正常的。使用set unusable屬性。

SQL> alter table t set unused column owner;

Table altered

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

SYS_C00002_13080423:42:23$ YES NO 2

OBJECT_NAME NO NO 3

LAST_DDL_TIME NO NO 4

對owner列進行unused處理之後，我們發現資料字典中，原有的欄位被修改為一個系統內部名稱“SYS_C00002_13080423:42:23$”。欄位的hindden_column取值為Yes，說明不再顯示出來。

注意：對一個欄位進行unused屬性設定，是一個標記性的動作。對應的資料表段結構沒有進行收縮動作，對應的空間也不會回收。重要的是，對一個欄位進行unused處理過程持續時間很短，遠低於drop一個資料大表資料列的過程。

而且，修改的列名中，有很強烈的時間資訊，也就反映了進行操作的時間點。

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DDHH2

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

2013-08-04 23:52:26

被設定為unused之後，欄位就不會出現在select *和desc列表中，Oracle原則上也不會承認這個欄位了。

SQL> desc t;

Name Type Nullable Default Comments

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

OBJECT_ID NUMBER Y

OBJECT_NAME VARCHAR2(128) Y

LAST_DDL_TIME DATE Y

SQL> col object_name for a10;

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

SQL> insert into t (owner) values ('kk');

insert into t (owner) values ('kk')

ORA-00904: "OWNER": 識別符號無效

注意，在Oracle Index裡面，如果一個索引狀態是unusable了，表示該索引需要重建rebuild操作。但是資料欄位如果unused，目前是沒有方法將其逆轉過來的。

SQL> alter table t set used column owner;

alter table t set used column owner

ORA-02000: 缺失 UNUSED 關鍵字

設定成unused的column資訊理論上還存在在資料段segment結構裡面。我們可以選擇一個“合適”的時間進行刪除。

SQL> alter table t drop unused columns;

(持續時間長)

Table altered

SQL> desc t;

Name Type Nullable Default Comments

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

OBJECT_ID NUMBER Y

OBJECT_NAME VARCHAR2(128) Y

LAST_DDL_TIME DATE Y

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

Drop unused columns可以一次性的將資料表上面所有的unused列刪除掉。這個過程是真正進行的刪除，如果資料表比較大的話，這個操作持續時間還是比較長的。

最後我們聊聊unused column的實際意義。應該說，這個功能對於開發階段而言，沒有任何意義。對於投產上線過程和升級過程的DBA運維工作，有一定作用。

在進行投產上線的時候，比如系統新版本需要刪除某些大表的欄位。如果系統是7×24小時執行，投產上線階段給定的時間視窗不夠，直接刪除欄位引起的阻塞風險和時間成本可能是運維單位不能承受的。Unused column提供了一種可能，就是快速的將資料表欄位遮蔽住，減少這種阻塞時間。段級別的刪除，可以調整到系統正式的維護視窗中一點點的去完成。

下面我們會繼續討論幾種column特性。

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

相關文章