Optimizers

Most relational database systems include a cost-based optimizer, a complex program responsible for choosing an access path to satisfy a particular query, such as a Select statement issued by PS/nVision. Using statistics stored in the database, the optimizer tries to determine the index to use for each of the tables accessed in the query and the table access sequence that yields the data with minimal searching.

Some database optimizers have a choice between cost-based and rule-based optimizers. For PS/nVision, and for most PeopleSoft software, you should use the cost-based optimizer because:

• PS/nVision creates dynamic SQL based on the report criteria you provide.

  Rule-based optimizers are designed for static SQL that is written following its rules.

• Cost-based optimizers adapt much better to changes in data and indexes because they use statistics in their optimum access path calculations.

This discussion of tuning PS/nVision's SQL performance assumes use of a cost-based optimizer.