Повышение производительности запросов DB2 в среде интеллектуального бизнес-анализа

Специализированные методы оптимизации запросов для хранилищ данных и бизнес-анализа

Эффективное выполнение больших запросов является ключевым фактором повышения производительности в среде интеллектуального бизнес-анализа. Узнайте, как повысить производительность обработки запросов сервером IBM® DB2® в такой среде. Изучите шаг за шагом различные методы, а затем проведите эксперименты с собственной системой. Каждый метод применяется к одному запросу SQL, производительность изменяется с помощью инструмента db2batch.

Винсент Корвинелли, консультант по разработке программного обеспечения, IBM

Винсент Корвинелли (Vincent Corvinelli) является консультантом по разработке программного обеспечения в группе DB2® для Linux®, UNIX®, Windows® (DB2) Optimizer подразделения IBM Canada. Он обладает огромным опытом в диагностировании и решении проблем клиентов.



Самир Капур, ведущий аналитик службы поддержки DB2 UDB, IBM

Самир Капур (Samir Kapoor) – сертифицированный технический эксперт IBM по DB2® UDB для Linux®, UNIX® и Windows®. В настоящее время Самир работает в группе DB2 UDB Advanced Support - Down system division (DSD). Он обладает глубокими знаниями в области механизма системы.



29.05.2007

Введение

Эта статья посвящена методам, которые позволяют эффективно выполнять большие запросы в системах поддержки принятия решений (DSS). Как правило, такие запросы представляют собой исключительно SELECT-запросы, обращающиеся к значительным объемам данных. Перечислим методы, которые мы будем рассматривать:

  1. Установка соответствующих ограничений ссылочной целостности
  2. Использование материализованных таблиц запросов (MQT) для репликации таблиц в другие разделы базы данных, что позволяет создавать соседские соединения (collocated joins) по полям, не являющимся ключами разбиения
  3. Использование многомерной кластеризации (MDC)
  4. Использование разбиения таблиц (новой функции DB2® 9)
  5. Использование разбиения таблиц в сочетании с многомерной кластеризацией
  6. Использование MQT для предварительного расчета агрегированных результатов

Примеры, приведенные в этой статье, относятся к DB2 9, работающей на платформе Windows. Однако основные идеи и сведения применимы к любой платформе. Кроме того, для распределения данных по нескольким физическим или логическим разделам в наших примерах использовалась функция разбиения базы данных DB2 (Database Partitioning Feature, DPF), поскольку DPF применяется в большинстве сред бизнес-анализа.


Установка и структура базы данных

В этом разделе описывается физическая и логическая структура базы данных, используемой для выполнения проверок на нашей системе.

Подробное описание схемы типа "звезда"

Была использована следующая схема типа "звезда":

Листинг 1. Схема типа "звезда"
                                  PRODUCT_DIM             DATE_DIM
                                            \            /
                                             \          /
                                              SALES_FACT
                                                  |
                                                  |
		                             STORE_DIM

таблицы в которой определены следующим образом:

НАЗВАНИЕ ТАБЛИЦЫТИПНАЗВАНИЕ ПОЛЯТИП ДАННЫХОПИСАНИЕ ПОЛЯ
SALES_FACTFACT TABLEDATE_IDDATEДата продажи продукта.
PRODUCT_IDINTИдентификатор приобретенного продукта.
STORE_IDINTИдентификатор магазина, в котором был приобретен продукт.
QUANTITYINTКоличество продукта, проданного в рамках этой операции.
PRICEINTЦена покупки продукта. [Для простоты использовано целочисленное поле, однако более уместным было бы поле в десятичном формате]
TRANSACTION_DETAILSCHAR(100)Описание/подробная информация об операции.
DATE_DIMDIMENSION TABLEDATE_ID NOT NULLDATEУникальный идентификатор
MONTHINTМесяц, к которому относится запись даты.
QUARTERINTКвартал (1-й, 2-й, 3-й или 4-й), к которому относится запись даты.
YEARINTГод, к которому относится запись даты.
PRODUCT_DIMDIMENSION TABLEPRODUCT_ID NOT NULLINTУникальный идентификатор продукта.
PRODUCT_DESCCHAR(20)Описание продукта.
MODELCHAR(200)Модель продукта.
MAKECHAR(50)Производитель продукта.
STORE_DIMDIMENSION TABLESTORE_ID NOT NULLINTУникальный идентификатор магазина.
LOCATIONCHAR(15)Место расположения магазина.
DISTRICTCHAR(15)Район, к которому относится магазин.
REGIONCHAR(15)Регион, к которому относится магазин.

В таблице фактов SALES_FACT содержится суммарная информация о продажах за 2006 год. В ней размещаются данные о дате продажи продукта, идентификаторе продукта и магазина, продавшего этот продукт, количестве проданного продукта и его цене. Поле TRANSACTION_DETAILS специально добавлено к таблице фактов для того, чтобы увеличить поток ввода-вывода при обращении к данным этой таблицы.

В таблице размерности DATE_DIM содержатся уникальные даты, когда были открыты магазины, с указанием соответствующего месяца, квартала и года.

В таблице размерности PRODUCT_DIM содержится информация о различных продуктах, предлагаемых компанией. У каждого продукта есть уникальный идентификатор, описание, модель и производитель.

В таблице размерности STORE_DIM содержатся идентификаторы различных магазинов, а также информация о месте их расположения, районе и области.

Информация о разделах базы данных
НАЗВАНИЕ ГРУППЫ РАЗДЕЛОВ БАЗЫ ДАННЫХНОМЕР РАЗДЕЛА БАЗЫ ДАННЫХ
FACT_GROUP0,1,2,3
DATE_GROUP1
PRODUCT_GROUP2
STORE_GROUP3

Каждая таблица хранится в собственной группе разделов. Три таблицы размерностей невелики, поэтому они хранятся в одном разделе базы данных. Напротив, таблица фактов распределена на все четыре раздела.

Информация о табличных пространствах
НАЗВАНИЕ ТАБЛИЧНОГО ПРОСТРАНСТВАГРУППА РАЗДЕЛОВ БАЗЫ ДАННЫХТАБЛИЦА
FACT_SMSFACT_GROUPSALES_FACT
DATE_SMSDATE_GROUPDATE_DIM
PRODUCT_SMSPRODUCT_GROUPPRODUCT_DIM
STORE_SMSSTORE_GROUPSTORE_DIM

Каждая таблица хранится в собственном табличном пространстве. Распространенный альтернативный метод заключается в группировании трех таблиц размерностей в единое табличное пространство.

Информация о буферном пуле

В ходе испытаний использовался стандартный буферный пул IBMDEFAULTBP, состоящий из 1000 страниц по 4K. В наших испытаниях все табличные пространства использовали один и тот же буферный пул. В типичной среде бизнес-анализа создаются раздельные буферные пулы.

Основной запрос

Для проверки различных методов, рассматриваемых в этой статье, используется приведенный ниже запрос. Этот запрос выполняет внешнее соединение для сравнения информации по продажам в десяти определенных магазинах в январе и в ноябре.

Листинг 2. Основной запрос [Query1.sql]
WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS
( SELECT
     D.MONTH AS MONTH, 
     S.STORE_ID AS STORE_ID,
     S.DISTRICT AS DISTRICT,
     S.REGION AS REGION,
     SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT

  FROM
     SKAPOOR.SALES_FACT F1,
     SKAPOOR.DATE_DIM D,
     SKAPOOR.PRODUCT_DIM P,
     SKAPOOR.STORE_DIM S
     
  WHERE
     P.MODEL LIKE '%model%' AND
     F1.DATE_ID=D.DATE_ID AND
     F1.PRODUCT_ID=P.PRODUCT_ID AND
     F1.STORE_ID=S.STORE_ID AND
     F1.DATE_ID BETWEEN '2006-01-01' AND '2006-01-31' AND
     F1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND		
     D.MONTH = 1 

  GROUP BY
     S.STORE_ID,S.DISTRICT,S.REGION,D.MONTH) ,
     
TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS
( SELECT 
     D1.MONTH AS MONTH,
     S1.STORE_ID AS STORE_ID,
     S1.DISTRICT AS DISTRICT,
     S1.REGION AS REGION,
     SUM(F2.QUANTITY * F2.PRICE) AS AMOUNT

  FROM
     SKAPOOR.SALES_FACT F2,
     SKAPOOR.DATE_DIM D1,
     SKAPOOR.PRODUCT_DIM P1,
     SKAPOOR.STORE_DIM S1

  WHERE
     P1.MODEL LIKE '%model%' AND
     F2.DATE_ID=D1.DATE_ID AND
     F2.PRODUCT_ID=P1.PRODUCT_ID AND
     F2.STORE_ID=S1.STORE_ID AND
     F2.DATE_ID BETWEEN '2006-11-01' AND '2006-11-30' AND
     F2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND		
     D1.MONTH=11

  GROUP BY
     S1.STORE_ID,S1.DISTRICT,S1.REGION,D1.MONTH) 

SELECT 
     A.*, 
     B.*
FROM
     TMP1 A LEFT OUTER JOIN TMP2 B ON
       (A.STORE=B.STORE AND A.REGION=B.REGION AND A.DISTRICT=B.DISTRICT)
ORDER BY A.AMOUNT_1 DESC, B.AMOUNT_11 DESC;

Настройка среды

Тесты производились в следующей среде:

Листинг 3. db2level
DB2 9 Enterprise Edition:

DB21085I  Instance "DB2" uses "32" bits and DB2 code release "SQL09010" with
level identifier "02010107".
Informational tokens are "DB2 9.1.0.356", "s060629", "NT32", and Fix Pack "0".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".
Листинг 4. Операционная система
System: WIN32_NT SKAPOOR Service Pack 2 5.1 x86 Family 15, model 4, stepping 3
Листинг 5. Аппаратное обеспечение
CPU: total:2 online:2 Cores per socket:1 Threading degree per core:2
Physical Memory(MB): total:2551 free:1988 available:1949
Virtual  Memory(MB): total:4950 free:6575
Swap     Memory(MB): total:2399 free:4587
1 Physical disk Size 100GB

Требования к дисковому пространству

Для воспроизведения всех тестов, приведенных в данной статье, для файлов данных и журнала требуется до 20 ГБ дискового пространства. Для файлов журнала выделяется до 13 ГБ. Использовалось круговое ведение журнала, с выделением 100 основных журналов:

Листинг 6. Конфигурация журналов базы данных
Log file size (4KB)                         (LOGFILSIZ) = 8192
Number of primary log files                (LOGPRIMARY) = 100
Number of secondary log files               (LOGSECOND) = 150

