Log:
valutazioni - 0, GPA: 0 ( )

Istruzioni per l'uso HP, Modello HP NonStop G-Series

Produttore : HP
File Size : 36.17 kb
File Nome :
Lingua di insegnamento: enfr
Vai a scaricare



da questo dispositivo ha anche altre istruzioni :

Facilità d'uso


Introduction
HP NonStop SQL/MX Release 2.1.1 includes a new internal algorithm that improves the accuracy of
histograms created by the UPDATE STATISTICS statement. This white paper describes several tests that
were performed to measure the improvements in histogram accuracy. The test results and
accompanying discussion provide clues to understanding how statistics are updated and how to use
the UPDATE STATISTICS statement more effectively.
UPDATE STATISTICS Background
The SQL/MX UPDATE STATISTICS statement gathers information about the distribution of specified
columns in a table and stores this information in the form of a histogram in user metadata tables.
Histograms that are built by UPDATE STATISTICS consist of some number of intervals, each
representing a range of column values. Within each histogram interval, two counts are stored:
• Number of rows
• Number of distinct values in the interval
For example, if statistics are updated on the Salary field of an Employee table, the resulting histogram
contains intervals that divide up the range of salaries in the table. Each interval, in turn, contains a
count of the number of Employee rows and the number of distinct salaries in the associated range.
The SQL/MX compiler uses histograms that are built by the UPDATE STATISTICS statement to translate
the text of an SQL statement into an efficient execution plan. To ensure that the compiler selects the
most efficient plan, the histograms used during compilation must be as accurate as possible. It is
especially important for the distinct value counts, also known as unique entry counts (UECs), to be
accurate. The compiler uses these counts to make important decisions, such as determining the order
in which tables in a query are joined and deciding which join methods to use.
The UPDATE STATISTICS statement supports options that allow histograms to be built from all the rows
in a table or from a sample of rows of a specified size. Building histograms from all rows in a table
results in perfectly accurate histograms, but also prohibitively long run times for large tables.
When histograms are built from a sample, the row count and UEC in each interval must be estimated
from the sample, and the estimates are rarely perfectly accurate. Fortunately, SQL/MX users can use
relatively small sample sizes to achieve acceptable levels of histogram accuracy and UPDATE
STATISTICS performance. However, selecting a sample size that provides a good balance between
accuracy and performance can be a difficult task.
In general, for a given sample size, the histogram accuracy improvements introduced in Release
2.1.1 result in more accurate histogram UECs than the histograms built by UPDATE STATISTICS
before Release 2.1.1. Alternatively, the improvements allow the same level of accuracy as in
previous releases, but with a smaller sample size. The use of a smaller sample size is desirable,
because it reduces the time it takes UPDATE STATISTICS to build histograms.
The improved accuracy of UPDATE STATISTICS in Release 2.1.1 results from the use of a new internal
algorithm for estimating the UEC of a column in a full table from a sample of rows in the table. You
can use the test results described in this paper to help determine an appropriate sample size when
updating statistics.
Tests Performed
UPDATE STATISTICS tests were performed on three different data sets:
• Synthetic data drawn from Normal, Uniform, and Zipf distributions
• Synthetic data from a large SQL/MX retail demonstration system
2


...

Questo manuale è adatto anche per i modelli :
Computer - HP NonStop L-Series (36.17 kb)

Scrivi la tua opinione del dispositivo



Il messaggio
Il tuo nome :
Inserire le due cifre :
capcha





categorie