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

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

теги:

Автор: AlexeiF

В данной части я буду продолжать излагать некоторые идеи по упорядочиванию данных , которые я использую на практике и которые дают неплохие результаты с т.з. производительности огромных баз данных (VLDB).
Для начала небольшое отступление от основной темы , но затрагивающая подходы далее излагаемые. Я был слегка удивлен работаю более внимательно с построением хэш партиций в Oracle, точнее с тем как Oracle назначает то или иное хэш значение использую свою внутреннюю, скрытую от пользователя, хэш функцию. Оказывается, что Oracle функция ORA_HASH(), работает совсем не так чем функция используемая Oracle при построении хэш партиций. Я имею ввиду следующую ситуацию. К примеру если у вас есть 10 различных значений и вы используете 10 значений для хэш, то очевидно, что каждому значению хэш от 1 до 10 у вас будет соответствовать единственное изначальное значение. Так и работает функция ORA_HASH(). Но так не работает внутренняя функция Oracle, при назначении хэш значения при использовании хэш партиций. И у вас могут попасть в одну партицию различные значения, которые по логике вещей и при использовании функции ORA_HASH() туда попадать не должны. Что я обнаружил, так это то, что внутренняя функция Oracle работает, так как мне хотелось бы, только со значениями кратными степени 2, т.е. со значениями типа 2,4,8,16 и т.д.

