Индексируйте корректные таблицы и столбцы

Используйте следующие рекомендации, принимая решение о создании индекса:

Размер одной записи индекса не может превышать приблизительно одной трети размера блока данных. Проконсультируйтесь с администратором базы данных, чтобы определить память, потребную для создаваемого индекса.

Ограничивайте число индексов на таблицу

Таблица может иметь любое число индексов. Однако чем больше индексов, тем больше накладные расходы при изменениях в таблице. Так, при вставке и удалении строк должны быть обновлены все индексы по таблице. Аналогично, при обновлении столбца должны быть обновлены все индексы, содержащие этот столбец. Таким образом, следует искать компромисс между скоростью извлечения данных из таблицы и скоростью обновления таблицы. Например, если таблица большей частью читается, то иметь много индексов может быть полезно, однако, если таблица интенсивно обновляется, предпочтительнее иметь меньше индексов.

Упорядочивайте столбцы индекса для производительности

Порядок, в котором столбцы инлекса перечисляются в команде CREATE INDEX, не обязан соответствовать порядку, в котором эти столбцы определены в таблице. Однако порядок столбцов в команде CREATE INDEX существенен, так как он может повлиять на производительность запросов. В общем случае, вы должны первым в индексе указывать тот столбец, который будет использоваться наиболее часто.

Например, предположим, что таблица VENDOR_PARTS имеет следующие столбцы:

Таблица VENDOR_PARTS

VEND_ID (поставщик)	PART_NO (изделие)	UNIT_COST (цена единицы)
1012	10-440	.25
1012	10-441	.39
1012	457	4.95
1010	10-440	.27
1010	457	5.10
1220	08-300	1.33
1012	08-300	1.19
1292	457	5.28

Предположим, что существуют пять поставщиков, и каждый поставщик выпускает около 1000 изделий. Допустим, таблица VENDOR_PARTS обычно опрашивается предложениями SQL, подобными следующему:

SELECT * FROM vendor_parts
WHERE part_no = 457
AND vendor_id = 1012;

Чтобы увеличить производительность таких запросов, вам следует создать составной индекс, указав первым самый селективный столбец, т.е. столбец, имеющий БОЛЬШЕ различных значений:

CREATE INDEX ind_vendor_id
ON vendor_parts (part_no, vendor_id);

Индексы ускоряют извлечение для любого запроса, в котором участвует ВЕДУЩАЯ ЧАСТЬ индекса. Так, в приведенном выше примере, запросы, в которых фраза WHERE использует только столбец PART_NO, также получат выигрыш производительности. Поскольку столбец VENDOR_ID имеет лишь пять различных значений, создание отдельного индекса по этому столбцу не имеет смысла.  

Создание индексов

Индекс может быть создан для таблицы, чтобы улучшить производительность запросов, выдаваемых по соответствующей таблице. Индекс может также быть создан для кластера. Вы можете создать СОСТАВНОЙ индекс по нескольким (до 16) столбцам. ORACLE вводит в действие ограничение целостности UNIQUE или PRIMARY KEY, автоматически создавая уникальный индекс по уникальному или первичному ключу. В общем случае, для обеспечения уникальности предпочтительнее создавать ограничения, чем использовать устаревший синтаксис CREATE UNIQUE INDEX.

Индексы создаются с помощью команды SQL CREATE INDEX. Например, следующее предложение создает индекс с именем EMP_ENAME для столбца ENAME таблицы EMP:

CREATE INDEX emp_ename
ON emp(ename)
TABLESPACE users
STORAGE (INITIAL 20K
NEXT 20K
PCTINCREASE 75)
PCTFREE 0;

Заметьте, что для этого индекса явно специфицировано несколько параметров памяти.

Привилегии, требуемые для создания индексов

Чтобы создать новый индекс, вы должны владеть соответствующей таблицей или иметь для нее объектную привилегию INDEX. Схема, в которой создается индекс, должна также иметь квоту для табличного пространства, в котором будет содержаться индекс, либо системную привилегию UNLIMITED TABLESPACE. Чтобы создать индекс в схеме другого пользователя, вы должны иметь системную привилегию CREATE ANY INDEX.

