Invisible Columns

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

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (

  id          NUMBER,

  description VARCHAR2(50) INVISIBLE

);

DESC tab1;

 Name                                      Null?    Type

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

 ID                                                 NUMBER

SQL>

INSERT INTO tab1 VALUES (1);

COMMIT;

SELECT * FROM tab1;

        ID

----------

         1

SQL>

在明确的指定它的名字进行查询的时候仍然是可用的

INSERT INTO tab1 (id, description) VALUES (2, 'TWO');

COMMIT;

SELECT id, description

FROM   tab1;

        ID DESCRIPTION

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

         1

         2 TWO

SQL>

下面关于隐含列的几项内容

• 虚拟列可以设成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 A15

SELECT column_id,

       column_name,

       hidden_column

FROM   user_tab_cols

WHERE  table_name = 'TAB1'

ORDER BY column_id;

 COLUMN_ID COLUMN_NAME     HID

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

         1 A               NO

         2 B               NO

           C               YES

SQL> 

ALTER TABLE tab1 MODIFY b INVISIBLE;

ALTER TABLE tab1 MODIFY c VISIBLE;

SELECT column_id,

       column_name,

       hidden_column

FROM   user_tab_cols

WHERE  table_name = 'TAB1'

ORDER BY column_id;

 COLUMN_ID COLUMN_NAME     HID

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

         1 A               NO

         2 C               NO

           B               YES

SQL>

ALTER TABLE tab1 MODIFY b VISIBLE;

SELECT column_id,

       column_name,

       hidden_column

FROM   user_tab_cols

WHERE  table_name = 'TAB1'

ORDER BY column_id;

 COLUMN_ID COLUMN_NAME     HID

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

         1 A               NO

         2 C               NO

         3 B               NO

SQL>

强制类型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 A20

SELECT column_id,

       column_name,

       nullable,

       hidden_column

FROM   user_tab_cols

WHERE  table_name = 'TAB1'

ORDER BY column_id;

 COLUMN_ID COLUMN_NAME          N HID

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

         1 ID                   N NO

         2 DESCRIPTION          N NO

           CREATED_DATE         N YES

SQL>

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>