Вы можете сократить объем дискового пространства, занимаемого файлами журнала, внеся небольшие изменения в сценарии заполнения таблицы фактов. Мы рассмотрим этот вопрос ниже в этой статье.


Настройка базы данных

На первом шаге необходимо создать тестовую базу данных.

В наших примерах было создано четыре логических раздела данных. Убедитесь, что в файле etc\services указано достаточно портов для создания четырех разделов данных. В нашей среде в файле C:\WINDOWS\system32\drivers\etc\services для экземпляра "DB2" содержалась следующая запись:

Листинг 7. Содержимое файла services
DB2_DB2           60000/tcp
DB2_DB2_1         60001/tcp
DB2_DB2_2         60002/tcp
DB2_DB2_END       60003/tcp
DB2c_DB2          50000/tcp

Чтобы добавить раздел к этому экземпляру, использовалась следующая команда DB2 CLP:

Листинг 8. Создание разделов базы данных с помощью команды db2ncrt
db2stop 
db2ncrt /n:1 /u:username,password /i:DB2 /m:machine /p:1
db2ncrt /n:2 /u:username,password /i:DB2 /m:machine /p:2
db2ncrt /n:3 /u:username,password /i:DB2 /m:machine /p:3

Необходимо указать имя пользователя и пароль для параметра /u, имя компьютера для параметра /m и имя экземпляра для параметра /i, соответствующие вашей среде.

Создание базы данных

Создайте базу данных DSS_DB. Для хранения базы данных использовался диск D:. Если в вашей среде необходимо использовать другой диск, внесите соответствующие изменения.

Листинг 9. Команда на создание базы данных
db2 create database dss_db on D:\;

База данных и диспетчер базы данных настраиваются в соответствии с приведенной ниже конфигурацией. Для внесения изменений в настройки базы данных и диспетчера базы данных во всех разделах базы данных используется инструмент db2_all.

Листинг 10. Запросы на изменение конфигурации диспетчера базы данных
db2_all update dbm cfg \
     using cpuspeed 2.282997e-007 intra_parallel NO comm_bandwidth 100.000000

db2_all update db cfg for DSS_DB \
     using locklist 2450 dft_degree 1 maxlocks 60 \
           avg_appls 1 stmtheap 16384 dft_queryopt 5

Создание табличных пространств и групп разделов базы данных

Выполните приведенные ниже запросы для создания табличных пространств и групп разделов базы данных. Вы можете скопировать их в файл с именем STORAGE.ddl, после чего выполнить его командой:

db2 -tvf STORAGE.ddl -z storage.log
Листинг 11. Запросы на создание табличных пространств и групп разделов базы данных
CONNECT TO DSS_DB;

--------------------------------------------------
-- DDL Statements for DATABASE PARTITION GROUPS --
--------------------------------------------------
 
CREATE DATABASE PARTITION GROUP "FACT_GROUP" ON DBPARTITIONNUMS 
		(0,
		 1,
		 2,
		 3);

CREATE DATABASE PARTITION GROUP "DATE_GROUP" ON DBPARTITIONNUMS 
		(1);

CREATE DATABASE PARTITION GROUP "PRODUCT_GROUP" ON DBPARTITIONNUMS 
		(2);

CREATE DATABASE PARTITION GROUP "STORE_GROUP" ON DBPARTITIONNUMS 
		(3);

COMMIT WORK;

------------------------------------
-- DDL Statements for TABLESPACES --
------------------------------------

CREATE REGULAR TABLESPACE FACT_SMS IN DATABASE PARTITION GROUP FACT_GROUP
    PAGESIZE 4096 MANAGED BY SYSTEM 
	 USING ('d:\database\fact_tbsp0') ON DBPARTITIONNUMS (0)
	 USING ('d:\database\fact_tbsp1') ON DBPARTITIONNUMS (1)
	 USING ('d:\database\fact_tbsp2') ON DBPARTITIONNUMS (2)
	 USING ('d:\database\fact_tbsp3') ON DBPARTITIONNUMS (3)
	 EXTENTSIZE 32
	 PREFETCHSIZE AUTOMATIC
	 BUFFERPOOL IBMDEFAULTBP
	 OVERHEAD 7.500000
	 TRANSFERRATE 0.060000 
	 NO FILE SYSTEM CACHING  
	 DROPPED TABLE RECOVERY ON;

CREATE REGULAR TABLESPACE DATE_SMS IN DATABASE PARTITION GROUP
DATE_GROUP PAGESIZE 4096 MANAGED BY SYSTEM 
	USING ('d:\database\date_group') ON DBPARTITIONNUMS (1)
	EXTENTSIZE 32
	 PREFETCHSIZE AUTOMATIC
	 BUFFERPOOL IBMDEFAULTBP
	 OVERHEAD 7.500000
	 TRANSFERRATE 0.060000 
	 NO FILE SYSTEM CACHING  
	 DROPPED TABLE RECOVERY ON;

CREATE REGULAR TABLESPACE PRODUCT_SMS IN DATABASE PARTITION GROUP
PRODUCT_GROUP PAGESIZE 4096 MANAGED BY SYSTEM 
	USING ('d:\database\product_group') ON DBPARTITIONNUMS (2)
	EXTENTSIZE 32
	 PREFETCHSIZE AUTOMATIC
	 BUFFERPOOL IBMDEFAULTBP
	 OVERHEAD 7.500000
	 TRANSFERRATE 0.060000 
	 NO FILE SYSTEM CACHING  
	 DROPPED TABLE RECOVERY ON;

CREATE REGULAR TABLESPACE STORE_SMS IN DATABASE PARTITION GROUP
STORE_GROUP PAGESIZE 4096 MANAGED BY SYSTEM 
	USING ('d:\database\store_group') ON DBPARTITIONNUMS (3)
	EXTENTSIZE 32
	 PREFETCHSIZE AUTOMATIC
	 BUFFERPOOL IBMDEFAULTBP
	 OVERHEAD 7.500000
	 TRANSFERRATE 0.060000 
	 NO FILE SYSTEM CACHING  
	 DROPPED TABLE RECOVERY ON;

COMMIT WORK;

-- Mimic tablespace

ALTER TABLESPACE SYSCATSPACE
      PREFETCHSIZE AUTOMATIC
      OVERHEAD 7.500000
      TRANSFERRATE 0.060000;


ALTER TABLESPACE TEMPSPACE1
      PREFETCHSIZE AUTOMATIC
      OVERHEAD 7.500000
      TRANSFERRATE 0.060000;


ALTER TABLESPACE USERSPACE1
      PREFETCHSIZE AUTOMATIC
      OVERHEAD 7.500000
      TRANSFERRATE 0.060000;

COMMIT WORK;

------------------------------------------------
-- Update the bufferpool to use 1000 4K pages --
------------------------------------------------

ALTER BUFFERPOOL IBMDEFAULTBP SIZE 1000;

COMMIT WORK;
CONNECT RESET;

Примечание: Табличные пространства определяются с параметром "NO FILE SYSTEM CACHING", что позволяет исключить искажение результатов тестирования различных методов, вызванное кэшированием файловой системы.

Измерение производительности с помощью инструмента db2batch

Для запуска основного запроса в листинге 2 используется программа db2batch. Чтобы выполнить запрос с помощью команды db2batch, необходимо сохранить запрос в файле, заканчивающемся точкой с запятой, и задать следующие параметры, которые укажут db2batch на необходимость регистрации времени:

Листинг 12. Использование db2batch для измерения производительности выполнения запроса
db2batch -d <dbname> -f <input_file>
 -i <elapsed_time> -iso <isolation level>
-o p <perf_detail> o <optlevel> r
 <rows_out> -r <result_file>

Здесь <dbname> - это имя базы данных, а<input_file> - имя файла, содержащего запрос и заканчивающегося точкой с запятой.

  • -iso <isolation level> (уровень изоляции):
    В наших примерах по умолчанию устанавливался уровень изоляции CS, однако инструмент db2batch по умолчанию использует уровень изоляции RR. При выполнении запросов с уровнем изоляции RR материализованные таблицы запросов, созданные с уровнем изоляции CS, учитываться не будут. Чтобы обойти эту проблему, необходимо указать в команде db2batch параметр -iso со значением CS, чтобы уровень изоляции соответствовал выбранному для MQT. Кроме того, в случае, если в вашем приложении используется уровень изоляции CS, запуск db2batch без параметра -iso, то есть с уровнем изоляции RR, может приводить к возникновению блокировок.
  • Параметры -o (опции):
    • p <perf_detail>: Подробная информация о производительности. Возвращает моментальную копию диспетчера базы данных, базы данных, приложения и запроса (последний возвращается только в том случае, если отключена функция автоматического завершения транзакций, и выполняются единичные запросы, а не блоки запросов). Также возвращает моментальную копию буферных пулов, табличных пространств и FCM (копия FCM создается только в среде с несколькими разделами базы данных). __Мы использовали самый высокий уровень детализации, например, p 5, однако вы можете указать другое значение.
    • o <optlevel>: Уровень оптимизации запроса. (В статье используется уровень оптимизации 5, который не нужно указывать специально, поскольку он является уровнем, назначаемым в базе данных по умолчанию, как описано в Листинге 10.)
    • r <rows_out>: Количество извлекаемых строк, отправляемых на выход. В нашем случае мы не извлекаем строки, то есть r 0.
  • -r <result_file>: Файл результатов. В нашем примере db2batch будет выводить результат работы в файл results.txt.

В этой статье мы используем следующую команду:

db2batch -d DSS_DB -f QUERY1.SQL -i complete -iso CS -o p 5 o 5 r 0 -r <output filename>

Методы повышения производительности запросов

В этом разделе мы подробно рассмотрим различные методы повышения производительности запроса, описанного в Листинге 2. Прежде чем приступить к обсуждению этих методов, необходимо создать таблицу фактов и таблицы размерностей.

ШАГ A: После создания табличных пространств создайте таблицу фактов и таблицы размерностей описываемым ниже способом. Вы можете изменить название схемы с SKAPOOR на любое другое, подходящее для вашей среды. Если вы делаете это, не забудьте изменить запрос в Листинге 2, указав в нем новое имя схемы. Вы можете скопировать приведенные ниже запросы в файл TEST1.ddl и выполнить его с помощью команды:

db2 -tvf TEST1.ddl -z test1.log
Листинг 13. Содержимое файла TEST1.ddl
CONNECT TO DSS_DB;

---------------------------------------------------
-- DDL Statements for table "SKAPOOR "."SALES_FACT"
---------------------------------------------------
 

