Oracle8 Features for Large Databases

Oracle8 has a lot of features that can be used to support the tremendous demands placed by high-end applications, and it provides a viable alternative to the mainframes and non-relational databases that are difficult to manage.

Table Partitioning

Very large databases usually contain several huge tables and indexes rather than a large number of small objects. Partitioning, a very important Oracle8 feature, allows developers and DBAs to break these huge tables into smaller, more manageable pieces. By using partitioning, you can create a single large table or index across several tablespaces and, at the same time, control space allocation for each piece of the object. Another advantage of partitioning is the ability to perform maintenance on a part of the table or index without affecting the rest of the table or index. As a result, the overall management and maintenance of data becomes more flexible because it is at the partition level—a much finer-grained level—while queries can still be performed at the table level.

Oracle7 allows a type of table partitioning by creating a set of tables and placing them on different disks or different tablespaces and having different storage clauses. A UNION ALL operation can be performed to obtain a larger table. The problem with this approach is that the application needs to be aware of the underlying structure. The application has to know which underlying table to modify. Any addition or deletion of tables affects the overall view, and it is not possible to work with just part of the view if all the underlying tables are not available. Earlier versions of Oracle prevented the insertions, updates, and deletions through views, which makes this approach not very useful.

Using Oracle8, partitioned tables can be created dynamically. Oracle8 can determine the underlying partition, which needs to be modified when a table is modified. It should be understood that applications do not need to be modified in order to run against the newly partitioned tables. In most cases you can perform partition-level operations in parallel with the same operations on other partitions of the same table, thereby improving the overall performance.

DBAs face a lot of challenges on a daily basis, such as the following:

•  Defining and creating large tablespaces

•  Balancing I/O to minimize disk contention

•  Pinging (in Oracle Parallel Server environments)

Use of Oracle8 partitioning can help in all these areas by providing the capabilities discussed in the following sections.

High Availability

Oracle8 provides high availability by allowing partitions to be independent entities—partition autonomy. This reduces the amount and duration of scheduled downtime by allowing the downtime operations to be performed while the database is open and in use. Part of the table can be taken offline and the rest of the table can be accessed.

Improved Performance

Partitioning improves performance by using a divide-and-conquer paradigm and parallelism. Most operations performed in Oracle7 at the table level can now be performed in Oracle8 at the partition level. It is important to understand that the level of parallelism and the resulting performance gains are strongly affected by the database design.

---

Note:  
The partitioning strategy used in Oracle7 may not be sufficient to make optimal use of Oracle8 features.

---

Easy Management of Data

Data management involves backup, recovery, moving data in and out of the database, and data reorganization in order to take care of fragmentation and performance problems. In Oracle7 the unit of data management was the table, while in Oracle8 the unit of data management is a partition. As the table grows in size, the partition does not necessarily grow in size; instead, the number of partitions increases. Parallel data loads can be performed resulting in faster data loads. Partitions also allow a part of the table to be relocated to another drive.

---

Note:  
You can have up to 64,000 partitions per table.

---

The following guidelines can be used for determining when partitioning of tables should be done:

•  Tables used to perform parallel data manipulation (PDML) should be partitioned.

•  Tables that need to be accessed using parallel index should be partitioned.

•  Tables containing historical data should be partitioned because you may need to take part of these tables offline.

•  If you are migrating from Oracle7 and then identifying views, which are obtained from UNIONs, these are great candidates for partitioned tables.

•  Large tables greater than 2GB should be partitioned.

•  A table should be partitioned if certain portions of the table are modified and the rest are read only.

•  Tables in a parallel server that are modified by multiple instances should be partitioned to minimize pinging, resulting in improved performance.

The following tables cannot be partitioned:

•  Tables that are part of a cluster

•  Tables containing LOBs, LONG, LONG RAW, or object types

•  Index-organized tables

There are several challenges associated with using partitioned tables:

•  The required tablespaces should be created before creating the partitioned tables.

•  It is very important to determine a backup strategy that will have minimum impact on active applications because, if a partition is taken offline and you have queries that try to access data in that partition, the queries will fail.

•  The size of tablespaces should be determined to support the size and growth of the partition that will be using the tablespace.

•  The layout of partitioned tables and tablespaces should be done with the space and performance impact on the overall system in mind.