Data Design Tuning - Oracle DBA

Oracle has a number of solutions to improve performance from a data design perspective. We will cover two techniques in this section: partitioned tables and materialized views.

Partitioned Tables

When tables grow very large, it becomes advantageous to use partitioned tables to divide the rows of a table into more manageable pieces based on the values of one or more columns. Because the data is subdivided into smaller pieces, it makes the DBA's job easier when doing backups; each partition of a partitioned table may be backed up or restored separately. One partition of a table can be in the process of being repaired, while the rest of the partitions are available to the database users, increasing the overall availability of the table.

Partitioned Table A table that stores its rows in smaller and more manageable pieces based on the values of one or more columns of the table.

Partitioned tables can have a performance benefit for database users. In many cases, a query may need to retrieve rows from only a subset of the partitions of a partitioned table. As a result, either index accesses or direct table accesses are reduced because the partition key automatically limits the partitions that need to be searched for the rows requested by the query.

There are four different ways to partition a table:

Range partitioningWith this type, the partition keys are in a range. For example, each partition can hold sales data by quarter or for a given month date range.

Hash partitioningWhen the sizes of each partition may vary widely or you do not know how much data will end up in a partition, hash partitioning is useful. This type of partitioning uses an algorithm on the partition key column to automatically balance the number of rows that end up in each partition.

List partitioningIf you know the values that will divide the data into partitions, but they are not necessarily sequential either numerically or alphabetically, list partitioning is useful. For example, it may be desirable to store all rows with state codes by region into separate partitions. Rows with state codes of WI, IL, IA, IN, and MN would reside in the MIDWEST partition.

Composite partitioningThis is a hybrid method that uses the range partition method for partitions and the hash method for subpartitions.

Creating a partitioned table is very similar to creating a nonpartitioned table, with the addition of the PARTITION BY clause:

Note that the SUBPARTITION BY HASH or LIST clause is valid only if the primary partitioning is BY RANGE. Also, specifying multiple columns in the PARTITION BY clause is valid only for HASH and RANGE partitioning, since LIST partitioning assigns rows to a partition based on the value of a single column.

The Order Entry department has asked Janice, the DBA, to look into improving the performance of the OE.ORDERS table. Response time against this table has been increasing, and the customer service representatives have reported that the web customers are waiting too long for their orders to be confirmed after clicking the Place My Order button on the checkout page.

Janice decides that since the ORDERS table now has hundreds of thousands of rows, she will partition the table by month. Partitioning by a date range makes sense, since rows from the ORDERS table are rarely accessed across more than one month. Janice retrieves the DDL for the original CREATE TABLE statement:

Janice creates a new version of the table for testing on the development server by adding partition-related options to the CREATE TABLE statement:

In the new table NEW_ORDERS, all orders before August 1, 2004, will end up in the first partition, FY2004_07. At the other end are partitions defined for the rest of 2004. It is assumed that for 2005, the DBA will create additional partitions on this table to accommodate orders placed in 2005. In the meantime, any orders with a date mistakenly keyed in as 2005 or later will be stored in the partition FY9999. If this partition were not created, any INSERT statement containing a date value outside the range of any partition would return an error.

Materialized Views

materialized view can help speed queries by storing data in a previously joined or summarized format. Unlike a traditional view, which stores only the query and runs that query every time the view is accessed, a materialized view stores the results of the query in addition to the SQL statements of the view itself. Because the materialized view already contains the results of the view's underlying query, using a materialized view can be as fast as accessing a single table.

Materialized View A view that stores the results of the query the view is based on, in addition to the SQL join statement of the view itself. Materialized views may be refreshed manually (on demand), on a regular basis, or when there is a change in the underlying tables on which that view is based.

But what if the underlying tables of the materialized view change? A materialized view can be refreshed either manually or automatically. If the refresh is automatic, it can occur as a scheduled event, such as every day at 2 a.m., or the materialized view can be refreshed automatically whenever the underlying tables of the view change. Materialized views can be refreshed manually by using the REFRESH procedure in the system package DBMS_MVIEW.

To further enhance the performance of a materialized view, it can be indexed and partitioned in the same way as any standard table.

Another key performance enhancement related to materialized views is the QUERY REWRITE feature. If a materialized view is created with the QUERY REWRITE option, any user SQL statements that use tables and columns similar to those found in the materialized view's query are automatically rewritten to use the materialized view directly. In other words, the database user does not need to know about the existence of the materialized view to take advantage of the pre-joined result of the materialized view.

The syntax for creating a materialized view is similar to that of the CREATE VIEW command from "Creating and Maintaining Database Objects":

At Scott's widget company, Janice has been helping some of the users in the HR department with their queries. She notices that they often use the view she created for them earlier with this statement:

In its present form, this view must perform the join every time it is accessed. Janice thinks that rewriting this view as a materialized view will not only improve the performance of the view but may also improve the performance of other queries that join the EMPLOYEES and DEPARTMENTS table using Oracle's QUERY REWRITE feature. Janice creates the materialized view as follows:

The new materialized view looks like any table or regular view:


The ENABLE QUERY REWRITE clause directs Oracle to use the materialized view instead of the EMPLOYEES and DEPARTMENTS table when a user writes a query similar to the one used to create the materialized view.

To manually refresh the view, Janice uses the DBMS_MVIEW package:

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle DBA Topics