В связи с этим я дополнил некоторые подходы , описанные мною в предудущем сообщении. Прежде всего я попробовал упорядочить данные не только по двум колонкам ,а по всем колонкам используемых в индексе и создал количесво партиций кратным 2.
Итак
CREATE TABLE SQH_AF_HASH
(
 ...
 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 (CLASS_SYM,UNDER_SEC_SYM,EXER_PRICE,EXPR_DATE)
PARTITIONS <NUM_PART_HASH>;

Где под <NUM_PART_HASH> я использовал различные значения степени 2 для изучения "степени" упорядоченности данных. Как и ранее я использовал SQL , приведенный в 1-ой части, для исследования распределения данных по блокам. Результаты шриведенны ниже в таблице Табл. 6

Табл. 6

Тест Кол-во блоков небходимых прочитать
Естественно Упорядоченные 17473
NUM_PART_HASH = 256 1561
NUM_PART_HASH = 128 2962
NUM_PART_HASH = 64 5427
Вообщем почти линейная зависимость.

Далее я хочу предложить и описать идею как бы объединяющую Подход N3 из предыдущей части и вышеописанный. Часто бывает так, в данном случае вопрос вытек из реальной ситуации, что надо как-бы работать и быстро получать результаты под двум возможным критерия. Один, связан с обработкой данных по целому классу и второй получения данных в деталях. Говоря конкретно, необходимо с одной стороны агрегировать данные по UNDER_SEC_SYM, а с другой получить более конкретные данные в деталях ну типа что-то вроде этого
select  * from
 SQH_AF_*****					
where UNDER_SEC_SYM='SPY'
and trade_date='12-DEC-2012'					
and EXER_PRICE=142					
and CLASS_SYM ='SPY'					
and EXPR_DATE ='16-MAR-2013'					
and PUT_CALL_CODE='C';

Т.к. данные загружаются ежедневно, объемы я уже указывал, то была предложена некая динамическая модель упорядочивания данных. Для загрузки данных для конкретного дня создаются новые партиции по дню и символу
(UNDER_SEC_SYM) с хэш сабпартициями (SUBPARTITION) по (CLASS_SYM,UNDER_SEC_SYM,EXER_PRICE,EXPR_DATE). Причем можно использовать два динамических подхода. Первый состоит в том, что для загрузки по дню и символу будут выделены специальные партиции для ПЕРВЫХ заранее определяемых, скажем 10 из соображений даже не знаю каких, символов, а все остальные будут загружаться в отдельную партицию. Либо возможен другой подход. Определяется ПОРОГОВОЕ значение количества загружаемых рядов по символам и потом прежде чем создать партиции для загрузки определяется список символов попадающих под ОПРЕДЕЛЕННЫЙ критерий выбора, для выделения отдельной партиции. Скажем кол-во рядов для символа должно быть не менее 10 млн рядов на при загрузки текущего дня. Понятно , что такой список символов и их количество будет варьироваться дня ото дня. Ну, к примеру, что-то вроде этого, на TRADE_DATE='DD-MON-YYYY':

UNDER_SEC_SYM КОЛ-ВО рядов
---------- ----------
AAPL 92419782
SPY 53049416
IWM 25982964
GLD 18934107
ISRG 16941749
NFLX 16090382
PCLN 13994121
GOOG 13992741
FAS 13090826
DIA 11048924
AAFF 10048924
а все остальные будут загружаться в отдельную партицию.

Для работы с такой конструкцией нам понадобится, прежде всего,вспомогательная таблица, позволяющая соотносить для каждого TRADE_DATE и символа используемую партицию.
CREATE TABLE AF_LIST_HASH_REF
   (TRADE_DATE date ,
   UNDER_SEC_SYM  VARCHAR2(10 BYTE),
   PART_KEY NUMBER(16));

В рассматриваем примере данные в таблице имеют вид:
Табл. 7

TRADE_DATE UNDER_SEC_SYM PART_KEY
12/24/2012 SPY 2012122401
12/24/2012 SPX 2012122402
12/24/2012 SLV 2012122403
12/24/2012 SLB 2012122404
12/24/2012 SSO 2012122405
12/24/2012 OTHER 2012122450

И соотвественно основная таблица:
 CREATE TABLE SQH_AF_R_HASH
(
  TRADE_DATE              DATE                  NOT NULL,
  SQH_UID      NUMBER(20)            NOT NULL,
  .....
  UNDER_SEC_SYM           VARCHAR2(10 BYTE),
  CLASS_SYM               VARCHAR2(10 BYTE),
  EXPR_DATE               DATE,
  EXER_PRICE              NUMBER(20,2),
  PART_KEY 					NUMBER(16)
  )
  partition by range(part_key)
SUBPARTITION BY HASH (CLASS_SYM,UNDER_SEC_SYM,EXER_PRICE,EXPR_DATE)
(PARTITION P_20121224_1 VALUES LESS THAN (2012122402)
SUBPARTITIONS 64,
PARTITION P_20121224_2 VALUES LESS THAN (2012122403)
SUBPARTITIONS 16,
PARTITION P_20121224_3 VALUES LESS THAN (2012122404)
SUBPARTITIONS 16,
PARTITION P_20121224_4 VALUES LESS THAN (2012122405)
SUBPARTITIONS 16,
PARTITION P_20121224_5 VALUES LESS THAN (2012122406)
SUBPARTITIONS 16,
PARTITION P_20121224_OTHER VALUES LESS THAN (2012122451)
SUBPARTITIONS 256
);

Количество SUBPARTITIONS , понятное дело, чисто субъективное и опирается исключительно на опыт и некие соображения описанные выше.
Очевидно, что в партиции зашифрована дата поступления данные (TRADE_DATE) и некий порядковый номер UNDER_SEC_SYM в таблице AF_LIST_HASH_REF куда вставленны ряды в порядке убывания количества рядов по UNDER_SEC_SYM, в данном примере под вышеописанный критерий для данного дня 24-DEC-2012 попало 5 UNDER_SEC_SYM.
Перед загрузкой следующего дня 25-DEC-2012 данные анализируютса, автоматически с помощью специальный утилиты, и данные в таблице AF_LIST_HASH_REF перед загрузкой основной таблицы имеют вид:
Табл. 8

TRADE_DATE UNDER_SEC_SYM PART_KEY
12/24/2012 SPY 2012122401
12/24/2012 SPX 2012122402
12/24/2012 SLV 2012122403
12/24/2012 SLB 2012122404
12/24/2012 SSO 2012122405
12/24/2012 OTHER 2012122450
12/25/2012 AAPL 2012122501
12/25/2012 SPY 2012122502
12/25/2012 IWM 2012122503
12/25/2012 GLD 2012122504
12/25/2012 ISRG 2012122505
12/25/2012 NFLX 2012122506
12/25/2012 PCLN 2012122507
12/25/2012 GOOG 2012122508
12/25/2012 OTHER 2012122550

И соотвественно команды добавления партицию имеют вид:
alter table SQH_AF_R_HASH ADD PARTITION
 P_20121225_1 VALUES LESS THAN (2012122502)
SUBPARTITIONS 16;

alter table SQH_AF_R_HASH ADD PARTITION
 P_20121225_2 VALUES LESS THAN (2012122503)
SUBPARTITIONS 16;
.....
.....
alter table SQH_AF_R_HASH ADD PARTITION
 P_20121225_8 VALUES LESS THAN (2012122509)
 SUBPARTITIONS 16;
 
 alter table SQH_AF_R_HASH ADD PARTITION
 P_20121225_OTHER VALUES LESS THAN (2012122551)
SUBPARTITIONS 256; 

Для удобства работы было создана функция:
 CREATE OR REPLACE function F_af_list_hash(P_UNDER_SEC_SYM varchar2,P_trade_date date) return NUMBER IS
v_PART_KEY NUMBER :=NULL;
BEGIN
select PART_KEY into v_PART_KEY from AF_LIST_HASH_REF
where 
trade_date=P_trade_date
and
UNDER_SEC_SYM=P_UNDER_SEC_SYM;
  return v_PART_KEY;
exception
  when no_data_found then
     return to_char(P_trade_date,'YYYYMMDD')||to_char(50);
end;
/

Тогда основной SQL по выборки данных из основной таблицы будет выглядеть
 
 WITH af AS (select F_af_list_hash ('SLB', '24-DEC-2012') bb from dual)
  SELECT а.*
     FROM SQH_AF_R_HASH а, af
    WHERE PART_KEY =af.bb
    and ... 
	and ...
	...
 ;
 

возможно, конечно , создать полноценную LOOK-UP таблицу вида
create table AF_LIST_HASH_REF1 as 
select /*+ PARALLEL(a , 8) */ distinct trade_date,UNDER_SEC_SYM,
( CASE UNDER_SEC_SYM
    WHEN 'SPY' THEN 2012122401
    WHEN 'SPX' THEN 2012122402
        WHEN 'SLV' THEN 2012122403
            WHEN 'SLB' THEN 2012122404
                WHEN 'SSO' THEN 2012122405
                ELSE 2012122450
                END 
                ) part_kye
from SQH_AF_R_HASH a;

И пополнять таблицу AF_LIST_HASH_REF1 каждый день. В этом случае можно создать VIEW
CREATE OR REPLACE FORCE VIEW AF_F1 as 
select
 b.TRADE_DATE             
 ,a.SBT_QUOTE_HIST_UID     
....
..... 
 ,a.CLUSTR_CODE 
 ,b.part_key
from SQH_HIST_AF_R_HASH a, AF_LIST_HASH_REF1 b
where
a.part_key=b.part_key
;

В таком случае
select * from af_f1
where
UNDER_SEC_SYM='SJM'
and    TRADE_DATE='24-DEC-2012';

Дает EXPLAIN PLAN , где видно что мы БУДЕТ РАБОТАТЬ с ОДНОЙ партицией, а не с целой таблицей.
Plan
SELECT STATEMENT  ALL_ROWSCost: 75,206  Bytes: 1,720,834,802  Cardinality: 8,353,567  				
	5 HASH JOIN  Cost: 75,206  Bytes: 1,720,834,802  Cardinality: 8,353,567  			
		1 TABLE ACCESS FULL TABLE D03_SBT_OWNER.AF_LIST_HASH_REF1 Cost: 2  Bytes: 29  Cardinality: 1  		
		4 PARTITION RANGE SUBQUERY  Cost: 74,749  Bytes: 8,871,488,508  Cardinality: 50,121,404  Partition #: 3  Partitions accessed #KEY(SUBQUERY)		
			3 PARTITION HASH ALL  Cost: 74,749  Bytes: 8,871,488,508  Cardinality: 50,121,404  Partition #: 4  Partitions accessed #1 - #LAST	
				2 TABLE ACCESS FULL TABLE D03_SBT_OWNER.SBT_QUOTE_HIST_AF_R_HASH Cost: 74,749  Bytes: 8,871,488,508  Cardinality: 50,121,404  Partition #: 4  Partitions determined by Key Values

При таком подходе результаты теста,а именно "Кол-во блоков небходимых прочитать"=2636

Комментарии




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