Екатерина Кондратьева
ekondratieva@bk.ru
СОДЕРЖАНИЕ
К основным проблемным местам хранилища относят следующие:
Для "борьбы" со вышеперечисленными недостатками хранилищ данных предлагается разбивать факторные таблицы на секции и
периодически сжимать архивные секции. Необходимо отметить, что в хранилищах данных основной объем информации приходится именно на
факторные таблицы.
Далее будет рассмотрен законченный пример с помощью которого можно будет реально оценить эффективность предлагаемого подхода.
На схеме приведена структура факторной таблицы sample, которая содержит данные по продажам.
Данные по продажам формируются по четырем аналитикам: – "Время", "Продукт", "Клиент", "Отдел продаж". Минимальный уровень по измерению "Время" – год. Данные факторной таблицы можно разбить на логические диапазоны по годам. Т.е. в каждой секции таблицы sample будут содержаться данные за определенный год. Наибольшая производительность при работе с секционированными таблицами достигается при равномерном распределении данных по секциям. На схеме представлена таблица sample, секционированная по годам. Каждый раздел хранится в отдельном табличном пространстве. Секция year_current является загрузочной, то есть в текущий период времени (на рисунке - за 2007 год) данные из информационных источников поступают только в эту секцию; в остальных секциях хранятся архивные данные.
Перед созданием таблицы необходимо сформировать табличные пространства для ее секций.
Пример:create tablespace wh_fact_2001_data datafile 'с:oracleoradatadwawwh_fact_2001_data01.dbf' size 50M autoextend on next 10M maxsize 2048M extent management local segment space management auto; ... ... ...
В прилагаемом файле приведен полный cкрипт по созданию табличных пространств.
Далее создаем факторную таблицу sample.
Пример:create table sample ( time_id number(9), product_id number(9), customer_id number(9), department_id number(9), sale number ) nologging partition by range(time_id) ( partition year_2001values less than (-3995) nocompress tablespace wh_fact_2001_data, ... ... ...
В целях эксперимента, индексы на столбцы таблицы отсутствуют.
Законченный скрипт создания факторной таблицы приведен в файле.
Следующим шагом заполняем факторную таблицу sample случайными значениями.
Приведенный ниже код служит лишь для
заполнения данных в таблицу Sales; в реальной производственной системе загрузка данных будет происходить с помощью специализированных ETL-процедур.
... ... ... for v_xTimeId in 1..1005 loop for v_xVersionId in 1..10 loop for v_xProductId in 1..10 loop for v_xCustomerId in 1..10 loop for v_xDepartmentId in 1..20 loop v_xCount := v_xCount + 1; v_xFactValue := to_number(to_char(sysdate,'ss')); v_xTimeIds(v_xCount) := v_xTimeId; v_xProductIds(v_xCount) := v_xProductId; v_xCustomerIds(v_xCount) := v_xCustomerId; v_xDepartmentIds(v_xCount) := v_xDepartmentId; v_xFactValues(v_xCount) := v_xFactValue; if v_xCount = v_xCountLimit then v_xCount := 0; forall v_xIndex in 1..v_xCountLimit insert into sample values(v_xTimeIds(v_xIndex), v_xProductIds(v_xIndex), v_xCustomerIds(v_xIndex), v_xDepartmentIds(v_xIndex), v_xFactValues(v_xIndex)); end if; end loop; end loop; end loop; end loop; end loop; ... ... ...
Законченный скрипт приведен в файле по следующей ссылке.
Далее проверяем: какое количество записей содержится в секции year_current.
select count(*) from sample partition (year_current);
Итак, сейчас в таблице samples содержится 20 миллионов записей.
После вставки записей собираем статистику для таблицы sample. Для этого необходимо вызвать процедуру GATHER_TABLE_STATS из пакета DBMS_STATS.
Код для сбора статистики:begin DBMS_STATS.GATHER_TABLE_STATS (ownname => 'DEMO', tabname => 'sample'); end; /
Скрипт сбора статистики приведен в файле по следующей ссылке.
Повторно запускаем sql-запрос на подсчет количества записей в секции year_current и sql-запрос на поиск записи с кодом клиента для полного сканирования всей таблицы и анализируем время выполнения этих запросов.
select count(*) from sample partition (year_current); select * from sample partition (year_2007) where customer_id = 999;
Скрипты представлены в файле.
Время выполнения запроса А – 7,77 секунд (время сканирования секции year_current), время выполнения запроса B – 21,20 секунд (время полного сканирования таблицы).
Следующим шагом, с помощью оператора split partition секцию year_current разбиваем на две – на секцию year_2007, куда относятся значения показателя для 2007 года,
и на секцию year_current, готовую для следующей порции загружаемых данных. Предварительно создаем табличное пространство для данных 2007 года.
create tablespace wh_fact_2007_data datafile 'c:oracleoradatadwawwh_fact_2007_data01.dbf' size 50M autoextend on next 10M maxsize 2048M extent management local segment space management auto;
Скрипт по созданию табличного пространства приведен в файле.
Расщепляем партицию year_current на две, использую диапазон значений поля time_id, принадлежащих 2007 году.
alter table sample split partition year_current at (1005) into ( partition year_2007 tablespace wh_fact_2007_data, partition year_current );
Скрипт приведен в файле.
Следующим шагом иследования будет сжатие (compress) вновь полуенной архивной секции ( в нашем случае - year_2007) и выяснение: насколько это повысит быстродействие системы. Выясняем: сколько места на диске занимает секция year_2007, - для этого используем представление dba_segments.
select * from dba_segments where owner = 'DEMO';
Размер секции year_2007 составляет 464 Мб.
Далее, с помощью оператора, move partition производим сжатие секции year_2007.
alter table sample move partition year_2007 tablespace wh_fact_2007_data compress;
Повторно выясняем размер секции year_2007.
Теперь, после сжатия, размер секции year_2007 составляет 240 МБ. Таким образом, коэффициент сжатия равен 1,93.
После того, как поступит уведомление о том, что данные 2007 года больше модифицироваться не будут (т.е. этот период в учетных системах закрыт),
для ускорения времени выполнения запросов на чтение, и контроля на изменение данных, переведем табличное пространство wh_fact_2007_data в режим "только для чтения" (read only).
alter tablespace wh_fact_2007_data read only;
Проверяем как изменилось время выполнения тестовых запросов (файл).
Время выполнения запроса А – 3,11 секунд, время выполнения запроса B – 9,06 секунд.
Время выполнения запросов существенно сократилось - примерно в 2,5 раза !
Далее выполняем команду на уменьшение файла данных вновь полученного табличного пространства с архивными данными.
alter database datafile 'C:oracleoradatadwawwh_fact_current_data01.dbf' resize 245M;Заключение
Были получены следующие положительные результаты:
Законченные скрипты, использованные в статье, можно загрузить по следующей ссылке.
.
|
Программирование для чайников.
|