Hey, did you catch that redirect? The Toad World URL is now community.toadworld.com. Don't worry -- you'll still find all the same great content here (and more on the way). We're just in the process of giving our Toad World site some well-deserved love. Stay tuned for some more updates coming this way.
Meanwhile, enjoy community.toadworld.com.
Starting in Oracle 184.108.40.206 there are several new features, +500 I have heard, and one of a good features for developers is "Invisible Columns". Invisible Columns allows a developer create a table with some special columns. These special columns are not shown to everybody who is using the table, in order to get the value of that column whoever is performing DMLs against the table must specify the name of the column explicitly, otherwise the behavior of that table will be as if it hadn't that column. This is useful when an application has changed, but some users are still using the former "structure" of the table. In this case "Invisible Columns" can be used, and let the new users know that they must specify the new columns explicitly while the old users can still using the former structure without issues. I will show you a couple of examples in this article in order to know all the "properties" around "Invisible Columns".
To begin, you have to know that Invisible Columns can be created at the time of the table creation, the syntax has changed a little bit for columns as I show you in the following picture:
Now let's create a table with invisible columns:
SQL> create table dgomez.TableWithInvisibleColumns (col1 varchar2 (20) visible,col2 varchar2 (20) invisible);
Now let's see how DMLs work with Invisible Columns:
In an insert operation where we don't specify explicitly the invisible column however we try to use it we will get an error. For example, in the following sentence, I am not specifying explicitly the column "col2" which is our invisible column, however I am trying to use it because I am inserting two values:
SQL> insert into dgomez.TableWithInvisibleColumns values ('b','b');insert into dgomez.TableWithInvisibleColumns values ('b','b')*ERROR at line 1:ORA-00913: too many values
The correct way to use the invisible column is as following, specifying the "col2", that will let Oracle know that we are aware of that invisible column and indeed we want to use it:
SQL> insert into dgomez.TableWithInvisibleColumns (col1, col2) values ('a','a');
1 row created.
In a select operation is the same, if we want to get the values of the invisible columns we have to specify the name of the invisible column in the "SELECT" sentence. For example, in the following sentence, we are trying to get all the columns from the table "dgomez.TableWithInvisibleColumns", however only one column is returned. This is because even if we specify "*" that is not a guarantee for oracle that we are aware about the invisible column, based on that, oracle returns us only the "visible" columns.
SQL> select * from dgomez.TableWithInvisibleColumns;
If we want to get the values of the invisible columns we have to specify the names, as the following example:
SQL> select col1, col2 from dgomez.TableWithInvisibleColumns;
COL1 COL2----- -----a a
Are the values stored physically into the table?
Yes, invisible columns are not the same than "Virtual Columns". This is totally different, with Virtual Columns the value (or the function that produces the value) is stored as metadata of that column but the value is not stored physically. This is different in indexes as you can read in my last article. But when we are using Invisible Columns the value is in fact stored physically. The visibility of those values are only managed as metadata, but the data is there.
data_block_dump,data header at 0x7f340fe60264===============tsiz: 0x1f98hsiz: 0x14pbl: 0x7f340fe6026476543210flag=--------ntab=1nrow=1frre=-1fsbo=0x14fseo=0x1f91avsp=0x1f7btosp=0x1f7b0xe:pti nrow=1 offs=00x12:pri offs=0x1f91block_row_dump:tab 0, row 0, @0x1f91tl: 7 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 1] 61 --> In ascii 'a'col 1: [ 1] 61 --> In ascii 'a' (This is the value of Invisible Column)end_of_block_dumpEnd dump data blocks tsn: 4 file#: 6 minblk 227 maxblk 227
Metadata of the Invisible Columns:
So, what about if I am not one more user that is using the table?, What about if I am the DBA of that table and I want to know which columns are invisible and which columns are not? There should be a way to know this. The first thought would be a "DBA_" table, but which one? Then we would think that the table DBA_TAB_COLUMNS has that information and we perform a "DESC DBA_TAB_COLUMNS", but unfortunately we see that there is not a column called "VISIBLE" or "VISIBILITY" or something like that. This is because Oracle didn't add a new column to describe the visibility of every column in a table, indeed the view "DBA_TAB_COLUMNS" has our information but is handled in a column that already exist, that column is "COLUMN_ID". When a column has NULL as the value of "COLUMN_ID" that means that column is Invisible, as in the following example:
SQL> select table_name, column_name, column_id from dba_tab_columns where owner='DGOMEZ' and table_name='TABLEWITHINVISIBLECOLUMNS';
TABLE_NAME COLUMN_NAME COLUMN_ID------------------------- ------------ ----------TABLEWITHINVISIBLECOLUMNS COL1 1TABLEWITHINVISIBLECOLUMNS COL2
We clearly see that the column "COL2" has a NULL value, that means that COL2 is Invisible.
Adding Invisible Columns:
Not only at the time of the table creation we can create the invisible columns, we can add them as well after the table creation by using "ALTER TABLE. In the following example I will show you how to add a Invisible Column but also I will confirm another property of invisible columns, this is that Virtual Columns can be also invisible:
SQL> alter table dgomez.TableWithInvisibleColumns add (col3 invisible as (col1||col2) virtual ) ;
Does the structure of the table has the invisible columns information?
To answer this question, let's describe the table. Usually we use "DESCRIBE" to have a quick look at the table's structure:
SQL> desc dgomez.TableWithInvisibleColumns;
Name Null? Type ------ ------ ---------------------------- COL1 VARCHAR2(20)
But as we see, the "DESCRIBE" command doesn't show any information about it. Now let's extract the structure but using "DBMS_METADATA":
SQL> select dbms_metadata.get_ddl('TABLE','TABLEWITHINVISIBLECOLUMNS','DGOMEZ') from dual;
CREATE TABLE "DGOMEZ"."TABLEWITHINVISIBLECOLUMNS" ( "COL2" VARCHAR2(20) INVISIBLE, "COL3" VARCHAR2(40) INVISIBLE GENERATED ALWAYS AS ("COL1"||"COL2") VIRTUAL , "COL1" VARCHAR2(20) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"
There is a very interesting thing here, do you remember how we were creating the columns in that table? At the table creation I put "COL1" as the first column, and "COL2" as the second column. After that I added a third column (COL3) via "ALTER TABLE". But see how DBMS_METADATA returns the DDL of that able, all the invisible columns are put at the beginning. If you use that DDL to create new tables and later you decide to put those columns VISIBLE the order of the columns will be different from the "original table's DDL".
Are indexes supported on Invisible Columns?
The answer is yes, we can. I will put a couple of examples here:
SQL> create index dgomez.Index1OnInvisibleColumn on dgomez.TableWithInvisibleColumns (col2);
SQL> create index dgomez.Index2OnInvisibleColumn on dgomez.TableWithInvisibleColumns (col2,col3);
Are Partition Keys supported on Invisible Columns?
This is interesting as well, when we are creating partitioned tables we can select an invisible column for the partition key:
SQL> create table dgomez.Table3WithInvisibleColumns (col1 varchar2 (20),col2 varchar2 (20) invisible)partition by hash (col2)partitions 2;
How to change the visibility of a column?
Fo finish this article I will show you how to change from a column from "Invisible" to "visible" and from "visible" to "invisible":
SQL> alter table dgomez.Table3WithInvisibleColumns modify (col2 visible);
SQL> alter table dgomez.Table3WithInvisibleColumns modify (col2 invisible);
Very helpful ! thanks!