Удаление индексов

Вы можете захотеть удалить индекс по любой из следующих причин:

Когда вы удаляете индекс, все экстенты его сегмента возвращаются в содержащее табличное пространство и становятся доступными для других объектов в этом табличном пространстве. Для удаления индекса используйте команду SQL DROP INDEX. Например, следующее предложение удаляет индекс EMP_ENAME:

DROP INDEX emp_ename;

Когда вы удаляете таблицу, все ассоциированные индексы удаляются автоматически.

Привилегии, требуемые для удаления индекса

Чтобы удалить индекс, вы должны либо иметь его в своей схеме, либо обладать системной привилегией DROP ANY INDEX.

Управление кластерами, кластеризованными таблицами и индексами кластеров

Так как кластеры хранят взаимосвязанные строки разных таблиц вместе в одних и тех же блоках данных, при правильном использовании кластеров достигаются два основных преимущества:

Поэтому для данных связанных таблиц в кластере расходуется меньше памяти, чем для таких же таблиц в некластеризованном формате.

Указания по созданию кластеров

Следующие секции приводят некоторые рекомендации по созданию кластеров. Относительно характеристик производительности обратитесь к секции "Как использовать кластеры".

Выбирайте подходящие таблицы для кластера.

Используйте кластеры для хранения одной или нескольких таблиц, которые главным образом опрашиваются (не так часто изменяются), и для которых запросы часто выполняют соединение данных из нескольких связанных таблиц, либо извлекают связанные данные из одной таблицы.

Выбирайте подходящие столбцы для ключа кластера

Тщательно выбирайте столбцы для ключа кластера. Если в запросах, выполняющих соединение таблиц, используются несколько общих столбцов, сделайте ключ кластера составным ключом. В общем, характеристики, которые указывают на хороший ключ кластера, те же, что и для любого индекса; см. секцию "Индексируйте корректные таблицы и столбцы".

Хороший ключ кластера имеет достаточно уникальных значений, так что группа строк, соответствующих каждому значению ключа, заполняет приблизительно один блок данных. Слишком малое число строк на значение ключа кластера приводит к расходу памяти и мало дает для улучшения производительности. Ключи кластера столь специфичны, что слишком малое количество строк, имеющих одинаковое значение ключа, дает большой процент неиспользуемой памяти в блоках, если только при создании кластера не было специфицировано маленькое значение SIZE.

Слишком большое число строк на значение ключа кластера может привести к лишним просмотрам при отыскании строк для данного ключа. Ключи кластера, созданные по слишком общим атрибутам (таким, как пол: МУЖ или ЖЕН), приводят к избыточным поискам и могут дать ухудшение производительности вместо улучшения.

Индекс кластера не может быть уникальным, и не может включать столбец, определенный с типом данных LONG.

Соображения производительности

Необходимо заметить, что кластеры могут ухудшить производительность предложений DML (INSERT, UPDATE и DELETE) по сравнению с хранением таблицы вне кластера со своим собственным индексом. Это объясняется способом использования памяти, а также количеством блоков, которые должны быть посещены при просмотре таблицы; поскольку в каждом блоке кластера сосредоточены данные нескольких таблиц, каждая отдельная таблица в кластере занимает больше блоков, чем если бы она хранилась вне кластера. Вы должны принимать ваши решения относительно кластеров, имея в виду эти компромиссы.

Чтобы идентифицировать данные, которые лучше было бы хранить в кластеризованной форме, чем в некластеризованной, поищите таблицы, связанные друг с другом через ограничения ссылочной целостности, а также таблицы, которые часто опрашиваются совместно, с помощью предложений SELECT, соединяющих данные из двух или более таблиц.