CREATE TABLE "SKAPOOR "."SALES_FACT"  (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )   
		 DISTRIBUTE BY HASH("DATE_ID")   
		   IN "FACT_SMS" ;

-------------------------------------------------
-- DDL Statements for table "SKAPOOR "."DATE_DIM"
-------------------------------------------------
 

CREATE TABLE "SKAPOOR "."DATE_DIM"  (
		  "DATE_ID" DATE NOT NULL , 
		  "MONTH" INTEGER , 
		  "QUARTER" INTEGER , 
		  "YEAR" INTEGER )   
		 IN "DATE_SMS" ; 


-- DDL Statements for primary key on Table "SKAPOOR "."DATE_DIM"
-- DATE_ID is the unique identifier
ALTER TABLE "SKAPOOR "."DATE_DIM" 
	ADD PRIMARY KEY
		("DATE_ID");



----------------------------------------------------
-- DDL Statements for table "SKAPOOR "."PRODUCT_DIM"
----------------------------------------------------
 

CREATE TABLE "SKAPOOR "."PRODUCT_DIM"  (
		  "PRODUCT_ID" INTEGER NOT NULL , 
		  "PRODUCT_DESC" CHAR(20) , 
		  "MODEL" CHAR(10) , 
		  "MAKE" CHAR(10) )   
		 IN "PRODUCT_SMS" ; 


-- DDL Statements for primary key on Table "SKAPOOR "."PRODUCT_DIM"
-- PRODUCT_ID is the unique identifier
ALTER TABLE "SKAPOOR "."PRODUCT_DIM" 
	ADD PRIMARY KEY
		("PRODUCT_ID");



--------------------------------------------------
-- DDL Statements for table "SKAPOOR "."STORE_DIM"
--------------------------------------------------
 

CREATE TABLE "SKAPOOR "."STORE_DIM"  (
		  "STORE_ID" INTEGER NOT NULL , 
		  "LOCATION" CHAR(15) , 
		  "DISTRICT" CHAR(15) , 
		  "REGION" CHAR(15) )   
		 IN "STORE_SMS" ; 


-- DDL Statements for primary key on Table "SKAPOOR "."STORE_DIM"
-- STORE_ID is the unique identifier
ALTER TABLE "SKAPOOR "."STORE_DIM" 
	ADD PRIMARY KEY
		("STORE_ID");


COMMIT WORK;

CONNECT RESET;

ШАГ B: После создания таблиц необходимо добавить данные в таблицы размерностей, как показано ниже, и скорректировать схему в соответствии с вашей средой:

Листинг 14. Заполнение таблицы DATE_DIM
db2 -td@ -vf date_insert.txt -z date_insert.log
Листинг 15. Заполнение таблицы PRODUCT_DIM
db2 -td@ -vf product_insert.txt -z product_insert.log
Листинг 16. Заполнение таблицы STORE_DIM
db2 -td@ -vf store_insert.txt -z store_insert.log

Содержимое трех файлов выглядит так:

Таблица DATE_DIM заполняется значениями для всех 365 дней в 2006 году.

Листинг 17. Содержимое файла date_insert.txt
connect to dss_db@

begin atomic
  declare cnt INT default 1;
  declare dat DATE default '01/01/2006';
  declare yer INT default 2006;
  declare quart INT default 1;

while (cnt <= 365) do    
    if (int(dat + cnt DAYS)/100) between 200601 and 200603 then
           set quart=1;
    elseif (int(dat + cnt DAYS)/100) between 200604 and 200606 then
           set quart=2;
    elseif (int(dat + cnt DAYS)/100) between 200607 and 200609 then
           set quart=3;
    elseif (int(dat + cnt DAYS)/100) between 200610 and 200612 then
           set quart=4;    
    end if;
    
    insert into SKAPOOR.DATE_DIM values (
	dat + cnt DAYS,
	(int(dat + cnt DAYS)/100) - 200600,
	quart,
	yer
    );
       
    set cnt=cnt+1;
end while;

end@

connect reset@

Таблица PRODUCT_DIM заполняется данными 60 000 продуктов.

Листинг 18. Содержимое файла product_insert.txt
connect to dss_db@

drop sequence seq1@
drop sequence seq2@

create sequence seq1 as integer start with 1 increment by 1@
create sequence seq2 as integer start with 1 increment by 1@

begin atomic
   declare cnt INT default 1;

   while (cnt < 60001) do
	insert into SKAPOOR.PRODUCT_DIM values (
	    nextval for SEQ2,
	    'product desc' concat char(nextval for SEQ1),
            'model ' concat char(integer(rand()*1000)),
	    'maker ' concat char(integer(rand()*500))
	);
 	set cnt=cnt+1;
    	end while;
end@

drop sequence seq1@
drop sequence seq2@

connect reset@

Таблица STORE_DIM заполняется данными 201 магазина.

Листинг 19. содержимое файла store_insert.txt
connect to dss_db@

drop sequence seq2@

create sequence seq2 as integer start with 0 increment by 1@

begin atomic
    declare cnt INT default 1;

    while (cnt < 202) do
       insert into SKAPOOR.STORE_DIM values (
	    nextval for SEQ2,
	    'location' concat char(integer(rand()*500)),
	    'district' concat char(integer(rand()*10)),
	    'region' concat char(integer(rand()*5))
       );
       set cnt=cnt+1;

    end while;
end@

drop sequence seq2@

connect reset@

ШАГ C: Введите данные в таблицу SALES_FACT. Скорректируйте схему в соответствии с вашей средой. В нашей тестовой среде заполнение таблицы фактов заняло примерно 1,5 часа.

Листинг 20. Заполнение таблицы SALES_FACT
db2 -td@ -vf sales_fact_insert.ddl -z sales_fact_insert.log
Листинг 21. Содержимое файла sales_fact_insert.ddl
connect to dss_db@

VALUES (CURRENT TIMESTAMP)@

begin atomic

   declare cnt INT default 1;
   declare cnt1 INT default 1;
   declare dat DATE default '01/01/2006';

   while (cnt <= 365) do    
    
    INSERT INTO SKAPOOR.SALES_FACT
    with v(DATE_ID, PRODUCT_ID, STORE_ID, QUANTITY, PRICE, TRANSACTION_DETAILS, U_ID) as
         (values(dat + CNT1 DAYS, 1, 1, 1, 1, '', 1) union all
          select DATE_ID, int(rand()*59999) + 1, int(rand()*200),  int(rand()*50) + 1,
          INT(RAND()*200 + 1), RESERVE, U_ID + 1
          from   v
          where  U_ID < 60000)
     select date_id, product_id, store_id, quantity, price, transaction_details from v;

     set cnt1 = cnt1 + 1;
     set cnt  = cnt + 1;
   end while;

end@

VALUES (CURRENT TIMESTAMP)@

connect reset@

Примечание: В Листинге 21, таблица SALES_FACT заполняется за одну транзакцию, что потребует большого объема дискового пространства для ведения журнала. Чтобы сократить воздействие журналирования, можно создать хранимую процедуру и выполнять завершение транзакций добавления поэтапно:

Листинг 22. Альтернативный метод заполнения таблицы SALES_FACT
connect to dss_db@

VALUES (CURRENT TIMESTAMP)@

-- Create a procedure to populate the SALES_FACT table
-- committing the inserts in stages to reduce the impact
-- of logging

create procedure salesFactPopulate()
specific salesFactPopulate
language sql

begin

   declare cnt INT default 1;
   declare cnt1 INT default 1;
   declare dat DATE default '01/01/2006';

   while (cnt <= 365) do    
    
    INSERT INTO SKAPOOR.SALES_FACT
    with v(DATE_ID, PRODUCT_ID, STORE_ID, QUANTITY, PRICE, TRANSACTION_DETAILS, U_ID) as
    (
       values(dat + CNT1 DAYS, 1, 1, 1, 1, '', 1)
      union all
       select DATE_ID, int(rand()*59999) + 1, int(rand()*200),  int(rand()*50) + 1,
              INT(RAND()*200 + 1), RESERVE, U_ID + 1
         from v
        where U_ID < 60000
    )
    select date_id, product_id, store_id, quantity, price, TRANSACTION_DETAILS from v;

    commit work;

    set cnt1 = cnt1 + 1;
    set cnt=cnt+1;

   end while;

end@

-- populate the SALES_FACT table
invoke salesFactPopulate@

VALUES (CURRENT TIMESTAMP)@

connect reset@

ШАГ D: Чтобы понять, как различные методы действуют на выбор плана доступа, нам необходимо выполнить команду Explain для запроса, чтобы посмотреть, какие планы доступа выбрал оптимизатор DB2. Это можно сделать с помощью инструмента EXPLAIN, для работы которого должны быть созданы таблицы EXPLAIN. Для создания таблиц EXPLAIN нужно выполнить следующие действия:

  1. Измените каталог на место расположения каталога sqllib\misc.
    В нашем случае это был каталог "C:\Program Files\IBM\SQLLIB\MISC".
  2. Выполните db2 connect to dss_db.
  3. Выполните db2 -tvf EXPLAIN .DDL.

МЕТОД 1: Определение соответствующих ограничений ссылочной целостности между таблицей фактов и тремя таблицами размерностей.

В DB2 можно определить ограничения первичных и внешних ключей, позволяющие диспетчеру базы данных поддерживать ссылочную целостность данных. Ссылочные ограничения (например, внешние ключи) могут также помочь повысить производительность запросов. Например, если вы добавите ограничение внешнего ключа на SALES_FACT.PRODUCT_ID, оптимизатор может изменить вложенное выражение TMP1 в запросе, описанном в листинге 2, удалив предикат таблицы PRODUCT_DIM и исключив соединение таблиц SALES_FACT и PRODUCT_DIM. Если создается ограничение по внешнему ключу, то соединение считается не создающим потерь и может быть исключено из запроса, поскольку все данные, необходимые из таблицы PRODUCT_DIM для этого запроса, уже есть в таблице SALES_FACT; в соединении с таблицей SALES_FACT используется только первичный ключ PRODUCT_DIM, ссылки на другие поля PRODUCT_DIM не используются.

В схеме типа "звезда", описанной в разделе Подробное описание схемы типа "звезда" , каждый из идентификаторов DATE_ID, PRODUCT_ID и STORE_ID, существующий в таблицах размерностей, также должен существовать в таблице фактов. Каждый идентификатор в таблицах размерности уникален и определяется ограничениями первичных ключей, созданными для каждой таблицы размерности. Таким образом, в таблице фактов содержатся архивные данные (количественные показатели) по датам продажи продуктов. В приведенной ниже таблице описываются первичные и внешние ключи, которые необходимо создать в этой схеме. Каждому уникальному идентификатору таблицы размерности соответствует ограничение на внешний ключ в таблице фактов.

