Partitioning tables is a great way to improve the performance and manageability of a table. Sometimes, non-partitioned tables are created that don’t contain much data, but over time the table becomes largely populated. Partitions can be used to manage the proliferation of data in the table. Common single-level partitioning strategies include list partitioning, range partitioning, and hash partitioning. Each partitioning strategy has its own advantages and disadvantages.
Viewing entries in
2011
Oracle has introduced a new SQL operator in 11g called PIVOT. The PIVOT operator allows query results to be presented in pivot form. Oracle stores data in a manner that is optimized for performance. Sometimes, this makes the data hard to read. Using the PIVOT operator allows the data to be displayed in a more readable and understandable format. The basic syntax for using the PIVOT operator is:
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.