Путем кластеризации таких таблиц по столбцам, используемым для соединения данных, вы уменьшите число блоков данных, которое должно быть обработано для выполнения запросов; все строки, необходимые для соединения по данному значению ключа кластера, находятся в одном и том же блоке. Поэтому производительность запросов для соединений улучшается. Аналогично, может оказаться полезным кластеризовать единственную таблицу. Например, таблицу EMP можно было бы кластеризовать по столбцу DEPTNO, чтобы сгруппировать вместе все строки с одинаковыми значениями номера отдела. Это оказалось бы выгодным для тех приложений, которые обрабатывают таблицу EMP по отделам.

Как и индексы, кластеры не влияют на проектирование приложений. Существование кластера прозрачно для пользователей и приложений. Данные, хранящиеся в кластере, обрабатываются с помощью тех же предложений SQL, что и данные, хранящиеся в некластеризованных таблицах.

Создание кластеров, кластеризованных таблиц и индексов кластеров

Кластер используется для хранения одной или нескольких таблиц, которые часто соединяются вместе в запросах. Не следует кластеризовать таблицы, которые, как правило, обрабатываются индивидуально. После того, как кластер создан, в нем можно создавать таблицы. Однако прежде чем в кластеризованные таблицы можно будет вставлять строки, вы должны создать индекс кластера. Использование кластеров не влияет на создание дополнительных индексов для кластеризованных таблиц; такие индексы можно создавать и удалять как обычно.

Для создания кластера используйте команду SQL CREATE CLUSTER. Например, следующее предложение создает кластер с именеи EMP_DEPT, который будет содержать таблицы EMP и DEPT, кластеризуемые по (общему) столбцу DEPTNO:

CREATE CLUSTER emp_dept (deptno NUMBER(3))
PCTUSED 80
PCTFREE 5;

Для создания таблицы в кластере используйте команду SQL CREATE TABLE с опцией CLUSTER. Например, таблицы EMP и DEPT могут быть созданы в кластере EMP_DEPT с помощью следующих предложений:

CREATE TABLE dept (
deptno NUMBER(3) PRIMARY KEY,
. . . );
CLUSTER emp_dept (deptno);
CREATE TABLE emp (
empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
. . .
deptno NUMBER(3) REFERENCES dept)
CLUSTER emp_dept (deptno);

Вы можете специфицировать в предложении CREATE TABLE схему для кластеризуемой таблицы; кластеризованная таблица не обязана быть в той же самой схеме, в которой содержится кластер. Индекс кластера должен быть создан до того, как в любую из кластеризованных таблиц можно вставлять строки. Например, следующее предложение создает индекс кластера для кластера EMP_DEPT:

CREATE INDEX emp_dept_index
ON CLUSTER emp_dept
INITRANS 2
MAXTRANS 5
PCTFREE 5;

Замечание: Ключ кластера не может быть уникальным. Более того, ORACLE не гарантирует уникальности отдельных столбцов ключа кластера, даже если они определены с ограничениями UNIQUE или PRIMARY KEY. Ключ кластера устанавливает отношение между таблицами в кластере.

Привилегии, требуемые для создания кластера, кластеризованной таблицы и индекса кластера

Чтобы создать кластер в своей схеме, вы должны иметь системную привилегию CREATE CLUSTER, а также квоту для табличного пространства, в котором будет содержаться кластер, либо системную привилегию UNLIMITED TABLESPACE. Чтобы создать кластер в схеме другого пользователя, вы должны иметь системную привилегию CREATE ANY CLUSTER, а владелец должен иметь квоту для табличного пространства, в котором будет содержаться кластер, либо системную привилегию UNLIMITED TABLESPACE. Чтобы создать таблицу в кластере, вы должны иметь системную привилегию CREATE TABLE или CREATE ANY TABLE. Вам не требуется иметь квоту табличного пространства или системную привилегию UNLIMITED TABLESPACE для создания кластеризованной таблицы. Чтобы создать индекс кластера, ваша схема должна содержать этот кластер, и вы должны иметь следующие привилегии:

