Note: Index article on some 12c features can be seen here
The name is self explanatory.
Invisible columns means just that -- columns that are invisible.
It gives one the ability to introduce a change while minimizing any negative side effects of that change.
So, in a table, if we add a column, it will show up in a "select *" query.
All the "insert into <table> values ...." queries will break.
When you add an invisible column, you won't be able to see it even when you execute a "DESC" or when you run a "select *" query.
The "insert into <table> values ...." queries will not break.
However, you will able to see it if you specifically query for it by name.
Lets have a look at an example.
We have a table "TEST_IDENTITY" which we had used to test the identity column. Refer article on identity column
Lets add an invisible column to it.
The command will be:
ALTER TABLE <TABLE_NAME> ADD (<COLUMN_NAME> <TYPE> INVISIBLE);
As you can see below, I added a column name as INVISIBLE and it does not show up in DESC command.
It does not show up in "select *" and has no impact on the "insert into <TABLE_NAME>" as well.
But you can insert data into it and also do a select by calling it explicitly.
You can make the column visible by running the following alter command:
ALTER TABLE <TABLE_NAME> MODIFY <INVISIBLE_COLUMN_NAME> VISIBLE;
Some questions that may help:
How to know a table exists with an invisible column?
Run a query on USER_TAB_COLS
SELECT COLUMN_NAME, HIDDEN_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME = '<TABLE_NAME>';
What if I create an invisible column as NOT NULL?
INSERT will fail. You need to create with DEFAULT option or use a trigger to populate on INSERT.
Is there any massive advantage of this feature?
Its a feature which could be used but not any with massive advantage. it could create problems for some applications when made visible, but if you have coded with proper INSERTs/SELECTs (mentioning columns), it won't harm your application.
The name is self explanatory.
Invisible columns means just that -- columns that are invisible.
It gives one the ability to introduce a change while minimizing any negative side effects of that change.
So, in a table, if we add a column, it will show up in a "select *" query.
All the "insert into <table> values ...." queries will break.
When you add an invisible column, you won't be able to see it even when you execute a "DESC" or when you run a "select *" query.
The "insert into <table> values ...." queries will not break.
However, you will able to see it if you specifically query for it by name.
Lets have a look at an example.
We have a table "TEST_IDENTITY" which we had used to test the identity column. Refer article on identity column
Lets add an invisible column to it.
The command will be:
ALTER TABLE <TABLE_NAME> ADD (<COLUMN_NAME> <TYPE> INVISIBLE);
As you can see below, I added a column name as INVISIBLE and it does not show up in DESC command.
It does not show up in "select *" and has no impact on the "insert into <TABLE_NAME>" as well.
But you can insert data into it and also do a select by calling it explicitly.
You can make the column visible by running the following alter command:
ALTER TABLE <TABLE_NAME> MODIFY <INVISIBLE_COLUMN_NAME> VISIBLE;
Some questions that may help:
How to know a table exists with an invisible column?
Run a query on USER_TAB_COLS
SELECT COLUMN_NAME, HIDDEN_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME = '<TABLE_NAME>';
What if I create an invisible column as NOT NULL?
INSERT will fail. You need to create with DEFAULT option or use a trigger to populate on INSERT.
Is there any massive advantage of this feature?
Its a feature which could be used but not any with massive advantage. it could create problems for some applications when made visible, but if you have coded with proper INSERTs/SELECTs (mentioning columns), it won't harm your application.
No comments:
Post a Comment