In one of his blogs on Open World Mark Rittman lists some of the features that Oracle may (subject to the usual comments from Oracle that this is a loose statement of intention, not a promise) include in 11g.
One of the small but, to my mind, very useful features that Mark mentions is this:
- Virtual columns, columns that are actually functions (“create table t1 (c1 number, c2 number, c3 as (c1+c2) virtual”), and similarly, virtual indexes that are based on functions
One of the commonest root causes of errors in the calculations made by the Cost Based Optimizer is the use of incorrect data types (e.g. dates stored as numbers). In the past I have occasionally been able to work around such problems by creating “function-based indexes” – which I prefer to call “indexes involving virtual columns”.
But if you don’t really need (or want) to create new indexes it’s a nasty maintenance overhead simply to get improved statistics so, as I said in my book (Cost Based Oracle Fundamentals) Chapter 6, Page 133:
- It would be nice if Oracle Corp added the option for unindexed virtual columns, so we could have the benefit of correct statistics for commonly used expressions without the need to create the index.
With a little luck the 11g implementation will allow us to add virtual columns to existing 3rd party tables in a way that doesn’t affect any bad code in the application but allows predicates involving functions to be rewritten by the optimizer as predicates against virtual columns.