Re: Oracle 11g Virtual Columns?
From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Mon, 19 Jan 2009 14:56:38 -0500
Message-ID: <6tk47nFb8j9uU1_at_mid.individual.net>
zigzagdna_at_yahoo.com wrote:
> I have read various articles on virtual columns? I still do not
> understand their advantages other than they save some disk space, One
> can put logic of virtual columns in a trigger which will save
> information in a real column. Real column will need some additional
> disk space, but it will save some cpu time when one is doing slelects
> on “virtual” columns.
Sometimes the path how these features get born can be quite odd. The first to add virtual columns was SQL Server 2000. At the time when we did generated columns I puzzled a lot on what MS motivation might have been for virtual columns. Here are some thoughts I had: 1. In SS you could (can?) not insert into a view unless all its columns are updatable. So you cannot define a view where you provide a convenience expression and insert data through it. Say: CREATE VIEW circle(radius, circumference) AS SELECT radius, 2*PI*radius FROM circletable
Date: Mon, 19 Jan 2009 14:56:38 -0500
Message-ID: <6tk47nFb8j9uU1_at_mid.individual.net>
zigzagdna_at_yahoo.com wrote:
> I have read various articles on virtual columns? I still do not
> understand their advantages other than they save some disk space, One
> can put logic of virtual columns in a trigger which will save
> information in a real column. Real column will need some additional
> disk space, but it will save some cpu time when one is doing slelects
> on “virtual” columns.
Sometimes the path how these features get born can be quite odd. The first to add virtual columns was SQL Server 2000. At the time when we did generated columns I puzzled a lot on what MS motivation might have been for virtual columns. Here are some thoughts I had: 1. In SS you could (can?) not insert into a view unless all its columns are updatable. So you cannot define a view where you provide a convenience expression and insert data through it. Say: CREATE VIEW circle(radius, circumference) AS SELECT radius, 2*PI*radius FROM circletable
So providing a virtual column circumference on the table may have been a
workaround.
2. A view is a much heavier gun than a virtual column. for a given query
the text needs to be parsed, added to the parse tree and ultimately
optimized. A generated column has no compile overhead unless it's used.
Add to that management overhead of maintaining the view, forcing users
to use it rather than the table etc...
3. It's easier to maintain statistics on these expressions.
As for the advantages of materialized generated columns it they are pretty straight forward and I described them here: http://www.freepatentsonline.com/6636846.html
Cheers
Serge
-- Serge Rielau DB2 Solutions Development IBM Toronto LabReceived on Mon Jan 19 2009 - 13:56:38 CST