Хеш секционирование

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

Oracle рекомендует в качестве n значения использовать степень двойки (2, 4, 6, 8, 16 ...)
чтобы данные распределялись между секциями более равномерно.

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


Создадим табличные пространства:

drop tablespace tsp1;
drop tablespace tsp2;
drop tablespace tsp3;
drop tablespace tsp4;
drop tablespace tsp5;
drop tablespace tsp6;
drop tablespace tsp7;
drop tablespace tsp8;
create tablespace tsp1 datafile '+DATA' size 500m reuse
autoextend on next 100m extent management local uniform size 100m;
create tablespace tsp2 datafile '+DATA' size 500m reuse
autoextend on next 100m extent management local autoallocate;
create tablespace tsp3 datafile '+DATA' size 500m reuse
autoextend on next 100m extent management local uniform size 100m;
create tablespace tsp4 datafile '+DATA' size 500m reuse
autoextend on next 100m extent management local autoallocate;
create tablespace tsp5 datafile '+DATA' size 500m reuse
autoextend on next 100m extent management local uniform size 100m;
create tablespace tsp6 datafile '+DATA' size 500m reuse
autoextend on next 100m extent management local autoallocate;
create tablespace tsp7 datafile '+DATA' size 500m reuse
autoextend on next 100m extent management local uniform size 100m;
create tablespace tsp8 datafile '+DATA' size 500m reuse
autoextend on next 100m extent management local autoallocate;


Создадим таблицу t с ключом секционирования по столбцу object_id:

drop table t;
create table t (object_id, owner, object_type, object_name, created)
partition by hash(object_id)
(
partition part_1 tablespace tsp1,
partition part_2 tablespace tsp2,
partition part_3 tablespace tsp3,
partition part_4 tablespace tsp4,
partition part_5 tablespace tsp5,
partition part_6 tablespace tsp6,
partition part_7 tablespace tsp7,
partition part_8 tablespace tsp8
)
as
select object_id, owner, object_type, object_name, created
from all_objects,
         (select rownum as r from dual connect by rownum <=100);



-- секции таблицы t
select partition_name from user_tab_partitions where table_name = 'T';

PART_1
PART_2
PART_3
PART_4
PART_5
PART_6
PART_7
PART_8


-- табличные пространства в которых расположены секции таблицы t
select tablespace_name from user_segments where segment_type = 'TABLE PARTITION' and segment_name = 'T' ;

TSP1
TSP2
TSP3
TSP4
TSP5
TSP6
TSP7
TSP8

-- сколько выделено и сколько свободного пространства в мегабайтах в этих табличных пространствах
select b.tablespace_name,  mbytes_alloc,  mbytes_free
     from ( select round(sum(bytes)/1024/1024) mbytes_free,  tablespace_name
                  from dba_free_space
                group by tablespace_name ) a,
              ( select round(sum(bytes)/1024/1024) mbytes_alloc, tablespace_name
                  from dba_data_files
                group by tablespace_name ) b
 where a.tablespace_name (+) = b.tablespace_name
 and b.tablespace_name in (select tablespace_name from user_segments
                                        where segment_type = 'TABLE PARTITION' and segment_name = 'T');



TSP4    500    427
TSP7    500    300
TSP1    500    300
TSP2    500    427
TSP3    500    300
TSP6    500    427
TSP5    500    300
TSP8    500    427


Секционированные объекты более удобны в обслуживании.
Например вместо того, чтобы перестроить и реорганизовать всю таблицу:

alter table t move;

Можно перестроить каждую секцию по отдельности:

alter table t move partition part_1;
alter table t move partition part_2;
alter table t move partition part_3;
alter table t move partition part_4;
alter table t move partition part_5;
alter table t move partition part_6;
alter table t move partition part_7;
alter table t move partition part_8;


-- или так:

begin
    for x in ( select partition_name  from user_tab_partitions  where table_name = 'T' )
    loop
         execute immediate
         'alter table t move partition ' ||  x.partition_name;
    end loop;
end;
/



Запросы по отдельным секциям можно делать так:

select * from t partition(part_1);

select count(*) from t partition(part_1);


Используя хеш - секционирование, мы тем самым просим Oracle случайно распределять данные,
надеясь при этом, что распределение между секциями будет равномерным:

Посмотрим какое количество строк попало в каждую секцию:

DECLARE
    CURSOR cur IS
    SELECT table_name, partition_name FROM all_tab_partitions WHERE table_name = 'T';
    v_count number:=0;
BEGIN
    FOR rec IN cur
    LOOP
        execute immediate 'select count(*) from '||rec.table_name||' partition('||rec.partition_name||')' into v_count;
        dbms_output.put_line('The Partition '||rec.partition_name||' of table '||rec.table_name||' has '||v_count||' rows');
    END LOOP;
END;


The Partition PART_1 of table T has 1119500 rows
The Partition PART_2 of table T has 1118400 rows
The Partition PART_3 of table T has 1120800 rows
The Partition PART_4 of table T has 1109900 rows
The Partition PART_5 of table T has 1098900 rows
The Partition PART_6 of table T has 1097300 rows
The Partition PART_7 of table T has 1122100 rows
The Partition PART_8 of table T has 1102400 rows



Секционирование также может повысить доступность данных.
Например если произойдет повреждения диска, который содержал данные одной из секций,
(симитировать можно так alter tablespace  p1 offline; )
то вся база будет доступна и в рабочем состоянии не будет доступна только одна секция.

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

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