ITPub博客

首页 > 数据库 > Oracle > oracle 12c new feature 列不可见

oracle 12c new feature 列不可见

原创 Oracle 作者:paulyibinyi 时间:2015-11-13 20:39:17 0 删除 编辑

Invisible Columns in Oracle Database 12c


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 400000020 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

We go ahead and dump the block containing the row in order to look for information regarding the column ordering.

SQL> select dbms_rowid.rowid_relative_fno(rowid) File#, dbms_rowid.rowid_block_number(rowid) 
Block# from tabela_col_inv;
 
      
FILE#   	  BLOCK# 
----------    ---------- 	 
6	          335

SQL> alter system dump datafile 6 block 335;

System altered.

data_block_dump,data header at 0x7f0b49520064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x7f0b49520064
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x16
fseo=0x1f79
avsp=0x1f63
tosp=0x1f63
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1f89
0x14:pri[1]     offs=0x1f79
block_row_dump:
tab 0, row 0, @0x1f79
tl: 16 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [ 3]  c2 02 02
col  1: [ 3]  c2 02 03
col  2: *NULL*   <<<<<<< INVISIBLE COLUMN
col  3: [ 3]  c2 02 05
end_of_block_dump


SQL> select dump(coluna1,16) col1, dump(coluna2,16) col2, dump(coluna3,16) col3, dump(coluna4,16)
 col4 from tabela_col_inv;

COL1		      COL2		   COL3		        COL4 
-------------------- -------------------- -------------------- -------------------- 
Typ=2 Len=2: c2,2    Typ=2 Len=2: c2,3    NULL		        Typ=2 Len=2: c2,5 


Partitioning


Table partitioning based on invisible columns is also supported as follows:

SQL> create table tabela_col_inv4 (coluna1  number, coluna2 INVISIBLE generated always as (coluna1+1) 
 virtual) partition by range(coluna2) (partition part1 values less than(100), partition part2 values less 
 than(maxvalue) ); 
   
Table created. 

By Alex Zaballa, Oracle Ace and Daniel Da Meda (OCM)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7199859/viewspace-1837438/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
oracle 10g ocm oracle 10g/11g/12c ocp aix 6.1 administrator,ogg expert,ITSS 技术交流群 201703254 微信公众号 paulyibin 探讨技术,开心工作 电话 13719354869 ,深入研究数据库和开始研究big data

注册时间:2007-12-11

  • 博文量
    902
  • 访问量
    6493874