Распределение памяти для кластера вручную

ORACLE динамически распределяет дополнительные экстенты для сегмента данных кластера при необходимости. Однако вы можете захотеть явно распределить для кластера дополнительный экстент. Например, в среде параллельного сервера ORACLE экстент кластера может быть явно распределен для конкретной инстанции. Новый экстент можно распределить для кластера с помощью команды SQL ALTER CLUSTER с опцией ALLOCATE EXTENT. Для дополнительной информации об этом параметре команды ALTER CLUSTER обратитесь к документу ORACLE7 Parallel Server Administrator's Guide.

Удаление кластеров, кластеризованных таблиц и индексов кластеров

Кластер может быть удален, если таблицы в кластере больше не нужны. При удалении кластера удаляются все таблицы в кластере и соответствующий индекс кластера; все экстенты, принадлежавшие как сегменту данных, так и сегменту индекса кластера, возвращаются в содержащее табличное пространство и становятся доступными для других объектов в этом табличном пространстве. Кластеризованные таблицы можно удалять индивидуально, что не затрагивает кластер, другие кластеризованные таблицы или индекс кластера.

Кластеризованная таблица удаляется так же, как и некластеризованная таблица - командой SQL DROP TABLE. Обратитесь к секции "Удаление таблиц" для информации об удалении индивидуальных таблиц.

Замечание: Когда вы удаляете из кластера одиночную таблицу, ORACLE удаляет каждую строку этой таблицы индивидуально. Если вы намереваетесь удалить весь кластер, для максимизации эффективности используйте команду DROP CLUSTER с опцией INCLUDING TABLES.

Удаляйте из кластера индивидуальную таблицу (с помощью команды DROP TABLE) лишь в том случае, если вы хотите оставить остальную часть кластера.

Можно удалить индекс кластера, не затрагивая сам кластер и его таблицы. Однако, кластеризованные таблицы не могут использоваться, пока не существует индекс кластера; вы должны пересоздать индекс кластера, чтобы открыть доступ к кластеру. Индексы кластера иногда приходится удалять как часть процедуры пересоздания фрагментированного индекса кластера. См. "Удаление индексов" для дополнительной информации об удалении индексов. Чтобы удалить кластер, не содержащий таблиц, вместе с его индексом, используйте команду SQL DROP CLUSTER. Например, следующее предложение удаляет пустой кластер с именем EMP_DEPT:

DROP CLUSTER emp_dept;

Если кластер содержит одну или несколько кластеризованных таблиц, и вы намереваетесь удалить кластер вместе с таблицами, добавьте в команду DROP CLUSTER опцию INCLUDING TABLES, например:

DROP CLUSTER emp_dept
INCLUDING TABLES;

Если опция INCLUDING TABLES опущена, а кластер содержит таблицы, то будет возвращена ошибка. Если одна или несколько таблиц в кластере содержат первичные или уникальные ключи, на которые ссылаются ограничения FOREIGN KEY таблиц, находящихся вне кластера, то этот кластер нельзя удалить, не удаляя одновременно зависимых ограничений FOREIGN KEY. Это легко сделать, используя в команде DROP CLUSTER опцию CASCADE CONSTRAINTS. Например:

DROP CLUSTER emp_dept
INCLUDING TABLES
CASCADE CONSTRAINTS;

ORACLE возвратит ошибку, если вы не используете опцию CASCADE CONSTRAINTS и существуют зависимые ограничения.

Привилегии, требуемые для удаления кластера

Чтобы удалить кластер, вы должны иметь его в своей схеме, либо владеть системной привилегией DROP ANY CLUSTER. Вы не обязаны иметь дополнительных привилегий для удаления кластера, содержащего таблицы, даже если кластеризованные таблицы не принадлежат владельцу этого кластера.

Продолжение статьи

Дополнительную информацию Вы можете получить в компании Interface Ltd.

Обсудить на форуме Oracle
Отправить ссылку на страницу по e-mail