Monday, January 14, 2008

My Top 15 Oracle DB 11g NF for APEX Developers: 15

I thought it a good idea to write a series of blog posts about some new features of the Oracle 11g database I find useful in an APEX project.


I decided to do a "Top 15". It's very difficult to order them, as for one person or project a feature would be number 1, but for another, it would only be found on place 10. So don't see it as I like the feature I put on place 15 less than the one on place 10. I like all new features ;-)

My research... this top 15 came together after reading:
That was the background...

15 - Virtual Columns

Why would you like to use this feature?

If you've an orders table with the unit price and the quantity, wouldn't it be useful to have the total (= unit price * quantity)?
Or when a store has a price list of items they buy, they add 30% to it to get the price they sell the item too. Or getting the initials of a name. Or seeing the date in a specific format. Or... Having that kind of information already in the table may be of help.

What does Oracle say about it?

Virtual columns enable application developers to define computations and transformations as the column (metadata) definition of tables without space consumption. This makes application development easier and less error-prone, as well as enhances query optimization by providing additional statistics to the optimizer for these virtual columns.

Syntax

Part of the create/alter table statement:

column [datatype] [GENERATED ALWAYS] AS (column_expression) [VIRTUAL] 
[ inline_constraint [inline_constraint]... ]

Example
SQL> CREATE TABLE EMP_WITH_VC (
EMPNO NUMBER(4,0) NOT NULL ENABLE,
ENAME VARCHAR2(10 BYTE),JOB VARCHAR2(9 BYTE),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0),
NEW_SAL AS (ROUND(sal*1.10,2)),
TOTAL_SAL_COMM NUMBER GENERATED ALWAYS AS (ROUND(sal*1.10+NVL(comm,0),2)) VIRTUAL,
HIRE_YEAR AS (TO_CHAR(hiredate, 'YYYY')),

PRIMARY KEY (EMPNO)
);

SQL> INSERT INTO EMP_WITH_VC (empno, ename, job, mgr,
hiredate, sal, comm, deptno)
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM EMP;

If we query the new EMP with Virtual Column table, we'll see all data of the normal EMP table + the new virtual columns (red): the new salary (10% higher), the total package of the salary and commission and the hire year.
SQL> SELECT * FROM EMP_WITH_VC

I wanted to put the examples online on the new APEX 3.1 instance, but it doesn't seem to run on 11g... (as my creation of my table failed). But as you see on the screenshot, it's running on my local machines.

Behind the Scenes

If you look at the user_tab_columns view you'll notice a column called data_default, which contains the expression of the virtual column.


Other useful information
  • An index can be created on a Virtual Column (this would be a function based index)
  • You can not do DML (insert, update, delete) on a Virtual Column, but you can use it in a WHERE clause
  • Virtual Columns are only available on relational heap tables
  • Virtual Column expression work only on the same table, so not cross table and they can't contain other Virtual Columns
  • You can use Vitual Columns in Constraints (PK, FK, Check)
  • You can partition on Virtual Columns !
The full documentation about Virtual Columns can be found here.

12 comments:

Anonymous said...

Nice feature, although i don't see the use the VIRTUAL keyword in your example, while new_sal and new_year do not have the VIRTUAL keyword, but still are showed as virtual columns? (I just started with the new features book so i'm not at this point yet ;-)

Dimitri Gielis said...

Hello,

The VIRTUAL keyword is optional. So you've the choice of using it or not.

The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.

Hope that clarifies,
Dimitri

Paulo Vale said...

This top15 features posting it's a great idea congratulations.
Virtual column seems like a nice feature, but nothing you cannot achieve creating a view.

Dimitri Gielis said...

Hi Paulo,

Sure you can create it with a view, but by using this feature you don't have to write it in all views over and over again, which should give less errors.
Also the performance with virtual columns would be better.
Something I didn't touch that much, virtual columns could also be used as business rules.

So, at the end I believe Virtual Columns give you a lot more than you can have in views.

Thanks,
Dimitri

Steve said...

Virtual columns have to be approached with caution. They sound like a very neat idea, but they are nothing new, they have been around for 30 years in Pick. Like GOTOs, they are useful in certain circumstances but the unbridled use of them will cause confusion and may cause severe performance problems. In my Pick days, I often came across tables with many more virtual columns than real columns - sorting out which were "good" and which were "experiments gone wrong and then forgotten about" was a nightmare (never documented), and any applications that did the equivalent of "select * from table_with_lots_of_virtuals" performed very poorly indeed. Contrast this with views, where you can bypass all the extra processing by going to the base tables. Once a virtual column is attached to a table, you run the risk that performance on a whole range of applications is immediately affected. I am not saying Virtual Columns are a bad idea, its just that I have seen the dark side of this feature. Developers & DBAs sometimes go wild on a particular feature, like indexing every column on a table "just in case".

Patrick Wolf said...

Hi Dimitri,

>Also the performance with virtual columns would be better.

why would a virtual column be faster than the same expression in a view? To my knowledge, both are generated on the fly.

As you noticed, one of the big advantages of them is that you can create constraints on this columns which can be much more complex (eg. function calls) than what you can do in regular constraints.

Patrick

Dimitri Gielis said...

@ Steve,

I can understand your feeling... you can use a knife to kill somebody or to rescue somebody or to make something beautiful with it...
But yes, handle virtual columns with care ;-)

@ Patrick,

You can create indexes on Virtual Columns, which would mean you don't need to compute the value anymore (as it's in the index). You can also base a partition on a virtual column which could lead to an increase in performance.

Thanks,
Dimitri

Patrick Wolf said...

>You can create indexes on Virtual Columns,
>which would mean you don't need to compute
>the value anymore (as it's in the index).

Ok, in that case it would speed it up. But would be interesting how often the optimizer would really use this index if the column is not used in the where clause and he is for example doing a full table scan on the table. If he still would decide to read the index and do a merge or if he just skips it and calculates the value from scratch again...

Patrick

John Scott said...

Patrick,

Don't forget, the column doesn't necessarily need to be used in the where clause to have the index considered. It could be used due to a MIN/MAX/AVG (or any of the analytics) etc being used.

Virtual columns won't be suitable for every case, however they are yet another (very useful) tool we have at our disposal when we do need to do something like this.

John.

Patrick Wolf said...

Hi John,

I know that the CBO sometimes uses an index for columns in the SELECT clause to avoid reading them from the table, I was just not so happy with the statement that the new virtual columns will be faster than putting the same expression into a view. They are just faster under some conditions when the calculated values is persisted with an index and the CBO decides to use this index.

Without doubt is this new feature very interesting and in some conditions very useful. Just think about concatenating FIRST_NAME and LAST_NAME with a space between them and all the special conditions when one of the columns can be NULL. Having this rule one time with the table definition itself and being able to index it, so that a search is also fast is great! I'm just thinking how often have we written such stuff on and on again.

Looking forward to use it.
Patrick

Stew said...

Ho hum. I could create computed columns in a table 20 years ago on DEC's Rdb. I was amazed that Oracle 8i didn't use it when I moved to an Oracle shop! Especially since DEC sold Rdb to Oracle about 15 years ago! :-/

Stew said...

But I should have complimented Dimitri for publishing this post. Sorry for the social lapse. It's a great idea. Unfortunately for me, we won't be upgrading to 11 until next fall.