|Home||Contents||Index||Master Index||New Book|
UPDATE STATISTICSUse the UPDATE STATISTICS statement to:
When to Update StatisticsUpdate the statistics in the following situations:
Updating Statistics for TablesThe optimizer estimates the effect of a WHERE clause by examining, for each column included in the WHERE clause, the proportionate occurrence of data values contained in the column. To prepare for the optimizer, the UPDATE STATISTICS statement distributes cell values from a one column into ranges, each of which contains an equal portion of the column data.
A distribution is a mapping of the data in a column into a set of column values. The contents of the column are divided into bins, each of which represents a percentage of data. For example, if one bin holds 2 percent of the data, 50 of these 2-percent bins hold all the data. A bin contains the particular range of data values that reflects the appropriate percentage of entries in the column.
Using the FOR TABLE KeywordsWithout a FOR TABLE clause, UPDATE STATISTICS updates data for every table in the current database, including the system tables.
Use FOR TABLE to exclude statistics on system tables.
The FOR TABLE clause without a table name updates the statistics for all tables, including temporary tables, in the current database.
Specify a table name or synonym name to update statistics for only that table. You can explicitly update the statistics for a temporary table or build distributions for a temporary table by specifying the name of the table.
To narrow the scope of UPDATE STATISTICS further, specify column names.
Using the FOR TABLE ONLY KeywordsIf the table specified in the UPDATE STATISTICS ON TABLE statement has subtables, the database server creates distributions for that table and every table under it in the hierarchy. For example, assume your database has the typed table hierarchy that appears in Figure 1-2, which shows a supertable named employee that has a subtable named sales_rep. The sales_rep table, in turn, has a subtable named us_sales_rep.
Using the LOW KeywordTo create a low distribution, use the LOW keyword or issue the UPDATE STATISTICS statement without a distribution level keyword. A low distribution update does the following:
Using LOW with DROP DISTRIBUTIONSThe DROP DISTRIBUTIONS keywords force the removal of distribution information from the sysdistrib system catalog table to accompany the construction of a low distribution.
When you issue the statement with a table name but no column names, all the distributions for the table name are dropped. When you specify column names in the UPDATE STATISTICS, only the distribution data for those columns is dropped from sysdistrib.
You must have the DBA privilege or be the owner of the table in order to drop distributions.
The following example shows how to remove distributions for the customer_num column in the customer table:
Using the MEDIUM or HIGH KeywordA medium or high distribution update does the following:
Both the MEDIUM or HIGH keywords provide a RESOLUTION percent clause with which you can set the percentage of data distributed to every bin. Unless you change the percent value with a RESOLUTION clause:
You can specify a confidence ratio with the MEDIUM keyword. If you do not specify a value for conf, the default confidence is
The HIGH keyword can take considerable time to gather the information across the database, particularly a database with large tables. The HIGH keyword might scan each table several times. The MEDIUM keyword scans tables at least once and takes longer to execute on a given table than the LOW keyword. To minimize processing time, specify a table name and column names within that table.
MEDIUM or HIGH with DISTRIBUTIONS ONLYThe UPDATE STATISTICS statement reads through index pages to:
Examining index information can consume considerable processing time. If you specify the DISTRIBUTIONS ONLY option with the MEDIUM or HIGH keywords, you do not collect statistics for index information.
The DISTRIBUTIONS ONLY keyword has no effect on information about tables, such as the number of pages used, the number of rows, and fragment information. UPDATE STATISTICS needs this data to construct accurate column distributions and requires little time and system resources to collect it.
In the following example, the UPDATE STATISTICS statement gathers distributions information, index information, and table information for the customer table:
However, in the following example, only distributions information and table information are gathered for the customer table. The DISTRIBUTIONS ONLY option prevents the construction of index information.
Updating Statistics for RoutinesUse the FOR ROUTINE, FOR FUNCTION, FOR PROCEDURE, or FOR SPECIFIC clause to avoid updating tables. You specify a routine name or specific name, or a routine parameter list to limit the scope of the UPDATE STATISTICS statement to a particular definition of a routine.
1. Run UPDATE STATISTICS in medium mode with the DISTRIBUTIONS ONLY option for each table. (If you are the database owner or DBA, and you want to gather statistics for the entire database, you can do that with a single command instead.). The default parameters are sufficient unless the table is very large. In this case, use a resolution of
2. Run UPDATE STATISTICS in high mode for all columns that head an index. For the fastest execution time of the UPDATE STATISTICS statement, you must execute one UPDATE STATISTICS statement in the high mode for each such column.
ReferencesIn the INFORMIX-Universal Server Performance Guide, see the discussion of UPDATE STATISTICS. In the Informix Migration Guide, see the discussion of how to use the dbschema utility to view distributions created with UPDATE STATISTICS.
WHENEVERUse the WHENEVER statement to trap exceptions that occur during the execution of SQL statements.
UsageUse of the WHENEVER statement is equivalent to placing an exception-checking routine after every SQL statement. The following table summarizes the types of exceptions for which you can check with the WHENEVER statement.
If you do not use the WHENEVER statement in a program, the program does not automatically abort when an exception occurs. Your program must explicitly check for exceptions and take whatever corrective action you desire. If you do not check for exceptions, the program simply continues running. However, as a result of the errors, the program might not perform its intended purpose.
The following ESQL/C example program has three WHENEVER statements, two of which are WHENEVER SQLERROR statements. Line 4 uses STOP with SQLERROR to override the default CONTINUE action for errors. Line 8 specifies the CONTINUE keyword to return the handling of errors to the default behavior. For all SQL statements between lines 4 and 8, the preprocessor inserts code that checks for errors and halts program execution if an error occurs. Therefore, any errors that the INSERT statement on line 6 generates cause the program to stop.
After line 8, the preprocessor does not insert code to check for errors after SQL statements. Therefore, any errors that the INSERT statement (line 10), the SELECT statement (line 11), and DISCONNECT statement (line 12) generate are ignored. However, the SELECT statement does not stop program execution if it does not locate any rows; the WHENEVER statement on line 7 tells the program to continue if such an exception occurs.
3 EXEC SQL connect to 'test';
4 EXEC SQL WHENEVER SQLERROR STOP;
5 printf("\n\nGoing to try first insert\n\n");
6 EXEC SQL insert into test_color values ('green');
7 EXEC SQL WHENEVER NOT FOUND CONTINUE;
8 EXEC SQL WHENEVER SQLERROR CONTINUE;
9 printf("\n\nGoing to try second insert\n\n");
10 EXEC SQL insert into test_color values ('blue');
12 EXEC SQL disconnect all;
13 printf("\n\nProgram over\n\n");
SQLERROR KeywordIf you use the SQLERROR keyword, any SQL statement that encounters an error is handled as the WHENEVER SQLERROR statement directs. If an error occurs, the SQLCODE variable (sqlca.sqlcode) is less than zero and the SQLSTATE variable has a class code with a value greater than
If you do not use any WHENEVER SQLERROR statements in a program, the default for WHENEVER SQLERROR is CONTINUE.
SQLWARNING KeywordIf you use the SQLWARNING keyword, any SQL statement that generates a warning is handled as the WHENEVER SQLWARNING statement directs. If a warning occurs, the first field of the warning structure in SQLCA (sqlca.sqlwarn.sqlwarn0) is set to
In addition to setting the first field of the warning structure, a warning also sets an additional field to
The following statement causes a program to stop execution if a warning condition exists:
If you do not use any WHENEVER SQLWARNING statements in a program, the default for WHENEVER SQLWARNING is CONTINUE.
NOT FOUND KeywordsIf you use the NOT FOUND keywords, exception handling for SELECT and FETCH statements is treated differently than for other SQL statements. The NOT FOUND keyword checks for the following cases:
The following statement calls the no_rows() function each time the NOT FOUND condition exists:
If you do not use any WHENEVER NOT FOUND statements in a program, the default for WHENEVER NOT FOUND is CONTINUE.
CONTINUE KeywordUse the CONTINUE keyword to instruct the program to ignore the exception and to continue execution at the next statement after the SQL statement. The default action for all exceptions is CONTINUE. You can use this keyword to turn off a previously specified option.
STOP KeywordUse the STOP keyword to instruct the program to stop execution when the specified exception occurs. The following statement halts execution of an ESQL/C program each time that an SQL statement generates a warning:
GOTO KeywordUse the GOTO clause to transfer control to the statement that the label identifies when a particular exception occurs. The GOTO keyword is the ANSI-compliant syntax of the clause. The GO TO keywords are a non-ANSI synonym for GOTO.
The following example shows a WHENEVER statement in INFORMIX-ESQL/C code that transfers control to the label missing each time that the NOT FOUND condition occurs:
EXEC SQL WHENEVER NOT FOUND GO TO missing;
EXEC SQL fetch lname into :lname;
printf("No Customers Found\n");
To correct this error, either put a labeled statement with the same label name in each function, issue another WHENEVER statement to reset the error condition, or use the CALL clause to call a separate function.
CALL ClauseUse the CALL clause to transfer program control to the named function or procedure when a particular exception occurs. Do not include parentheses after the function or procedure name. The following WHENEVER statement causes the program to call the error_recovery() function if the program detects an error:
When the named function completes, execution resumes at the next statement after the line that is causing the error. If you want to halt execution when an error occurs, include statements that terminate the program as part of the named function.
Observe the following restrictions on the named function:
ReferencesSee the EXECUTE FUNCTION, FETCH and GET DIAGNOSTICS statements in this manual.
See the chapter on exception checking and error checking in your SQL API product manual.