Data Design Tuning

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 into 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 partitioning With 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 partitioning When 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 partitioning If 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 partitioning This 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:

CREATE TABLE ... PARTITION BY {RANGE | LIST | HASH} (column1, column2, ...)      [SUBPARTITION BY {HASH | LIST} (column1, column2, ...)         SUBPARTITIONS n]

Note that the SUBPARTITION BY HASH or LIST clause is only valid if the primary partitioning is BY RANGE. Also, specifying multiple columns in the PARTITION BY clause is only valid 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:

create table orders (   order_id      number (12)   not null,   order_date    date          constraint order_date_nn not null,   order_mode    varchar2 (8),   customer_id   number (6)          constraint order_customer_id_nn not null,   order_status  number (2),   order_total   number (8,2),   sales_rep_id  number (6),   promotion_id  number (6),    constraint order_mode_lov          check (order_mode in (‘direct’,’online’)) ,    constraint order_total_min          check (order_total >= 0),    constraint order_pk primary key ( order_id ) ) ;

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

create table new_orders (   order_id      number (12)   not null,   order_date    date          constraint new_order_date_nn not null,   order_mode    varchar2 (8),   customer_id   number (6)          constraint new_order_customer_id_nn not null,   order_status  number (2),   order_total   number (8,2),   sales_rep_id  number (6),   promotion_id  number (6),    constraint new_order_mode_lov          check (order_mode in (‘direct’,’online’)) ,    constraint new_order_total_min          check (order_total >= 0),    constraint new_order_pk primary key ( order_id ) )  partition by range (order_date)  (partition FY2002_07 values less than  (to_date(‘08012002’,’MMDDYYYY’)),  partition FY2002_08 values less than  (to_date(‘09012002’,’MMDDYYYY’)),  partition FY2002_09 values less than  (to_date(‘10012002’,’MMDDYYYY’)),  partition FY2002_10 values less than  (to_date(‘11012002’,’MMDDYYYY’)),  partition FY2002_11 values less than  (to_date(‘12012002’,’MMDDYYYY’)),  partition FY2002_12 values less than  (to_date(‘01012003’,’MMDDYYYY’)),  partition FY9999 values less than (maxvalue)  ); 

In the new table NEW_ORDERS, all orders before August 1, 2002, will end up in the first partition, FY2002_07. At the other end are partitions defined for the rest of 2002. It is assumed that for 2003, the DBA will create additional partitions on this table to accommodate orders placed in 2003. In the meantime, any orders with a date mistakenly keyed in as 2003 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

A 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 Chapter 10, "Creating and Maintaining Database Objects":

CREATE MATERIALIZED VIEW materialized_view_name    [ENABLE QUERY REWRITE] AS subquery;

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:

create view       emp_dept(emp_id, lname, fname, dept_id, dname) as select employee_id, last_name, first_name,       department_id, department_name from employees join departments using(department_id);

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 Oracle9i’s QUERY REWRITE feature. Janice creates the materialized view as follows:

create materialized view emp_dept    enable query rewrite as select employee_id, last_name, first_name,       department_id, department_name from employees join departments using(department_id); Materialized view created.

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

describe emp_dept  Name                         Null?    Type  ---------------------------- -------- --------------  EMP_ID                       NOT NULL NUMBER(6)  LNAME                        NOT NULL VARCHAR2(25)  FNAME                                 VARCHAR2(20)  DEPT_ID                      NOT NULL NUMBER(4)  DNAME                        NOT NULL VARCHAR2(30)

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:

exec dbms_mview.refresh(‘emp_dept’); PL/SQL procedure successfully completed.