Compaq ProLiant 6500 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 17

DSS Sizing and Tuning of Oracle8 for Windows NT on CompaqServers Page 15 • Table Partitioning - Oracle8 allows range partitioning, which uses a range of column values (partitioning key) to map rows or index entries to partitions. The partitioning key consists of an ordered list of up to sixteen columns and is based on the partitioning column values. You will need to determine which data values you wish to partition. The most common range partitioning is by date. A table cannot be partitioned if it is a cluster; contains LOBs, LONG or LONG RAW datatypes or objects; or is an index-organized table. • Index Partitioning - Is similar to table partitioning. There are two types of index partitioning: Local and Global. In a local partitioned index, all keys in a particular index partition refer only to rows stored in a single underlying table partition (i.e. All keys in a particular index partition map to all rows in a single underlying table partition). In a global partitioned index, the keys in a particular index partition may refer to rows stored in more than one underlying table partition (i.e. All indexes in one partition may map to rows in three different partitions.) An index cannot be partitioned if it is a cluster index, defined on a clustered table, or a bitmap index on a partitioned table. Oracle8 supports prefixed and non-prefixed local indexes and prefixed global indexes. A prefixed index is partitioned on a left prefix of the index columns, while a non-prefixed index is partitioned on something other than a left prefix of the index columns. For DSS systems, local non-prefixed indexes can improve performance because many index partitions can be scanned in parallel by range queries on the index key. While these indexes are easier to manager, there is a potential of having to search every partitioned index to find a single element. • Partitions - Each partition should be assigned to its own tablespace, which allows for a single partition to be assigned to a single physical disk. This feature enables the developer to place data on specific drives and, ultimately, balance the I/O across all disks. Since DSS databases are read-intensive, partitioning a table will allow for a faster read time. The number of partitions you have will, in part, determine the number of disks you will need. For example: in figure 9 above, each of the partitions created is assigned to its own tablespace (i.e. Partition 1 on all disks is reserved for partsupp. Partition 2 on all disks is reserved for orders. Thus partition 1 on disks 1-40 are assigned to the PS_01, PS_02, ... , PS_40 tablespaces. Partition 2 on disks 1-40 are assigned to ORD_01, ORD_02, ... , ORD_04 tablespaces) • Storage parameters - All partitions of a table or index have the same logical attributes, but their physical attributes can be different. Assigned to its own tablespace, each partition can utilize different physical attributes (PCTFREE, PCTUSED, INITRANS, MAXTRANS) and storage parameters (INITIAL, NEXT, MINEXTENTS, MAXEXTENTS, FREELIST, FREELIST GROUPS). PCTFREE should be as close to zero as possible if no/little changes are done to data (i.e. updates). Inserts are okay with small PCTFREE value. • Physical Device Selection - The frequency of partition used also impacts the selection of drives. More frequently used partitions may be moved to a faster device (i.e. moving from a Compaq 4.3-Gigabyte Ultra to a Compaq 9.1-Gigabyte drive), allowing for a decrease in the response time. For more information on partitioning, please refer to the Oracle8 Server Concepts Manual or the Oracle8 Server Application Developer's Guide. Parallelism Parallelism offers performance improvement when full table scans, large joins, partitioning and sorting and other aggregate functions are used. Parallelism depends primarily on the number of processes, the number of CPU's, memory and the I/O layout. © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398