Index-Only Tables

The following operations are allowed on index-only tables:

•  Select, Insert, Update, and Delete.

•  Drop/Truncate table.

•  Export/Import/SQL*Loader.

•  Analyze.

•  Explain Plan.

•  Constraints are fully supported.

Index-only tables have the following restrictions:

•  Primary key is required.

•  Cannot create any other type of index on index-only tables.

•  Cannot be part of a cluster.

•  Does not support LOBs datatype.

•  Does not support replication.

•  Cannot be partitioned.

•  No CTAS (Create Table As Select) operations.

Parallel DML (PDML)

Very large databases are benefited tremendously by the ability of Oracle8 to allow data manipulation operations in parallel. Oracle8 allows you to perform parallel inserts, updates, and deletes on partitioned tables. In VLDB systems it can be very difficult to insert, update, and delete a large number of rows because of the time constraints involved in running application programs in such systems.

Parallel Insert

Parallel insert allows the insertion of rows into partitioned and nonpartitioned tables with multiple write streams.

Parallel insert can be of two types: direct path and parallel direct path.

Insert with Direct Path

This method works similar to the direct path option of SQL*Loader. Rows are inserted above the high-water mark of the table and, on successful insertion, the high-water mark is updated to the new values.

Insert with Parallel Direct Path

This method works similarly to the parallel direct path option of SQL*Loader. Rows are inserted into the temporary extents of the table’s tablespace and are then merged with the existing extents.

Parallel insert implies two types of parallelization:

•  Parallel write streams into a single nonpartitioned table.

•  Multiple inserts simultaneously into separate partitions.

The type of insert used depends on the type of table you are inserting into and the hints provided in the insert statement.

Parallel Update and Delete

Prior to Oracle8, very large databases were constantly faced with the problem of updating and deleting large amounts of data in a short period of time, for example:

•  Data archiving

•  Data loads

•  Routine deletions of data from DSS systems

•  Batch update/delete jobs in OLTP systems

•  Backup/recovery

Parallel updates and deletes make use of parallel processes or slaves that perform the actual work. These operations are similar to parallel query, and the number of slaves depends on several factors such as the optimizer, system resources, and hints used.

---

Note:  
There is no parallelization with nonpartitioned tables.

---

---

Note:  
There is no parallelization within a single partition of a partitioned table.

---

It should be clear from this discussion that partitions are very important in the usage of parallel DML, and this fact should be considered during database design. Factors to consider include the following:

•  The number of partitioned tables.

•  The number of partitions within the partitioned tables.

•  Partition sizes.

•  Type of indexes.

•  Enough rollback segments—(n+1) rollback segments for an n-way parallel DML.

Bitmap Indexes

Oracle8 provides five types of indexing schemes:

•  B*-tree indexes

•  B*-tree cluster indexes

•  Hash cluster indexes

•  Reverse key indexes

•  Bitmap indexes

Indexes provide pointers to the rows in a table that contain a particular key value. Regular indexes store a list of ROWIDs for each key corresponding to the rows with that key value. Bitmap indexes use a bitmap for each key value. Each bit in the bitmap corresponds to a possible ROWID, and a set bit means that the corresponding ROWID contains the key value for which you are looking. Internally, even though a bitmap uses a different representation, it provides the same functionality as a regular index because a mapping function converts the bit position to an actual ROWID.

Use of bitmap indexes provides the following advantages in a Data Warehousing application:

•  A significant reduction in space usage compared to regular indexes (for low cardinality columns only)

•  Efficient Parallel DMLs

•  Improved response time for ad hoc queries

The following new initialization parameters can be used with bitmap indexes:

•  CREATE_BITMAP_AREA_SIZE Default 8MB. This parameter determines the amount of memory allocated for creating bitmap indexes. This parameter cannot be dynamically altered at the session level. The higher the cardinality, the more memory needed for improving performance.

•  BITMAP_MERGE_AREA_SIZE Default is 1MB. This parameter determines the amount of memory used to merge bitmaps retrieved from index range scans. This parameter cannot be dynamically altered at the session level. Large values for this parameter can improve performance because bitmap indexes must be sorted before being merged together.

Bitmap indexes have the following restrictions:

•  Rule-based optimizer does not consider them.

•  They cannot be used for checking referential integrity.

•  SORTED_INDEX flag does not apply for bitmap indexes with direct load.

•  Subqueries that have a predicate of the form col=(subquery) cannot be used as keys for a bitmap index.

•  The ALTER TABLE command, which adds a column to a bitmap index or modifies a bitmap-indexed column, causes the index to be invalidated.

Bitmap indexes can significantly improve the performance of queries with the following characteristics:

•  Tables being queried contain many rows.

•  Low cardinality columns.

•  The individual predicates on the bitmapped columns (low-cardinality) should select a large number of rows.

•  The WHERE clause of the select statement contains multiple predicates on low- cardinality columns.