ОПТИМИЗАЦИЯ СТРУКТУРЫ ТАБЛИЦЫ ДЛЯ РАЗРАБОТКИ И ТЕСТИРОВАНИЯ ИСТОРИЧЕСКИХ/ОБЫЧНЫХ БАЗ ДАННЫХ.

добавлено: 08 май 16
понравилось:0
просмотров: 1804
комментов: 0

теги:

Автор: AlexeiF

Используемые понятия и постановка проблемы:
1. База данных (production) хранящая исторические данные - множество таблиц хранящая данные в партициях (partition) по бизнес дню/месяцу и т.д. Т.е для каждого дня/месяца создается партиция для хранения данных.
2. Данный подход позволяет работать и с обычными базами данных , не историческими. Ниже будет показан такой подход, он более простой
3. Среда разработки/тестирования - множество таблиц структурно/логически на уровне колонок и типа данных соответствующих production таблицам, но организованные в тестовой базе данных в схемах аналогичным production.
Например:
Операционная(Production) база данных имеет схемы:
DB_OWNER - схема для хранения всех таблиц и представление (view) на таблицу
ABC_JOBS - схема для подсоединения всех аппликаций и работы с таблицами INSERT/SELECT/UPDATE/DELETE данных в таблицах через их представление (view).
Среды разработок для операционной базы данных предъявлены в отдельной базе данных несколькими срезами, к примеру
DEV1_DB_OWNER 
DEV1_ABC_JOBS
.....
DEVm_DB_OWNER 
DEVm_ABC_JOBS

В реальной жизни это необходимо потому как в разработке участвуют несколько независимых групп разработчиков, которым необходимы отдельные среды для работы. Практика использования, при явных плюсах, такого подхода показала крайне большую громоздкость и значительные усилия по его поддержанию. Особенно это заметно для Datawerhouse, когда у вас сотни таблиц и партиции организованны так, что данные по одному дню хранятся в отдельной партиции. А если к этому еще добавляется несколько срезов в одной базе данных , то системный каталог Oracle разрастается до огромных размеров и если надо, к примеру, выполнить ALTER SYSTEM FLUSH SHARED POOL, то это иногда занимает часы. Добавление и удаление партиций для следующего месяца во всех срезах тоже задачка и громоздкая и достаточно длительная. Плюс ко всему нужно постоянно следить за тем чтобы логическая структура всех таблиц во всех срезах соответствовала операционной(production) базе данных. Не маловажным фактором считается также не оптимальное использование дискового пространства

Описанный далее подход позволяет уменьшить все перечисленные трудности за счет немного другой, физической организации базы данных для разработки и тестирования, при этом не меняя исходный текст программ, но увеличивая некую небольшую нагрузку на ORACLE DBA/DA, при имлиментации изменений в операционную базу данных.

Итак. Организация срезов для ****_JOBS схем остается прежней, т.е.
DEV1_ABC_JOBS
DEV2_ABC_JOBS
.....

DEVm_ABC_JOBS 

Но основная схема DB_OWNER создается одна и она соотвествует логически операционной базе данных на текущий момент, т.е. перечень таблиц, колонок с их типами данных и т.д. А вот физическая структура таблиц меняется следующим образом.
Допустим у нас в production есть таблица
CREATE TABLE DB_OWNER.AF_T
(
  C1          NUMBER,
  C2          NUMBER,
  BUS_DATE  DATE
)
PARTITION BY RANGE (BUS_DATE)
(  
  PARTITION P_AF_20160410  values less than ('11-APR-2016')
  ,  
  PARTITION P_AF_20160411 values less than ('12-APR-2016')
  ,  
  PARTITION P_AF_20160412 values less than ('13-APR-2016')
  ,  
......
);

тогда в базе данных для разработки с тремя , к примеру, срезами DEV1, DEV2, DEV3 мы создаем таблицу со следующей физической структурой
CREATE TABLE DB_OWNER.AF_T
(
  C1          NUMBER,
  C2          NUMBER,
  BUS_DATE  DATE,
  KEY         VARCHAR2(16 BYTE)
)
PARTITION BY LIST (KEY)
(  
  PARTITION P_DEV1_1 VALUES ('DEV1_1')
     ,  
  PARTITION P_DEV1_2 VALUES ('DEV1_2')
     ,  
  PARTITION P_DEV2_1 VALUES ('DEV2_1')
     ,  
  PARTITION P_DEV2_2 VALUES ('DEV2_2')
     , 
  PARTITION P_DEV3_1 VALUES ('DEV3_1')
     ,  
  PARTITION P_DEV3_2 VALUES ('DEV3_2')
);

