A great feature introduced in the Oracle 11g database is the SQL query ‘Result Cache’. A result cache is an area of memory that stores the results of a database query or query block for reuse. The cached rows are shared across SQL statements and sessions unless they become stale. The caching mechanism is efficient and easy to use, and it relieves you of designing and developing your own legacy cache procedures and cache-management policies.
Viewing entries tagged
Oracle 11g
This article will be discussing a new feature of Oracle 11g, which is invisible indexes. Invisible indexes simply means indexes that are not visible to optimizer, meaning the optimizer will not consider such index while preparing query execution plans. It is possible to force the optimizer to consider invisible indexes, but let’s cover the benefits of invisible indexes first:
Benefits
- Test the benefit of an index: Every DML statement will have an extra cost to maintain an index. To ensure that an index is actually useful for our system before dropping it, we can test it by simply making it invisible. The index will not be considered by optimizer anymore. If SQL statements that were using this index perform acceptably, then we should drop the index.
- Introduce New Index: We can introduce a new index without effecting execution plans of existing sql statements.
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.
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: