Making the Rules Practical

To put these rules to practical use, you need to identify SQL statements that performed badly, and examine each "where" clause in those SQL statements. What you are trying to obtain from each SQL statement are the columns, accessed in the "where" clause, that you believe are acceptable to the system for index selection. In other words, you get to count all the columns that use an equal value, but none of the columns using "between" logic, and so on. To logically view this huge amount of information, it is best to make a chart with the columns in the "where" clause across the top and the origin of the SQL statement down the left side. As you go through each SQL statement, place an X in the box under the column that the index is likely to use. Create a chart for each record that concerns you.

Viewing a Sample "Where" Clause Chart

The following is a sample "where" clause chart for the ledger record with SQL statements generated from the demo database:

  BU LED ACCT DEPT STAT FY PERIOD CURR

ONLINE

               

Budget Detail

X

X

X

X

X

X

  

X

 

X

X

X

X

X

X

X

X

Budget Copy

X

X

      

X

 

  
 

X

X

      

X

X

  

Budget Spread

X

X

X

X

X

X

X

  
 

X

X

X

X

X

X

X

X

Ledger Inquiry

X

X

X

  

X

X

  

X
 

X

X

X

X

  

X

X

X
 

X

X

X

X

X

X

  

X
 

X

X

X

    

X

    

InterUnit Inquiry

X

X

X

    

X

X

  

TimeSpan Inquiry

X

X

X

X

X

    

X

Journal Entry

X

X

X

X

X

X

X

X

REPORTS

               

GLS7004

X

X

    

X

X

  

X

GLS7005

X

X

    

X

X

  

X

GLS1003

X

X

    

X

X

X

X

GLS7002

X

X

    

X

X

    

GLS7003

X

X

    

X

X

  

X

BATCH

               

Posting

X

X

X

X

X

X

X

X

Closing

X

X

      

X

X

  

 

X

X

    

X

X

    

 

X

X

    

X

X

X

  

 

X

X

    

X

X

  

X

 

X

X

X

    

X

 

  

Summary Ledger

X

X

      

X

X

  

 

X

X

    

X

    

X

Consolidate

   

X

  

X

X

X

X

 

X

X

X

  

X

X

  

X

Currency

X

X

X

X

X

X

X

X

Armed with these charts and the rules of indexing, now work to create indexes that access the records more quickly. Looking across the columns used in ledger "where" clauses, assess the viability of each column.

Business unit is included in every "where" clause, but in the demo database there are only 79 of them. One of these, US001, is used much more frequently than in the others, so the cardinality is relatively low. Because it is always used, you will probably include it in indexes.

The ledger column is also included in each clause, but the cardinality is low (three are used in the LEDGER table and one used the majority of the time).

Account is used in a good percentage of the "where" clauses and is required in most of the online inquiry transactions. The cardinality is also high (735 unique values of account in the ledger table in the demo database), so this is a good possibility in an index.

Other ChartFields, including DEPTID, PRODUCT, and PROJECT, are lumped together because the demo database does not require them and accepts a wildcard in their place on the inquiry pages. This wildcard generates a "like" SQL statement that works well if you supply the field with a value; it is less efficient if the field is left as a wildcard ('%'). If you have ChartFields that you always enter, you should include these in the index in the same way the account field is included. You might also want to consider making any "always enter" fields required on the inquiry pages to make the select statements more efficient.

Fiscal year is included on nearly every "where" clause. At present the cardinality is relatively low (3 - 4 different values); however, expect it to increase as time goes by. Accounting period is used on a good number of "where" clauses, again with limited cardinality.

Currency code is included in many of the "where" clauses. There are many values in the currency code record, but in practice the vast majority of transactions in the ledger record have a currency code of USD, so the cardinality of this field is also relatively low. Therefore, this column might not be included in most indexes.

Hints for Indexing

The following hints can help you create better indexes:

  • Strive for the minimum number of indexes to accomplish the processes.

    • Each index has to be updated every time an update, insert, or delete is performed on the underlying table; so each index has an overhead cost associated with it.

      In considering the right number of indexes for a table, be sure to consider the use of the table. Fairly static tables (like Chartfield tables) can have numerous indexes with relatively little negative impact because they are frequently accessed and rarely updated. Other tables, however, are updated continually and each additional index could make quite a difference in the amount of time it takes to perform these functions.

    • These extra indexes on fairly static tables (like Chartfield tables) are not a problem.

      However, if there are list items designated on records that are never used as edit (prompt) tables and the index generated is not assisting any processing, you have actually created additional overhead during record updates and inserts without any benefit. The bottom line is that you should carefully consider designating fields as alternate search key fields.

    • Because the vast majority of "where" clauses that access the PS_LEDGER table begin with equality checks on business unit and ledger, these common fields are included at the beginning of most of the Oracle indexes.

  • Sometimes it is beneficial to put a column in the index that would not usually be included in a "where" clause but is usually retrieved from the table when the table is accessed.

    • An example of this is the account type on the GL_ACCOUNT_TBL.

      This column is generally accessed when the table is queried, and adding this column to the index might prevent table access when only the account type is needed. The Alternative Search Key indexes actually do this for us in most cases, because these indexes generally contain descriptions, and this information is frequently accessed when a code table is accessed. This approach is only useful if it prevents table access in some instances and does not interfere with the normal operation of the index in other situations.

    • For this reason, these columns are generally at the end of the indexes.

      Some customers have experienced an improvement in background processing against the ledger record when the posted total amount field is added to the end of the duplicate indexes, because it results in an index-only scan. During testing on the demo database, there was some negative impact on the online performance, so this field was not added to the delivered indexes. But it might be worth testing in your production environment.

  • The system is specific about the indexes chosen.

    Sometimes the most well thought-out index does not get used as expected or does not yield the expected results. Test the new index, taking a look at the plan to be sure it is used, then take another timing to compare the new index access with the original baseline timing. Based on the results, you might need to adjust the sequence of the columns in the index or the columns included in the index to find the optimal combination.

Once you find the best combination for the SQL statements under review, run through all the processes again. Sometimes one new index can cause changes in the indexes used by other processes. Often the change is good, but sometimes it is worse, and more evaluation is required.