Создание физической модели базы данных

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

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

Пример. Вернемся к нашей учебной базе данных и напишем фрагмент скрипта для создания кластера для таблиц DEPARTAMENT и EMPLOYEE. Для создания кластеров используется команда SQL CREATE CLUSTER, которая в нашем случае будет иметь вид

CREATE CLUSTER emp_dept_c (DEPNO integer) 
SIZE 512,
-- TABLESPACE ѕ
-- STORAGE ѕ
INDEX;
CREATE TABLE DEPARTAMENT 
( 
	DEPNO 	integer NOT NULL, 
	DNAME 	char(20),
	LOC 		char(20),
	MANAGER 	char(20),
	PHONE  	char(15),
	CLUSTER emp_dept_c (DEPNO)
);

CREATE TABLE EMPLOYEE
(
	EMPNO		integer NOT NULL,
	ENAME		char(25),
	LNAME		char(10),
	DEPNO		int NOT NULL,,
	SSECNO		char(10),
	JOB  		char(25),
	AGE  		date,
	HIREDATE 	date NOT NULL WITH DEFAULT,
	SAL  		dec(9,2),
	COMM		dec(9,2),
	FINE		dec(9,2),
	PRIMARY KEY (EMPNO),
	CLUSTER emp_dept_c (DEPNO) 
);

CREATE INDEX emp_dept_c_id ON CLUSTER emp_dept_c;

Назначение и смысл закомментированных предложений команды CREATE мы будем обсуждать в следующей главе в отдельном разделе. Они приведены здесь для полноты изложения. Параметр SIZE определяет размер кластера и, фактически, максимальное число кластерных ключей, которые могут сохраняться на одной физической странице данных. Ключевое слово INDEX означает, что создаваемый кластер является индексным.

Предложение CLUSTER emp_dept_c (DEPNO) указывает СУБД, что таблица должна быть добавлена в кластер. Обратите внимание, что в таблице DEPARTAMENT снято ограничение первичного ключа на колонку DEPNO. Это связано с тем, что Oracle автоматически создает индекс на первичный ключ, а этот индекс в данном случае не нужен. Последнее предложение создает кластерный индекс для кластерного ключа кластера.

В одном из предыдущих разделов мы уже обсуждали вопрос использования таблиц хэширования, которые на самом деле представляют один из способов реализации кластера. Там же был приведен пример создания хэш-кластера на диалекте SQL СУБД SQLBase. Рассмотрим теперь подробно, как работать с хэш-кластером СУБД Oracle.

Напомним, что хэширование является способом хранения таблиц данных для увеличения производительности выборки. Физическим механизмом реализации хэширования в СУБД Oracle является хэш-кластер. Данные выбираются из хэш-кластера в соответствии с функцией хэширования, которая используется для генерации распределения значений ключа таблицы в числовые значения, определяющие физические страницы базы данных. Хэш-кластер является альтернативной техникой создания таблиц данных по отношению к индексному кластеру или некластеризованной таблице.

Пример. Рассмотрим нашу учебную базу данных с целью создания хэш-кластера для таблицы EMPLOYEE. На рис. 11.3 ниже показано, как будет выполняться доступ к записям таблицы до и после кластеризации.

Рис. 11.3. Доступк строке таблицы EMPLOYEE через индекс по колонке EPMNO

SELECT * FROM EMPLOYEE WHERE EMPNO= 997;

До кластеризации по колонке EPMNO доступ будет выполняться через индекс, и согласно рисунку 11.3 потребуется 4 операции ввода/вывода, чтобы получить результирующую строку.

После кластеризации по колонке EPMNO строки таблицы EMPLOYEE будут сохраняться в структуре, которая условно приведена на рисунке ниже. После хэширования ключа потребуется одна операция ввода/вывода, чтобы получить результирующую строку, если нет цепочек переполнения.

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

Пример. Создадим хэш-кластер для таблицы EMPLOYEE нашей учебной базы данных. Фрагмент скрипта приведен ниже.

CREATE CLUSTER PERSONNEL (EMPNO integer) 
SIZE 512 
HASHKEYS 500 
-- STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 10)
;

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

Параметр SIZE определяет максимальное число хэш-ключей, размещаемое на физической странице базы данных. Он равен оценке общего пространства в байтах, требуемого для сохранения среднего числа строк, связанного с каждым значением хэш-ключа. Если доступного пространства на странице - 1600 байт, а значение параметра - 512 байт, то три значения хэш-ключа будут распределяться на физической странице.

С помощью предложения HASH IS вы можете переопределить хэш-функцию, которую СУБД Oracle использует по умолчанию.

Пример. Если у нас есть хэш-кластер для таблицы EMPLOYEE и кластерный ключ определен как код домашнего адреса сотрудника, то вероятно, что будет случаться много коллизий в хэш-кластере, если городок, где живут сотрудники, невелик. Для того чтобы избежать такой коллизии, можно переопределить встроенную хэш-функцию Oracle в команде CREATE CLUSTER, добавив предложение HASH IS, как показано ниже.

CREATE CLUSTER personnel 
 (home_area_code  number,
home_prefix     number ) 
HASHKEYS 20
HASH IS MOD(home_area_code + home_suffix_tel, 101);

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

В заключение отметим следующее. Несмотря на то, что СУБД Oracle, так же как и СУБД SQLBase, интенсивно использует кластеры для доступа к системным таблицам базы данных, автор настоящего курса рекомендует проектировщикам базы данных проявлять осторожность при принятии решения о кластеризации таблиц при создании новой базы данных. Выигрыш в производительности может быть не слишком высок по сравнению с другими проектными решениями. Проектирование кластеров - штучная работа. Очень полезно знать статистику использования аналогичного кластера при эксплуатации аналогичной базы данных, чтобы построить высокопроизводительный кластер. Придерживайтесь следующих эмпирических правил:

1. До 1000 записей СУБД не имеет больших преимуществ перед последовательным файлом.
2. От 1000 до 10000 записей это преимущество незначительно.
3. От 10000 до 100000 записей между настольными и промышленными СУБД не ощущается разницы в производительности.
4. От 100000 до 1000000 записей промышленные СУБД обеспечивают приемлемую производительность без специальных способов ее повышения.
5. От 1000000 записей надо начинать думать о повышении производительности.

Литература: [7], [14], [20], [23], [45].