Секционирование таблиц и индексов

Администрирование БД, Настройка производительности Add comments

Фев 032011

Секционирование таблиц и индексов в Oracle - неплохой способ повысить производительность базы данных и упростить администрирование. Все секции таблицы или индекса могут располагаться в разных табличных пространствах, что позволит, например, расположить их на разных дисках для увеличения производительности и степени параллелизма выполнения операций.
Oracle 10g поддерживает следующие виды секционирования:

Range partitioning

Данные распределяются по секциям в зависимости от диапазона значения поля. Например, этот вид секционирования удобен для разбиения данных по месяцам или годам (таблица с данными о проданных товарах) на основании поля, содержащего дату продажи.

CREATE TABLE sales
( invoice_no NUMBER,
  name VARCHAR2 (64)
  sale_date DATE)
PARTITION BY RANGE (sale_date)
(PARTITION sales_2008 VALUES LESS THAN
		(TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
      TABLESPACE sales_2008,
PARTITION sales_2009 VALUES LESS THAN
		(TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
      TABLESPACE sales_2009,
PARTITION sales_2010 VALUES LESS THAN
		(TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
      TABLESPACE sales_2010,
PARTITION sales_2011 VALUES LESS THAN
		(TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
      TABLESPACE sales_2011,
PARTITION sales_max VALUES LESS THAN (MAXVALUE)
      TABLESPACE sales_max );

Hash partitioning

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

CREATE TABLE hash_example
	(id NUMBER,
	name VARCHAR2 (64))
PARTITION BY HASH (id)
PARTITIONS 3
STORE IN (sect1, sect2, sect3);

List partitioning

Отличается от Range partitioning тем, что для секции задаётся не диапазон, а список значений. Пример - хранение информации о продажах по странам.

CREATE TABLE list_example
      (id NUMBER
       name VARCHAR2 (64)
       country VARCHAR2 (2))
   PARTITION BY LIST (country)
      (PARTITION CIA VALUES ('RU', 'UA', 'BY'),
       PARTITION NATO VALUES ('US', 'FR');

Можно также указать секцию, в которую попадут записи, не попавшие ни в один из списков.

Composite range-hash partitioning

Таблица разбивается на секции по диапазону, секции разбиваются на подсекции (subpartitions) по хешу.

Composite range-list partitioning

Таблица разбивается на секции по диапазону, секции разбиваются на подсекции (subpartitions) по списку.

Индексы тоже могут быть секционированными. Для примера, создадим индекс для таблицы sales:

CREATE INDEX sales_n ON sales (invoice_no)
LOCAL
( PARTITION p_sales_2008 TABLESPACE sales_2008,
  PARTITION p_sales_2009 TABLESPACE sales_2009,
  PARTITION p_sales_2010 TABLESPACE sales_2010,
  PARTITION p_sales_2011 TABLESPACE sales_2011,
  PARTITION p_sales_max TABLESPACE sales_max );

А теперь представим, что закончился 2011 год и нам надо добавить ещё одну секцию в sales:

ALTER TABLE sales
SPLIT PARTITION "SALES_MAX" AT
	(TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
INTO
	(PARTITION "SALES_2012"  ,
	 PARTITION "SALES_MAX");

При этом наш индекс в dba_indexes будет иметь статус "VALID", но это не совсем верно, т.к. для секций индексов есть специальное представление dba_ind_partitions, в котором видно, что некоторые секции теперь имеют статус "UNUSABLE". Надо перестроить инвалидированные секции индекса, в этом нам поможет такой запрос:

SELECT 'alter index '|| index_name || ' rebuild partition "' ||
	partition_name || '";', p.* FROM dba_ind_partitions p
WHERE
	p.status = 'UNUSABLE'
	AND p.index_owner = 'CUBE'
	AND p.index_name = 'SALES_N';

В первом столбце результата будут готовые команды перестройки секций индекса.

Заметки DBA