ТАБЛИЦАПОЛЕПЕРВИЧНЫЙ/ВНЕШНИЙ КЛЮЧЦЕЛЕВАЯ ТАБЛИЦА (ПОЛЕ)
DATE_DIMDATE_IDПЕРВИЧНЫЙ КЛЮЧнет
PRODUCT_DIMPRODUCT_IDПЕРВИЧНЫЙ КЛЮЧнет
STORE_DIMSTORE_IDПЕРВИЧНЫЙ КЛЮЧнет
SALES_FACTDATE_IDВНЕШНИЙ КЛЮЧDATE_DIM (DATE_ID)
SALES_FACTPRODUCT_IDВНЕШНИЙ КЛЮЧPRODUCT_DIM (PRODUCT_ID)
SALES_FACTSTORE_IDВНЕШНИЙ КЛЮЧSTORE_DIM (STORE_ID)

ШАГ 1A: Изменение (ALTER) таблицы фактов и создание соответствующих отношений внешних ключей с таблицами размерностей. Отношения между таблицей фактов и таблицами размерностей приведены в приведенной выше таблице. Кроме того, нам понадобится индекс по полю SALES_FACT (DATE_ID,STORE_ID) для правильного сравнения с методом MDC, описываемом в Методе 3, который использует блочный индекс по полям (DATE_ID,STORE_ID).

Листинг 23. Создание отношений внешних ключей и индексов таблицы SALES_FACT
db2 -tvf alter_sales_fact.txt -z alter_sales_fact.log
Листинг 24. Содержимое файла alter_sales_fact.txt
CONNECT TO DSS_DB;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT DATE_DIM_FK FOREIGN KEY
(DATE_ID) REFERENCES DATE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT STORE_DIM_FK FOREIGN KEY
(STORE_ID) REFERENCES STORE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT PRODUCT_DIM_FK FOREIGN KEY
(PRODUCT_ID) REFERENCES PRODUCT_DIM;
CREATE INDEX SKAPOOR.IND_DATE_STORE ON SKAPOOR.SALES_FACT (DATE_ID, STORE_ID);
CONNECT RESET;

ШАГ 1B: Сбор статистики по всем таблицам:

Для расчета стоимости альтернативных планов выполнения запросов (QEP) и выбора оптимального плана оптимизатор использует данные статистики. Перед продолжением работы необходимо собрать некоторую статистику.

Листинг 25. Сбор статистики по всем таблицам
db2 -tvf runstats.ddl -z runstats.log
Листинг 26. Содержимое файла runstats.ddl
CONNECT TO DSS_DB;
RUNSTATS ON TABLE SKAPOOR.DATE_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.STORE_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.PRODUCT_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.SALES_FACT WITH DISTRIBUTION AND INDEXES ALL;
CONNECT RESET;

Теперь, когда созданы внешние ключи, мы можем посмотреть, как оптимизатор DB2 может использовать ограничения ссылочной целостности для устранения соединений.

ШАГ 1C: Разъяснение (Explain) запроса:

Листинг 27. Запрос с соединением без потерь
SELECT
		D.MONTH AS MONTH, 
		S.STORE_ID AS STORE_ID,
		S.DISTRICT AS DISTRICT,
		S.REGION AS REGION,
		SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT

  FROM
		SKAPOOR.SALES_FACT F1,
		SKAPOOR.DATE_DIM D,
		SKAPOOR.PRODUCT_DIM P,
		SKAPOOR.STORE_DIM S
  WHERE
		F1.DATE_ID=D.DATE_ID AND
		F1.PRODUCT_ID=P.PRODUCT_ID AND
		F1.STORE_ID=S.STORE_ID AND
		F1.DATE_ID BETWEEN '2006-01-01' AND '2006-01-31' AND
		F1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND		
		D.MONTH = 1 
  GROUP BY
		S.STORE_ID,S.DISTRICT,S.REGION,D.MONTH)

Один из способов разъяснения запроса состоит в следующем:

db2 connect to dss_db
db2 set current explain mode explain
db2 -tvf JOIN_ELIM_QUERY.SQL -z JOIN_ELIM_QUERY.log
db2 set current explain mode no
db2 connect reset

Здесь в JOIN_ELIM_QUERY.SQL содержится запрос, приведенный в листинге 27, завершающийся точкой с запятой.

План выполнения запроса можно просмотреть с помощью инструмента db2exfmt следующим образом:

db2exfmt -d dss_db -g TIC -w -1 -n % -s % -# 0 -o join_elim.txt

Выходная информация записывается в файл join_elim.txt. Более подробную информацию об инструменте db2exfmt можно получить с помощью параметра -h.

Откройте файл JOIN_ELIM, приведенный в разделе Материалы для загрузки, и посмотрите планы доступа, созданные оптимизатором запросов при исключении соединения с PRODUCT_DIM.

Кроме того, обратите внимание на раздел "Optimized Statement" выходного файла db2exfmt и заметьте, что таблица PRODUCT_DIM исключена из запроса.

Примечание: При использовании ссылочных ограничений (например, внешних ключей), также может снизиться производительность операций добавления, удаления и обновления. Если производительность этих операций существенна, но оптимизация с исключением соединений будет полезна для ваших запросов, можно определить ограничения внешних ключей как информационные. Этот вариант рассматривается в упражнении, приведенном в конце описания этого метода.

ШАГ 1D: Разъяснение и выполнение запроса.

Чтобы разъяснить запрос, выполните те же действия, что и в ШАГЕ 1C:

db2 connect to dss_db
db2 set current explain mode explain
db2 -tvf QUERY1.SQL -z QUERY1.log
db2 set current explain mode no
db2 connect reset

Здесь в QUERY1.SQL содержится запрос, приведенный в листинге 2, завершающийся точкой с запятой.

План выполнения запроса можно просмотреть с помощью инструмента db2exfmt:

db2exfmt -d dss_db -g TIC -w -1 -n % -s % -# 0 -o test1.txt

План выполнения запроса будет похож на план, приведенный в файле Test 1 из раздела Материалы для загрузки.

Для запуска запроса и измерения его производительности будет использован инструмент db2batch. Перед началом эксперимента необходимо перезапустить экземпляр db2, чтобы правильно сравнить методы и устранить влияние на производительность других факторов (например, созданные позже методы могут работать лучше вследствие наличия данных в буфере, что исказит результаты измерений).

Примечание: Наша тестовая система во время экспериментов была полностью свободна, никаких других задач на ней не выполнялось.

Остановите экземпляр db2 с помощью команды db2stop force и запустите его заново с помощью команды db2start. Воспользуйтесь db2batch для получения информации о времени выполнения запроса:

db2batch -d DSS_DB -f QUERY1.SQL -i complete -iso CS -o p 5 o 5 r 0 -r test1.results.txt

В файле test1.results.txt будет содержаться время компиляции и выполнения запроса в таком виде:

* Prepare Time is:       7.278206 seconds
* Execute Time is:     107.729436 seconds
* Fetch Time is:         0.000102 seconds
* Elapsed Time is:     115.007744 seconds (complete)

Упражнения:

  1. В ШАГЕ 1A для таблицы SALES_FACT были созданы ограничения по внешнему ключу, однако они могут снизить производительность операций по добавлению, удалению и обновлению записей, поскольку диспетчер базы данных должен обеспечивать ссылочную целостность. Если важна производительность этих операций, можно создать информационные ограничения, которые позволяют исключить соединение, предполагая, что ссылочная целостность обеспечивается иными средствами. Если это не так, применение информационных ограничений может привести к ошибочным результатам.

    Информационные ограничения определяются таким же образом, как и ссылочные, за исключением того, что в конце добавляется ключевое слово not enforced, например:

    ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT DATE_DIM_FK FOREIGN KEY (DATE_ID) REFERENCES DATE_DIM NOT ENFORCED;

    Чтобы завершить выполнения этого упражнения, удалите ограничение по внешнему ключу на таблицу SALES_FACT и повторите шаги 1A-1D, используя информационные ограничения.


МЕТОД 2: Репликация материализованных таблиц запросов для таблиц размерностей

В этом эксперименте используется тот же запрос и те же таблицы, что и в методе 1, однако здесь дополнительно создаются реплицированные MQT для таблиц размерностей.

В методе 1 таблицы размерностей хранятся в различных разделах и данные приходится передавать между разделами. Для репликации таблиц размерностей в другие разделы можно использовать MQT, это позволит выполнять соседские соединения и исключить передачу данных между разделами, что потенциально может повысить производительность выполнения запроса

ШАГ 2A: Создайте реплицированные MQT следующим образом:

db2 -tvf replicated.ddl
Листинг 28. Содержимое файла replicated.ddl file
connect to dss_db;

drop table skapoor.store_dim_rep;
drop table skapoor.product_dim_rep;
drop table skapoor.date_dim_rep;

create table skapoor.store_dim_rep as (select * from skapoor.store_dim)
data initially deferred refresh deferred in FACT_SMS replicated;
create table skapoor.product_dim_rep as (select * from skapoor.product_dim)
data initially deferred refresh deferred in FACT_SMS replicated;
create table skapoor.date_dim_rep as (select * from skapoor.date_dim)
data initially deferred refresh deferred in FACT_SMS replicated;

refresh table skapoor.store_dim_rep;
refresh table skapoor.product_dim_rep;
refresh table skapoor.date_dim_rep;

create index skapoor.store_dim_id_rep on skapoor.store_dim_rep (store_id);
create index skapoor.product_dim_id_rep on skapoor.product_dim_rep (product_id);
create index skapoor.date_dim_id_rep on skapoor.date_dim_rep (date_id);

runstats on table skapoor.store_dim_rep with distribution and indexes all;
runstats on table skapoor.product_dim_rep with distribution and indexes all;
runstats on table skapoor.date_dim_rep with distribution and indexes all;

connect reset;

Чтобы соседство было возможным, реплицированные таблицы размерностей должны находиться в той же группе разделов базы данных, что и таблица фактов. Для простоты мы использовали то же табличное пространство, что и у таблицы с данными, однако табличные пространства могут быть различными, если они находятся в одной и той же группе разделов базы данных. Более того, для того, чтобы реплицированные таблицы были сравнимы с оригиналами для оптимизатора, рассчитывающего стоимость различных вариантов доступа, у реплицированных таблиц должны быть определены такие же индексы, а также должна быть собрана одинаковая статистика. В MQT нельзя создавать уникальные индексы, поэтому для первичного ключа исходной таблицы создаются обычные индексы.