В данном примере предполагается, что для начала мы создаем срезы для тестирования по двум различным BUS_DATE.
Следующей этап состоит в создании таблицы ссылок между BUS_DATE, названием среза и партиции по каждой таблицы.
Таблица: REF_DEV
BUS_DATE             KEY              ENV_NAME         TABLE_NAME                                                       
11-APR-2016              DEV1_1           DEV1             AF_T                                                             
12-APR-2016              DEV1_2           DEV1             AF_T                                                             
11-APR-2016              DEV2_1           DEV2             AF_T                                                             
12-APR-2016              DEV2_2           DEV2             AF_T 
11-APR-2016              DEV3_1           DEV3             AF_T                                                             
12-APR-2016              DEV3_2           DEV3             AF_T     
......
Далее создаютса VIEW по одному на каждый срез и каждую таблицу.

CREATE OR REPLACE VIEW AF_DEV1
AS SELECT A.* FROM AF_T A,REF_DEV B 
WHERE
A.KEY=B.KEY
AND B.TABLE_NAME='AF_T'
AND B.ENV_NAME = 'DEV1';

CREATE OR REPLACE VIEW AF_DEV2
AS SELECT A.* FROM AF_T A,REF_DEV B 
WHERE
A.KEY=B.KEY
AND B.TABLE_NAME='AF_T'
AND B.ENV_NAME = 'DEV2';

CREATE OR REPLACE VIEW AF_DEV3
AS SELECT A.* FROM AF_T A,REF_DEV B 
WHERE
A.KEY=B.KEY
AND B.TABLE_NAME='AF_T'
AND B.ENV_NAME = 'DEV3';

И далее SYNONYM
create or reaplace synonym DEV1_ABC_JOBC.AF for DB_OWNER.AF_DEV1;
create or reaplace synonym DEV2_ABC_JOBC.AF for DB_OWNER.AF_DEV2;
create or reaplace synonym DEV3_ABC_JOBC.AF for DB_OWNER.AF_DEV3;

Для поддержки , к примеру, INSERT команды вам необходимо создать TRIGGER на соотвествующий VIEW.

CREATE OR REPLACE TRIGGER AF_DEV2_TRG
   INSTEAD OF INSERT ON AF_DEV2
   BEGIN
		CASE :NEW.BUS_DATE
			WHEN '11-APR-2016' THEN INSERT INTO AF_T VALUES(:NEW.C1,:NEW.C2,:NEW.BUS_DATE,'DEV2_1');
			WHEN '12-APR-2016' THEN INSERT INTO AF_T VALUES(:NEW.C1,:NEW.C2,:NEW.BUS_DATE,'DEV2_2');
			ELSE dbms_output.put_line('No such BUS date');
		END CASE;
   END;
   /

Теперь если вы под-соединитесь к срезу DEV1 вы можете выполнять команды INSERT/SELECT используя BUS_DATE колонку в основной таблице AF_T.

При имлипментации в production вам необходимо будет создать таблицу AF_T используя выше указанный DDL для CREATE TABLE и постоить VIEW следующего вида
CREATE OR REPLACE VIEW AF_V AS SELECT A.* FROM AF_T A;
и
create or replace PUBLIC synonym AF for DB_OWNER.AF_V;

Понятно, что код программы при таком подходе не меняется.

ЗАМЕЧАНИЯ:
1. Понятно, что если у вас закончился цикл разработки или вам необходимо использовать другой BUS_DATE, то вам надо сделать изменения в таблице REF_DEV и если неоходимо то перестроить TRIGGER AF_DEV2_TRG
2. Понятно, что если вам не хватает 2-х BUS_DATE или же вам нужно дополнительный срез для разработки/тестирования, то вам необходимо вначале добавить соответствующую партицию в таблицу , дополнить таблицу REF_DEV новой ссылкой, создать новое VIEW, если это новый срез и обновить/создать новый TRIGGER, если необходимо.
3. Синхронизировать таблицы с production в таком подходе намного проще чем с множеством таблиц в нескольких срезах.
4. Если несколько групп разработчиков работает с одной и той же таблицей используя различные срезы, то понятно что логическая структура таблицы должна соответствовать всем изменениям для различных групп, а через VIEW для каждого среза можно высвечивать колонки только для данной группы разработчиков в данном срезе.
5. При описании ситуации в 4 , если вдруг новая дополнение к таблице требует использование колонки NOT NULL, то эта проблема легко обходится для другой группы разработчиков, не использующих новую колонку , дополнением DEFAULT value для новой колонки. Очевидно , что при имлиментации в production DEFAULT нужно будет убрать. Собственный практический опыт показывает , что такая ситуация крайне редка.
6. Выше описанная достаточно сложная структура таблиц использующая партиции по BUS_DATE. Для обычных таблиц , нужно убрать колонку
BUS_DATE в REF_DEV таблице и соотвественно изменим VALUES для LIST партиций в основной таблице.

Комментарии




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