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

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

Table Maintenance Statements
1166
Important
If the checksums for two tables are different, then it is almost certain that the
tables are different in some way. However, because the hashing function used
by
CHECKSUM TABLE
is not guaranteed to be collision-free, there is a slight
chance that two tables which are not identical can produce the same checksum.
13.7.2.5.
OPTIMIZE TABLE
Syntax
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_name [, tbl_name ] ...
OPTIMIZE TABLE
should be used if you have deleted a large part of a table or if you have made many
changes to a table with variable-length rows (tables that have
VARCHAR
,
VARBINARY
,
BLOB
, or
TEXT
columns). Deleted rows are maintained in a linked list and subsequent
INSERT
operations reuse old
row positions. You can use
OPTIMIZE TABLE
to reclaim the unused space and to defragment the data
file. After extensive changes to a table, this statement may also improve performance of statements
that use the table, sometimes significantly.
This statement requires
SELECT
[578] and
INSERT
[577] privileges for the table.
OPTIMIZE TABLE
works only for
MyISAM
,
InnoDB
, and (as of MySQL 5.0.16)
ARCHIVE
tables. It
does not work for tables created using any other storage engine.
For
MyISAM
tables,
OPTIMIZE TABLE
works as follows:
1. If the table has deleted or split rows, repair the table.
2. If the index pages are not sorted, sort them.
3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the
index), update them.
For
BDB
tables,
OPTIMIZE TABLE
currently is mapped to
ANALYZE TABLE
. See Section 13.7.2.1,
“
ANALYZE TABLE
Syntax” .
For
InnoDB
tables,
OPTIMIZE TABLE
is mapped to
ALTER TABLE
, which rebuilds the table to update
index statistics and free unused space in the clustered index.
You can make
OPTIMIZE TABLE
work on other storage engines by starting
mysqld --skip-new
option. In this case,
OPTIMIZE TABLE
is just mapped to
ALTER TABLE
.
OPTIMIZE TABLE
returns a result set with the following columns.
Column Value
Table
The table name
Op
Always
optimize
Msg_type status
,
error
,
info
,
note
, or
warning
Msg_text
An informational message
Note that MySQL locks the table during the time
OPTIMIZE TABLE
is running.
By default, the server writes
OPTIMIZE TABLE
statements to the binary log so that they replicate to
replication slaves. To suppress logging, use the optional
NO_WRITE_TO_BINLOG
keyword or its alias
LOCAL
.
OPTIMIZE TABLE
does not sort R-tree indexes, such as spatial indexes on
POINT
columns. (Bug
#23578)
13.7.2.6.
REPAIR TABLE
Syntax