Page 127
Figure 7.1.
Oracle dedicated server
architecture.
Page 128
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.
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.
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 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:
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 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 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.
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.