Репликация таблицы размерности приведет к появлению дополнительной копии таблицы. Для экономии пространства хранения в DB2 9 была добавлена функция сжатия строк. Чтобы сократить издержки на хранение копии таблицы размерности, ее можно сжать. При использовании такой методики мы рекомендуем также сжимать реплицированные MQT. В противном случае оптимизатор может решить, что необходимо выполнять несоседское соединение с исходной таблицей размерностей, поскольку она сжата и меньше, чем реплицированная MQT.

ШАГ 2B: Обновите конфигурацию базы данных DSS_DB, установив параметру dft_refresh_age значение "ANY", чтобы оптимизатор выбрал реплицированные MQT:

Листинг 29. Изменение конфигурации базы данных
db2_all db2 update db cfg for DSS_DB using dft_refresh_age any
db2 terminate

ШАГ 2C: Создайте выходной файл db2exfmt для основного запроса, как и в ШАГЕ 1C Метода 1. Изучите план доступа и убедитесь в том, что используются реплицированные MQT (то есть выбираются date_dim_rep, product_dim_rep и store_dim_rep). Пример того, как выглядит план доступа, можно найти в файле Test 2 в разделе Материалы для загрузки.

В приведенном выше плане доступа между соединениями отсутствуют операторы очереди таблиц (TQ), как это было в методе 1, поскольку оптимизатор выбирает использование реплицированных таблиц размерностей, что позволяет создать соседские соединения.

ШАГ 2D: После того как вы убедились, что в плане доступа используются MQT, переходите к измерению производительности с помощью инструмента db2batch, как было описано в ШАГЕ 1D Метода 1. Перед запуском db2batch не забудьте перезапустить экземпляр db2. После этого сохраните результаты.

Примечание: Для этого метода необходимо установить конфигурационному параметру DFT_REFRESH_AGE значение ANY для всех разделов базы данных. Если вы захотите выполнить эксперимент, описанный в методе 1, вам необходимо изменить значение параметра конфигурации базы данных DFT_REFRESH_AGE на "0". В противном случае вместо основных таблицы размерностей будут использоваться MQT.

Упражнения

  1. Используйте сжатие строк для сжатия основных таблиц размерностей STORE_DIM, PRODUCT_DIM и DATE_DIM. Необходимо повторить сбор статистики для всех трех таблиц размерностей. По окончании сбора статистики повторите шаги 2C-2D.

  2. Если оптимизатор не выбрал доступ к реплицированным MQT в первом эксперименте, повторите его и сожмите также реплицированные MQT.


МЕТОД 3: Таблица фактов MDC с реплицированными MQT таблиц размерностей

Этот эксперимент похож на метод 2, однако здесь таблица SALES_FACT заменяется таблицей фактов MDC. MDC предоставляет способ автоматической кластеризации данных в таблицах по нескольким измерениям и может значительно повысить производительность запросов при правильном выборе полей размерностей и величин экстентов.

ШАГ 3A Расчет величины экстента.

Для табличного пространства была выбрана величина EXTENTSIZE, равная 12, расчет проводился следующим образом:

  1. Согласно рекомендациям Информационного центра в качестве размерностей таблицы MDC были выбраны поля (date_id,store_id).

    Для расчета количества уникальных комбинаций (date_id, store_id) в таблице sales_fact был использован следующий запрос:

    Листинг 30. Запрос на расчет количества уникальных комбинаций (date_id, store_id)
    WITH TMP (DATE_ID, STORE_ID) AS 
      (SELECT DISTINCT DATE_ID, STORE_ID FROM SALES_FACT)
    SELECT COUNT(*) AS CELL_COUNT FROM TMP;
    
    CELL_COUNT
    -----------
          73097
  2. Приведенный ниже запрос рассчитывает среднее количество строк на ячейку (RPC), минимальное и максимальное количество строк на ячейку.

    Листинг 31. Определение среднего количества строк
    WITH CELL_TABLE(DATE_ID,STORE_ID,RPC) AS 
    (
       SELECT DISTINCT DATE_ID,STORE_ID, COUNT(*) 
         FROM SALES_FACT 
       GROUP BY DATE_ID,STORE_ID
    )
    SELECT 
    	AVG(RPC) AS RPC, MIN(RPC) AS MINRPC, MAX(RPC) AS MAXRPC 
    FROM CELL_TABLE;
    
    RPC         MINRPC      MAXRPC
    ----------- ----------- -----------
            298           1         380
    
      1 record(s) selected.
  3. Для расчета пространства на ячейку мы использовали формулу из руководства администратора DB2 9, приведенную в разделе Требования к пространству для таблиц пользователей.

    Таким образом можно оценить количество страниц по 4 КБ для каждой таблицы базы данных: Для начала определим среднюю длину строки. В нашем случае поля имеют фиксированные типы данных, и мы можем определить длину строки путем сложения длин полей. Длины полей можно определить с помощью запроса DESCRIBE:

    Листинг 32. Запрос DESCRIBE
    DB2 DESCRIBE SELECT * FROM SALES_FACT
    
    SQLDA Information
    
     sqldaid : SQLDA     sqldabc: 896  sqln: 20  sqld: 6
    
     Column Information
    
     sqltype               sqllen  sqlname.data                    sqlname.length
     --------------------  ------  ------------------------------  --------------
     385   DATE                10  DATE_ID                                      7
     497   INTEGER              4  PRODUCT_ID                                  10
     497   INTEGER              4  STORE_ID                                     8
     497   INTEGER              4  QUANTITY                                     8
     497   INTEGER              4  PRICE                                        5
     453   CHARACTER          100  TRANSACTION_DETAILS                                     8

    Колонка "sqllen" в результате, возвращаемом по запросу DESCRIBE, указывает длину каждого поля.

    Формула для расчета среднего количества записей на страницу такова:

    ЗАПИСЕЙ_НА_СТРАНИЦУ = ОКРУГЛЕНИЕ В МЕНЬШУЮ СТОРОНУ (4028 / (СРЕДНИЙ РАЗМЕР СТРОКИ + 10)).

    В нашем случае СРЕДНИЙ РАЗМЕР СТРОКИ = 126 байт (сумма длин полей: 10+4+4+4+4+100).

    Таким образом, ЗАПИСЕЙ_НА_СТРАНИЦУ = ОКРУГЛЕНИЕ (4028 / (126+10)) = 29.

    Дополнительные 10 байт в формуле ЗАПИСЕЙ_НА_СТРАНИЦУ учитывают накладные расходы.

    Количество страниц по 4 КБ, необходимых для хранения в среднем 298 записей (RPC из листинга 31), можно рассчитать следующим образом:

    КОЛИЧЕСТВО_СТРАНИЦ = (КОЛИЧЕСТВО_ЗАПИСЕЙ / ЗАПИСЕЙ_НА_СТРАНИЦУ) * 1.1, где КОЛИЧЕСТВО_ЗАПИСЕЙ = RPC=298

    КОЛИЧЕСТВО_СТРАНИЦ = (298 записей / 29 записей на страницу) * 1.1 = 11.3 ~ 12 страниц по 4 КБ

    Следовательно, значение EXTENTSIZE составляет 12.

ШАГ 3B: Создание табличного пространства MDC с величиной экстента 12:

Листинг 33. Создание табличного пространства MDC
db2 -tvf mdc_tablespace.ddl -z mdc_tablespace.log
Листинг 34. Содержимое файла mdc_tablespace.ddl
CREATE REGULAR TABLESPACE FACT_SMS_MDC_EX IN DATABASE PARTITION GROUP
FACT_GROUP PAGESIZE 4096 MANAGED BY SYSTEM
USING ('d:\database\fact_tbsp_mdc_EX120') ON DBPARTITIONNUMS (0)
USING ('d:\database\fact_tbsp_mdc_EX121') ON DBPARTITIONNUMS (1)
USING ('d:\database\fact_tbsp_mdc_EX122') ON DBPARTITIONNUMS (2)
USING ('d:\database\fact_tbsp_mdc_EX123') ON DBPARTITIONNUMS (3)
	 EXTENTSIZE 12
	 PREFETCHSIZE 24
	 BUFFERPOOL IBMDEFAULTBP
	 OVERHEAD 7.500000
	 TRANSFERRATE 0.060000 
	 NO FILE SYSTEM CACHING  
	 DROPPED TABLE RECOVERY ON;

ШАГ 3C: Создание таблицы MDC

Листинг 35. Создание таблицы MDC
db2 -tvf sales_fact_mdc.ddl -z sales_fact_mdc.log
Листинг 36. Содержимое файла sales_fact_mdc.ddl
CONNECT TO DSS_DB;

---------------------------------------------------------
-- DDL Statements for table "SKAPOOR "."SALES_FACT_MDC_1"
---------------------------------------------------------
 

CREATE TABLE "SKAPOOR "."SALES_FACT_MDC_1"  (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "RESERVE" CHAR(100) )   
		 DISTRIBUTE BY HASH("DATE_ID")   
		   IN "FACT_SMS_MDC_EX"  
		 ORGANIZE BY ( 
		  ( "DATE_ID" ) , 
		  ( "STORE_ID" ) ) 
		 ; 

COMMIT WORK;
CONNECT RESET;

Примечание: Блочные индексы, используемые для таблицы MDC, автоматически создаются для таблицы фактов по полям размерностей (date_id, store_id).

ШАГ 3D: Добавление данных в таблицу MDC. В нашей тестовой среде добавление данных в таблицу MDC заняло примерно четыре часа.

Листинг 37. Добавление данных в таблицу MDC
db2 -tvf sales_fact_mdc_insert_alter.ddl -z sales_fact_mdc_insert.log
Листинг 38. Содержимое файла sales_fact_mdc_insert_alter.ddl
CONNECT TO DSS_DB;

VALUES(CURRENT TIMESTAMP);

-----------------------------------
-- SET OPTLEVEL 0 TO FAVOUR INDEX ACCESS TO IMPROVE PERFORMANCE OF INSERT.
SET CURRENT QUERY OPTIMIZATION 0;
-----------------------------------

-- INSERTING THE DATA IN THE ORDER OF THE MDC COLUMNS IMPROVES 
-- THE PERFORMANCE OF THE INSERT.
INSERT INTO SKAPOOR.SALES_FACT_MDC_1 SELECT *
FROM SKAPOOR.SALES_FACT ORDER BY DATE_ID,STORE_ID;

