Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Braga.

Asked: August 30, 2016 - 11:57 am UTC

Last updated: September 01, 2016 - 3:23 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

Hi team,

I have a quick question for you today.

I am creating a large (in terms of rows) table that will be queried by month, mainly for reporting purposes (each row represents a day and has a date column).

Usually, when I have to design a table similar to this one, I create an extra column - Month - and index the column in order to speed up the reporting query.

Thanks to Ask Tom team, I am now aware of virtual columns. Using this Oracle feature I can instead create a virtual column "Month", computed from the "Date" column, and index it. This would save some space regarding the data stored.

However, I realize that the space benefit is the only benefit I am aware of. And probably there are also some disadvantages of using this approach.

Could you please clarify what are the advantages and disadvantages of choosing between this 2 approaches (extra physical column + index VS virtual column + index)? Would you recommend another approach?

Thank you for your time and help

and Chris said...

Well in the advantage camp:

- The value is always in sync with the source data. i.e. your virtual month can never be different to the month in the date column. This is possible with physical cols (unless you add constraints).
- They consume minimal space
- The CBO can gather stats on them just like a normal column

On the downside:

- They are calculated at runtime. This could slow your queries down. Especially if its based on a PL/SQL function
- There are a few restrictions:

- You can create virtual columns only in relational heap tables. Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.

- The column_expression in the AS clause has the following restrictions:
- It cannot refer to another virtual column by name.
- Any columns referenced in column_expression must be defined on the same table.
- It can refer to a deterministic user-defined function, but if it does, then you cannot use the virtual column as a partitioning key column.
- The output of column_expression must be a scalar value.

- The virtual column cannot be an Oracle supplied data type, a user-defined type, or LOB or LONG RAW.

- You cannot specify a call to a PL/SQL function in the defining expression for a virtual column that you want to use as a partitioning column.

http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#BABIIGBD

But unless you're affected by the restrictions above or performance is critical I'd recommend using them for calculated values!

If you want more details, Adrian Billington has a detailed discussion of them at:

http://www.oracle-developer.net/display.php?id=510

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Surprise virtual columns (thanks to extended stats)

Duke Ganote, August 30, 2016 - 8:44 pm UTC

Also note that virtual columns may "just appear" if you use extended statistics. Oracle may create a virtual column in order to calculate statistics on a group of columns.

This should improve performance, but may surprise you if you attempt to rename columns.
https://blogs.oracle.com/sql/entry/ora_54033_and_the_hidden

Perfect!

Braga Simões, August 31, 2016 - 4:40 pm UTC

Thanks! That was exactly, as always, the type of guidance I needed!

Performance is more important then space in this project. But I will assess anyway if using virtual columns could be a approach to use. Now I am aimed with the overall info :)
Chris Saxon
September 01, 2016 - 3:23 am UTC


Braga Simões, August 31, 2016 - 4:46 pm UTC

Thanks for your input Duke!

Very interesting!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.