Introduction
There are two basic sets of decisions you must make to plan the upgrade:
-
Whether and when to convert to a partitioned Responses table
-
If you are not partitioning, when and how to upgrade the index structure of the Responses table
These two decisions are inter-related — the conversion to partitioning subsumes the index upgrade. If you decide that you will be partitioning shortly after upgrading, you might want to skip the separate index upgrade. Conversely, if you are postponing partitioning you might want to do the separate index conversion soon after upgrade. If you are not converting to partitioning, you need to consider the path to index upgrade (see Figure 9-1).
If you have a fresh installation of Oracle Clinical, you do not need the index upgrade. You do need to decide about partitioning.
For more information , see:
Parent topic: Partitioning and Indexing
Timing Considerations and Deferral of Index or Partition Upgrade
The index upgrade and partitioning both require impact analysis in advance and both can take a significant amount of time to implement. Assuming that the impact analysis is done prior to upgrading and the upgrade path chosen, the primary consideration for when to upgrade is the elapsed time needed to perform the actual upgrades. While either could be done at the same time as the upgrade, deferring the index or partition upgrade to a separate time might reduce the risk and time pressure. The index upgrade is the least time-consuming and is easily performed overnight even on a large database. Partitioning is more time-consuming and, while it can easily be accomplished over a weekend on even the largest Oracle Clinical database, it needs more careful advanced planning.
If you decide to defer both partitioning and the index upgrade, you can force Oracle Clinical to continue to create validation and derivation procedures and data extract views that are optimized for the pre-4.0-style index structure (see Enforcing Pre-Version 4.0 Optimization).
The upgrade for Responsest should be done at the same time as either partitioning or the Responses table index upgrade.
See Upgrading Indexes for more information. This upgrade can be done at any time that is convenient.
Figure 9-1 Partitioning and Indexing Decision Paths
For more information , see:
Parent topic: Introduction
Enforcing Pre-Version 4.0 Optimization
If you decide to postpone both the partitioning and indexing upgrades, you can force Oracle Clinical to continue to create validation and derivation procedures and data extract views that are optimized for the pre-4.0-style index structure.
You do this by inserting a record in the local OCL_STATE reference codelist with the short value of USE RESP_DCMQG and a long value of YES.
Later, when you are ready to convert to the new index scheme or partitioning, change the long value to NO before you regenerate views and procedures. The absence of an entry is equivalent to NO.
About Partitioning
Partitioning is an Oracle database capability that allows you to divide a single Oracle table into separate physical partitions, each with its own storage characteristics. The indexes on a table can be partitioned as well. When a table is partitioned, each partition functions physically like a separate table while the table, as a whole, can still be treated as a single table for purposes of data access through SQL. Partitions can be managed like independent tables. They can be reorganized, imported, exported, taken off line and even dropped.
This section provides an overview of partitioning as it has been used for the Responses table in Oracle Clinical. For complete information on partitioning, see the Oracle Database documentation.
See the following:
How Has the Responses Table Been Partitioned?
The response table is range partitioned on clinical_study_id. This means that the data is placed into partitions based upon ranges of the internal clinical_study_id identifier. The two indexes on the Responses table are equipartitioned with the Responses table on clinical_study_id. This means they share the same partitioning scheme as defined for the Responses table. This equipartitioning guarantees partition independence, that is, operations on one partition or its indexes do not affect any other partition or its indexes. Equipartitioning also means that the index partitions are automatically maintained during most partition maintenance operations on the Response table partitions. It is important to note that the Response table partitions and the index partitions have separate storage specifications, so it is still possible to place the index partitions on separate tablespace/physical devices from the table partitions.
Parent topic: About Partitioning
Why Partition?
In Oracle Clinical Version 3.1, the Responses table together with its indexes is 20 times larger than the next largest table, Received DCMs. Partitioning the Responses table and reorganizing the indexes accomplish a number of goals:
Improves Database and System Management
Partitioning improves database and system management in several ways. First, by dividing the Responses table into many physical segments, you can manage the physical growth and structure of the table at a finer level of granularity. Individual partition segments can be rebuilt independently to consolidate space and improve performance. Partition segments can be placed on separate tablespaces on separate physical devices to improve performance; for example, by moving studies that will be used for intensive reporting onto separate disks from studies with active entry. Studies that are no longer active can be periodically moved to a read-only tablespace on a physical device that need only be backed up after the periodic maintenance and not as part of nightly backups. Similarly, if table export is used as a backup mechanism, only active partitions need to be exported.
Removes Single Point of Failure
Prior to partitioning, a single bad index block would require rebuilding the entire index and a bad data block might require rebuilding the entire responses table. Since all of the partitioned indexes are local indexes, with partitioning, a bad index only affects the particular partition and a bad data block only requires the rebuild of that particular partition; the remaining partitions can remain in use while the one with problems is restored.
Improves Performance
Both transactional and retrieval performance are improved by partitioning. Transactional performance is improved in two ways: by reducing contention for the data associated with a particular study and reducing transaction overhead. Contention is reduced by distributing inserts, updates, and deletes over different data blocks for each study and by allowing data blocks that are actively being accessed for reporting to be separated from those that are actively being modified by data entry.
Retrieval performance is improved in two ways as well: by physically grouping study data and by allowing physical reorganization on a study basis. By allowing data for each study to be stored their own data and index blocks instead of interspersed with data from many other studies, retrieval by study performs significantly less disk I/O and, in fact, a study can usually be buffered entirely in the SGA. This can produce dramatically improved extract view performance. In addition, when a study is about to be heavily accessed for reporting, the particular partition can be reorganized to consolidate space used by the index and data blocks, thus reducing I/O, and even to place the partition on a separate, perhaps faster, physical device to optimize access.
Reduces Space Requirements
The most dramatic reduction in space requirements comes from the re-optimization of Oracle Clinical to do away with the need for three of the indexes on the Responses table. Combined with the Oracle database leading key compression on one of the remaining indexes, this drops the space required by the Responses table and its indexes by over 50%. This benefit can be realized whether or not you partition the Responses table. In addition, the ability to rebuild partitions individually means that space can be recovered from partitions that are no longer being modified by rebuilding them with reduced free space. Since freshly rebuilt indexes are frequently two-thirds the size of an actively growing index, and the free space requirement can be reduced by 10%, the net improvement over time could be an additional 30-40% reduction in space requirements.
Parent topic: About Partitioning