oracle 12c中的隱含列

標籤：des   style   blog   color   使用   io   ar   for   div   

  Invisible Columns

使用select * from ，desc 等看不到該列，

DROP TABLE tab1 PURGE;CREATE TABLE tab1 (  id          NUMBER,  description VARCHAR2(50) INVISIBLE);DESC tab1; Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- ID                                                 NUMBERSQL>INSERT INTO tab1 VALUES (1);COMMIT;SELECT * FROM tab1;        ID----------         1SQL>

 

在明確的指定它的名字進行查詢的時候仍然是可用的

INSERT INTO tab1 (id, description) VALUES (2, ‘TWO‘);COMMIT;SELECT id, descriptionFROM   tab1;        ID DESCRIPTION---------- --------------------------------------------------         1         2 TWOSQL>

 

下面關於隱含列的幾項內容

• 虛擬列可以設成invisible
• 一個表可以對隱含列就行分區，
• 外部表格，表簇，暫存資料表中不能包含隱含列
• 使用者自訂的類型不能包含隱含屬性
• You can not make system generated hidden columns visible.

Invisible Columns and Column Ordering

隱含列不分配列的順序（colum_id），如果一個可見的列設為不可見，那麼這個列將放到最後一列

DROP TABLE tab1 PURGE;CREATE TABLE tab1 (  a NUMBER,  b NUMBER,  c NUMBER INVISIBLE);COLUMN column_name FORMAT A15SELECT column_id,       column_name,       hidden_columnFROM   user_tab_colsWHERE  table_name = ‘TAB1‘ORDER BY column_id; COLUMN_ID COLUMN_NAME     HID---------- --------------- ---         1 A               NO         2 B               NO           C               YESSQL> ALTER TABLE tab1 MODIFY b INVISIBLE;ALTER TABLE tab1 MODIFY c VISIBLE;SELECT column_id,       column_name,       hidden_columnFROM   user_tab_colsWHERE  table_name = ‘TAB1‘ORDER BY column_id; COLUMN_ID COLUMN_NAME     HID---------- --------------- ---         1 A               NO         2 C               NO           B               YESSQL>ALTER TABLE tab1 MODIFY b VISIBLE;SELECT column_id,       column_name,       hidden_columnFROM   user_tab_colsWHERE  table_name = ‘TAB1‘ORDER BY column_id; COLUMN_ID COLUMN_NAME     HID---------- --------------- ---         1 A               NO         2 C               NO         3 B               NOSQL>

 

強制類型Invisible Columns

隱含列的強制/可選等屬性仍然是有效，如下例：（隱含列的not null屬性仍然是生效的）

DROP TABLE tab1 PURGE;CREATE TABLE tab1 (  id          NUMBER NOT NULL,  description VARCHAR2(50) NOT NULL,  created_date DATE INVISIBLE NOT NULL);COLUMN column_name FORMAT A20SELECT column_id,       column_name,       nullable,       hidden_columnFROM   user_tab_colsWHERE  table_name = ‘TAB1‘ORDER BY column_id; COLUMN_ID COLUMN_NAME          N HID---------- -------------------- - ---         1 ID                   N NO         2 DESCRIPTION          N NO           CREATED_DATE         N YESSQL>INSERT INTO tab1 VALUES (1, ‘ONE‘);*ERROR at line 1:ORA-01400: cannot insert NULL into ("TEST"."TAB1"."CREATED_DATE")SQL>ALTER TABLE tab1 MODIFY created_date NULL;INSERT INTO tab1 VALUES (1, ‘ONE‘);1 row created.SQL>

 

oracle 12c中的隱含列