Joomla 3.1 Templates by FatCow Coupon

Фрагментация, секционирование – partition. О фрагментации таблиц. Часть первая

Категория: Блог Опубликовано 24.05.2017

Фрагментация (секционирование) это

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

Кому и зачем это нужно? Вопрос с секционированием таблиц, тесно связан с другим важным вопросом , вопросом масштабируемости проекта.

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

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


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

Создадим три независимых табличных пространства, они нам понадобятся для демонстрационных примеров

CREATE TABLESPACE TBLSP1 DATAFILE 'D:\BASE\TBLSP1.DAT'
SIZE 10M REUSE AUTOEXTEND ON NEXT 2M MAXSIZE 20M;
CREATE TABLESPACE TBLSP2 DATAFILE 'D:\BASE\TBLSP2.DAT'
  SIZE 10M REUSE AUTOEXTEND ON NEXT 2M MAXSIZE 20M;
CREATE TABLESPACE TBLSP3 DATAFILE 'D:\BASE\TBLSP3.DAT'
  SIZE 10M REUSE AUTOEXTEND ON NEXT 2M MAXSIZE 20M;



Фрагментация таблиц
В Oracle используется три типа фрагментации(партицирования) для таблиц - это:
Фрагментация по диапазону значений
Данные относящиеся таблицы , где значения в заданных колонках относятся к некоторому диапазону распределяются по соответствующим фрагментам(секциям) таблицы
Например все проводки с 2001-2002 года , помещаются в первую секцию , за 2002-2003 во второй и так далее.

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


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


Совмещенный тип фрагментации
Или тип фрагментации совмещающий в себе фрагментацию с использованием хэш функции и фрагментации по диапазону значений

Синтаксис
Создания и фрагментации таблиц используется дополнительная синтаксическая конструкция в команде CREATE ТABLE – PATITION BY

Обычный синтаксис для создания ферментированной таблицы выглядит следующим образом 

CREATE TABLE Имя таблицы
(столбец1 тип, столбец2 тип, столбец n)
PARTITION BY HASH(имя столбца по которому строится хэшфункция) или

Описание фрагментов
(partition имя фрагмента1(партиции) название табличного пространства,
partition имя фрагмента2(партиции) название табличного пространства)
Или 
PARTITION BY RANGE(имя столбца для проверки на соответствие диапазону  фрагмента)
(PARTITION имя фрагмента1 VALUES LESS THAN
(выражение1) ) имя табличного пространства,
(PARTITION имя фрагмента1 VALUES LESS THAN
(выражение2 )) имя табличного пространства
Выгажение 1 , Выражение 2 задают диапазон для данных , для определения фрагмента таблицы(партиции) к которому эти данные относятся.



Специфика использования оператора SELECT для выбора данных из фрагментированных таблиц

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

select * from таблица partition(фрагмент);


пример

select * from pro_hash partition(pt_3);



данный запрос выведет данные из фрагмента таблицы pt_3

Фрагментация по диапазону значений
Создадим таблицу проводок с фрагментацией по диапазону значений

CREATE TABLE pro_range
( summ int,
  docdate date,
  docnum number
 )
 PARTITION BY RANGE(docdate)
 (partition pt_1 values less than (to_date('01.02.2014','DD.MM.YYYY')) tablespace TBLSP1,
  partition pt_2 values less than (to_date('01.03.2014','DD.MM.YYYY')) tablespace TBLSP2,
  partition pt_3 values less than (to_date('01.04.2014','DD.MM.YYYY')) tablespace TBLSP3,
  partition p_othermax values less than (maxvalue) tablespace TBLSP3
 );


Заполним таблицу проводок значениями

insert into pro_range(summ,docdate,docnum)
select trunc(dbms_random.value * 10000) summ,
       to_date('01012014', 'DDMMYYYY') + trunc(dbms_random.value * 99) + 1 dtdoc,
       trunc(dbms_random.value * 50000) numdoc
  from dual
connect by level <= 1100


Выберем данные из таблицы

