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

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

ALTER TABLE
Syntax
1017
index_option
values specify additional options for an index.
USING
is one such option. For details
about permissible
index_option
values, see Section 13.1.8, “
CREATE INDEX
Syntax” .
• After an
ALTER TABLE
statement, it may be necessary to run
ANALYZE TABLE
to update index
cardinality information. See Section 13.7.5.18, “
SHOW INDEX
Syntax” .
•
ORDER BY
enables you to create the new table with the rows in a specific order. Note that the table
does not remain in this order after inserts and deletes. This option is useful primarily when you know
that you are mostly to query the rows in a certain order most of the time. By using this option after
major changes to the table, you might be able to get higher performance. In some cases, it might
make sorting easier for MySQL if the table is in order by the column that you want to order it by later.
ORDER BY
syntax permits one or more column names to be specified for sorting, each of which
optionally can be followed by
ASC
or
DESC
to indicate ascending or descending sort order,
respectively. The default is ascending order. Only column names are permitted as sort criteria;
arbitrary expressions are not permitted. This clause should be given last after any other clauses.
ORDER BY
does not make sense for
InnoDB
tables that contain a user-defined clustered index
(
PRIMARY KEY
or
NOT NULL UNIQUE
index).
InnoDB
always orders table rows according to such
an index if one is present. The same is true for
BDB
tables that contain a user-defined
PRIMARY
KEY
.
• If you use
ALTER TABLE
on a
MyISAM
table, all nonunique indexes are created in a separate
batch (as for
REPAIR TABLE
). This should make
ALTER TABLE
much faster when you have many
indexes.
This feature can be activated explicitly for a
MyISAM
table.
ALTER TABLE ... DISABLE KEYS
tells MySQL to stop updating nonunique indexes.
ALTER TABLE ... ENABLE KEYS
then should
be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster
than inserting keys one by one, so disabling keys before performing bulk insert operations should
give a considerable speedup. Using
ALTER TABLE ... DISABLE KEYS
requires the
INDEX
[577]
privilege in addition to the privileges mentioned earlier.
While the nonunique indexes are disabled, they are ignored for statements such as
SELECT
and
EXPLAIN
that otherwise would use them.
• If
ALTER TABLE
for an
InnoDB
table results in changes to column values (for example, because a
column is truncated),
InnoDB
's
FOREIGN KEY
constraint checks do not notice possible violations
caused by changing the values.
• The
FOREIGN KEY
and
REFERENCES
clauses are supported by the
InnoDB
storage engine,
which implements
ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (...)
REFERENCES ... (...)
. See Section 14.2.3.4, “
InnoDB
and
FOREIGN KEY
Constraints” . For
other storage engines, the clauses are parsed but ignored. The
CHECK
clause is parsed but ignored
by all storage engines. See Section 13.1.10, “
CREATE TABLE
Syntax” . The reason for accepting but
ignoring syntax clauses is for compatibility, to make it easier to port code from other SQL servers,
and to run applications that create tables with references. See
Section 1.8.5, “MySQL Differences
from Standard SQL” .
For
ALTER TABLE
, unlike
CREATE TABLE
,
ADD FOREIGN KEY
ignores
index_name
if given and
uses an automatically generated foreign key name. As a workaround, include the
CONSTRAINT
clause to specify the foreign key name:
ADD CONSTRAINT name FOREIGN KEY (....) ...
Important
The inline
REFERENCES
specifications where the references are defined as
part of the column specification are silently ignored by
InnoDB
. InnoDB only