Некоторые подходы к упорядочиванию данных(Часть1)

добавлено: 17 мар 13
понравилось:0
просмотров: 2070
комментов: 0

теги:

Автор: AlexeiF

Работая с огромными объемами данных загружаемыми ежедневно в таблицу Datawarehouse мы сталкиваемся с крайне неприятной задачей связанной с производительностью обработки этих данных в дальнейшем. Создание индекса на те или иные колонки помогает этому, но не всегда в достаточной мере. Крайне простой пример иллюстрирующий это. Допустим в таблицы, ежедневно , загружается порядка 6-7 млрд. строк и дальнейшая обработка этой таблицы состоит в извлечении данных и , скажем, агрегировании их по тому или иному критерию. Мы здесь будем рассматривать проблему извлечения данных ,а не их дальнейшей обработки. Так вот, допустим у вас создан индекс для убыстрения извлечения данных и он оптимален, но надо выбрать, согласно индексу, скажем 30000 рядов из тех самых 6-7 млрд. строк. Что это означает? Это означает , что надо произвести, как правило, 30000 физических чтений (physical reads), что уже само по себе , с т.з. времени обработки данных, задачка трудоемкая. Рассматривая с этой т.з. задачку извлечения данных сразу же возникает идея. А как бы так сделать , что бы скажем вместо 30000 физических чтений сделать, ну, хотя бы 10000. Т.к. Oracle используя индекс читает данные по блочно, 30000 физических чтений означает , что у нас данные расположились так, что в каждом блоке находится по ОДНОМУ ряду необходимых нам данных. Если же мы каким-то образом УПОРЯДОЧИМ данные, что будет , скажем так, 3 ряда необходимых данных в блоке, то очевидно что нам надо будет сделать в 3 раза меньше физических чтений, т.е. 10000 physical reads. Указанный ниже подход как раз и является одним из подходов по УПОРЯДОЧИВАНИЯ данных для их быстрейшего извлечения.
Данные загружаемые в Datawarehouse, обычно упорядочены естественным образом, ПО ВРЕМЕНИ их поступления в базу данных. Наше же идея состоит в том, чтобы их бы каким-то образом упорядочить по ДРУГОМУ КРИТЕРИЮ, согласно их дальнейшего использования. Сразу же напрашивается простое логическое решение: загрузить данные в таблицу использую ORDER BY по колонкам, которые потом будут учувствовать в индексе, используемого для извлечения данных. Все хорошо, одна маленькая загвоздка, ORDER BY на таблице в 6-7 млрд. строк «сожрет» все ресурсы и будет работать крайне долго, если вообще отработает.
Для начала, хочу предложить SQL для проверки распределения рядов по блокам, в соответствии перечню колонок используемых в индексе.


select cn Num_of_rows_per_Block,count(*)  Num_of_Blocks from (					
select  DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) BLOCK_NUMBER, count(*) cn from
 SQH_AF_MIX  a					
where UNDER_SEC_SYM='SPY'					
and EXER_PRICE=142					
and CLASS_SYM ='SPY'					
and EXPR_DATE ='16-MAR-2013'					
and PUT_CALL_CODE='C'					
group by  DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid))					
group by cn					
order by 1;

Пример результата выборки:
Таб.1

Num_of_rows_per_Block Num_of_Blocks
1 8238 8238
2 3723 7446
3 1713 5139
4 881 3524
5 436 2180
6 213 1278
7 108 756
8 55 440
9 21 189
10 14 140
11 7 77
13 4 52

15413 29459
Очевидно, что SUM(Num_of_rows_per_Block(i)* Num_of_Blocks(i)) даст нам количество рядов которое выбирается из таблицы согласно фильтру.
where UNDER_SEC_SYM='SPY'					
and EXER_PRICE=142					
and CLASS_SYM ='SPY'					
and EXPR_DATE ='16-MAR-2013'					
and PUT_CALL_CODE='C'

Для нашего примера эта величина равна 29459.
А количество блоков которое необходимо при этом прочитать есть SUM( Num_of_Blocks(i)) и в нашем примере соответствует величине 15413.

Итак есть таблица загружаемая ежедневно огромным количеством данных, упорядоченных естественным образом ,по времени , и необходимо создать механизм который бы на этапе загрузки этих данных в таблицу упорядочивал эти данные в соответствии с индексом который будет использоваться в дальнейшем для основных аппликаций и репортов.
В дальнейшем для всех экспериментов я буду использовать одну и туже, в логическом плане, исходную таблицу, но упорядоченную различным образом и один и тот же SQL с фильтром указанным выше. Табл.1 показывает исходное распределение данных, т.е данных упорядоченных естественным образом, по времени.
Итак , исходная таблица имеет вид:
CREATE TABLE SQH_AF_MIX  
(
 ...
 UNDER_SEC_SYM VARCHAR2(10),					
 EXER_PRICE NUMBER(10,3),					
 CLASS_SYM VARCHAR2(10),				
 EXPR_DATE DATE,					
 PUT_CALL_CODE CHAR(1)
 );

Имеющая порядка 1 млрд рядов загруженных в порядке поступления, т.е. упорядоченная по времени и индекс
CREATE BITMAP INDEX SQH_AF_MIX_B2 ON SQH_AF_MIX
(UNDER_SEC_SYM , EXER_PRICE ,CLASS_SYM, EXPR_DATE, PUT_CALL_CODE)

Подход N1.
Упорядочивание по UNDER_SEC_SYM и EXER_PRICE используя хеширование и 1000 партиций
CREATE TABLE SQH_AF_1000
(
 ...
 UNDER_SEC_SYM VARCHAR2(10),					
 EXER_PRICE NUMBER(10,3),					
 CLASS_SYM VARCHAR2(10),				
 EXPR_DATE DATE,					
 PUT_CALL_CODE CHAR(1),
 PART_KEY VARCHAR2(20)
 )
 PARTITION BY HASH (PART_KEY)