ALTER TABLE SKAPOOR.SALES_FACT_MDC_1
ADD CONSTRAINT DATE_DIM_FK FOREIGN KEY (DATE_ID) REFERENCES SKAPOOR.DATE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT_MDC_1
ADD CONSTRAINT STORE_DIM_FK FOREIGN KEY (STORE_ID) REFERENCES SKAPOOR.STORE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT_MDC_1
ADD CONSTRAINT PRODUCT_DIM_FK FOREIGN KEY (PRODUCT_ID)
REFERENCES SKAPOOR.PRODUCT_DIM;

VALUES(CURRENT TIMESTAMP);

RUNSTATS ON TABLE SKAPOOR.SALES_FACT_MDC_1 WITH DISTRIBUTION AND INDEXES ALL;

ШАГ 3E: Для проверки преимуществ MDC в запросе, представленном в листинге 2, была произведена замена названия таблицы "SALES_FACT" на "SALES_FACT_MDC_1". В приведенном ниже листинге 39 содержится новый запрос. Скомпилируйте запрос в режиме "explain" и создайте выходные данные db2exfmt для основного запроса, как было описано в ШАГЕ 1C метода 1. Убедитесь в том, что в плане доступа используются индексы MDC и он похож на Test 3, приведенный в разделе Материалы для загрузки.

Листинг 39. Запрос MDC
WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS
( SELECT
		D.MONTH AS MONTH, 
		S.STORE_ID AS STORE_ID,
		S.DISTRICT AS DISTRICT,
		S.REGION AS REGION,
		SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT

  FROM
		SKAPOOR.SALES_FACT_MDC_1 F1,
		SKAPOOR.DATE_DIM D,
		SKAPOOR.PRODUCT_DIM P,
		SKAPOOR.STORE_DIM S
  WHERE
		P.MODEL LIKE '%model%' AND
		F1.DATE_ID=D.DATE_ID AND
		F1.PRODUCT_ID=P.PRODUCT_ID AND
		F1.STORE_ID=S.STORE_ID AND
		F1.DATE_ID BETWEEN '2006-01-01' AND '2006-01-31' AND
		F1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND
		D.MONTH = 1 
  GROUP BY
		S.STORE_ID,S.DISTRICT,S.REGION,D.MONTH) ,
TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS
(	SELECT 

		D1.MONTH AS MONTH,
		S1.STORE_ID AS STORE_ID,
		S1.DISTRICT AS DISTRICT,
		S1.REGION AS REGION,
		SUM(F2.QUANTITY * F2.PRICE) AS AMOUNT
		
	FROM
		SKAPOOR.SALES_FACT_MDC_1 F2,
		SKAPOOR.DATE_DIM D1,
		SKAPOOR.PRODUCT_DIM P1,
		SKAPOOR.STORE_DIM S1
	WHERE
		P1.MODEL LIKE '%model%' AND
		F2.DATE_ID=D1.DATE_ID AND
		F2.PRODUCT_ID=P1.PRODUCT_ID AND
		F2.STORE_ID=S1.STORE_ID AND
		F2.DATE_ID BETWEEN '2006-11-01' AND '2006-11-30' AND
		F2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND
		D1.MONTH=11
	GROUP BY
		S1.STORE_ID,S1.DISTRICT,S1.REGION,D1.MONTH) 
SELECT 
	A.*, 
	B.*
FROM
	TMP1 A LEFT OUTER JOIN TMP2 B ON
          (A.STORE=B.STORE AND A.REGION=B.REGION AND A.DISTRICT=B.DISTRICT)
ORDER BY A.AMOUNT_1 DESC, B.AMOUNT_11 DESC;

ШАГ 3F:Перезапустите экземпляр и измерьте производительность с помощью инструмента db2batch, как было показано в методе 1, ШАГ 1D.

Примечание: Запрос, содержащийся в файле QUERY1.SQL, был изменен на запрос, представленный в листинге 39. Сохраните полученные результаты.


МЕТОД 4: Разбиение таблиц с репликацией MQT по размерностям

Этот эксперимент похож на метод 2, но вместо таблицы SALES_FACT используется разбитая на разделы таблица фактов. Разбиение таблиц на разделы - это новая функция, реализованная в DB2 9. Она представляет собой схему организации данных, в которой таблица разделяется между несколькими объектами хранения – т.н. разделами данных или диапазонами - в соответствии со значениями одного или нескольких полей таблицы. Каждый раздел данных представляет собой отдельный физический объект; разделы могут храниться в различных табличных пространствах и (или) в одном табличном пространстве. Такая схема полезна для очень больших таблиц, встречающихся в среде бизнес-анализа; она обеспечивает простое сворачивание и разворачивание данных, а также эффективное выполнение запросов, исключая сканирование ненужных разделов в соответствии с условиями запроса.

ШАГ 4A: Создание таблицы с разбиением на разделы

Во-первых, необходимо определить диапазон, по которому будет производиться разбиение. Во многих случаях разбиение проводится по дате, и мы будем разбивать таблицу SALES_FACT по полю DATE_ID. В Информационном центре представлена более подробная информация об определении диапазонов для разбиения таблиц. Поскольку в таблице SALES_FACT содержатся транзакции целого года, а в нашем запросе проводится сравнение в рамках одного месяца, каждый диапазон будет состоять из данных за один месяц.

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

Листинг 40. Создание таблицы SALES_FACT с разбиением
db2 -tvf tablepart.ddl -z tablepart.log
Листинг 41. Содержимое файла tablepart.ddl
CONNECT TO DSS_DB;

CREATE REGULAR TABLESPACE FACT_TPART_SMS IN DATABASE PARTITION GROUP FACT_GROUP
PAGESIZE 4096 MANAGED BY SYSTEM 
	 USING ('d:\database\fact_tpart_tbsp0') ON DBPARTITIONNUMS (0)
	 USING ('d:\database\fact_tpart_tbsp1') ON DBPARTITIONNUMS (1)
	 USING ('d:\database\fact_tpart_tbsp2') ON DBPARTITIONNUMS (2)
	 USING ('d:\database\fact_tpart_tbsp3') ON DBPARTITIONNUMS (3)
	 EXTENTSIZE 32
	 PREFETCHSIZE AUTOMATIC
	 BUFFERPOOL IBMDEFAULTBP
	 OVERHEAD 7.500000
	 TRANSFERRATE 0.060000 
	 NO FILE SYSTEM CACHING;

