Methods of Storing Data


There are several different methods of storing data in tables in the database. These methods are covered in this section. From the user's perspective, however, all appear the same, and all the different storage options provide the same functionality. From the user's perspective, SELECT, INSERT, UPDATE, or DELETE all appear to function in the same way.

Regular Tables

A regular table, or more commonly just a table, is the most commonly used method of storing user data in a typical Oracle database. It is the default method used and is also the main focus for this chapter. Database administrators have little control over how the rows are distributed in a regular table, and users have even less. Rows are stored randomly in the table, depending on the activity on the table.

Partitioned Tables

Partitioned tables have one or more partitions, each of which stores rows that have been placed into a particular partition based on the partitioning criteria specified. This is accomplished either by range partitioning, hash partitioning, composite partitioning, or list partitioning. Each partition is a segment, and each can be located in either the same tablespace or a different tablespace from the others.

Range Partitioning

Range partitioning maps data to the appropriate individual partitions based on ranges of partition key values (the values for the column on which you are going to base the partitioning) that you establish for each partition. Range is the most common type of partitioning used, and it is often used when partitioning by date range. For example, you might want to partition claim data by month into a monthly partitioning scheme or by year, each year into its own partition.

When deciding whether to use range partitioning, consider the following general rules:

  • Each partition has a VALUES LESS THAN clause. This clause specifies the noninclusive upper bound for the partitions. Any values for the partitioned column that are less than the clause specified value are directed into one partition. Those that are equal to or higher than this literal value are added to the next higher partition.

  • All partitions, except the first and lowest partition, have an implicit lower bound specified by the VALUES LESS THAN clause on the next lower partition.

  • A MAXVALUE literal, which represents a virtual infinite value that will always sort higher than any other possible non-null value, can be defined for the highest partition.

Hash Partitioning

Hash partitioning is the method that enables the easy partitioning of data that does not lend itself to range or list partitioning. Either because of the distribution or the type of data involved, it just doesn't seem to fit with the other partitioning concepts. This partitioning is accomplished with simple syntax that is also easy to implement. Hash partitioning is a better choice than range partitioning under the following conditions:

  • You do not know beforehand how much data will end up mapping into a given range.

  • The sizes of the resulting range partitions would differ substantially from each other or would be difficult to balance manually. This would mean that queries retrieving data from one partition (a larger partition) would likely run longer than those against another, smaller partition.

List Partitioning

List partitioning, new in Oracle 9i, enables you to explicitly control how rows map to partitions. You accomplish this controlled mapping by specifying a customized list of discrete values for values of the partitioning key. This is different from range partitioning, where a range of values is defined for a partition, and from hash partitioning, where a hash function controls the row-to-partition mapping. The primary advantage of list partitioning is that you can group and organize unordered and apparently unrelated sets of data in a more intuitive and natural way.

This means that you can group data by locationfor example, all Eastern European data in one partition and all Western European data in another partitionor group state-related data by area of the United States. This would be particularly effective if queries on the data were typically concerned with finding answers by regions (all sales for the Midwest could be found in one partition; all claims for workers in London would be in their own partition).

Composite Partitioning

Composite partitioning partitions data first using the range method. This level of partitioning places the data within its primary partition. Within each partition, the data is further subpartitioned using either the hash or list method.

Composite range-hash partitioning provides the improved manageability of range partitioning along with the more well distributed data placement (fewer hotspots for data) advantages of hash partitioning.

Composite range-list partitioning provides the manageability of range partitioning along with the finer grain, more explicit control of list partitioning for the subpartitions.

Partitioning is particularly useful for large tables that can be queried or have their data manipulated using several different processes concurrently. Special commands are used for the maintenance and manipulation of partitioned tables.

Index-Organized Tables

An index-organized table is similar to a regular table that has a primary key on one or more of its columns; however, instead of maintaining both the table and the index as two different and separate storage spaces, an index-organized table maintains a single B-tree containing both the primary key of the table and the other associated column values. Overflow segments may exist that are associated with index-organized tables due to the PCTTHRESHOLD value being set. The results of longer row lengths requiring overflow are to hold extra values.

Storage requirements are reduced for index-organized tables because the values for the key columns don't have to be duplicated in both the table and the index. The other values in the row are also stored in the index unless the entry becomes very large. If this is the case, Oracle provides an OVERFLOW clause to handle the situation.

Cluster Tables

Cluster tables provide optional methods for storing table data. Clusters (in relationship to tables) are made up of a table, or a group of tables, that share the same data blocks. They are grouped in this manner because they share common columns and often have their data queried or manipulated at the same time.

Clusters have what is known as a cluster key, which is used to identify the rows that need to be stored together. The cluster key can be one column or multiple columns. All tables that participate in the cluster have columns that correspond to the column key.

Clustering, like index-organizing and partitioning, is a mechanism completely transparent to the users and applications using the tables. Data within the cluster's participating tables can be manipulated just as it could in any other table; however, updating one of the columns in the cluster key (not to be confused with the primary key) may entail the physical relocation of the row. Because the cluster key is independent of the primary key, each table that participates in a cluster can have its own primary key, which is, or can be, a different set of columns than those in the cluster key.

Clusters are usually implemented as a means of speeding up data access to improve performance. However, although random access to clustered data may be faster, full table scans on clustered tables are usually slower.

Finally, clusters can renormalize the physical storage of tables without actually affecting the logical structure associated with those tables.

Clusters, index-organized tables, and partitioned tables are covered in greater detail for the Fundamentals II exam, but are covered here to provide you with an understanding that they exist and of their basic properties. Although these advanced topics are dealt with in great detail in later exams, basic questions are possible on the exam dealing with what they are.


Tables are the means by which Oracle stores data. It is therefore important to look at the different types of data that can be found in an Oracle database.



    Oracle 9i Fundamentals I Exam Cram 2
    Oracle 9i Fundamentals I Exam Cram 2
    ISBN: 0789732653
    EAN: 2147483647
    Year: 2004
    Pages: 244
    Authors: April Wells

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net