Index Fast Full Scans

Oracle8 introduces an alternative to full table scans called the index fast full scan (FFS). An FFS is performed on a table when the index contains all the columns requested in the query. The index scan is fast because the entire index is read using multiblock reads, which is much faster than full table scans. An index fast full scan is specified by means of the INDEX_FFS hint in the query, and it should be used when the size or number of columns in the index is significantly less than the size or number of columns in the table being accessed. For example

SELECT /*+ INDEX_FFS(department, dept_idx) */  dept_id, dept_loc_city,

dept_loc_state FROM department;

Reverse Key Indexes

When a very large database system uses OLTP applications, there is a very common contention problem associated with tables that use sequences or other such structures to generate primary keys. The problem is that everyone is inserting an index at the end of the index, resulting in contention for the last free leaf block. Oracle8 provides a feature called reverse order indexes that attempts to solve this problem by storing the index in reverse key order. For example, a column with a key value of MEGH would be stored as HGEM. As a result of this reverse key storage, the contention for the last free leaf block should be resolved, resulting in a more random distribution of values.

It should be noted that such reverse key indexes are useful for equality lookups only because the reversal of value results in a loss of relationship needed during range scans. For example, suppose you have an index on the last_name and the values are Smith, Robinson, King, Trent, and Simpson. If a regular B*-Tree is created on this column and you execute a query to find all the people whose last names start with S, you can easily find Smith and Simpson. However, in a reverse key index, the values would be stored as htims, nosnibor, gnik, tnert, and nospmis. Therefore, the range scan is not efficient.

Tuning for Very Large Databases

Tuning a VLDB system involves tuning the database environment and then tuning the queries and transactions that are most demanding on the system resources.

The following tips can be used to improve the performance in a VLDB:

•  Make the System Global Area (SGA) as large as possible. Recommended size is 2 percent of the database size. Check your operating system manuals for the largest supported size.

•  Set the DB_BLOCK_SIZE to be the highest value supported by Oracle on your platform.

•  Use the partitioning techniques described earlier in the chapter and create partitioned tables and indexes to distribute the I/O load among the components of the partitioned table.

•  Use local indexes where possible because they are efficient and easy to manage.

•  VLDB systems would have very large sorts and transactions, and you can improve the performance by using the enhanced parallel query options discussed earlier in the chapter.

•  Make use of fully indexed tables.

•  Create and use bitmap indexes.

•  Use indexes with care, because in VLDB systems, indexes, if not used properly, can degrade performance. Check the hit ratio to see if the blocks in the SGA are being reused. If the hit ratio is less than 90 percent, it indicates that the indexes are not used properly. VLDB has large tables and the associated indexes are also very large. As a result, the SGA may not be able to hold the index blocks.

•  Size the SGA properly and minimize the amount of physical disk access that the queries may be performing.

•  Properly plan the physical layout of the files on disk with the intention of minimizing I/O contention. Consider using RAID systems and disk mirroring.

---

Tip:  
The SGA keeps track of the blocks accessed. Blocks accessed by means of an index access or via a table access with ROWID are held the longest, while blocks accessed via a table scan are removed earlier.

---

•  Data proximity is very important in dealing with VLDB systems. Maximize data proximity by inserting records into a table sequentially and order them by columns frequently used in range scans. This will increase the reusability of data blocks in the SGA because now the index and table blocks that would be loaded are reduced.

---

Tip:  
Range scan of indexes followed by table access via ROWID does not perform well in VLDB systems.

---

•  Consider using table scans when reading a large quantity of rows. This is more important in VLDB than in smaller systems. This is due to the manner in which the SGA deals with data blocks. Index scans would cause the blocks read in to the SGA to stay in the SGA for a long time and may remove other blocks from the SGA, while table scanned blocks would be flushed out quickly.

Transaction Management in VLDB Systems

Most of the transactions in a VLDB system are batch data loads. This is true in about 99 percent of such systems because it is much faster to populate the large database by using techniques such as Direct Path SQL*Loader instead of an operator keying in the information. These batch loads may account for about 80–85 percent of the transactions in such systems, so you should try and focus all your energies on optimizing their performance.

---

Tip:  
Perform your batch jobs when online transactions are not occurring in the system—this will minimize rollback segment-related problems in VLDB systems.

---

Rollback Segment Considerations

As mentioned earlier, VLDBs are characterized by batch jobs. It is very beneficial to create a few large rollback segments instead of a lot of small rollback segments.

---

Tip:  
Use the following to force a transaction to use a particular rollback segment.

Set transaction use rollback segment RB1;

---

---

Tip:  
Use good naming conventions in order to easily identify rollback segments you will use for your batch operations.

---