Optimizing Indexes

Updating Statistics

Once the indexes are created, you must tell the system to use these new indexes by "updating statistics." The specifics vary by platform (described in the following chart). After new indexes are created, or after inserting or deleting substantial rows of data from existing tables, you should run the update statistics procedure. The purpose of this procedure is to update the system tables with index and table information to assist the optimizer in choosing the most efficient index for an operation. If this procedure is not performed frequently, your system performance could suffer.

Platform	Method to Update Statistics
DB2	Batch RUNSTATS process, by Table Space.
ORACLE	ANALYZE TABLE xxx COMPUTE STATISTICS ANALYZE INDEX xxx COMPUTE STATISTICS For large tables, it is faster to replace COMPUTE with ESTIMATE. Determine through testing whether estimated statistics yield optimum access plans.

Platform

Method to Update Statistics

DB2

Batch RUNSTATS process, by Table Space.

ORACLE

ANALYZE TABLE xxx COMPUTE STATISTICS

ANALYZE INDEX xxx COMPUTE STATISTICS

For large tables, it is faster to replace COMPUTE with ESTIMATE. Determine through testing whether estimated statistics yield optimum access plans.

Table Seeding

Indexes are delivered for several temporary tables and hold data only for the duration of a specific process. Because the tables are generally empty, running update statistics on the empty version of the table causes the optimizer to think that the table is always empty and to favor a full-table scan instead of using the index. For this reason, a script is available to seed these temporary tables with 100 rows of data for the purpose of updating the statistics on the index.

The script is delivered in your SQL subdirectory and is called SEEDGL with the SQL extension appropriate to your database platform. The script inserts 101 rows of data into the temporary tables and runs the update statistics command on the seeded table. The tables that are seeded are; PSTREESELECT05, PSREESELECT06, PSTREESELECT08, and PSTREESELECT10 to correspond to the length of the ChartFields delivered with the demo system.

If rows currently exist in your PSTREESELECTxx tables, you should not delete this data. The system populates these rows when you execute a PS/nVision report. These rows correspond to a control table named PSTREESELCTL, and if removed by them, result in incorrect data or no data in your PS/nVision report the next time you execute it.

Review each script before running to ensure that the key values loaded do not conflict with any that would be used by the existing system, and to determine if changes are needed for any specific modifications you might have done. When running the scripts in your production environment, be sure that you seed the PSTREESELECT tables that correspond to the field length of your ChartFields; these are the PSTREESELECT tables that are used in your environment.

Note:

If you are a DB2 customer, you can maximize the benefits of seeding these tables, by seeding them with the cardinality that is correct for your particular environment. A script named SEEDMULT.DMS is an example of seeding the PSTREESELECT06 table with correct cardinality. Use this version, rather than the above versions, when seeding the PSTREESELECT tables.

Table seeding is also needed for temp tables that are being reserved for Application Engine processes called from PeopleCode. The AE processes have a logic to update statistics of these temporary tables after each Insert. But each %UpdateStats requires an explicit commit, and since the commit is not allowed within an Application Engine program called from PeopleCode, therefore the %UpdateStats is not executed. To ensure that Sql statements referring to those temporary tables run well, you can try the following:

To leave the temporary tables with no statistics. That means never do any update statistics on these tables. If you had updated statistics of these tables when they were empty, you need to drop and recreate them. For Oracle, you can run the analyze table command with the option to delete the statistics. You then need to test the process to see if the performance is acceptable for you.

If having no statistics on the temporary table does not yield the performance required (like the case of table PS_COMB_EXP_TAOx of Journal Combination Edit process), then you need to seed the table by running an Application Engine trace of level 131, extract all the "insert into PS_COMB_EXP_TAOx" Sql statements, and run them outside from the program to seed the table. Then run the update statistics command on the seeded table.

Physical Performance Considerations

Even the best index planning and execution cannot overcome performance problems caused by disk and index fragmentation. Fragmentation happens over time as records are deleted, updated, and added to the database. When new records are inserted, the system places the new information wherever space is available—not necessarily in the same location as the rest of the physical table. As records are deleted, gaps might be left in the physical space that the record occupied which can or cannot be filled in with new information. As the physical locations of these records become more spread out, the system must work harder to find the specific record you requested, and response time suffers. Both indexes and tables can become fragmented and hamper performance, so it is important to take the steps outlined in your database administration documentation to eliminate database fragmentation.