Многoмерное секционирование (partition) данных в Oracle

добавлено: 03 фев 13
понравилось:0
просмотров: 10289
комментов: 1

теги:

Автор: AlexeiF

Одна из мощнейших опций Oracle состоит в возможности секционирования данных в таблице (partitioning), что позволяет существенно увеличить производительность баз данных и построить более оптимально и красиво логическую и физическую структура таблицы и индексов. При этом в Oracle есть существенное ограничение. Вы может создать ТОЛЬКО два уровня секционирования ( partition – subpartition). Иногда этого не достаточно и хотелось бы создать более глубокое секционирование данных, скажем 3-х уровневое, 4-х и более.
Я покажу на конкретном примере как легко можно это обойти. Данный подход позволяет создать ЛЮБОЕ кол-во вложенного секционирования, ограниченного лишь нашим воображением , целесообразностью и ограничениями самого Oracle.

ПОСТАНОВКА ЗАДАЧИ.
Есть процесс репликации данных из множества баз данных одновременно в одну, при этом данные должны быть хранится по дням недели и разделятся для дальнейшего использования по времени. Количество данных в течении одного дня может достигать более 25 млрд. строк, потому для дальнейшей обработки желательно из разделять по времени поступления в таблицу. Данная задача реальная. Обобщенно говоря, данная система должна поддерживать торговую площадку на бирже в режиме 24/7. Огромный объем есть следствие электронных торгов , а именно сохранения торговых котировок (Trading Quotes) в течении дня. Т.о. говоря языком Oracle у нас есть:

TRD_DB_ID -> 1,…,30 – ID базы данных из которых идет репликация. Таких баз данных , в данном примере, 30
DAY_OF_WEEK_ID -> 1,..5 – ID дня недели
TIME_SLICE_ID -> 1,…,48 – ID периода времени. Все сутки делятся на получасовые интервалы.

Основанная идея состоит в том чтобы разделить огромный объем данных на независимые части, дабы позволить производить репликацию параллельно (TRD_DB_ID), использовать опять же параллельно и независимо перекачку данных в DATEWARHOUSE (TIME_SLICE_ID) и возможность поддерживать торговлю в течении всего дня и всей недели. Огромное удобство такого разделения заключается еще в том , что можно делать рестарт любого процесса перекачки независимо от других и выполнять команду TRUNCATE независимо и если надо параллельно для нескольких партиций одновременно, что существенно сократит время подготовки базы данных для последующего использования.
Налицо проблема. Необходимо 3 уровня секционирования данных. Oracle позволяет делать только 2. Если посмотреть, как Oracle создает partition table with subpartition, на физико-логическом уровне, то легко увидеть, что каждая subpartition есть не что иное, как отдельный объект. И основная идея работы с таким объектом состоит в том , что разработчик не задумывается с какой именно subpartition/partition он работает. Для этого ему достаточно указать в
SELECT …. FROM …
WHERE
значение ключей subpartition/partition и ORACLE автоматически будет работать ТОЛЬКО с этой subpartition/partition и не будет использовать ПОЛНОГО сканирование таблицы (FULL TABLE SCAN). Очевидно, что сканирование 25 млрд. рядов не реально в принципе, если скажем такую таблицу, использует аппликация, работающая в реальном времени.

РЕШЕНИЕ
Будем использовать ту же идею, что и Oracle. Будем создавать LIST партиции, где в основе ключа будет комбинироваться значение всех трех независимых переменных. Скажем
PART_22340 – будет означать, что в данной партиции мы собираемся хранить данные для 2-го дня недели, из базы данных #23 , для 40-го периода времени суток (19:30 – 20:00). Очевидно, что надо создать N=30*5*48=7200 партиций.

CREATE TABLE SQH_AF
(C_1 ,
…..
C_N,
PART_KEY INTEGER(10)
)
partition by list (part_key)
(
partition PART_10101 values (10101),
partition PART_10102 values (10102),
partition PART_10103 values (01013),
…..
partition PART_53047values (53047),
partition PART_53048 values (53048)
);

Далее создаем вспомогательную таблицу
CREATE TABLE AF_REF
(
PART_KEY INTEGER(10),
DAY_OF_WEEK_ID INTEGER(1),
TRD_DB_ID INTEGER(2),
TIME_SLICE_ID INTEGER(2)
)
;
CREATE UNIQUE INDEX AF_REF_U1 ON AF_REF
(DAY_OF_WEEK_ID , TRD_DB_ID , TIME_SLICE_ID );
;
Которая заполняется очевидным образом
*****************************************************

PART_KEY DAY_OF_WEEK_ID TRD_DB_ID TIME_SLICE_ID
_________ _______________ _________ ______________
10101 1 1 1
10102 1 1 2
…..
53047 5 30 47
53048 5 30 48
********************************************************************************
И далее строится VIEW
create or replace view SQH_AF_V
as
select
a.C_1,
a.C_2,

a.C_N,
b.PART_KEY, b.DAY_OF_WEEK_ID, b. TRD_DB_ID, b.TIME_SLICE_ID

from SQH_AF a, AF_ref b
where
a.part_key=b.part_key;

Понятно, что INSERT разумно производить, зная предварительно значение всех переменных для определения PART_KEY. А вот SELECT, используя SQH_AF_V.
Ниже приведен EXPLAIN PLAN Показывающий , что Oracle будет работать с конкретной партицией а не со всей таблицей

select * from SQH_AF_V
where
TRD_DB_ID =1
and
TIME_SLICE_ID =4
and
DAY_OF_WEEK_ID =1;


Plan
SELECT STATEMENT ALL_ROWS Cost: 1 Bytes: 216 Cardinality: 4
5 NESTED LOOPS Cost: 1 Bytes: 216 Cardinality: 4
2 TABLE ACCESS BY INDEX ROWID TABLE DBA_ADMIN.AF_REF Cost: 1 Bytes: 46 Cardinality: 1
1 INDEX UNIQUE SCAN INDEX (UNIQUE) DBA_ADMIN.AF_REF_U1 Cost: 0 Cardinality: 1
4 PARTITION LIST ITERATOR Cost: 0 Bytes: 32 Cardinality: 4 Partition #: 4 Partitions determined by Key Values
3 TABLE ACCESS FULL TABLE DBA_ADMIN.SQH_AF Cost: 0 Bytes: 32 Cardinality: 4 Partition #: 4 Partitions determined by Key Values

Комментарии


  • Небольшое дополнение. Если использовать Oracle 11g и виртуальные колонки, то можно построить таблицу

    CREATE TABLE SQH_AF
    (C_1 ,
    …..
    C_N,
    TRD_DB_ID INTEGER(2),
    DAY_OF_WEEK_ID INTEGER(1),
    TIME_SLICE_ID INTEGER(2),
    PART_KEY AS DAY_OF_WEEK_ID*10000+TRD_DB_ID*100+TIME_SLICE_ID
    )
    partition by list (part_key)
    (
    partition PART_10101 values (10101),
    partition PART_10102 values (10102),
    partition PART_10103 values (01013),
    …..
    partition PART_53047values (53047),
    partition PART_53048 values (53048)
    );
    В этом случае можно вставлять колонки непосрественно в таблицу и Oracle будем автоматически определять партицию.



Необходимо войти на сайт, чтобы оставлять комментарии