Virtual columns are new in Oracle 11g and allow a column to be dynamically filled with data based off an expression or function. Virtual columns do not store data on disk. Instead, the data is generated at run time. Virtual columns can be useful when there is an expression that is used on a table often. Instead of having to code the expression in the SELECT statement every time, the expression can be stored in a virtual column.
In data warehouse environments, it is common to use materialized views to speed up the performance of commonly used queries. A materialized view is simply a pre-built query that stores the results of the query in a table. With the query rewrite capability enabled, Oracle can automatically make use of materialized views without the user even knowing.Let’s take a look at a simple order table for a generic store along with a denormalized table for dates in the order system and some sample data:
In the Oracle 10g release, Automatic Storage Management (ASM) was introduced, which provided a simplified way for DBAs to manage the storage of Oracle-related files. However, ASM did make maintenance of the files more complicated as the files could not be directly accessed using system commands. For the most part, this was not a problem, but it does hinder the use of another Oracle feature: transportable tablespaces.
Increasingly architects and DBAs are spending more time working with aggregates. As computational power increases, so does the clients’ desire to analyze their data in a multitude of ways. Even though we have to ability to track every event that occurs, challenges still exist in presenting that data to users in an efficient method.
A convenient new feature introduced in Oracle 11g is interval-based partitioning. One simply has to create a single starting partition and specify the interval for partitions above that transition point. As data is added to the table, Oracle will automatically create new partitions as needed. For example, consider the following table: