Oracle 5.0, Справочное руководство

Страница: 717 / 3232

MyISAM
Index Statistics Collection
697
table and
S
is the average value group size. That ratio yields an approximate number of value groups
in the table.
For a join based on the
<=>
comparison operator,
NULL
is not treated differently from any other value:
NULL <=> NULL
, just as
N <=> N
for any other
N
.
However, for a join based on the
=
operator,
NULL
is different from non-
NULL
values:
expr1 =
expr2
is not true when
expr1
or
expr2
(or both) are
NULL
. This affects
ref
[646] accesses for
comparisons of the form
tbl_name.key = expr
: MySQL will not access the table if the current value
of
expr
is
NULL
, because the comparison cannot be true.
For
=
comparisons, it does not matter how many
NULL
values are in the table. For optimization
purposes, the relevant value is the average size of the non-
NULL
value groups. However, MySQL does
not currently enable that average size to be collected or used.
For
MyISAM
tables, you have some control over collection of table statistics by means of the
myisam_stats_method
[476] system variable. This variable has three possible values, which differ
as follows:
• When
myisam_stats_method
[476] is
nulls_equal
, all
NULL
values are treated as identical
(that is, they all form a single value group).
If the
NULL
value group size is much higher than the average non-
NULL
value group size, this
method skews the average value group size upward. This makes index appear to the optimizer to be
less useful than it really is for joins that look for non-
NULL
values. Consequently, the
nulls_equal
method may cause the optimizer not to use the index for
ref
[646] accesses when it should.
• When
myisam_stats_method
[476] is
nulls_unequal
,
NULL
values are not considered the
same. Instead, each
NULL
value forms a separate value group of size 1.
If you have many
NULL
values, this method skews the average value group size downward. If
the average non-
NULL
value group size is large, counting
NULL
values each as a group of size
1 causes the optimizer to overestimate the value of the index for joins that look for non-
NULL
values. Consequently, the
nulls_unequal
method may cause the optimizer to use this index for
ref
[646] lookups when other methods may be better.
• When
myisam_stats_method
[476] is
nulls_ignored
,
NULL
values are ignored.
If you tend to use many joins that use
<=>
rather than
=
,
NULL
values are not special in comparisons
and one
NULL
is equal to another. In this case,
nulls_equal
is the appropriate statistics method.
The
myisam_stats_method
[476] system variable has global and session values. Setting the global
value affects
MyISAM
statistics collection for all
MyISAM
tables. Setting the session value affects
statistics collection only for the current client connection. This means that you can force a table's
statistics to be regenerated with a given method without affecting other clients by setting the session
value of
myisam_stats_method
[476] .
To regenerate table statistics, you can use any of the following methods:
• Execute
myisamchk --stats_method=method_name --analyze
• Change the table to cause its statistics to go out of date (for example, insert a row and then delete it),
and then set
myisam_stats_method
[476] and issue an
ANALYZE TABLE
statement
Some caveats regarding the use of
myisam_stats_method
[476] :
• You can force table statistics to be collected explicitly, as just described. However, MySQL may also
collect statistics automatically. For example, if during the course of executing statements for a table,
some of those statements modify the table, MySQL may collect statistics. (This may occur for bulk
inserts or deletes, or some
ALTER TABLE
statements, for example.) If this happens, the statistics
are collected using whatever value
myisam_stats_method
[476] has at the time. Thus, if you