Space announcement This space provides the same content as before, but the organization of the home page has changed. The content is now organized based on logical branches instead of legacy book titles. We hope that the new structure will help you quickly find the content that you need.

Updating statistics on an index by using What-If Index

Use this procedure to update statistics for an index in your What-If Index analysis.

Before you begin

Perform the following procedures to create an index by using cloned objects:

The examples in this section are continued from the previous task, Dropping-an-index-by-using-What-If-Index.

Related topic

Using-the-What-If-Index-function

To update statistics

  1. At the Explain Results panel (in the following figure), type U in the space beside the index and press Enter.

    FILE  COMMANDS  OPTIONS  HELP
    -------------------------------------------------------------------------------
    PSSPW200      Explain Results for SQLTEXT                                      
    Command ===>                                                  Scroll ===> CSR
                                                                     More:       >
       Actions: S H R RS RW RI XD XS XP W P T C D U IM SA                             
       CLONE  Objects in database PSS60364                                         
       STMTNO SQL-STATEMENT                                                        
          117 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'                       
             CREATOR TBNAME          CARD    NPAGES COMP  NACTIVE PARENT CHILD BPOO
             PXB     EMP               -1        -1   -1       -1      0     0 BP0
                CREATOR IXNAME          1STKEY     FULLKEY NLEAF NLEVELS CLUSTERRAT
    U           PXB     myindex             -1          -1    20       2 0.80000000
       LBL   STMTNO     COST*RATE SQL-STATEMENT                                    
       WI02     117    344.638184 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
       WI01*    117    344.638184 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
       XD01     117   1012.256592 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
             COST*RATE QB PL MIX QTYPE  METH ACC MTCH IX TBNAME   IXNAME   NU J O G
       WI02  344.63818  1  1   0 SELECT    0 I      1 N  EMP      myindex  N  N N N
       WI01* 344.63818  1  1   0 SELECT    0 I      1 N  EMP      myindex  N  N N N
       XD01  1012.2565  1  1   0 SELECT    0 R      0 N  EMP               N  N N N
    ******************************** Bottom of Data *******************************

    The Update Index Access Path Statistics panel  is displayed.

    PSSPWIXS             Update Index Access Path Statistics         
    Command ===>                                                     
                                                                    
    Specify statistics and press END to continue                     
                                                                    
    Name . . . . . . myindex                                         
                                                                    
    First Key Card   -1
    Full Key Card    -1
    Cluster Ratio    80              (0-100)                         
    Leaf Pages . . . 20                                              
    Number of Levels 2                                               
                                                                    
    _ Update first key column statistics

  2. Update the statistical values as needed (see Specifying statistics to be updated for an index using What-If Index).

    Important

    To update statistics for the first key column, select the Update first key column statistics option. You can modify this information on the Update First Key Column Statistics panel that is displayed.

    The updated statistics are displayed in Update Index Access Path Statistics panel with updated statistics.

    PSSPWIXS             Update Index Access Path Statistics         
    Command ===>                                                     
                                                                    
    Specify statistics and press END to continue                     
                                                                    
    Name . . . . . . myindex                                         
                                                                    
    First Key Card   100                                             
    Full Key Card    100                                             
    Cluster Ratio    90              (0-100)                         
    Leaf Pages . . . 20                                              
    Number of Levels 2                                               
                                                                    
    _ Update first key column statistics

  3. Press F3 to display the Explain Results panel (see Explain Results panel showing updated statistics ).

    FILE  COMMANDS  OPTIONS  HELP
    -------------------------------------------------------------------------------
    PSSPW200      Explain Results for SQLTEXT                                      
    Command ===>                                                  Scroll ===> CSR
                                                                     More:       >
       Actions: S H R RS RW RI XD XS XP W P T C D U IM SA                             
    XD CLONE  Objects in database PSS60364                                         
       STMTNO SQL-STATEMENT                                                        
          117 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'                       
             CREATOR TBNAME          CARD    NPAGES COMP  NACTIVE PARENT CHILD BPOO
             PXB     EMP               -1        -1   -1       -1      0     0 BP0
                CREATOR IXNAME          1STKEY     FULLKEY NLEAF NLEVELS CLUSTERRAT
                PXB     myindex            100         100    20       2 0.90000000
       LBL   STMTNO     COST*RATE SQL-STATEMENT                                    
       WI02     117    344.638184 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
       WI01*    117    344.638184 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
       XD01     117   1012.256592 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
             COST*RATE QB PL MIX QTYPE  METH ACC MTCH IX TBNAME   IXNAME   NU J O G
       WI02  344.63818  1  1   0 SELECT    0 I      1 N  EMP      myindex  N  N N N
       WI01* 344.63818  1  1   0 SELECT    0 I      1 N  EMP      myindex  N  N N N
       XD01  1012.2565  1  1   0 SELECT    0 R      0 N  EMP               N  N N N
    ******************************** Bottom of Data *******************************

  4. To perform a dynamic Explain on the cloned object, type XD in the space beside the clone, and press Enter.The results of the Explain are displayed (see Results of dynamic Explain on cloned objects after statistical update). The WI03 label represents the Explain on the cloned objects after the following changes:

    FILE  COMMANDS  OPTIONS  HELP
    -------------------------------------------------------------------------------
    PSSPW200      Explain Results for SQLTEXT                                      
    Command ===>                                                  Scroll ===> CSR
                                                                     More:       >
       Actions: S H R RS RW RI XD XS XP W P T C D U IM SA                             
       CLONE  Objects in database PSS60364                                         
       LBL   STMTNO     COST*RATE SQL-STATEMENT                                    
       WI03*    117    439.320312 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
       WI02     117    344.638184 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
       WI01*    117    344.638184 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
       XD01       0   1012.256592 SELECT * FROM PXB.EMP WHERE LASTNAME = 'BAKER'   
             COST*RATE QB PL MIX QTYPE  METH ACC MTCH IX TBNAME   IXNAME   NU J O G
       WI03* 439.32031  1  1   0 SELECT    0 I      1 N  EMP      myindex  N  N N N
       WI02  344.63818  1  1   0 SELECT    0 I      1 N  EMP      myindex  N  N N N
       WI01* 344.63818  1  1   0 SELECT    0 I      1 N  EMP      myindex  N  N N N
       XD01  1012.2565  1  1   0 SELECT    0 R      0 N  EMP               N  N N N
    ******************************** Bottom of Data *******************************
  5. You can now perform additional What-If Index operations such as adding another index, dropping an index, and so on.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*