PARTITIONS 1000;

Далее для работу с основной таблицей создаем вспомогательную таблицу:
CREATE TABLE AF_REF_PART
(
  UNDER_SEC_SYM  VARCHAR2(100 BYTE),
  EXER_PRICE     NUMBER(20,8),
  PART_KEY       VARCHAR2(30) , 
  CONSTRAINT AF_REF_PART_PK
  PRIMARY KEY
  (UNDER_SEC_SYM, EXER_PRICE)
  ENABLE VALIDATE
)
ORGANIZATION INDEX;

Вносим данные в таблицу, использую SQL
insert into AF_REF_PART(UNDER_SEC_SYM,EXER_PRICE,PART_KEY)
select UNDER_SEC_SYM,EXER_PRICE,UNDER_SEC_SYM||to_char(EXER_PRICE) from
(
select /*+ PARALLEL(a,8) */distinct UNDER_SEC_SYM,EXER_PRICE  from  SQH_AF_MIX  a
)
;

Строим VIEW позволяющее работать ТОЛЬКО со специфической партицией в зависимости от значения UNDER_SEC_SYM и EXER_PRICE:
create view SQH_AFV as
select * from SQH_AF_1000
where (UNDER_SEC_SYM,
EXER_PRICE,
PART_KEY) in (select * from AF_REF_PART);
[/REATE BITMAP INDEX SQH_AF_1000_B2 ON SQH_AF_1000
(CLASS_SYM, EXPR_DATE,  PUT_CALL_CODE)
  LOCAL;

Загрузка данных осуществлялась с.о :
INSERT INTO SQH_AF_1000
(.
UNDER_SEC_SYM ,
  EXER_PRICE ,
  PART_KEY  )
SELECT..
UNDER_SEC_SYM ,
  EXER_PRICE ,
  UNDER_SEC_SYM||to_char(EXER_PRICE)
FROM  SQH_AF_MIX  ;

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

Таб 2

Num_of_rows_per_Block Num_of_Blocks
1 59 59
2 78 156
3 74 222
4 116 464
5 140 700
6 179 1074
7 170 1190
8 198 1584
9 180 1620
10 216 2160
11 180 1980
12 202 2424
13 193 2509
14 159 2226
15 141 2115
16 100 1600
17 73 1241
18 69 1242
19 64 1216
20 37 740
21 27 567
22 21 462
23 19 437
24 15 360
25 8 200
26 7 182
27 7 189
28 5 140
29 3 87
31 2 62
32 2 64
35 2 70
36 1 36
40 1 40
41 1 41

2749 29459
Подход N2.
Тоже самый подход , что и Подход N1, но количество hash партиций равно 100 , вместо 1000.

Таб 3

Num_of_rows_per_Block Num_of_Blocks
1 1594 1594
2 1215 2430
3 978 2934
4 707 2828
5 566 2830
6 473 2838
7 376 2632
8 323 2584
9 232 2088
10 164 1640
11 97 1067
12 86 1032
13 65 845
14 36 504
15 23 345
16 17 272
17 10 170
18 7 126
19 4 76
20 7 140
21 3 63
22 5 110
23 4 92
24 3 72
26 2 52
28 1 28
33 1 33
34 1 34

7000 29459

Подход N3.
Идея данного подхода состоит не столько в нахождении в преимуществе по отношения к Подходу 1 или2 , сколько в иллюстрации другой возможности упорядочивания данных и возможно для других целей он как раз и будет наиболее целесообразным. Итак создаем таблицу с 3200 LIST PARTITIONS на UNDER_SEC_SYM. Т.о. мы распределили все данные как бы по «разным корзинам». Понятное дело что делаем последней партицию DEFAULT , где можем хранить до поры до времени данные вне существующего перечня UNDER_SEC_SYM. Можно, скажем раз в неделю, проверять наличие данных в DEFAULT партиции и дополнять существующий перечень партицией новыми.
Таб 4

Num_of_rows_per_Block Num_of_Blocks
1 8391 8391
2 3724 7448
3 1727 5181
4 873 3492
5 420 2100
6 207 1242
7 114 798
8 50 400
9 18 162
10 14 140
11 6 66
13 3 39

15547 29459
Результаты данного эксперимента по упорядочиванию данных приведены ниже в Табл. 5. SQL для теста был использован следующего вида:
select  * from
 SQH_AF_*****					
where UNDER_SEC_SYM='SPY'					
and EXER_PRICE=142					
and CLASS_SYM ='SPY'					
and EXPR_DATE ='16-MAR-2013'					
and PUT_CALL_CODE='C';

Табл. 5

Тест Кол-во выбранных рядов Физических Чтений Время Выполнения
Естественно Упорядоченные 29459 15592 00:35.2
Hash=1000(Подход N1) 29459 2757 00:04.3
Hash=100(Подход N2) 29459 7032 00:10.5
3200 UNDER_SEC_SYM(Подход N3) 29459 15678 00:35.4

Результаты теста показывают явное преимущество по упорядочиванию данных. Хотя Подход N3 и не показывает никаких преимуществ , для данного типа SQL, все же данный подход имеет интересное реальное использование. Его основными преимуществами являются:
1. Легкость дополнения новых партиций для новых UNDER_SEC_SYM
2. Экономиста время на построения индекса на UNDER_SEC_SYM для убыстрения выборки данных
3. Нет необходимости анализировать таблицу, что тоже экономит время
4. Явное преимущество чтения данных, т.к. за один физическое чтение (Physical Reads) ORACLE читает несколько блоков , в соответствии с INI ORACLE parameters, а не один в случае использования индекса.

Комментарии




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