|
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 TablesA 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 TablesPartitioned 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 PartitioningRange 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:
Hash PartitioningHash 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:
List PartitioningList 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 PartitioningComposite 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 TablesAn 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 TablesCluster 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.
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. |
|