Oracle 11g Virtual Columns?
zigz...@yahoo.com
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.
hpuxrac
Well of course triggers are evil but views can also serve the same
purpose.
As I remember Tom Kyte was excited about the virtual columns so there
must be some benefits.
I guess one way is to have a virtual column and be able to build an
index on it.
Mladen Gogala
> I guess one way is to have a virtual column and be able to build an
> index on it.
It was called a "functional index" back in the 8i version.
--
Mladen Gogala
http://mgogala.freehostia.com
Shakespeare
Filling columns with triggers is slightly different, because Oracle does
not know
a) column values are derived from other column values in the same row
(and maybe some functions)
b) column values can ONLY change if the other values change. Unless you
write extra triggers to prohibit this, a column based on a trigger can
be changed directly by an update statement, VC's can not.
You don't save CPU, because Oracle will 'cache' the values, which is
made possible by the knowledge above.
Function Based Indexes are about the same as virtual columns, except you
can not really SELECT by name on these values. I think I remember Oracle
already 'secretly' stored FBI-values in a hidden column.
Another advantage over FBI's is you can use the column name in the where
clause, in stead of a function which had to be exactly the same one as
used in the index.
Shakespeare
Connor McDonald
Well, they're columns...so lots of goodies just in that, eg constraints
(foreign key, not null, unique, primary key), optimizer stats, fgac, etc
etc
--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_...@yahoo.com
"Semper in excremento, sole profundum qui variat."
------------------------------------------------------------
Robert Klemme
> On Sun, 18 Jan 2009 15:34:34 -0800, hpuxrac wrote:
>
>> I guess one way is to have a virtual column and be able to build an
>> index on it.
>
> It was called a "functional index" back in the 8i version.
With the added benefit that you can get the value without using the
expression which can make your life a lot simpler depending on the
expression. Basically you get an alias for the expression which makes
treating columns more uniform than before. Also, this opens some
migration paths (e.g. you have a column that is rendered redundant by a
design change, then you can simply replace it by a virtual column saving
the space).
Btw, MS SQL Server has this quite a long time already. It's called
"computed column" over there. SCNR :-)
Kind regards
robert
--
remember.guy do |as, often| as.you_can - without end
Serge Rielau
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 Lab
joel garry
> zigzag...@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:
The db-independent app software I work on has had this as an added on
feature for many years. In some ways it can be very confusing, as it
is often displayed the same as a real column, then you go to update it
and it won't let you. Conversely, they show up as regular varchar2
columns in (oracle) SQL so you can really shoot yourself in the foot
if you don't go through the app. I'm assuming they will eventually
coordinate internally with the Oracle and SS thingies, but I may be an
optimist.
From a functionality standpoint, they are extremely useful for coding
business logic into the data. For example, if you have attributes for
different accounting things like overhead and labor etc., in a costs
table, you might want a column that adds them up properly, rather than
relying on stored procedures or heaven forbid, application
programmers. Inventory type things like "quantity outstanding" can
get somewhat complex.
jg
--
@home.com is bogus.
http://www3.signonsandiego.com/stories/2009/jan/19/1m19video223959-they-came-brawl-rock-out-and-play/?uniontrib
Michael Austin
It was also called Computed By in DEC-now-Oracle/Rdb as far back as V3
or V4 (cira 1990-1993?) So the concept is not new - just finally made
it into RDBMS.
Way back when Oracle bought Rdb from DEC (December 1994), they
originally stated that there would be a merger of the two engines. A
lot of what you see in 8/9/10/11 are major functionality in Rdb slowly
making its way into RDBMS... examples: the method RMAN uses for doing
backups, partitioning, CBO, LMT, Bit-mapped indexes, Computed-by Columns
to name just a few... One of the biggest is going to be "versions" or
what ever they chose to call it..) which will allow for a RAC database
to be upgraded one node at a time. It appears that you can upgrade the
db from 11r1 to 11r2 on node1 while still running 11r1 on the other
nodes, then bring node1 back online, bounce db on node2 - it is now
running 11r2 and so on... Another fine Rdb feature for actually being
able to accomplish full rolling upgrades. (its about freakin time...)
When AVAILABILITY counts - THIS is why you want RAC. (and if done
correctly, you will also achieve the performance you want. BTW where
can I find one of those 120K jobs... I always get "that's the going
rate, but not this job" (to which I say - no thanks!) Also most
recruiters wouldn't know a qualified candidate if we smacked them up
side the head...
>
> Kind regards
>
> robert
>
>
Michael Austin
> zigz...@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.
>
> Well, they're columns...so lots of goodies just in that, eg constraints
> (foreign key, not null, unique, primary key), optimizer stats, fgac, etc
> etc
Although, you would need your head examined if you did use it for a
PK/FK. It makes no sense... as the values could potentially change.
zigz...@yahoo.com
> Connor McDonald wrote:
Thanks a lot to all of your comments.
Serge Rielau
columns that mark them as "generated" and "not updatable". Not sure
about JDBC, but ODBC has it for sure.
Jonathan Lewis
"Connor McDonald" <connor_...@yahoo.com> wrote in message
news:497470...@yahoo.com...
>
> Well, they're columns...so lots of goodies just in that, eg constraints
> (foreign key, not null, unique, primary key), optimizer stats, fgac, etc
> etc
Of which, it's the optimizer stats which are the really big benefit
over function-based indexes. If you write the query:
select from emp where sal + nvl(comm,0) > 10000
then the selectivity is 1%.
If you create a function based index on (sal + nvl(comm,0))
then you have a hidden column definition, but can generate
real statistics on the values - but you have to have the index
when (possibly) all you really want is the stats.
If you declare a virtual column, tot_remun say, as (sal + nvl(comm,0))
then you get the stats on tot_remun without taking up any space.
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Jonathan Lewis
"Jonathan Lewis" <jona...@jlcomp.demon.co.uk> wrote in message
news:C96dnZGzkNyL3uvU...@bt.com...
>
> "Connor McDonald" <connor_...@yahoo.com> wrote in message
> news:497470...@yahoo.com...
>>
>> Well, they're columns...so lots of goodies just in that, eg constraints
>> (foreign key, not null, unique, primary key), optimizer stats, fgac, etc
>> etc
>
> Of which, it's the optimizer stats which are the really big benefit
> over function-based indexes. If you write the query:
> select from emp where sal + nvl(comm,0) > 10000
> then the selectivity is 1%.
>
Correction:
That would be 5% for ">", the 1% would be for "="
iv...@hotmail.com
wrote:
> zigzag...@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.
>
> Well, they're columns...so lots of goodies just in that, eg constraints
> (foreign key, not null, unique, primary key), optimizer stats, fgac, etc
> etc
... and don't forget you can partition based on virtual columns -
could be very handy.
Igor
> --
> Connor McDonald
> Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
> Co-author: "Oracle Insight - Tales of the OakTable"
>
> web:http://www.oracledba.co.uk
> web:http://www.oaktable.net
> email: connor_mcdon...@yahoo.com
Mladen Gogala
> If you create a function based index on (sal + nvl(comm,0)) then you
> have a hidden column definition, but can generate real statistics on the
> values - but you have to have the index when (possibly) all you really
> want is the stats.
And, on 11G you can generate an extended stats, so that you can reap the
benefits even without the virtual column. Of course, generating an
extended stats does create a virtual hidden column for you....
Mladen Gogala
> BTW where
> can I find one of those 120K jobs...
NYC is the first place that comes to my mind.