0127-0130

Previous Table of Contents Next

Page 127

Figure 7.1.
Oracle dedicated server
architecture.

• Distributed option: Provides the mechanisms for advanced symmetric replication, which allows data to propagate among separate instances (and physical databases) through snapshots and deferred transactions. Replication activities are scheduled in the job queue and run in the background. In addition to lock processes, the Distributed option requires at least one snapshot refresh process (SNP) background process to execute queued jobs. You can use additional SNP processes to improve performance. The Distributed option also requires a recoverer background process to complete distributed transactions that failed because of network or instance failures.
• Parallel Query option: Can dramatically improve performance of complex Structured Query Language (SQL) and Data Manipulation Language (DML) statements, particularly when Oracle is running on a server with multiple CPUs. You also can use the

Page 128

Parallel Query option to improve the performance of Data Definition Language (DDL) operations, such as index creation. This option allows multiple server processes to work together on a single request. A SQL query containing a UNION, for example, could use a separate query server process to gather each of the partial results concurrently. The server process executing the statement (whether a dedicated or a shared multihreaded server, or MTS, process) becomes the query coordinator . The process then combines the results of parallel operations and returns the final result of the UNION to the user process. You can fine-tune parallel query configurations for a specific application through a number of initialization parameters that affect the degree of parallelism, the number of query server processes, and so on.

This brief overview of the Oracle architecture was provided as a review and a frame of reference. The following section presents the new features of Oracle8 and assumes that you are familiar with Oracle7.

New Features in Oracle8

The new object-relational features of Oracle8 have been highly publicized, but a number of significant enhancements to the core RBMS deserve attention as well. This section presents some of the most significant Oracle8 enhancements in terms of physical storage improvements, SQL and PL/SQL extensions (including object-oriented features), and optimizer improvements.

Physical Storage Improvements

Oracle8 offers a number of new physical storage options you can use to optimize performance for specific types of applications, including table partitioning, index partitioning, and several new index types.

Table Partitioning

Table partitioning divides table data between two or more tablespaces and physical data files, ideally on separate disks. You can use table partitioning to improve transaction throughput and certain types of queries for large tables. Currently, you can partition a table only horizontally, based on the value of one or more columns .

Suppose that a national sales organization wants to partition historical sales data by date and regional sales office. Listing 7.1 shows what the DDL to create the partitioned table might look like.

Listing 7.1. Creating a partitioned table.

 CREATE TABLE region_sales_hist (invoice_date DATE,     region_id      NUMBER(1),     invoice_num NUMBER(10),     customer_id  NUMBER(10), 

Page 129

 total_amt       NUMBER(10, 2),     status_code    NUMBER(1)) PARTITION BY RANGE (invoice_date, region_id)     (PARTITION inv96r1 VALUES LESS THAN (`01-JAN-96', 2)         TABLESPACE ts_inv96r1,      PARTITION inv96r2 VALUES LESS THAN (`01-JAN-96', 3)         TABLESPACE ts_inv96r2,      PARTITION inv96r3 VALUES LESS THAN (`01-JAN-96', MAXVALUE)         TABLESPACE ts_inv96r3,      PARTITION inv97r1 VALUES LESS THAN (`01-JAN-97', 2)         TABLESPACE ts_inv97r1,      PARTITION inv97r2 VALUES LESS THAN (`01-JAN-97', 3)         TABLESPACE ts_inv97r2,      PARTITION inv97r3 VALUES LESS THAN (`01-JAN-97', MAXVALUE)         TABLESPACE ts_inv97r3); 

Note that because MAXVALUE was never specified in the VALUES LESS THAN clause for invoice date, an implicit check constraint on invoice_date < `01-JAN-97' will be enforced. You can add a partition by using the ALTER TABLE command to accommodate additional years .

You can define partitions in separate tablespaces, each of which may have different default storage parameters. If no storage specification is provided for the table, each partition uses the default storage parameters of its underlying tablespace. This enables you to size partitions independently. If the data files for each tablespace are on separate physical devices, this partitioning strategy can maximize transaction throughput and improve performance for queries based on data and region code. DML statements and SQL queries can reference the table as a logical unit, or they can operate against a specific partition. For example, the following query selects all rows from a specific partition of the region_sales_hist table:

 SELECT * FROM region_sales_hist PARTITION (inv96r1); 

Partitioning also can improve availability and simplify maintenance. In Listing 7.1, the 1997 data could be loaded using SQL*Loader without affecting the availability of 1996 data. Partitioning can provide increased availability, depending on how the data files are organized. Disk striping, in particular, can affect availability. Using Listing 7.1 as an example, suppose that each tablespace contains a single data file that is striped across two disks, using 12 disks total. In this scenario, the failure of a single disk would affect only one partition. On the other hand, if each data file is striped across all 12 disks, the failure of a single disk affects all partitions. This requires a design decision involving a common trade-off: performance versus availability.

Certain restrictions apply to table partitioning:

• A table that is part of a cluster cannot be partitioned.
• A table can be partitioned based on ranges of column values only.
• Attributes of a partitioned table cannot include LONG, LONG RAW, or any of the LOB datatypes.
• Bitmap indexes cannot be defined on partitioned tables.

Page 130

Index partitioning is related closely to table partitioning. Three distinct types of partitioned indexes are supported by Oracle8: local prefixed, local non-prefixed, and global prefixed.

Local Prefixed Partitions

Local prefixed indexes are partitioned on the same columns as the underlying partitioned table, and the partition columns must be a left prefix of the index. Using Listing 7.1 as an example, the index defined here is a local prefixed index:

 CREATE INDEX ix_region_sales_h ON region_sales_hist (invoice_date, region_id) LOCAL; 

Here, LOCAL is required to ensure that Oracle automatically generates the partitions based on the table definition and maintains index partitioning as the table is repartitioned. Local prefixed indexes provide the same performance, maintenance, and availability benefits as partitioned tables. The limitations inherent in partitioned tables also are present in local prefixed partitions.

Local Non-Prefixed Partitions

Local non-prefixed partitions use the same partitioning columns and ranges as the underlying table, but the partition columns are not in the leftmost positions of the index. For example, the following index based on Listing 7.1 uses a local non-prefixed partition:

 CREATE INDEX ix2_region_sales_h ON region_sales_hist (customer_id) LOCAL; 

Local non-prefixed indexes are useful particularly in decision-support systems (DSS). In many cases, a date range is the best means of partitioning historical DSS data, but fast access is required for queries based on other columns. Local non-prefixed indexes can be slower to search than local prefixed indexes, however, because the query cannot always be isolated to a specific partition or partitions in advance.

All indexes defined as LOCAL are not maintained directly; instead, they are maintained through operations on the underlying table. For example, consider this statement:

 ALTER TABLE region_sales_hist DROP PARTITION inv96r1; 

This statement drops the partition from the table and corresponding partitions from all LOCAL partitioned indexes based on the table.

Global Prefixed Indexes

Oracle also supports global prefixed indexes, which contain the partition range column(s) in the leftmost positions of the index but do not use the same partitioning range as the underlying table. Global prefixes can be used to spread index data over additional disks not used by table partitions to improve performance. The partitions of global indexes must be maintained manually, however.

Previous Table of Contents Next