select * from pro_range
SUMM	DOCDATE	DOCNUM
6226	11.01.2014	19170
3561	10.01.2014	41482
7106	09.01.2014	5604
5177	02.01.2014	1917
7090	02.01.2014	26399
692	07.01.2014	39100
8246	23.01.2014	38502
3364	30.01.2014	2813
7014	22.01.2014	41248
2331	11.01.2014	33239

select * from pro_range partition(pt_1) where rownum<3;
   	SUMM	DOCDATE	DOCNUM
	6226	11.01.2014	19170
	3561	10.01.2014	41482
	7106	09.01.2014	5604

 

select * from pro_range partition(pt_2) where rownum<3;
   	SUMM	DOCDATE	DOCNUM
	7131	13.02.2014	15345
	2765	26.02.2014	47587
	6594	16.02.2014	24707

select * from pro_range partition(pt_3) where rownum<3;
4832	30.03.2014	10615
	4300	06.03.2014	20531
	5897	07.03.2014	47711

select * from pro_range partition(p_othermax) where rownum<3;
SUMM	DOCDATE	DOCNUM
521	07.04.2014	35258
7416	05.04.2014	32045
1589	07.04.2014	46956



Следующий пример демонстрирует разбиение на фрагменты таблицу в зависимости года к которой принадлежит проводка

CREATE TABLE pro_range_year
( summ int,
  docdate date,
  docnum number
 )
 PARTITION BY RANGE(docdate)
 (partition pt_1 values less than ('01-01-2012') tablespace TBLSP1,
  partition pt_2 values less than ('01-01-2013') tablespace TBLSP2,
  partition pt_3 values less than ('01-01-2014') tablespace TBLSP3,
  partition p_othermax values less than (maxvalue) tablespace TBLSP3
 );



Следующий пример иллюстрирует использование фрагментации таблицы в зависимости от числового значения 

CREATE TABLE pro_range_num
( summ int,
  docdate date,
  docnum number
 )
 PARTITION BY RANGE(docnum)
 (partition pt_1 values less than (10) tablespace TBLSP1,
  partition pt_2 values less than (30) tablespace TBLSP2,
  partition pt_3 values less than (40) tablespace TBLSP3,
  partition p_othermax values less than (maxvalue) tablespace TBLSP3
 );


Фрагментация с использованием списка значений

Создание таблицы

CREATE TABLE pro_list
( summ int,
  docdate date,
  doctype varchar2(2))
 PARTITION BY LIST(doctype)
 (
  partition pt_1 values ('PP','PO' ) tablespace TBLSP1,
  partition pt_2 values ('RR') tablespace TBLSP2,
  partition pt_3 values ('RO','SO') tablespace TBLSP3
 );



Заполняем таблицу

insert into pro_list(summ,docdate,doctype)
select trunc(dbms_random.value * 10000) summ,
       to_date('01012011', 'DDMMYYYY') + trunc(dbms_random.value * 799) + 1 dtdoc,
       decode(trunc(dbms_random.value * 5)+1,1,'PP',2,'PO',3,'RR',4,'RO',5,'SO' ) doctype
  from dual
connect by level <= 1100 



Проверяем

select * from pro_list
   	SUMM	DOCDATE	DOCTYPE
1	9445	26.01.2013	PP
2	6120	21.01.2012	PP
3	8374	18.02.2013	PP
4	533	24.04.2011	PP
5	77	27.02.2013	PO
6	5046	12.01.2011	PO

select * from pro_list partition(pt_1) where rownum<4;
   	SUMM	DOCDATE	DOCTYPE
1	9445	26.01.2013	PP
2	6120	21.01.2012	PP
3	8374	18.02.2013	PP

select * from pro_list partition(pt_2) where rownum<4;
   	SUMM	DOCDATE	DOCTYPE
1	3046	21.12.2011	RR
2	2093	07.09.2012	RR
3	9233	14.08.2012	RR
select * from pro_list partition(pt_3) where rownum<4;
   	SUMM	DOCDATE	DOCTYPE
1	7980	11.12.2012	RO
2	9338	01.03.2012	SO
3	9784	22.11.2012	RO



Фрагментация с использованием хэш-функции

