Neoview SQL Reference Manual (R2.4)

ON (CITY), (STATE) TO (ZIP)ON CITY, STATE TO ZIP
ON (CITY) TO (STATE), (ZIP)ON CITY TO STATE, ZIP
The TO specification is useful when a table has many columns, and you want histograms on a
subset of columns. Do not confuse (CITY) TO (ZIP) with (CITY, STATE, ZIP), which refers to a
multi-column histogram.
You can clear statistics in any combination of columns you specify, not necessarily with the
column-group-list you used to create statistics. However, those statistics will remain until
you clear them.
Column Lists and Access Plans
Generate statistics for columns most often used in data access plans for a table—that is, the
primary key, indexes defined on the table, and any other columns frequently referenced in
predicates in WHERE or GROUP BY clauses of queries issued on the table. Use the EVERY
COLUMN option to:
• Generate histograms for every individual column or multicolumns that make up the primary
key and indexes
• Enable the optimizer to choose a better plan.
The EVERY KEY option generates histograms that make up the primary key and indexes.
If you often perform a GROUP BY over specific columns in a table, use multi-column lists in the
UPDATE STATISTICS statement (consisting of the columns in the GROUP BY clause) to generate
histogram statistics that enable the optimizer to choose a better plan. Similarly, when a query
joins two tables by two or more columns, multi-column lists (consisting of the columns being
joined) help the optimizer choose a better plan.
Examples of UPDATE STATISTICS
• This example generates four histograms for the columns jobcode, empnum, deptnum, and
(empnum, deptnum) for the table EMPLOYEE. Depending on the table’s size and data
distribution, each histogram should contain ten intervals.
UPDATE STATISTICS FOR TABLE employee
ON (jobcode),(empnum, deptnum)
GENERATE 10 INTERVALS;
--- SQL operation complete.
• This example generates histogram statistics using the ON EVERY COLUMN option for the
table DEPT. This statement performs a full scan, and Neoview SQL determines the default
number of intervals.
UPDATE STATISTICS FOR TABLE dept
ON EVERY COLUMN;
--- SQL operation complete.
• Suppose that a construction company has an ADDRESS table of potential sites and a
DEMOLITION_SITES table that contains some of the columns of the ADDRESS table. The
primary key is ZIP. Join these two tables on two of the columns in common:
SELECT COUNT(AD.number), AD.street,
AD.city, AD.zip, AD.state
FROM address AD, demolition_sites DS
WHERE AD.zip = DS.zip AND AD.type = DS.type
GROUP BY AD.street, AD.city, AD.zip, AD.state;
To generate statistics specific to this query, enter these statements:
UPDATE STATISTICS FOR TABLE address
ON (street), (city), (state), (zip, type);
206 SQL Statements