oracle不可见列和不可用列,oracle 12c new feature 列不可见

Invisible Columns in Oracle Database 12

c

Introduction

In Oracle Database 12c, table columns can be defined as invisible

either during its creation with the CREATE TABLE command or by modifying

existing table columns via the ALTER TABLE statement. By default, table

columns are always visible. Once a column has been set to invisible, it

can be reverted back to visible using the ALTER TABLE statement.

Among other operations, the following ones will not display or work with invisible table columns:

SELECT * FROM in SQL instructions

The DESCRIBE statement when used in either SQL*PLUS or via Oracle Call Interface (OCI)

%ROWTYPE attribute in PL/SQL variable declarations

From the table indexes standpoint, invisible columns are still

available for indexing and such indexes and are available to the

optimizer during the access path selection.

The following example shows the creation of a table where column3 is defined as invisible using the INVISIBLE keyword:

SQL> CREATE TABLE tabela_col_inv (

coluna1 NUMBER,

coluna2 NUMBER,

coluna3 NUMBER INVISIBLE,

coluna4 NUMBER  );

Table created.

By Default, invisible columns do not show up when the DESCRIBE statement is issued against the table:

SQL> desc tabela_col_inv

Name                          Null?    Type

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

COLUNA1                     NUMBER

COLUNA2                     NUMBER

COLUNA4                     NUMBER

A SQL*PLUS session can be set to display invisible columns by setting the new switch COLINVISIBLE to ON as follows:

SQL> SET COLINVISIBLE ON

SQL> desc tabela_col_inv

Name                   Null?    Type

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

COLUNA1            NUMBER

COLUNA2            NUMBER

COLUNA4            NUMBER

COLUNA3 (INVISIBLE)                    NUMBER

While being invisible, the column can still be accessed via DML and DDL statements as follows:

SQL> INSERT INTO tabela_col_inv (coluna1,coluna2,coluna3,coluna4) VALUES (100,200,300,400);

1 row created.

SQL> SELECT  coluna1,coluna2,coluna3,coluna4 FROM tabela_col_inv;

COLUNA1    COLUNA2    COLUNA3    COLUNA4

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

100   200      300 400

However, when performing inserts into tables containing invisible

columns, the column list must be defined in the statement. Failing to do

so, will result in an ORA-00913 error as follows:

SQL> INSERT INTO tabela_col_inv VALUES (101,102,103,104);

INSERT INTO tabela_col_inv VALUES (101,102,103,104)

* ERROR at line 1:

ORA-00913: too many values

However, inserts without column lists are possible if no value is

passed to the invisible column and if the it is either nullable or it

has a DEFAULT clause value:

SQL> INSERT INTO tabela_col_inv VALUES (101,102,104);

1 row created.

SQL> SELECT * FROM tabela_col_inv;

COLUNA1       COLUNA2    COLUNA4

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

100      200    400

101           102           104

Invisible Columns and Check Constraints

Check Constraints defined on invisible columns will continue to work just as in visible columns:

SQL> CREATE TABLE tabela_col_inv2 (coluna1 NUMBER not null,

coluna2 NUMBER INVISIBLE not null  );

Table created.

SQL> desc tabela_col_inv2

Name                  Null?    Type

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

COLUNA1          NOT NULL NUMBER

SQL>insert into tabela_col_inv2 values(1);

insert into tabela_col_inv2  values(1)

*

ERROR at line 1: ORA-01400: cannot insert NULL into ("SCOTT"."TABELA_COL_INV2"."COLUNA2")

Creating Invisible Virtual Columns

It is also possible to create an invisible virtual column by combining both features as follows:

SQL> create table tabela_col_inv3 ( coluna1  number, coluna2 INVISIBLE generated always

as (coluna1+1) virtual);

Table created.

Column Ordering

As the column is reset to visible, it will be displayed as the last column of the table:

SQL> ALTER TABLE tabela_col_inv MODIFY coluna3 VISIBLE;

Table altered.

SQL> SELECT * FROM tabela_col_inv;

COLUNA1    COLUNA2    COLUNA4    COLUNA3

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

100   200      400      300

101        102        104

SQL> desc tabela_col_inv

Name          Null?    Type

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

COLUNA1           NUMBER

COLUNA2           NUMBER

COLUNA4           NUMBER

COLUNA3           NUMBER

By querying the view SYS.COL$, it becomes clear that the column COL#

is modified so that it becomes the last column of the table:

SQL> SELECT name,col#,intcol#,segcol#,

TO_CHAR (property,'XXXXXXXXXXXX') property

FROM sys.col$

WHERE obj# = (SELECT obj# FROM sys.obj$ WHERE name = 'TABELA_COL_INV')

NAME      COL# INTCOL#    SEGCOL#    PROPERTY

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

COLUNA1 1         1      1  0

COLUNA2 2   2      2  0

COLUNA3 4   3      3  0

COLUNA4 3   4      4  0

SQL> ALTER TABLE tabela_col_inv MODIFY coluna4 INVISIBLE;

Table altered.

Checking the Database Dictionary

SQL> select column_id, segment_column_id, internal_column_id, column_name, hidden_column,

virtual_column from user_tab_cols where table_name ='TABELA_COL_INV';

COLUMN_ID  SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID  COLUMN_NAME    HID   VIR

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

1          1                   1   COLUNA1      NO   NO

2   2       2   COLUNA2      NO   NO

3    3       3                  COLUNA3      NO   NO

4        4   COLUNA4      YES  NO

When a table column is set to invisible, the content of the column property from table SYS.COL$ is set to the following value:

SQL> SELECT name,col#,intcol#,segcol#,TO_CHAR (property,'XXXXXXXXXXXX') property

FROM sys.col$

WHERE obj# =

(

SELECT obj# FROM sys.obj$ WHERE name = 'TABELA_COL_INV'

);

2    3    4    5    6

NAME   COL#    INTCOL#   SEGCOL# PROPERTY

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

COLUNA1       1  1    1  0

COLUNA2       2  2    2  0

COLUNA3       3  3    3  0

COLUNA4       0  4    4  SQL> ALTER TABLE tabela_col_inv MODIFY coluna4 VISIBLE;

Table altered.

SQL> ALTER TABLE tabela_col_inv MODIFY coluna3 INVISIBLE;

Table altered.

SQL> desc tabela_col_inv

Name                   Null?    Type

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

COLUNA1            NUMBER

COLUNA2            NUMBER

COLUNA4            NUMBER

COLUNA3 (INVISIBLE)            NUMBER

SQL> truncate table tabela_col_inv;

Table truncated

SQL> INSERT INTO tabela_col_inv (coluna1,coluna2,coluna3,coluna4) VALUES (100,200,null,400);

1 row created.

SQL> SELECT  coluna1,coluna2,coluna3,coluna4 FROM tabela_col_inv;

COLUNA1    COLUNA2    COLUNA3    COLUNA4

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

100   200 400