-- CREATE THE SALES_FACT TABLE PARTITIONED ON DATE_ID
-- WITH A SINGLE PARTITION TO START WITH, CONTAINING
-- ALL SALES FROM JANUARY
CREATE TABLE "SKAPOOR "."SALES_FACT_TPART"  (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")   
                   IN "FACT_TPART_SMS"
                 PARTITION BY ("DATE_ID")
                    (PART Jan STARTING ('1/1/2006') ENDING ('1/31/2006'))
;

VALUES (CURRENT TIMESTAMP);

-- POPULATE THE SALES FROM JAN
INSERT INTO "SKAPOOR"."SALES_FACT_TPART"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '1/1/2006' AND '1/31/2006';

commit work;

VALUES (CURRENT TIMESTAMP);

-- CREATE TABLES FOR SALES FROM EACH MONTH
-- WHICH WILL THEN BE ATTACHED TO SALES_FACT_TPART TABLE
CREATE TABLE "SKAPOOR"."SALES_FEB" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_MAR" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_APR" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_MAY" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_JUN" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_JUL" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_AUG" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_SEP" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_OCT" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_NOV" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_DEC" (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )
		 DISTRIBUTE BY HASH("DATE_ID")
                   IN "FACT_TPART_SMS";

VALUES (CURRENT TIMESTAMP);

-- POPULATE EACH TABLE WITH SALES FOR THE CORRESPONDING MONTH
INSERT INTO "SKAPOOR"."SALES_FEB"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '2/1/2006' AND '2/28/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_MAR"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '3/1/2006' AND '3/31/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_APR"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '4/1/2006' AND '4/30/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_MAY"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '5/1/2006' AND '5/31/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_JUN"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '6/1/2006' AND '6/30/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_JUL"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '7/1/2006' AND '7/31/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_AUG"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '8/1/2006' AND '8/31/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_SEP"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '9/1/2006' AND '9/30/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_OCT"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '10/1/2006' AND '10/31/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_NOV"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '11/1/2006' AND '11/30/2006';

commit work;

INSERT INTO "SKAPOOR"."SALES_DEC"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '12/1/2006' AND '12/31/2006';

commit work;

VALUES (CURRENT TIMESTAMP);

-- Attach SALES from February and March
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Feb STARTING FROM '2/1/2006' ENDING AT '2/28/2006' 
  FROM "SKAPOOR"."SALES_FEB";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Mar STARTING FROM '3/1/2006' ENDING AT '3/31/2006'
  FROM "SKAPOOR"."SALES_MAR";

-- Make the partitions visible
SET INTEGRITY FOR "SKAPOOR"."SALES_FACT_TPART"
IMMEDIATE CHECKED;

commit work;

-- Attach SALES from April to June
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Apr STARTING FROM '4/1/2006' ENDING AT '4/30/2006'
  FROM "SKAPOOR"."SALES_APR";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION May STARTING FROM '5/1/2006' ENDING AT '5/31/2006'
  FROM "SKAPOOR"."SALES_MAY";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Jun STARTING FROM '6/1/2006' ENDING AT '6/30/2006'
  FROM "SKAPOOR"."SALES_JUN";

SET INTEGRITY FOR "SKAPOOR"."SALES_FACT_TPART"
IMMEDIATE CHECKED;

commit work;

-- Attach SALES from July to Dec
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Jul STARTING FROM '7/1/2006' ENDING AT '7/31/2006' 
  FROM "SKAPOOR"."SALES_JUL";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Aug STARTING FROM '8/1/2006' ENDING AT '8/31/2006' 
  FROM "SKAPOOR"."SALES_AUG";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Sep STARTING FROM '9/1/2006' ENDING AT '9/30/2006' 
  FROM "SKAPOOR"."SALES_SEP";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Oct STARTING FROM '10/1/2006' ENDING AT '10/31/2006'
  FROM "SKAPOOR"."SALES_OCT";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Nov STARTING FROM '11/1/2006' ENDING AT '11/30/2006'
  FROM "SKAPOOR"."SALES_NOV";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART" 
  ATTACH PARTITION Dec STARTING FROM '12/1/2006' ENDING AT '12/31/2006'
  FROM "SKAPOOR"."SALES_DEC";

SET INTEGRITY FOR "SKAPOOR"."SALES_FACT_TPART"
IMMEDIATE CHECKED;
yo
commit work;

VALUES(CURRENT TIMESTAMP);

RUNSTATS ON TABLE SKAPOOR.SALES_FACT_TPART WITH DISTRIBUTION;

commit work;

CONNECT RESET;

Заполнение этих разделов в нашей среде заняло примерно три часа. Подключение каждого из разделов проходило очень быстро. После подключения каждой таблицы она становится физическим объектом таблицы SALES_FACT_TPART, и к ней больше нельзя обратиться как к отдельной таблице. Для того чтобы сделать это, необходимо отключить ее от таблицы SALES_FACT_TPART.

ШАГ 4B: Для проверки преимуществ MDC в запросе, представленном в листинге 2, название таблицы "SALES_FACT" было заменено на "SALES_FACT_TPART". В приведенном ниже листинге 42 содержится новый запрос. Скомпилируйте запрос в режиме "explain" и создайте выходные данные db2exfmt для основного запроса, как было описано в ШАГЕ 1C метода 1. Убедитесь в том, что в плане доступа используется таблица с разбиением и он похож на TPART, приведенный в разделе Материалы для загрузки.

Листинг 42. Запрос к таблице с разбиением
WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS
( SELECT
		D.MONTH AS MONTH, 
		S.STORE_ID AS STORE_ID,
		S.DISTRICT AS DISTRICT,
		S.REGION AS REGION,
		SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT

  FROM
		SKAPOOR.SALES_FACT_TPART F1,
		SKAPOOR.DATE_DIM D,
		SKAPOOR.PRODUCT_DIM P,
		SKAPOOR.STORE_DIM S
  WHERE
		P.MODEL LIKE '%model%' AND
		F1.DATE_ID=D.DATE_ID AND
		F1.PRODUCT_ID=P.PRODUCT_ID AND
		F1.STORE_ID=S.STORE_ID AND
		F1.DATE_ID BETWEEN '2006-01-01' AND '2006-01-31' AND
		F1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND
		D.MONTH = 1 
  GROUP BY
		S.STORE_ID,S.DISTRICT,S.REGION,D.MONTH) ,
TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS
(	SELECT 

		D1.MONTH AS MONTH,
		S1.STORE_ID AS STORE_ID,
		S1.DISTRICT AS DISTRICT,
		S1.REGION AS REGION,
		SUM(F2.QUANTITY * F2.PRICE) AS AMOUNT
		
	FROM
		SKAPOOR.SALES_FACT_TPART F2,
		SKAPOOR.DATE_DIM D1,
		SKAPOOR.PRODUCT_DIM P1,
		SKAPOOR.STORE_DIM S1
	WHERE
		P1.MODEL LIKE '%model%' AND
		F2.DATE_ID=D1.DATE_ID AND
		F2.PRODUCT_ID=P1.PRODUCT_ID AND
		F2.STORE_ID=S1.STORE_ID AND
		F2.DATE_ID BETWEEN '2006-11-01' AND '2006-11-30' AND
		F2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND
		D1.MONTH=11
	GROUP BY
		S1.STORE_ID,S1.DISTRICT,S1.REGION,D1.MONTH) 
SELECT 
	A.*, 
	B.*
FROM
	TMP1 A LEFT OUTER JOIN TMP2 B ON
          (A.STORE=B.STORE AND A.REGION=B.REGION AND A.DISTRICT=B.DISTRICT)
ORDER BY A.AMOUNT_1 DESC, B.AMOUNT_11 DESC;

Подробная информация о доступе к разбитой на разделы таблице в выводе db2exfmt покажет, произошло ли исключение разделов, и к каким разделам было обращение:

	14) TBSCAN: (Table Scan)
		Cumulative Total Cost: 		15378
		Cumulative CPU Cost: 		8.77067e+008
		Cumulative I/O Cost: 		15213
		Cumulative Re-Total Cost: 	15378
		Cumulative Re-CPU Cost: 	8.77065e+008
		Cumulative Re-I/O Cost: 	15213
		Cumulative First Row Cost: 	8.22883
		Cumulative Comm Cost:		0
		Cumulative First Comm Cost:	0
		Estimated Bufferpool Buffers: 	15213

		Arguments:
		---------
		DPESTFLG: (Number of data partitions accessed are Estimated)
			FALSE
		DPLSTPRT: (List of data partitions accessed)
			10
		DPNUMPRT: (Number of data partitions accessed)
			1
			
	...
		DP Elim Predicates:
		------------------
		Range 1)
			Stop  Predicate: (Q10.DATE_ID <= '11/30/2006')
			Start Predicate: ('11/01/2006' <= Q10.DATE_ID)
        ...

Аргумент DPESTFLG показывает, как производилось исключение разделов – путем оценки (TRUE) или вычисления на момент компиляции (FALSE). Если использовалась оценка, то фактическое исключение раздела определялось во время выполнения. В нашем примере исключение раздела рассчитывалось во время компиляции. Аргумент DPLSTPRT показывает, к какому разделу или разделам были обращения, а DPNUMPRT показывает количество использованных разделов. Если аргумент DPESTFLG имеет значение TRUE, то это просто оценки, рассчитанные оптимизатором. В нашем примере обращение происходит только к одному разделу, разделу номер 10; остальные разделы игнорируются.

В разделе DP Elim Predicates перечисляются предикаты, на основании которых определялся перечень используемых разделов.

ШАГ 4C: Перезапустите экземпляр и измерьте производительность с помощью инструмента db2batch, как было показано в методе 1, ШАГ 1D.

Примечание: Запрос, содержащийся в файле QUERY1.SQL, был изменен на запрос, представленный в листинге 42. Сохраните полученные результаты. , ШАГ 1D. Сохраните полученные результаты.

Упражнения

  1. Индексы позволяют повысить производительность запросов, использующих разбитую на разделы таблицу SALES_FACT_TPART. Создайте один или несколько индексов, которые вы считаете полезными, и повторите шаги 4B и 4C. Не забывайте собирать статистику по индексам.
  2. Попробуйте отключить один или несколько разделов от таблицы SALES_FACT_TPART, чтобы почувствовать, как легко и эффективно можно проводить разворачивание данных с помощью разбиения таблиц.

МЕТОД 5: Разбиение таблиц с MDC и репликацией MQT по размерностям

Этот эксперимент похож на метод 4, однако здесь таблица SALES_FACT_TPART заменяется разбитой на разделы таблицей фактов MDC. Для еще большего повышения производительности выполнения запросов можно сочетать MDC и разбиение таблиц. Применяя те же методы, что и в методе 3, поля DATE_ID и STORE_ID используются в качестве полей размерности, и, так же как в методе 4, используется диапазон DATE_ID.

ШАГ 5A: Создание таблицы MDC с разбиением

Листинг 43. Создание таблицы MDC SALES_FACT с разбиением
db2 -tvf tablepart_mdc.ddl -z tablepart_mdc.log
Листинг 44. Содержимое файла tablepart_mdc.ddl
CONNECT TO DSS_DB;

CREATE TABLE "SKAPOOR "."SALES_FACT_TPART_MDC" ( "DATE_ID" DATE ,
 "PRODUCT_ID" INTEGER ,
 "STORE_ID" INTEGER ,
 "QUANTITY" INTEGER ,
 "PRICE" INTEGER ,
 "TRANSACTION_DETAILS" CHAR(100))
DISTRIBUTE BY HASH("DATE_ID")  
  PARTITION BY RANGE("DATE_ID")
  (PART "JAN" STARTING('2006-01-01') ENDING('2006-01-31') IN "FACT_TPART_SMS",
   PART "FEB" STARTING('2006-02-01') ENDING('2006-02-28') IN "FACT_TPART_SMS",
   PART "MAR" STARTING('2006-03-01') ENDING('2006-03-31') IN "FACT_TPART_SMS",
   PART "APR" STARTING('2006-04-01') ENDING('2006-04-30') IN "FACT_TPART_SMS",
   PART "MAY" STARTING('2006-05-01') ENDING('2006-05-31') IN "FACT_TPART_SMS",
   PART "JUN" STARTING('2006-06-01') ENDING('2006-06-30') IN "FACT_TPART_SMS",
   PART "JUL" STARTING('2006-07-01') ENDING('2006-07-31') IN "FACT_TPART_SMS",
   PART "AUG" STARTING('2006-08-01') ENDING('2006-08-31') IN "FACT_TPART_SMS",
   PART "SEP" STARTING('2006-09-01') ENDING('2006-09-30') IN "FACT_TPART_SMS",
   PART "OCT" STARTING('2006-10-01') ENDING('2006-10-31') IN "FACT_TPART_SMS",
   PART "NOV" STARTING('2006-11-01') ENDING('2006-11-30') IN "FACT_TPART_SMS",
   PART "DEC" STARTING('2006-12-01') ENDING('2006-12-31') IN "FACT_TPART_SMS")
 ORGANIZE BY (
        DATE_ID,
 	STORE_ID) 
 ;

COMMIT WORK ;

INSERT INTO SKAPOOR.SALES_FACT_TPART_MDC
  SELECT * FROM SKAPOOR.SALES_FACT_MDC_1;

COMMIT WORK;

RUNSTATS ON TABLE SKAPORR.SALES_FACT_TPART_MDC WITH DISTRIBUTION AND INDEXES ALL;

COMMIT WORK;
CONNECT RESET;

ШАГ 5B: Чтобы оценить, какие преимущества дает сочетание MDC и разбиения на разделы, в запросе, представленном в листинге 2, название таблицы "SALES_FACT" было заменено на "SALES_FACT_TPART_MDC". Новый запрос приведен ниже в листинге 45. Скомпилируйте запрос в режиме "explain" и создайте выходные данные db2exfmt для основного запроса, как было описано в ШАГЕ 1C метода 1. Убедитесь, что план доступа использует разбитые на разделы таблицы и блочный индекс и похож на TPART_MDC, приведенный в разделе Материалы для загрузки.

Листинг 45. Запрос к таблице MDC с разбиением
WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS
( SELECT
		D.MONTH AS MONTH, 
		S.STORE_ID AS STORE_ID,
		S.DISTRICT AS DISTRICT,
		S.REGION AS REGION,
		SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT

  FROM
		SKAPOOR.SALES_FACT_TPART_MDC F1,
		SKAPOOR.DATE_DIM D,
		SKAPOOR.PRODUCT_DIM P,
		SKAPOOR.STORE_DIM S
  WHERE
		P.MODEL LIKE '%model%' AND
		F1.DATE_ID=D.DATE_ID AND
		F1.PRODUCT_ID=P.PRODUCT_ID AND
		F1.STORE_ID=S.STORE_ID AND
		F1.DATE_ID BETWEEN '2006-01-01' AND '2006-01-31' AND
		F1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND
		D.MONTH = 1 
  GROUP BY
		S.STORE_ID,S.DISTRICT,S.REGION,D.MONTH) ,
TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS
(	SELECT 

		D1.MONTH AS MONTH,
		S1.STORE_ID AS STORE_ID,
		S1.DISTRICT AS DISTRICT,
		S1.REGION AS REGION,
		SUM(F2.QUANTITY * F2.PRICE) AS AMOUNT
		
	FROM
		SKAPOOR.SALES_FACT_TPART_MDC F2,
		SKAPOOR.DATE_DIM D1,
		SKAPOOR.PRODUCT_DIM P1,
		SKAPOOR.STORE_DIM S1
	WHERE
		P1.MODEL LIKE '%model%' AND
		F2.DATE_ID=D1.DATE_ID AND
		F2.PRODUCT_ID=P1.PRODUCT_ID AND
		F2.STORE_ID=S1.STORE_ID AND
		F2.DATE_ID BETWEEN '2006-11-01' AND '2006-11-30' AND
		F2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND
		D1.MONTH=11
	GROUP BY
		S1.STORE_ID,S1.DISTRICT,S1.REGION,D1.MONTH) 
SELECT 
	A.*, 
	B.*
FROM
	TMP1 A LEFT OUTER JOIN TMP2 B ON
          (A.STORE=B.STORE AND A.REGION=B.REGION AND A.DISTRICT=B.DISTRICT)
ORDER BY A.AMOUNT_1 DESC, B.AMOUNT_11 DESC;

ШАГ 5C:Перезапустите экземпляр и измерьте производительность с помощью инструмента db2batch, как было показано в методе 1 .

Примечание: Запрос, содержащийся в файле QUERY1.SQL, был изменен на запрос, представленный в листинге 39. Сохраните полученные результаты, см. ШАГ 1D. После этого сохраните результаты.


Метод 6: Использование MQT для предварительного расчета агрегированных результатов

Этот эксперимент похож на метод 1, но с добавлением MQT для предварительного расчета агрегированных результатов. Использование MQT для материализации результатов, выраженных в виде агрегатов, может значительно повысить производительность запроса. В запросе, представленном в листинге 2, каждое плечо внешнего соединения содержит агрегацию по этому соединению. Единственным отличием являются локальные предикаты, применяемые к таблице фактов. Если возможно провести предварительный расчет соединений перед выполнением запроса, производительность выполнения запроса может значительно возрасти.

ШАГ 6A: Создание и обновление MQT

Листинг 46. Создание таблицы MQT
db2 -tvf mqt2.ddl -z mqt2.log
Листинг 47. Содержимое файла mqt2.ddl
CONNECT TO DSS_DB;

------------------------------------------------
-- DDL STATEMENTS FOR TABLE "SKAPOOR "."MQT2"

CREATE TABLE SKAPOOR.MQT2 AS
( 
  SELECT 
     D.MONTH AS MONTH, 
     S.STORE_ID AS STORE_ID,
     S.DISTRICT AS DISTRICT, 
     S.REGION AS REGION, 
     SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT , 
     F1.DATE_ID 
  FROM
     SKAPOOR.SALES_FACT F1, 
     SKAPOOR.DATE_DIM D,
     SKAPOOR.PRODUCT_DIM P, 
     SKAPOOR.STORE_DIM S 
  WHERE 
     F1.DATE_ID=D.DATE_ID AND 
     F1.PRODUCT_ID=P.PRODUCT_ID AND 
     F1.STORE_ID=S.STORE_ID AND 
     P.MODEL LIKE '%MODEL%' 
  GROUP BY S.STORE_ID,S.DISTRICT,S.REGION,D.MONTH, F1.DATE_ID
) 
DATA INITIALLY DEFERRED REFRESH DEFERRED IN FACT_SMS;

REFRESH TABLE "SKAPOOR "."MQT2";


-- DDL STATEMENTS FOR INDEXES ON TABLE "SKAPOOR "."MQT2"

CREATE INDEX "SKAPOOR "."MQT2_IND3" ON "SKAPOOR "."MQT2" 
		("MONTH" ASC,
		 "DATE_ID" ASC)
		ALLOW REVERSE SCANS;


-- DDL STATEMENTS FOR INDEXES ON TABLE "SKAPOOR "."MQT2"

CREATE INDEX "SKAPOOR "."MQT2_IND4" ON "SKAPOOR "."MQT2" 
		("DATE_ID" ASC,
		 "STORE_ID" ASC,
		 "DISTRICT" ASC,
		 "REGION" ASC,
		 "MONTH" ASC,
		 "AMOUNT" ASC)
		ALLOW REVERSE SCANS;

Для повышения производительности за счет доступа к данным из MQT в листинге 41 были созданы два индекса, MQT2_IND3 и MQT2_IND4.

ШАГ 6B: Соберите статистику по MQT указанным ниже способом, изменив схему в соответствии с вашей средой:

Листинг 48. Сбор статистики по таблице MQT
DB2 RUNSTATS ON TABLE SKAPOOR.MQT2 WITH DISTRIBUTION AND INDEXES ALL

ШАГ 6C: Разъясните запрос из листинга 2 и создайте выходную информацию db2exfmt, как было описано в шаге 1C метода 1. После этого изучите план доступа. Вы должны увидеть, что для выполнения внешнего соединения двух MQT была выбрана MQT2 с одним оператором соединения. План должен быть похож на Test 6, приведенный в разделе Материалы для загрузки.

Если эта MQT не была выбрана, убедитесь, что конфигурационному параметру базы данных DFT_REFRESH_AGE присвоено значение "ANY"; в противном случае оптимизатор не будет рассматривать возможность использования данной MQT.

ШАГ 6D: Перезапустите экземпляр и измерьте производительность с помощью инструмента db2batch, как было описано в шаге 1D метода 1.

После этого сохраните результаты.


Изучение влияния каждого из методов на эффективность выполнения запроса

Примечание: Все запросы выполнялись в среде, где не выполнялось никаких иных задач.

В представленной ниже таблице приведена измеренная с помощью db2batch продолжительность выполнения запросов (в секундах) на нашей системе.

МЕТОДЗАПРОСВРЕМЯ ВЫПОЛНЕНИЯ (СЕК)
1. Ограничения ссылочной целостностиЛистинг 2 115,00
2. Реплицированные MQTЛистинг 2 103,42
3. Многомерная кластеризация и реплицированные MQTЛистинг 3938,36
4. Разбиение таблиц и реплицированные MQTЛистинг 42197,74
5. Разбиение таблиц, MDC и реплицированные MQT Листинг 4532,21
6. MQT для предварительного расчета агрегированных результатовЛистинг 27,61

Результаты показывают, что использование MQT для предварительного расчета агрегированных результатов дает наиболее значительное повышение производительности выполнения запроса. Многомерная кластеризация и разбиение таблиц на разделы в сочетании с MDC также показали значительное повышение производительности по сравнению со схемой типа "звезда", приведенной в методе 1.

В нашей среде репликация таблиц размерностей повысила производительность незначительно. Это вызвано тем, что все четыре раздела базы данных являются логическими и созданы на одной физической машине; если для разделов базы данных используется несколько серверов этот метод должен обеспечить более существенную экономию времени, особенно в тех случаях, когда между разделами базы данных передаются значительные объемы данных.

Разбиение таблицы само по себе фактически снизило производительность. В нашем тесте мы не создавали индексы, чтобы оценить влияние одного лишь исключения разделов. Дополнительные условия, наложенные на таблицу фактов, еще сильнее сократят раздел, используемый каждой ветвью внешнего соединения. Еще одним преимуществом создания одного или нескольких индексов для таблицы является более высокая производительность. Это продемонстрировано в примере, предложенном читателю в методе 4.

Эти эксперименты показали, что использование различных возможностей DB2 9 может значительно повысить производительность выполнения запросов бизнес-анализа.


Заключение

Методы, описанные в этой статье, представляют лишь некоторые способы повышения производительности выполнения запросов в среде бизнес-анализа. Советуем вам попробовать выполнить упражнения, приведенные в следующем разделе и в тексте статьи.


Упражнения

Попробуйте:

  1. Вы можете воспользоваться инструментом DB2 Design Advisor, который может порекомендовать применение индексов, многомерной кластеризации, разбиения базы данных на разделы, материализованных таблиц запросов. Посмотрите с помощью Design Advisor, какие индексы, MDC и MQT рекомендуются для запроса, приведенного в листинге 2, используя базовую таблицу SALES_FACT.
  2. Опробуйте некоторые из описываемых в этой статье методов на какой-либо тестовой подборке запросов.
  3. Повторите упражнение 1 на тестовой подборке запросов.

Загрузка

ОписаниеИмяРазмер
JOIN_ELIMjoin_elim.txt49KB
Test 1test1.txt117KB
Test 2test2.txt120KB
Test 3test3.txt117KB
Test 6test6.txt50KB
TPARTtpart.txt102KB
TPART_MDCtpart_mdc.txt118KB

Ресурсы

Научиться

Получить продукты и технологии

Обсудить

Комментарии

developerWorks: Войти

Обязательные поля отмечены звездочкой (*).


Нужен IBM ID?
Забыли Ваш IBM ID?


Забыли Ваш пароль?
Изменить пароль

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Профиль создается, когда вы первый раз заходите в developerWorks. Информация в вашем профиле (имя, страна / регион, название компании) отображается для всех пользователей и будет сопровождать любой опубликованный вами контент пока вы специально не укажите скрыть название вашей компании. Вы можете обновить ваш IBM аккаунт в любое время.

Вся введенная информация защищена.

Выберите имя, которое будет отображаться на экране



При первом входе в developerWorks для Вас будет создан профиль и Вам нужно будет выбрать Отображаемое имя. Оно будет выводиться рядом с контентом, опубликованным Вами в developerWorks.

Отображаемое имя должно иметь длину от 3 символов до 31 символа. Ваше Имя в системе должно быть уникальным. В качестве имени по соображениям приватности нельзя использовать контактный e-mail.

Обязательные поля отмечены звездочкой (*).

(Отображаемое имя должно иметь длину от 3 символов до 31 символа.)

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Вся введенная информация защищена.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Information Management
ArticleID=226990
ArticleTitle=Повышение производительности запросов DB2 в среде интеллектуального бизнес-анализа
publish-date=05292007