Создадим таблицу проводок с фрагментацией по хэш функции

CREATE TABLE pro_hash
( summ int,
  docdate date,
  docnum number
 )
 PARTITION BY HASH(docnum)
 (partition pt_l tablespace TBLSP1,
  partition pt_2 tablespace TBLSP2,
  partition pt_3 tablespace TBLSP3
 );


Заполним ее данными

insert into pro_hash(summ,docdate,docnum)
select trunc(dbms_random.value * 10000) summ,
to_date('01012014', 'DDMMYYYY') + trunc(dbms_random.value * 61) + 1   dtdoc,
       trunc(dbms_random.value * 50000) numdoc
  from dual
connect by level <= 1100;


Выберем данные из таблицы

select * from pro_hash


А так же выполним оператор select для каждого из фрагментов(секций) таблицы

select * from pro_hash partition(pt_1);
select * from pro_hash partition(pt_2);
select * from pro_hash partition(pt_3); 


Смешанный тип фрагментации

Смешанный тип фрагментации предусматривает как ферментацию по диапазону значений так и дополнительную фрагментацию по хэш функции или фрагментацию по списку значений

CREATE TABLE pro_range_hash
( summ int,
  docdate date,
  docnum number
 )
 PARTITION BY RANGE(docdate)
 SUBPARTITION BY HASH(docnum)
 SUBPARTITION TEMPLATE(
 SUBPARTITION spt_1 TABLESPACE TBLSP1,
 SUBPARTITION spt_2 TABLESPACE TBLSP2,
 SUBPARTITION spt_3 TABLESPACE TBLSP3
 )
 (partition pt_1 values less than (to_date('01.02.2014','DD.MM.YYYY')) tablespace TBLSP1,
  partition pt_2 values less than (to_date('01.03.2014','DD.MM.YYYY')) tablespace TBLSP2,
  partition pt_3 values less than (to_date('01.04.2014','DD.MM.YYYY')) tablespace TBLSP3,
  partition p_othermax values less than (maxvalue) tablespace TBLSP3
 );


Заполним таблицу pro_range_hash

insert into pro_range_hash(summ,docdate,docnum)
select trunc(dbms_random.value * 10000) summ,
       to_date('01012011', 'DDMMYYYY') + trunc(dbms_random.value * 799) + 1 dtdoc,
       trunc(dbms_random.value * 50000) numdoc
  from dual
connect by level <= 1100



Управление данными во фрагментах таблицы

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

insert into pro_list(summ,docdate,doctype) values (10,sysdate,'GT');


Сервер выведет ошибку 

Ora-144000. Вставленный ключ секции не соответствует ни одной секции.

Изменим данные таким образом чтобы изменилась принадлежность записей к фрагменту

update pro_list set pro_list.doctype = 'SO' where pro_list.doctype = 'RR'


Получим ошибку обновление ключа секции прведет к ее изменению...
Как же сделать возможным перенос строк?
Для этого необходимо включить для выбранной таблицы опцию row movement
Выполним скрипт

alter table pro_list enable row movement


выполним наш update повторно

update pro_list set pro_list.doctype = 'SO' where pro_list.doctype = 'RR'



на этот раз ошибок не было. Обновление строк в таблице прошло успешно.


А собственно ,зачем это все было необходимо? Как это применить?

У вас есть три табличных пространства, нам в данный момент нужна только оперативная информация по документам типа RR из таблицы pro_list в табличном пространстве номер TBLSP2 .
Отключим остальные табличные пространства и перенесем их на архивный диск.

alter tablespace TBLSP1 offline; 
alter tablespace TBLSP3 offline;


Выполним запрос по таблице pro_list 

Select * from pro_list



Получим системную ошибку о том, что сегмент не может быть прочитан
Скорректируем запрос так как нам нужны только документы RR

select * from pro_list where pro_list.doctype = 'RR'
запрос выполнен без ошибок
   	SUMM	DOCDATE	DOCTYPE
	3046	21.12.2011	RR
	2093	07.09.2012	RR
	9233	14.08.2012	RR
	5474	09.03.2013	RR


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

Просмотров: 80