Циклы зависимых таблиц
Использование ограничений ссылочной целостности может порождать так называемые ссылочные циклы. Для пояснения этого понятия обратимся к учебной базе данных. Таблица EMPLOYEE содержит колонку DEPNO (внешний ключ, содержащий ссылку на таблицу DEPARTAMENT). Если добавить в таблицу EMPLOYEE колонку MNGR, которая определяет руководителя каждого служащего, а в таблицу DEPARTAMENT - колонку с номером руководителя MRGNO, то мы получим пример ссылочного цикла (при определении ссылочной целостности по этим атрибутам).
Такой вид циклической связи порождает проблемы для операций манипулирования данными. Допустим, что на работу принят служащий, который должен руководить вновь созданным отделом 100. Последовательное выполнение операторов INSERT будет неуспешным (строка в дочерней таблице EMPLOYEE ссылается на отдел, которого еще нет, и такого менеджера еще не существует).
INSERT INTO EMPLOYEE (EMPNO,ENAME,LNAME,DEPNO, JOB,AGE,HIREDATE,SAL,COMM,FINE,MNGR) VALUES(4000,"Анисимов","Виктор",100,"Менеджер",,2500000,,,NULL);
INSERT INTO DEPARTAMENT (DEPNO, DNAME, LOC, MANAGER, MRGNO,PHONE) VALUES (100,'Маркетинг','Москва', 'Анисимов', 4000,1352519);
Операция добавления в базу данных записей, связанных с организацией нового отдела и прихода его руководителя, при поддержке ссылочной целостности в случае ссылочного цикла будет следующей:
INSERT INTO EMPLOYEE (EMPNO,ENAME,LNAME,DEPNO, JOB,AGE,HIREDATE,SAL,COMM,FINE,MNGR) VALUES(4000, "Анисимов","Виктор",NULL, "Менеджер",,2500000,,,NULL);
INSERT INTO DEPARTAMENT (DEPNO, DNAME, LOC, MANAGER, MRGNO,PHONE) VALUES (100,'Маркетинг','Москва', 'Анисимов', 4000,1352519);
UPDATE EMPLOYEE SET DEPNO=100 WHERE EMPNO=4000;
Аналогичным образом могут, при наличии ссылочных циклов, возникать проблемы с операцией удаления кортежей в родительской и дочерних таблицах. Следует избегать наличия ссылочных циклов в таблицах. Так, для учебной базы данных добавление новых полей и установка для них ограничений ссылочной целостности совсем не обязательна.
Более подробное описание работы с ограничениями ссылочной целостности приводятся в соответствующих руководствах фирм - изготовителей СУБД.
В этом разделе мы изучили ограничения, которые поддерживаются в реляционной базе данных средствами СУБД. Такие ограничения представляют встроенные в СУБД механизмы обеспечения целостности данных и поддержку ссылочной целостности. Исходными данными для наложения ограничений являются бизнес-правила предметной области базы данных. Решение о том, как будут использоваться эти бизнес-правила при проектировании физической модели базы данных в рамках предоставляемых СУБД механизмов добавления ограничений, находится полностью в компетенции проектировщиков баз данных. Проектировщик базы данных может принять решение о том, что ссылочная целостность будет контролироваться через триггеры, а целостность данных будет отдана на откуп разработчикам программ или опять же будут использованы триггеры. Или для выполнения такого контроля будет предусмотрено специальное приложение базы данных, или такой контроль будут осуществлять организационными мероприятиями.
Ниже приведен типичный синтаксис команды ALTER TABLE.
Типичный синтаксис команды ALTER TABLE (без учета ссылочной целостности):
ALTER TABLE имя_таблицы DROP [имя_колонки [,имя_колонки ѕ]] ADD имя_колонки тип_данных [(размер)] [NOT NULL | NOT NULL WITH DEFAULT] RENAME имя_колонки новое_имя | TABLE новое_имя MODIFY имя_колонки тип_данных [(размер)] [NULL | NOT NULL | NOT NULL WITH DEFAULT]
Добавление CHECK-ограничения в спецификацию колонки
Ограничение CHECK позволяет выполнять проверку содержимого колонки относительно некоторых условий и списка значений. Она налагается с помощью предложения CHECK. Для добавления этого ограничения нужно после объявления столбца в спецификации колонки определить синтаксическую конструкцию CHECK (предикат). Согласно требованиям стандарта с помощью ключевого слова VALUE в предикате вы ссылаетесь на значение колонки. Но практически во всех диалектах для этой цели используется имя колонки.
Пример. В учебной базе данных в таблице EMPLOYEE для сотрудников может указываться признак пола: 0 - мужской, 1 - женский. Бизнес-правило предметной области для значений этого поля может быть сформулировано так:
Лицо, принимаемое на работу, может иметь один из двух допустимых признаков пола.
Тогда спецификация колонки может выглядеть так:
SEX int NOT NULL CHECK (SEX=0 OR SEX=1),
Добавление колонок в таблицы
Следующим шагом проектировщика базы данных является определение колонок для базовых таблиц. Колонки таблицы должны представлять атрибуты отношений логической модели реляционной базы данных. Эти атрибуты необходимо преобразовать в спецификации колонок в команде CREATE TABLE. Спецификация колонки таблицы имеет следующий синтаксис: имя колонки, тип данных для значений, сохраняемых в колонке, список ограничений.
Сначала рассмотрим задачу добавления колонок. Колонка должна иметь имя. Имена атрибутов соответствующих отношений логической модели преобразуются в имена колонок в соответствии с правилами именования объектов, принятых в конкретной СУБД. Обычно, как указывалось выше, это ограничение на длину имени и использование в имени специальных символов. Например, в некоторых СУБД допускается использовать знак доллара в имени, однако этот знак обычно не распознается в командах выборки данных - SELECT.
Имеется еще одна проблема в именовании колонок: имена колонок должны интерпретироваться пользователем однозначно. Например, если проектировщик базы данных назначит для фамилии сотрудника короткое имя LN, то, наверное, потребуется комментарий, в котором необходимо указать, что это фамилия, а не линия (например, линия производства). Если невозможно по каким-то причинам применять длинные имена полей, то следует использовать словарь данных для интерпретации введенных аббревиатур.
При назначении имен колонок проектировщик базы данных продолжает формировать стандартный список имен колонок и их сокращений в словаре данных. Он также должен выполнить проверку списка имен в словаре данных, чтобы избежать конфликтов имен в базе данных в целом.
Пример. Продолжим работу с учебным примером. После именования колонок получим следующее:
CREATE TABLE DEPARTAMENT ( DEPNO, имя колонки DNAME, LOC, MANAGER, PHONE, );
CREATE TABLE EMPLOYEE ( EMPNO, ENAME, LNAME, DEPNO, SSECNO, PROJNO, JOB, AGE, HIREDATE, SAL, COMM, FINE, ); CREATE TABLE PROJECT ( PROJNO, PNAME, BUDGET, );
Добавление ограничения первичного ключа и внешнего ключа
Мы уже рассматривали вопрос о задании ограничений первичного ключа в предыдущем разделе. Там же был показан пример задания ограничений внешнего ключа при разрешении связей "многие-ко-многим". Более детально мы разберем работу с ограничением внешнего ключа при обсуждении поддержки ссылочной целостности ниже.
Добавление ограничения UNIQUE в спецификацию колонки
Ограничение UNIQUE гарантирует уникальность значения данных в колонке. Оно используется, если нужно следить за тем, чтобы значения колонки, не являющейся первичным ключом, были уникальны в таблице. При этом проверяется уникальность всех значений, отличных от NULL.
Пример. В учебной базе данных в таблице EMPLOYEE используется номер социальной страховки SSECNO, для которого бизнес-правило состоит в том, что для каждой персоны, если она имеет такой номер, он должен быть уникальным. Установить уникальность этих номеров можно следующей спецификацией колонки:
SSECNO char(10) UNIQUE,
Ограничение UNIQUE можно определить также в конце команды CREATE TABLE в следующей синтаксической форме: UNIQUE (SSECNO).
Добавление, удаление и блокирование ограничений
Ограничения задаются в спецификациях колонки или спецификациях ключей при создании таблицы в командах SQL CREATE TABLE или налагаются после создания таблицы в командах SQL ALTER TABLE. Как добавить ограничения в таблицу с помощью команды CREATE TABLE, мы уже знаем. Чтобы добавить ограничения с помощью команды ALTER TABLE, можно поступать следующим образом.
Пример. Для нашей учебной базы данных мы могли бы не определять первичный ключ в таблице EMPLOYEE в команде CREATE TABLE и после нее выполнить команду
ALTER TABLE EMPLOYEE PRIMARY KEY (EMPNO);
Аналогично, мы могли бы установить ограничение внешнего ключа в таблице EMP_PRJ следующим образом:
CREATE TABLE EMP_PRJ ( EMPNO integer NOT NULL, PROJNO char(8) NOT NULL, WORKS number, PRIMARY KEY (EMPNO, PROJNO), ); ALTER TABLE EMP_PRJ FOREING KEY (EMPNO) REFERENCES EMPLOYEE ON DELETE RESTRICT, FOREING KEY (PROJNO) REFERENCES PROJECT ON DELETE RESTRICT;
Чтобы удалять ограничения первичного и внешнего ключей, можно использовать команду ALTER TABLE в синтаксической форме
ALTER TABLE EMPLOYEE DROP PRIMARY KEY (EMPNO);
В СУБД Oracle 9i для создания ограничений на уровне таблицы используется следующий синтаксис команды ALTER TABLE:
ALTER TABLE имя_таблицы ADD CONSTRAINTS ограничение TYPE(колонка);
а для удаления
ALTER TABLE имя_таблицы DROP CONSTRAINTS ограничение.
Кроме этого, в СУБД Oracle 8i можно блокировать и деблокировать действие ограничений с помощью опций DISABLE и ENABLE команды ALTER TABLE, как показано в примере ниже:
ALTER TABLE EMPLOYEE DISABLE PRIMARY KEY; ALTER TABLE EMPLOYEE ENABLE PRIMARY KEY;
После использования опции DISABLE ограничение становится неактивным, но его определение остается в словаре базы данных. Вы можете вернуть активность ограничению с помощью опции ENABLE. Использование опции DROP полностью удаляет ограничение из базы данных (и словаря базы данных также).
Использование опции DEFAULT
Опция DEFAULT заставляет СУБД размещать значение по умолчанию в колонке, когда кортеж вставляется в таблицу и никакого значения колонки не представлено. Чтобы указать значение по умолчанию, нужно в спецификацию колонки добавить ключевое слово "DEFAULT" и после него указать любое значение, являющееся достоверным экземпляром типа данных колонки.
Пример. Для нашей учебной базы данных мы могли бы определить значение по умолчанию для числовых колонок в таблице EMPLOYEE:
SAL dec(9,2) DEFAULT(0), COMM dec(9,2) DEFAULT(0), FINE dec(9,2) DEFAULT(0),
Назначение первичных ключей таблицам
После определения всех колонок и их типов следует перейти к идентификации первичных ключей таблицы. Согласно требованиям реляционной теории каждая строка таблицы (кортеж) должна иметь уникальный первичный ключ. Обычно хорошим кандидатом на первичный ключ таблицы является первичный ключ отношения логической модели. Поскольку предполагается, что в отношении логической модели задан первичный ключ, обладающий свойством минимальности, то его просто нужно определить в команде CREATE TABLE. Такое определение первичного ключа таблицы для многих таблиц не является окончательным. Переопределение первичного ключа может происходить на следующих этапах физического проектирования базы данных.
Задание колонки как первичного ключа в контексте многих СУБД, в том числе и Oracle, считается ограничением на значение колонки (см. следующий подраздел).
Стандартом SQL-92 предусмотрено специальное предложение PRIMARY KEY команды CREATE TABLE для спецификации первичного ключа таблицы. Атрибуты первичного ключа перечисляются через запятую и заключаются в круглые скобки. Если спецификация PRIMARY KEY не определяется, то считается, что таблица не имеет первичного ключа. При этом допускается дублирование строк в таблице.
Пример. Для нашего примера колонками первичного ключа могут быть назначены: колонка DEPNO в отношении DEPARTAMENT, колонка EMPNO в отношении EMPLOYEE, колонка PROJNO в отношении PROJECT.
CREATE TABLE DEPARTAMENT ( DEPNO integer, DNAME char(20), LOC char(20), MANAGER char(20), PHONE char(15), PRIMARY KEY (DEPNO) определение первичного ключа );
CREATE TABLE EMPLOYEE ( EMPNO integer, ENAME char(25), LNAME char(10), DEPNO int, SSECNO char(10), PROJNO char(8), J OB char(25), AGE date, HIREDATE date, SAL dec(9,2), COMM dec(9,2), FINE dec(9,2), PRIMARY KEY (EMPNO) );
CREATE TABLE PROJECT ( PROJNO char(8), PNAME char(25), BUDGET dec(9,2), PRIMARY KEY (PROJNO) );
В СУБД Oracle можно задавать ограничение первичного ключа, т.е. определять колонку как первичный ключ, как часть спецификации колонки, а не как часть спецификации таблицы (см.
пример выше). При использовании такой техники команда CREATE TABLE будет выглядеть так, как показано ниже для таблицы DEPARTAMENT:
CREATE TABLE DEPARTAMENT ( DEPNO integer primary key, определение первичного ключа DNAME char(20), LOC char(20), MANAGER char(20), PHONE char(15), );
Когда вы определяете PRIMARY KEY при создании таблицы, многие реляционные СУБД требуют обязательного создания уникального индекса первичного ключа. Индексы, так же как и таблицы, являются объектами реляционной базы данных (но не реляционной модели). Логически индексы представляют собой таблицу, в которой каждому значению индексируемой колонки ставится в соответствие некоторая информация, связанная с ее месторасположением на физическом носителе. Индексы предназначены для организации быстрого доступа к строкам таблицы и обеспечения контроля целостности данных (механизм индексов будет блокировать вашу базу данных от повторного ввода строк в таблицу с одинаковыми значениями индексируемых атрибутов). Индекс создается с помощью команды
CREATE UNIQUE INDEX NDXDEPT ON DEPARTAMENT (DEPNO);
Предложение CREATE INDEX определяет имя индекса, предложение ON определяет имя таблицы и колонок, для которой и по которым строится индекс, ключевое слово UNIQUE указывает, что индексируемые значения колонок должны быть уникальными для таблицы, т.е. исключается дублирование значений в индексируемой колонке. Таблица должна быть уже создана, и должна содержать определения индексируемых столбцов. Спецификация UNIQUE опциональна, и вы можете также создавать и неуникальные индексы.
Следует помнить, что для многих СУБД целостность первичного ключа поддерживается заданием уникального индекса на колонку ключа, иначе создание таблицы считается незавершенным.
Поэтому для завершения процедуры определения первичных ключей базовых таблиц проектировщик базы данных должен будет добавить в скрипт три команды CREATE UNIQUE INDEX, как показано ниже:
CREATE UNIQUE INDEX NDXDEPT ON DEPARTAMENT (DEPNO); CREATE UNIQUE INDEX NDXEMPLOYEE ON EMPLOYEE(EMPNO); CREATE UNIQUE INDEX NDXPROJECT ON PROJECT(PROJNO);
Для диалекта SQL СУБД Oracle этого делать не нужно, т.к. она автоматически поддерживает целостность первичного ключа.
После выполнения вышеперечисленных действий задачу определения первичных ключей базовых таблиц в первом приближении можно считать законченной и перейти к решению следующей очень важной задачи определения таблиц - определению ограничений на значения колонок.
Обавление NOT NULL ограничения в спецификацию колонки
NOT NULL ограничение гарантирует, что колонка всегда содержит значения. СУБД не будет разрешать вставлять или обновлять строку таблицы, если в ней существует колонка с ограничением NOT NULL, а данных для этой колонки в добавляемой строке не представлено. Как мы уже видели выше, для колонок первичного ключа это ограничение нужно устанавливать всегда.
Пример. Для нашей учебной базы данных действует правило, что сотрудник всегда должен иметь имя и фамилию. Чтобы удовлетворить этому правилу, нужно определить следующую спецификацию колонок ENAME и LNAME в таблице EMPLOYEE:
ENAME char(25) NOT NULL, LNAME char(10) NOT NULL,
Иногда ограничение NOT NULL используется вместе с опцией DEFAULT, как это было определено в спецификации колонки HIREDATE (дата приема на работу) в таблице EMPLOYEE:
HIREDATE date NOT NULL WITH DEFAULT,
Ограничения и их использование в реляционной базе данных
В предыдущих разделах мы уже сталкивались с несколькими типами ограничений в спецификациях колонок - NOT NULL, и ограничениях в таблицах - PRIMARY KEY, FOREING KEY. В данном разделе мы изучим практически все виды ограничений, которые поддерживаются в реляционных базах данных. Ограничения являются важным инструментом проектировщика базы данных, с помощью которого он поддерживает целостность (strong) базы данных. Их можно использовать для того, чтобы быть уверенным в том, что колонка первичного ключа таблицы является уникальной и всегда содержит значения. Ограничения используются также для поддержки ссылочной целостности. Последнее означает, что значения в колонке внешнего ключа должны существовать как некоторое значение в колонке первичного ключа другой таблицы.
Замечание. Одна из главных концепций реляционной базы данных состоит в том, что всегда целесообразнее заставлять саму базу данных поддерживать свою непротиворечивость, чем заставлять решать эту задачу приложение базы данных. Вопрос о том, что лучше: передавать поддержку ограничений приложениям базы данных или оставлять самой базе данных, - остается все еще открытым.
Ограничения представляют собой способ применения бизнес-правил предметной области на уровне базы данных и гарантируют совместимость вводимых данных с теми, которые уже находятся в таблицах. В реляционной базе данных под ограничением понимается правило (условие), которому должен удовлетворять некоторый элемент в базе данных. Например, условия, которым должны дополнительно удовлетворять значения колонки таблицы в рамках определенного для нее типа данных (т.е. тип данных плюс правило), полностью воплощают концепцию домена в физической модели реляционной базы данных.
Как мы видели выше, ограничения могут применяться на уровне колонки (ограничения колонки) или на уровне таблицы (ограничения таблицы). Ограничения первичного ключа - это ограничения, действующие на уровне таблицы, а NOT NULL ограничения - это ограничения на уровне колонки. Существуют три основных типа ограничений, используемых в реляционной базе данных, - ограничения целостности данных, ограничения целостности ссылок и ограничения первичного ключа.
Ограничения целостности данных (data integrity constraints) относятся к значениям данных в некоторых колонках и определяются в спецификации колонки с помощью элементов SQL NOT NULL, UNIQUE, CHECK. Ограничения целостности ссылок (referential constraints) относятся к связям между таблицами на основе связи первичного и внешнего ключей. Ограничения первичного ключа относятся к значениям данных в колонках первичного ключа таблицы и должно налагаться на каждую базовую таблицу реляционной базы данных. В таблице ниже приведен список ограничений, применяемых в реляционных базах данных.
| Ограничение | Описание | |
Определение базовых таблиц
Как указывалось выше, первый шаг в построении внутренней схемы есть идентификация реляционной таблицы, которая будет сохраняться в базе данных. При решении этой задачи проектировщик базы данных имеет на входе отношения логической модели реляционной базы данных, представляющие сущности предметной области, а на выходе должен создать набор макетных команд CREATE TABLE, которые будут использоваться далее для добавления колонок и других деталей.
Базовые таблицы создаются для каждого отношения логической модели и являются главными объектами хранения данных в базе данных. Для каждой базовой таблицы определяется длинный идентификатор, который уникально идентифицирует таблицу в базе данных. Это имя должно соответствовать стандартам наименований сущностей предметной области базы данных, если такие стандарты были разработаны администратором данных на стадии анализа предметной области базы данных. Имена таблиц должны быть занесены проектировщиком базы данных в словарь данных базы данных.
Далее проектировщик базы данных формирует список всех пользователей каждой таблицы. При этом выделяются так называемые владельцы таблицы, т.е. пользователи, которые имеют все права доступа к таблице. Этот список используется на завершающем этапе проектирования физической структуры базы данных при авторизации пользователей и разграничении полномочий доступа.
Когда проектировщик заканчивает обработку всех отношений логической модели данных, он должен еще раз проверить, чтобы число базовых таблиц соответствовало числу отношений логической модели реляционной базы данных (т.е. было не меньше, чем число сущностей предметной области базы данных). Таким образом, при создании базовых таблиц проектировщик базы данных придерживается принципа "каждому отношению логической модели базы данных по базовой таблице".
Пример. Для нашего учебного примера результат решения задачи настоящего пункта может иметь следующий вид:
CREATE TABLE DEPARTAMENT имя таблицы ( ); CREATE TABLE EMPLOYEE имя таблицы ( ); CREATE TABLE PROJECT имя таблицы ( );
Определение типов данных для колонок
После идентификации колонок необходимо задать их тип в соответствии с допустимыми для данной СУБД типами данных. Эта задача упрощается, если в отношениях логической модели определены домены атрибутов. Некоторые из доменов могут быть определены уже в терминах СУБД. Для таких атрибутов практически ничего делать не нужно. Определение домена в терминах типа данных СУБД нужно просто перенести в спецификацию колонки. Возможно, проектировщику будет нужно уточнить второстепенные параметры типа. Например, если задан домен как DEC (9,2), а из контекста предметной области следует, что в этой колонке будет накапливаться итоговая сумма расходов за год, то может быть целесообразным определить тип как DEC (15,2), чтобы избежать возможного переполнения при работе приложений базы данных.
Если домен определен не в терминах СУБД, проектировщик базы данных должен преобразовать его в подходящий тип данных. При выполнении таких преобразования следует учитывать ряд факторов.
Следует уточнить, как СУБД физически хранит данные того или иного предопределенного типа, и затем уточнить интервалы изменения значений колонок. Например, если тип переменной - varchar (3), которая содержит код, чье значение изменяется в интервале от '10A' ' до '99Z', то целесообразно с точки зрения хранения изменить тип этой переменной на char(3). Это объясняется тем, что тип varchar при физическом хранении занимает на байт-два больше, чем тип char при одной и той же объявленной длине.Для числовых значений фиксированной длины предпочтительнее использовать тип DEC. Он обрабатывается процессором быстрее, чем тип FLOAT. Исключение составляют данные для научных расчетов, где представление чисел в экспоненциальной форме бывает необходимо.Используйте INT и SMALLINT исключительно для счетчиков.Старайтесь избегать использования LONG VARCHAR без лишней надобности. Обычно колонки такого типа хранятся на отдельном экстенте жесткого диска, причем не в той области диска, где хранятся остальные данные таблицы. Избегайте использовать тип CHAR для представления числовых данных.
Во-первых, может потребоваться дополнительная проверка, а во-вторых, могут возникнуть проблемы при сортировке таких колонок, поскольку число, заданное строкой '11', будет находиться выше, чем число, заданное строкой '9', при упорядочивании по возрастанию.Используйте типы DATE и TIME только для хранения хронологических данных.Используйте тип DATETIME исключительно для целей управления данными.
Пример. Продолжим изучение нашего примера. После определения типов колонок наши команды для создания таблиц будут выглядеть следующим образом:
CREATE TABLE DEPARTAMENT ( DEPNO integer, имя колонки, тип, длина DNAME char(20), LOC char(20), MANAGER char(20), PHONE char(15), );
CREATE TABLE EMPLOYEE ( EMPNO integer, ENAME char(25), LNAME char(10), DEPNO int, SSECNO char(10), PROJNO char(8), JOB char(25), AGE date, HIREDATE date, SAL dec(9,2), COMM dec(9,2), FINE dec(9,2), );
CREATE TABLE PROJECT ( PROJNO char(8), PNAME char(25), BUDGET dec(9,2), );
Особенности манипулирования данными при ограничениях ссылочной целостности
Естественно, что ограничения ссылочной целостности (фиксируя в базе данных связи между таблицами и отслеживая их) оказывают действие на команды манипулирования данными. Такие действия регламентируют правила использования данных команд, в случае, когда таблица имеет внешний ключ.
Для команды INSERT предусмотрены следующие правила:
каждое NOT NULL значение атрибута внешнего ключа должно соответствовать значению атрибута первичного ключа в таблице-родителе;если один из атрибутов внешнего ключа имеет нуль-значение, то принимается нуль-значение для всего ключа, при этом никаких проверок ограничений ссылочной целостности не выполняется;невозможно вставить строки в таблицу-родитель или таблицу-потомок, если таблица-родитель находится в незавершенном состоянии;можно вставить строки в таблицу-родитель, не делая одновременно аналогичных действий в таблице-потомке.
Для команды UPDATE предусмотрены следующие правила:
при обновлении таблицы-потомка каждое ненулевое значение внешнего ключа должно соответствовать значению первичного ключа в родительской таблице;если таблица-потомок имеет нескольких родителей, то значение внешнего ключа должны соответствовать значениям первичного ключа всех таблиц-родителей.
Попытка обновить первичный ключ родительской таблицы возможна лишь тогда, когда ему не соответствует ни одного значения внешнего ключа в таблицах-потомках, причем сделать это можно только для одной строки таблицы в команде UPDATE.
Правила для команды DELETE сообщают СУБД, что ей делать, когда пользователь пытается удалить строку в таблице-родителе. Отметим также, что в командах удаления нельзя использовать подзапрос, ссылающийся на заданную в команде таблицу.
Применение команды DROP в условиях ограничений ссылочной целостности также имеет свои особенности. Так, когда вы удаляете таблицу-родитель или первичный ключ, ограничения ссылочной целостности разрушаются. Если вы удаляете первичный индекс, то вы не разрушаете ограничений ссылочной целостности, но таблица становится незавершенной. Удаление первичного или внешнего ключа физически не удаляет соответствующие колонки.
Отношение "родитель-потомок" между таблицами
Первичный и соответствующий ему внешний ключ позволяют реализовать отношение "родитель-потомок" (parent/child relationship) между таблицами. Они отражают взаимосвязь между объектами предметной области (представленными кортежами таблиц) через значения некоторых их атрибутов по принципу иерархического подчинения, когда объект-родитель определяет существование объектов-потомков. Сами объекты-потомки могут также выступать в качестве родителей для других объектов (descendents).
Таблица реляционной базы данных, содержащая первичный ключ, называется таблицей-родителем (parent table) или родительской таблицей, а таблица, содержащая соответствующий первичному ключу внешний ключ, - таблицей-потомком (child table) или дочерней таблицей. Таблица DEPARTAMENT учебной базы данных является таблицей-родителем для таблицы EMPLOYEE.
Таблица базы данных может не иметь ни родителей, ни потомков. Такие таблицы называются независимыми таблицами (independent tables). Они не удовлетворяют никаким ограничениям ссылочной целостности и СУБД не контролирует и не проверяет правильность ссылок к таким таблицам.
Отношение "родитель-потомок" между таблицами реализуется через атрибуты-ключи соответствующих строк. Строка, принадлежащая таблице-родителю, называется родительской строкой, а строка в таблице-потомке, на которую ссылается родительская строка, называется строкой-потомком или дочерней строкой. Строка-потомок должна иметь по крайней мере один ненулевой атрибут внешнего ключа.
Совсем необязательно, чтобы каждая строка таблицы-родителя была родительской строкой. Аналогично, если строка в таблице-потомке имеет нуль-значение внешнего ключа, то она не будет строкой-потомком.
Отношение "родитель-потомок" между двумя таблицами отражает взаимосвязь по включению на доменах соответствующих атрибутов. Однако, таблица может реализовать отношение иерархического подчинения в самой себе. Примером такой таблицы может стать виртуальная таблица из этой лекции, реализующая отношение Руководитель-подчиненный, если ее сделать таблицей базы данных.
Первичные и внешние ключи
Понятие первичного ключа как уникального идентификатора кортежа отношения уже обсуждалось выше. Здесь нас интересует другой аспект данного понятия. А именно - то, что первичный ключ может гарантировать целостность данных. Если первичный ключ корректно используется, все кортежи отношения остаются различными и отсутствуют кортежи, у которых все атрибуты имеют нуль-значения. Напомним основные свойства первичного ключа:
отношение (таблица) может иметь только один первичный ключ;первичный ключ должен быть уникальным;первичный ключ должен быть минимальным, т.е. включать минимальное число атрибутов, необходимых для однозначной идентификации кортежа;первичный ключ не может содержать нулевых значений;значение первичного ключа не должно меняться при смене состояний базы данных.
Поддержка ссылочной целостности посредством первичного ключа осуществляется через его индекс. Уникальный индекс для первичного ключа отношения называется первичным индексом. Как правило, первичный индекс должен быть единственным для отношения. Если первичный индекс не создан, таблица реляционной базы данных находится в незавершенном состоянии. В такой таблице вы не можете искать, вставлять, удалять или модифицировать строки. Также невозможно создать внешний ключ для ссылки на первичный ключ таблицы.
Замечание. В СУБД Oracle 9i первичный индекс создается автоматически.
В конкретных реализациях СУБД существуют дополнительные ограничения на определение первичного ключа. Так, в SQLBase первичный ключ не может включать более 16 колонок, общая длина первичного ключа не может превышать 255 байт, нельзя использовать колонки типа LONG/LONG VARCHAR, в самоссылающихся строках значение первичного ключа невозможно модифицировать.
Внешний ключ представляет собой ссылку на первичный ключ в данной или другой таблице, т.е. один или несколько атрибутов отношения, которые содержат первичный ключ другого отношения. Примером внешнего ключа является номер отдела DEPNO в таблице EMPLOYEE нашей учебной базы данных.
Из определения следует, что внешний ключ может быть создан только после создания соответствующего первичного ключа (и первичного индекса).
Внешнему ключу обычно назначается специальное имя. Внешние ключи имеют следующие свойства:
Внешний ключ должен содержать такое же число колонок, такого же типа и в том же порядке следования, что и соответствующий первичный ключ.Имена колонок внешнего ключа и их значения по умолчанию могут отличаться от используемых в соответствующем первичном ключе (в том числе иметь нуль-значения).Таблица может иметь любое число внешних ключей.Упорядочение значений колонок внешнего ключа в его индексе может отличаться от соответствующего первичного ключа. Внешний ключ не может ссылаться на виртуальную таблицу.
Поддержка ссылочной целостности посредством внешних ключей не требует соответствующего индекса для внешнего ключа. Однако наличие такого индекса иногда может улучшить производительность базы данных.
В SQLBase существуют ограничения на определение внешнего ключа:
Внешний ключ не может содержать более 16 колонок.Внешний ключ может ссылаться только на первичный ключ родительской таблицы, которая должна находиться в той же базе данных.Внешний ключ не может ссылаться на таблицу системного каталога.
В других СУБД ограничения на определение внешнего ключа носят аналогичный характер.
Понятие внешней схемы
При обсуждении представлений мы уже касались понятия внешней схемы. В этом подразделе мы остановимся на этом понятии более подробно. Создание внешней схемы является желательным, но совсем не обязательным действием проектировщика данных.
В архитектуре трех схем ANSI/SPARC внешняя схема используется для изоляции требований к данным пользователей и приложений от физического размещения данных. Такая изоляция по определению делает пользователей независимыми от аспектов физической модели реляционной базы данных, включающих возможности СУБД и программно-аппаратной платформы. Эта изоляция делает пользователей также независимыми и от информационной модели предметной области, и от логической структуры базы данных или от стратегической политики обработки данных в масштабе организации, которая может быть неестественной для представления данных конкретного пользователя. Таким образом, под внешней схемой принято понимать такую организацию представления данных в базе данных, которое наиболее естественным и простым способом отражало бы взгляд пользователей на эти данные, когда они их обрабатывают. Цель разработки внешней схемы состоит в том, чтобы скрыть от пользователя особенности реализации физической модели базы данных.
В настоящее время стандарт SQL, так же как и большинство промышленных СУБД, не говоря уже об экспериментальных СУБД, обеспечивают поддержку внешней схемы ограниченно. Основным способом изоляции требований пользователей к данным от особенностей их физического хранения является механизм реляционных представлений, который мы обсуждали выше.
Приведем примеры, когда изменения на уровне физической организации базы данных могут быть скрыты от пользователей с помощью представлений.
Изменения владельцев объектов. Исключением может быть изменение владельцев представлений.Изменение имен колонок. Определение представления может отображать старые имена колонок в новые имена, делая, таким образом, эти изменения невидимыми пользователям.Изменения имен таблиц. Добавление колонок в таблицы, так как эти новые колонки не используются существующими представлениями.Физическое переупорядочивание колонок таблицы. В представлениях можно явно задавать нужный порядок.Создание, модификация и удаление индексов. Исключением может быть удаление индекса первичного ключа, когда базовая таблица, участвующая в соединении, становится незавершенной и, следовательно, не будет обрабатываться в запросе (для некоторых СУБД).Комбинация двух или более таблиц в одной. Обычно это предполагает, что первичные ключи этих таблиц одни и те же.Разделение одной таблицы на несколько таблиц.
Создание внешней схемы является в большей степени задачей администратора базы данных, чем задачей проектировщика. Более того, стоимость сопровождения представлений внешней схемы будет определяться стоимостью работ администратора базы данных по созданию и сопровождению представлений, а также по обеспечению безопасности, так как потребуется дополнительная авторизация доступа к представлениям.
Представления и множества
Представления (виртуальные таблицы) позволяют вам явным образом именовать результирующие отношения, получающиеся в промежуточных реляционных операциях, и использовать их как самостоятельные отношения. Так вы можете результату вложенного подзапроса (непоименованное отношение) присвоить имя и применять это отношение вместо вложенного подзапроса как самостоятельное.
Концепция представления особенно важна, когда требуется извлекать информацию из нескольких отношений. Во-первых, она является средством формирования пользователем своей виртуальной (внешней) схемы базы данных. Во-вторых, это средство формирования производных или выводимых атрибутов отношений базы данных, то есть таких атрибутов, которые непосредственно не хранятся в базе данных. Представление можно трактовать как макроопределение: любой запрос по отношению к нему подвергается макрорасширению и преобразуется SQL для ссылки на исходные базовые отношения. Чтобы представление (как производное отношение) стало доступно, вам необходимо дать ему уникальное имя и определить атрибуты.
Обратимся к примеру с футболом. Пусть имеется некоторая база данных, которая содержит всю информацию о чемпионате мира по футболу. Допустим, что вас интересует информация о стадионах и об играх, закончившихся с определенной разностью забитых и пропущенных мячей. Тогда вы можете первоначально определить представление как
CREATE VIEW СТАДРАЗН (стад, разн_мячей ) AS SELECT стадион, голыА - голыВ FROM ИГРЫ, РАЗМ_СТАДИОНОВ WHERE ИГРЫ.год = РАЗМ_СТАДИОНОВ.год AND ИГРЫ.группа = 2 АND РАЗМ_СТАДИОНОВ.группа=2 АND ИГРЫ.игра=РАЗМ_СТАДИОНОВ.игра
В этом примере также демонстрируется, как определять производные колонки в представлении.
Теперь вы можете выполнить запрос к виртуальной таблице, такой, чтобы получить ответ на вопрос: выдать все стадионы, на которых игры закончились с разностью мячей больше 4. Команда языка SQL в этом случае тривиальна:
SELECT стад FROM СТАДРАЗН WHERE разн_мячей > 4;
Таким образом, представление в алгебре отношений является операцией наименования промежуточных результатов.
Представления и независимость данных
Как вы могли видеть, виртуальные таблицы освобождают пользователя от необходимости знать, как хранятся данные. Пользователь имеет внешнюю схему (виртуальные таблицы), база данных определяется внутренней схемой (таблицы данных). Независимость данных определяется наличием двух уровней абстракции в представлении данных. Поддержка независимости данных для пользователя во многом упрощает работу с данными.
Рассмотрим пример из предыдущего раздела. Примененная схема базы данных учебного примера не позволяет использовать одного служащего в нескольких проектах (реализовано отношение "много (служащих) к одному (проекту)"). На практике обычно каждый служащий работает над несколькими проектами (отношение "многие-ко-многим"). Чтобы реализовать такое отношение, необходимо модернизировать структуру базы данных. При этом все ранее разработанные приложения и виртуальные таблицы должны работать соответствующим образом. Также следует использовать новые возможности. Определим отношение "многие-ко-многим" через создание новой таблицы PREM:
CREATE TABLE PREM ( EMPNO integer, PROJNO char(8), WORKS number);
которая будет служить для распределения служащих по проектам. В этой таблице каждому служащему отвечает столько строк, сколько проектов он выполняет. Колонка PROJNO таблицы EMPLOYEE при этом потеряла свой семантический смысл. Удалим эти данные:
UPDATE EMPLOYEE SET PROJNO=NULL;
При этом мы уже не можем пользоваться виртуальной таблицей PERSPROJ, которая использует эту колонку для соединения таблиц EMPLOYEE и PROJECT. Чтобы для пользователя ничего не изменилось, следует переопределить виртуальную таблицу PERSPROJ. Для этого удалим ее с помощью команды SQL DROP. Команда DROP является в SQL универсальной командой для удаления объектов реляционной базы данных: вы только должны определить, что вы хотите удалить: TABLE, VIEW или иной объект.
Удалим определение неподходящей виртуальной таблицы:
DROP VIEW PERSPROJ;
Создадим новую виртуальную таблицу с тем же именем, но учитывающую изменения в схеме базы данных:
CREATE VIEW PERSPROJ AS SELECT ENAME, JOB, PNAME FROM EMPLOYEE, PROJECT, PREM WHERE EMPLOYEE.EMPNO=PREM.EMPNO AND PREM.PROJNO=PROJECT.PROJNO;
Колонку PROJNO в таблице EMPLOYEE следует удалить за ненадобностью:
ALTER TABLE EMPLOYEE DROP PROJNO;
Вы видите, как легко изменить структуру базы данных, не внося слишком больших изменений в ваши приложения и используемые запросы (на виртуальных таблицах). Это объясняется тем, что SQL является непроцедурным языком, т.е. ваши программы и запросы не привязываются к структуре базы данных. За счет модификации виртуальных таблиц вы можете скрыть фактические изменения структуры базы данных от пользователя. Рассмотренный выше пример иллюстрирует основные моменты реализации концепции независимости данных в реляционных базах данных.
Синонимы
Синоним есть другое имя для таблицы или представления. Синонимы используются для того, чтобы сделать базу данных более дружественной для пользователя. Это означает, что объектам базы данных, которые попадают в сферу внимания пользователей, назначаются альтернативные, длинные имена в терминах предметной области базы данных. Такие имена более понятны неподготовленным пользователям базы данных и не будут создавать дополнительных психологических препятствий в работе с базой данных. С другой стороны, если объекты базы данных носят длинные имена, то их неудобно использовать в часто выполняемых запросах, т.к. каждый раз приходится набирать длинную последовательность символов. Синоним позволяет ввести сокращенное имя.
Задача назначения синонимов объектам базы данных является задачей администратора данных организации или администратора базы данных. Проектировщик может определить синонимы объектам базы данных, но он должен согласовать свои действия с администратором данных. Если синонимы определяются проектировщиком, то должен быть составлен список синонимов для передачи его администратору данных. Синонимы хранятся в словаре базы данных.
Синоним по определению может быть общим для всех пользователей базы данных (PUBLIC) или принадлежать пользователю (USER), который его создал. Опция PUBLIC позволяет обращаться к таблице с помощью синонима без уточнения имени таблицы именем владельца. Чтобы создать или удалить синоним PUBLIC, необходимо либо быть владельцем таблицы, либо иметь привилегии пользователей SYS или SYSTEM (Oracle). (Для СУБД SQLBase DBA или SYSADM соответственно.)
Пример. Для нашей учебной базы данных вы можете создать синоним EMP для таблицы EPMPLOYEE с помощью следующей команды:
CREATE PUBLIC SYNONYN EMP FOR EPMPLOYEE;
Или для пользователя SYS:
CREATE SYNONYN SYS.EMPL FOR EPMPLOYEE;
Чтобы удалить синоним EMP таблицы EPMPLOYEE, необходимо использовать команду
DROP PUBLIC SYNONYN EMP;
Создание начальной внутренней схемы реляционной базы данных
В настоящем разделе будет рассмотрена первая профессиональная задача проектировщика базы данных по созданию физической модели реляционной базы данных - создание объектов для хранения данных. Эта задача сводится к созданию таблиц и объектов в базе данных, в которых будет храниться информация о сущностях предметной области. Решая эту задачу, проектировщик базы данных отображает отношения логической модели реляционной базы данных (сущности предметной области, представленные в нормализованной форме на ER-диаграммах) в таблицы и индексы реляционной базы данных. Для выполнения этой задачи используется подмножество команд SQL - язык определения данных DDL (Data Definition Language) (например, для СУБД Oracle эти действия могут быть выполнены в программе SQL*PLUS).
В рамках концепции трехуровневой архитектуры баз данных ANSI/SPARC эту задачу проектировщика базы данных называют еще созданием внутренней схемы. Результатом решения этой задачи является скрипт для создания таблиц и индексов, что составляет первоначальный прототип физической модели базы данных.
Таким образом, физическая модель реляционной базы данных есть такое представление отношений базы данных и связей между ними, которое воплощено в последовательность команд SQL. Выполнение этой последовательности команд создает конкретную базу данных и ее объекты.
Одной из основных задач проектировщика данных при создании физической модели реляционной базы данных является превращение логических отношений базы данных в таблицы базы данных.
Элементарной подзадачей является создание таблицы базы данных. Таблицы в реляционных СУБД1 состоят из одной или более колонок или полей. Колонки представляют собой поименованные ячейки в записи, которые содержат значения. Колонки определяются посредством спецификации, которая определяет формат колонки и ее характеристики, задаваемые с помощью ограничений. Определение таблицы задается командой CREATE TABLE.
Создание первоначальной внешней схемы
Однако, если руководителем ИТ-проекта принято решение о разработке внешней схемы, проектировщик должен создать первоначальный вариант внешней схемы.
Для того чтобы создать внешнюю схему для новой базы данных, проектировщику базы данных необходимо начать с создания так называемых зеркальных представлений. Такое представление разрабатывается для каждой базовой таблицы внутренней схемы и включает все колонки этой таблицы, т.е. является полным зеркальным отображением базовой таблицы. Типичным требованием к таким представлениям является требование явного именования колонок представления, в противном случае приложениям может потребоваться модификация, если имена колонок будут изменены. Далее эти представления используются разработчиками и пользователями для доступа к базовой таблице.
Дополнительные представления могут быть созданы для приложений, которые выполняют так называемые типовые запросы к базам данных. Под типовым запросом здесь понимается запрос, который выполняется с высокой частотой использования либо одним пользователем, либо группой пользователей. Такое представление проектировщик базы данных может разработать, если требования к обработке данных задокументированы в спецификациях к базе данных.
Представления для эпизодических пользователей базы данных также могут быть созданы, если их требования к данным подробно описаны.
Создавать внешнюю схему наиболее удобно параллельно с внутренней, по мере того как создаются базовые таблицы. Решение о том, как проектировщик базы данных будет создавать первоначальную внешнюю схему, является его прерогативой.
Пример. Для нашей учебной базы данных первоначальная внешняя схема может выглядеть следующим образом:
CREATE VIEW DEPARTAMENT_V AS SELECT DEPNO, DNAME, LOC, MANAGER, PHONE FROM DEPARTAMENT;
CREATE VIEW EMPLOYEE_V AS SELECT EMPNO, ENAME, LNAME, DEPNO, SSECNO, JOB, AGE, HIREDATE, SAL, COMM, FINE FROM EMPLOYEE;
CREATE VIEW PROJECT_V AS SELECT PROJNO, PNAME, BUDGET FROM PROJECT;
CREATE VIEW PERSPROJ AS SELECT ENAME, JOB, PNAME FROM EMPLOYEE, PROJECT, EMPL_PROJ WHERE EMPLOYEE.EMPNO= EMPL_PROJ.EMPNO AND EMPL_PROJ.PROJNO=PROJECT.PROJNO;
Проектировщик базы данных в данном случае решил создать зеркальные представления для базовых таблиц, представляющих сущности предметной области. При этом он использовал суффикс _V в именах представлений, для того чтобы указать, что эти представления зеркально отображают поля базовой таблицы с именем без суффикса.
Кроме этого, он добавил представление, позволяющее получать информацию о служащих и выполняемых ими проектах, которая включает связывающую таблицу для этого отношения со степенью связи "многие-ко-многим".
В этом разделе мы рассмотрели механизм представлений реляционной базы данных. Представления как объекты базы данных занимают немного физического пространства в словаре базы данных, но обеспечивают функциональность базы данных на уровне таблиц. Помимо прочего они позволяют скрывать от пользователей информацию о физической структуре таблиц базы данных, что повышает безопасность эксплуатации базы данных в целом. Представления являются в реляционных базах данных основным механизмом для построения внешней схемы базы данных.
Ниже приведены рекомендации проектировщикам баз данных по созданию представлений в типичной реляционной базе данных.
Создавайте по одному представлению для каждой базовой таблицы, в точности совпадающему с таблицей, но имеющему другое имя.Создавайте по одному представлению для каждого взаимоотношения "первичный ключ-внешний ключ", через которое часто устанавливается соединение.Создавайте по одному представлению для часто используемого сложного запроса.Создавайте представления, ограничивающие доступ пользователей к конкретным колонкам и строкам таблицы.
Литература: [14], [15], [20], [45].
Создание представлений
СУБД предоставляет вам возможность создавать представления или так называемые виртуальные таблицы. Виртуальная таблица, или представление, является таблицей, которой физически нет в базе данных, но которая существует в представлении пользователя о логической структуре данных. Виртуальная таблица не содержит фактических данных, а реализуется как запрос к существующим таблицам базы данных. Определение представления хранится в словаре базы данных. Виртуальную таблицу можно рассматривать как поименованный запрос, который порождает таблицу для использования другими запросами. Таким образом, представление, или виртуальная таблица, - это поименованный запрос на выборку данных из одной или нескольких базовых таблиц, определение которого сохраняется в словаре базы данных.
Примечание. Далее в тексте термины "представление" и "виртуальная таблица" будут употребляться на равных правах.
Виртуальные таблицы используются в основном для реализации внешних схем данных (точек зрения пользователя). Они упрощают доступ к данным за счет замены сложных запросов более простыми запросами, обеспечивают независимость и защиту данных. Виртуальная таблица является объектом реляционной базы данных.
Для создания виртуальных таблиц в SQL предназначена команда CREATE VIEW. Пусть вам требуется регулярно просматривать списки служащих по отделам. Тогда вы можете использовать виртуальную таблицу
CREATE VIEW EMPLIST AS SELECT DEPNO, EMPNO, ENAME, JOB FROM EMPLOYEE GROOP BY DEPNO, EMPNO, ENAME, JOB;
Как видите, виртуальная таблица является средством именования часто используемых команд SELECT. Как известно, результат выполнения команды SELECT является таблицей. Виртуальная таблица, при создании которой используется предложение GROUP BY, иногда называется групповым представлением (grouped view)
Как только промежуточная таблица получает имя, к ней можно делать запрос. Например, выполнить команду
SELECT * FROM EMPLIST WHERE DEPNO=10;
которая дает список сотрудников 10-го подразделения.
Виртуальные таблицы можно определять с помощью других виртуальных таблиц.
Однако во многих реализациях SQL представления имеют сильные ограничения на выполнение операций обновления данных над ними. Некоторые СУБД не разрешают в определении представления использовать предложение ORDER BY. В некоторых диалектах SQL недопустимо выполнение обновлений на виртуальных таблицах, определенных на нескольких базовых таблицах, а также содержащих предложения GROUP BY, HAVING, опцию DISTINCT и функции агрегирования. Такие представления используются только для чтения. Например, в СУБД SQLBase представление используется только для чтения (read-only view), если в определяющей команде SELECT:
предложение FROM задействует имена более одной таблицы или представления;применяется: опция DISTINCT;предложение GROUP BY;предложение HAVING;функция агрегирования.
Иногда запрещается использовать и подзапросы.
В противном случае представление считается обновляемым представлением (updatable view). Для обновляемых представлений предусмотрена опция WITH CHECK OPTION. Когда она указана, любая вставка и обновление через данное представление будет выполняться только, если представление отвечает своему определению (данные в таблице могут быть изменены непосредственно). В противном случае такой проверки не делается. Если представление предназначено только для чтения или применяет подзапрос, то данная опция не должна использоваться.
Команда ALTER TABLE с такими же ограничениями также выполнима на виртуальных таблицах.
Создание связывающих таблиц для
Отношения "многие-ко-многим", как уже упоминалось в предыдущих лекциях, не могут быть в подавляющем большинстве случаев непосредственно реализованы в реляционных базах данных без создания промежуточных таблиц, иначе называемых связывающими таблицами (junction tables). Связывающая таблица является базовой таблицей (дочерней) базы данных, которая представляет отношение связи между двумя таблицами (родительскими), находящимися в отношении "многие-ко-многим". Процедура представления отношения "многие-ко-многим" с помощью связывающей таблицы на языке проектировщика базы данных носит название разрешение взаимосвязи (или связи) "многие-ко-многим". Эта задача может быть частично решена на стадии логического проектирования реляционной базы данных. На этой стадии требуется аккуратное воплощение решения этой задачи в операторах SQL.
Алгоритм задачи разрешения взаимосвязи следующий:
Создать связывающую таблицу (новую таблицу базы данных). Обычно проектировщики баз данных имеют такую таблицу конкатенацией имен двух связываемых таблиц.Добавить колонки в связывающую таблицу, как описано в предыдущем разделе. Обязательно добавляются колонки первичных ключей связываемых таблиц, которые образуют первичный составной ключ связывающей таблицы. Убедитесь, что все части составного ключа связываемой таблицы добавлены в связывающую таблицу!Определить первичный ключ связывающей таблицы и внешние ключи для связываемых таблиц. Внешним ключом будет часть первичного ключа родительской таблицы, которая ссылается назад к родительской таблице.
Этот алгоритм решает задачу разрешения отношения "многие-ко-многим". Однако можно подумать об атрибутах (колонках), которые могут существовать у связи (ее представляет связывающая таблица). Такие колонки могут быть помещены в связывающую таблицу. Это могут быть колонки, общие для связываемых таблиц.
Рассмотрим пример. Используемая схема базы данных учебного примера не позволяет использовать одного служащего в нескольких проектах (реализовано отношение "много (служащих) к одному (проекту)").
На практике обычно каждый служащий работает над несколькими проектами (отношение "многие-ко-многим"). Чтобы реализовать такое отношение, необходимо модернизировать структуру физической базы данных. Определим отношение "многие-ко-многим" через создание новой таблицы EMP_PRJ
CREATE TABLE EMP_PRJ ( EMPNO integer NOT NULL, PROJNO char(8) NOT NULL, WORKS number, PRIMARY KEY (EMPNO, PROJNO), FOREIGN KEY (EMPNO) REFERENCES EMPLOYEE, FOREIGN KEY (PROJNO) REFERENCES PROJECT, );
которая будет служить для распределения служащих по проектам. В этой таблице каждому служащему отвечает столько строк, сколько проектов он выполняет. Схема базы данных примет теперь вид как на рис. 9.1. Колонка PROJNO таблицы EMPLOYEE при этом потеряла свой семантический смысл. Ee следует удалить из таблицы EMPLOYEE за ненадобностью.
Рис. 9.1. Логическая структура учебной базы данных после разрешения отношения "многие-ко-многим"
Как работать с ограничением внешнего ключа, будет показано далее в соответствующем подразделе.
CREATE TABLE DEPARTAMENT ( DEPNO integer NOT NULL, DNAME char(20), LOC char(20), MANAGER char(20), PHONE char(15), PRIMARY KEY (DEPNO) определение первичного ключа );
CREATE TABLE EMPLOYEE ( EMPNO integer NOT NULL, ENAME char(25), LNAME char(10), DEPNO int, 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) ); CREATE TABLE PROJECT ( PROJNO char(8) NOT NULL, PNAME char(25), BUDGET dec(9,2), PRIMARY KEY (PROJNO) );
CREATE TABLE EMP_PRJ ( EMPNO integer NOT NULL, PROJNO char(8) NOT NULL, WORKS number, PRIMARY KEY (EMPNO, PROJNO), FOREIGN KEY (EMPNO) REFERENCES EMPLOYEE, FOREIGN KEY (PROJNO) REFERENCES PROJECT );
Таким образом, в результате описанных выше действий был получен скрипт для создания базовых таблиц учебного примера. Этот скрипт можно выполнить, чтобы создать базовые таблицы. Но обычно проектировщики баз данных не спешат, а продолжают далее совершенствовать полученный скрипт.
Будем считать разработанный скрипт первой итерацией в создании внутренней схемы базы данных - создании базовых таблиц.
Дальнейшими задачами проектировщика базы данных будут задачи усовершенствования и доработки разработанного скрипта в следующих направлениях:
добавления ограничений в спецификации колонок (требования непротиворечивости и целостности данных); добавление ссылочной целостности (требования целостности данных);добавление представлений, синонимов и ряда других опциональных объектов базы данных (требования по разграничению доступа пользователей, частичное обеспечение требований безопасности);обеспечение требований производительности базы данных методами, предоставляемыми выбранной СУБД;определение пользователей, их авторизация и разграничение полномочий (требования безопасности базы данных).
Первые три направления деятельности носят опциональный характер и определяются особенностями конкретного ИТ-проекта и вкусами руководителя данного проекта, а также требованиями к базе данных и бизнес-правилами предметной области, если они представлены в исходной документации. Часто, особенно при использовании объектно-ориентированной парадигмы в разработке приложений базы данных, эти три направления деятельности проектировщика базы данных могут быть переданы разработчикам приложений базы данных. Поэтому мы подчеркиваем здесь их необязательный характер. Как может решать такие задачи проектировщик базы данных, будет рассмотрено в других разделах этой лекции. Четвертое направление деятельности очень желательно. Его следует выполнять, если априорно имеются данные о возможном поведении данных в системе во времени. Обычно борьба за производительность относится к задачам обратного влияния, когда становятся известными параметры эксплуатации системы. Это направление деятельности будет рассматриваться в двух последу ющих лекциях.
Последнее направление деятельности носит обязательный характер для обеспечения функционирования базы данных, эта задача в большей степени является задачей администратора базы данных - лица, ответственного за эксплуатацию базы данных в организации.
Как решаются такие задачи, будет рассмотрено в следующей лекции.
Ниже приведен типичный синтаксис некоторых команд SQL.
Синтаксис оператора CREATE TABLE:
CREATE TABLE имя_таблицы (имя_колонки тип_данных [NOT NULL | NOT NULL WITH DEFAULT] [, имя колонки ѕ] [PRIMARY KEY (имя_колонки [,имя_колонки ѕ]] [FOREIGN KEY [имя_ключа] (имя_колонки [, имя_колонки ѕ ]) REFERENCES имя_таблицы_родителя [ON DELETE [RESTRICT | CASCADE | SET NULL]]] ) [IN [имя_базы_данных] имя_области_табличного_пространства | IN DATABASE имя_базы_данных] [PCTFREE целочисленная_константа]
Примечание. Здесь и ниже опции операторов SQL, которые не объясняются в настоящем разделе книги, поясняются далее, там, где логика изложения требует их детального обсуждения. Подчеркивание ключевого слова обозначает значение, принятое по умолчанию.
Синтаксис оператора CREATE INDEX:
CREATE [UNIQUE] [CLUSTERED HASHED] INDEX имя_индекса ON имя_таблицы (имя_колонки [ASC | DESC] [, имя_колонки ѕ]) [PCTFREE целочисленная_константа] [SIZE целочисленное_значение [ROWS | BUCKETS]]
Создание таблиц
С точки зрения стандарта SQL-92, таблицы подразделяются на три категории.
Постоянные базовые таблицы (Base Table) - таблицы, содержимое которых хранится в базе данных и которые остаются в базе данных постоянно, если не удаляются явным образом.Глобальные временные таблицы - таблицы, которые применяются в качестве рабочей области хранения данных и которые уничтожаются в конце сеанса работы с базой данных. Описание этих таблиц хранится в словаре данных, но их данные не сохраняются. С глобальными таблицами может работать только текущий пользователь, но они доступны в течение всего сеанса работы с базой данных.Локальные временные таблицы - таблицы, которые аналогичны глобальным временным таблицам, но доступны только тому программному модулю, в котором созданы.
Физическая модель реляционной базы данных содержит базовые таблицы. Для определения и создания таблиц в SQL-92 предусмотрена команда CREATE TABLE, которая определяет имя таблицы, имена и физический порядок колонок для нее, тип каждой колонки, а также некоторые указания для СУБД, такие как определение первичного или внешнего ключа, требования на запрет неопределенных значений в колонке таблицы и т.п. Полный формат команды CREATE TABLE для каждой СУБД приводится в соответствующем документе с названием типа "Справочное руководство по SQL для СУБДѕ".
Создание таблиц с ограничениями ссылочной целостности
Для создания таблиц с поддержкой ограничений ссылочной целостности в SQL предназначены команды CREATE TABLE и команда ALTER TABLE. Таким образом, вы имеете два основных способа для поддержки ссылочной целостности в реляционной базе данных:
использование предложений PRIMARY KEY и FOREIGN KEY команды CREATE TABLE;использование предложений ADD/DROP PRIMARY KEY и ADD/DROP FOREIGN KEY команды ALTER TABLE.
В предыдущих разделах уже было показано использование предложения PRIMARY KEY команды CREATE TABLE. Пример использования предложения FOREIGN KEY команды CREATE TABLE продемонстрируем на примере создания таблицы для иерархии "руководитель-подчиненный":
CREATE TABLE MANAGEMENT ( MANAGNO INT NOT NULL, EMPNO INT, JOB INT, PRIMARY KEY (MANAGNO), FOREIGN KEY fnkey (EMPNO) REFERENCES EMPLOYEE ON DELETE CASCADE);
CREATE UNIQUE INDEX ndxmng ON MANAGEMENT(MANAGNO);
fnkey - имя внешнего ключа, предложение REFERENCES, связанное с предложением FOREIGN KEY, определяет имя таблицы-родителя, предложение ON DELETE определяет правило удаления записей в связанных таблицах. Каждое правило удаления соответствует определенной взаимосвязи между объектами реляционной базы данных (т. е. предметной области).
Правила удаления используются только в определении внешнего ключа. Обычно СУБД в соответствии со стандартом SQL поддерживает три правила:
CASCADE - сначала удаляется заданная строка в родительской таблице, а затем удаляются зависимые от нее строки;RESTRICT - строка может быть удалена, если никакие другие строки не зависят от нее, в противном случае удаления не происходит;SET NULL - для любого удаляемого первичного ключа соответствующее ему значение внешнего ключа дочерней строки принимает нуль-значение.
Для самоссылающихся таблиц используется только правило CASCADE. Правило RESTRICT препятствует удалению строки в таблице-родителе, если ей соответствуют какая-либо строка в таблице-потомке. Правило CASCADE определяет, что, когда строка в таблице-родителе удаляется, то все связанные с ней строки в таблице-потомке автоматически должны быть удалены. Правило SET NULL определяет, что, когда строка в родительской таблице удаляется, значения внешнего ключа во всех строках таблицы-потомка должны автоматически устанавливаться в нуль-значение.
Применение команды ALTER TABLE продемонстрируем на примере создания отношения "родитель-потомок" для таблиц DEPARTAMENT и EMPLOYEE учебной базы данных. Первичные ключи и индексы для этих таблиц уже созданы. Создадим внешние ключи (в таблицу DEPARTAMENT должна быть добавлена колонка EMPNO).
ALTER TABLE DEPARTAMENT FOREIGN KEY EMP_DEP (EMPNO) REFERENCES EMPLOYEE ON DELETE RESTRICT;
Для того, чтобы удалить первичный или внешний ключ таблицы, необходимо использовать предложение DROP команды ALTER TABLE.
Ссылочная целостность
В реляционной теории концепция ссылочной целостности была предложена в 1976 году П. Ченом. В рамках этой концепции все отношения реляционной базы данных разделяют на два класса: объектные отношения и связные отношения. Объектное отношение предназначено для описания состояния объекта через значения его атрибутов. Ему в физической модели базы данных отвечает базовая таблица. Связное отношение предназначено для фиксации связей между объектами через значения ключевых атрибутов объектов. Ему в физической модели базы данных также может отвечать базовая таблица. Обычно связные отношения поддерживаются в физической модели базы данных через ограничения первичного и внешнего ключей.
Ссылочная целостность означает, что все связи между отношениями замкнуты, т.е. все ссылки между отношениями допустимы и нет ссылок на несуществующие объекты (отношения, кортежи). Это предохраняет приложения пользователей от ситуаций, когда изменения в одном отношении не отражаются в других, связанных с ним.
Допустимость ссылки еще не означает ее корректность. Так, в учебной базе данных вы можете приписать служащему в таблице EMPLOYEE несуществующий номер отдела. Однако значение номера отдела в этой таблице должно быть согласовано с соответствующим значением в таблице DEPARTAMENT. Поддержка ссылочной целостности будет гарантировать лишь то, что такой отдел существует в таблице DEPARTAMENT. При этом вставка служащего с неправильным номером отдела будет заблокирована. Однако правильность списка сотрудников отдела не контролируется. Допустимая ссылка еще не означает ее правильность.
Иными словами, ссылочная целостность задает ограничения на связи между отношениями реляционной базы данных за счет управления значениями некоторых атрибутов взаимосвязанных отношений. Ограничения ссылочной целостности называются еще правилами ссылочной целостности.
Такие ограничения (в виде бизнес-правил) определяются при анализе предметной области базы данных системным аналитиком и, следовательно, субъективны. Например, для нашей учебной базы данных можно сформулировать следующие правила ссылочной целостности:
каждый служащий работает в определенном отделе;каждый отдел имеет только одного менеджера;каждый служащий работает под управлением менеджера;каждый проект имеет уникальный шифр.
В настоящее время большинство промышленных реляционных СУБД имеют встроенный механизм поддержки ссылочной целостности. Таким образом, вам не нужно писать код для поддержки ссылочной целостности в своем приложении, СУБД делает эту работу сама.
Механизм поддержки ссылочной целостности основывается на использовании следующих понятий:
первичные и внешние ключи;отношение "родитель-потомок" между таблицами;отношение "родитель-потомок" между строками;самоссылающиеся отношения на множестве своих кортежей.
Виртуальные таблицы с соединениями
Если вам необходимо работать с несколькими таблицами как с одной, вы можете создать виртуальную таблицу, объединяющую информацию из нескольких таблиц. Пусть вам нужна информация о служащих и выполняемых проектах, тогда можно создать следующую виртуальную таблицу:
CREATE VIEW PERSPROJ AS SELECT ENAME, JOB, PNAME FROM EMPLOYEE, PROJECT WHERE EMPLOYEE.PROJNO=PROJECT.PROJNO;
Выполняя команду
SELECT * FROM PERSPROJ;
вы получите необходимую вам информацию. При этом вам не нужно помнить, что данные хранятся в двух таблицах. Даже если будет изменена структура базы данных, не затрагивающая колонки из виртуальной таблицы, вы этого не заметите.
Можно выполнять и более сложные запросы на виртуальных таблицах. Допустим, что вам нужен список руководителей по проектам, тогда вы можете выполнить
SELECT ENAME, PNAME FROM PERSPROJ WHERE JOB='руководитель';
С виртуальными таблицами используется предложение WITH CHECK OPTION. При этом каждая вставка или обновление будет проверяться на соответствие определению виртуальной таблицы и будет отвергаться, если такого соответствия не будет. В противном случае такой проверки не делается.
Пусть вам нужно иметь информацию о текущих проектах. Тогда можно определить виртуальную таблицу (предварительно определив поле START_DATE в таблице PROJECT и установив его значения) как
CREATE VIEW CURPROJ AS SELECT * FROM PROJECT WHERE START_DATE < SYSDATE WITH CHECK OPTION;
Колонка START_DATE при обновлении будет проверяться на соответствие текущей дате. Это предложение справедливо только для обновления данных в колонках базовых таблиц, для которых создана такая виртуальная таблица. Если виртуальная таблица только для чтения или при его создании используется подзапрос, то WITH CHECK OPTION не должно указываться.
Задание ограничений NOT NULL на значения колонок
При определении спецификаций колонок таблиц проектировщик базы данных должен рассмотреть ограничения, которые могут быть наложены на значения колонок. В реляционных СУБД этих ограничений предусмотрено достаточно много. Здесь мы остановимся на одном из главных ограничений такого рода - это обязательность присутствия значения в колонке. Такое ограничение на значение колонки называется NOT NULL ограничением.
Предопределенное значение колонки, равное NULL, означает, что в данный конкретный момент для данной конкретной строки (экземпляра сущности предметной области) значение не определено, или не известно, или отсутствует. Проектировщику базы данных необходимо идентифицировать возможность колонки принимать NULL-значения, т.к. пользователи могут иметь проблемы при использовании таких колонок. Примером этой проблемы может служить ситуация, в которой пользователю требуется выполнить соединение двух таблиц по колонкам, имеющим NULL-значения. При выполнении таких соединений любые строки, которые содержат NULL-значения в колонках соединения в любой из таблиц, не будут показаны в результирующей выборке для запроса. Подобная потеря данных может привести к тому, что пользователь получит неправильную выборку на запрос, особенно если ему необходимо видеть все строки хотя бы одной из таблиц.
Примечание. Одним из способов решения определенной выше проблемы является использование внешних соединений.
При назначении NULL-значений колонкам проектировщику базы данных необходимо принимать во внимание следующие факторы.
Колонки, являющиеся частью составного первичного ключа, всегда должны иметь ограничение NOT NULL, т.к. согласно реляционной теории значения колонок первичного ключа должны быть определены и уникальны для каждого кортежа.Внешние ключи должны также определяться как NOT NULL, поскольку дочерняя таблица зависит от родительской и внешний ключ родительской не может иметь NULL-значения. Это следует из того, что существование строки дочерней таблицы без соответствующей строки родительской таблицы нарушает правило зависимости связи. (О внешних ключах, родительских и дочерних таблицах см.
далее.) Только внешние ключи для таблицы с опциональной связью могут рассматриваться как кандидаты на наличие NULL-значений, чтобы показать, что для данной комбинации родительской и дочерних строк в этих таблицах связи нет.Внешние ключи с правилом удаления SET NULL должны определяться со спецификацией NULL.Используйте спецификацию NOT NULL WITH DEFAULT для колонок с типами данных DATE или TIME, чтобы сохранять текущие даты и текущее время автоматически.Разрешайте использовать NULL-значения только для тех колонок, которые действительно могут иметь неопределенные значения.Используйте NOT NULL WITH DEFAULT для всех колонок, которые не подпадают под перечисленные выше правила.Пример. Как можно увидеть ниже, проектировщик базы данных определил, что: дата поступления служащего в организацию HIREDATE определена со спецификацией NOT NULL WITH DEFAULT, которая означает, что если на вводе значение колонки не определено, то по умолчанию подставляется текущая дата;номер подразделения DEPNO, номер служащего EMPNO и номер проекта PROJNO имеют спецификацию NOT NULL как первичные ключи таблиц; для остальных полей базовых таблиц проектировщик базы данных принял решение разрешить наличие в них NULL-значений.
CREATE TABLE DEPARTAMENT ( DEPNO integer NOT NULL, DNAME char(20), LOC char(20), MANAGER char(20), PHONE char(15), PRIMARY KEY (DEPNO) определение первичного ключа );
CREATE TABLE EMPLOYEE ( EMPNO integer NOT NULL, ENAME char(25), LNAME char(10), DEPNO int, SSECNO char(10), PROJNO char(8), 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) );
CREATE TABLE PROJECT ( PROJNO char(8) NOT NULL, PNAME char(25), BUDGET dec(9,2), PRIMARY KEY (PROJNO) );
Денормализация методом "разделяй и властвуй"
Денормализация методом "разделяй и властвуй" - это процесс разбиения нормализованной таблицы на две и более таблиц и создание между ними отношения "один к одному" с целью устранения дополнительных операций ввода/вывода или по техническим причинам.
Использование этого приема носит причины технического характера. Во многих СУБД таблица не может иметь больше одного столбца типа LONG или LONG RAW. Допустим, что у вас есть таблица Films и нужно сохранить и окончательный вариант фильма (LONG), и вариант, который отсняли с множеством дублей (LONG RAW). Из-за вышеупомянутого ограничения в одной таблице это сделать нельзя, поэтому один из кодов нужно разместить в отдельной таблице. Проектировщику базы данных не остается ничего другого как разбить таблицу на две.
Иногда лучше вынести столбец LONG в отдельную таблицу, даже если вышеупомянутое ограничение не действует. Рассмотрим таблицу, строки которой содержат в начале ключевые колонки, потом неключевые колонки, а в конце - колонку типа LONG. Предположим, что в большинстве строк столбец LONG содержит данные. Если нет индексов по неключевым столбцам, то при выполнении запросов по любому из этих столбцов СУБД обычно будет осуществлять полное сканирование таблицы. При этом из-за наличия в таблице столбца LONG понадобятся дополнительные операции ввода/вывода.
Чтобы устранить эту проблему, разделите таблицу так, как показано на рис. 10.5.
Рис. 10.5. Выделение колонки LONG в отдельную таблицу
Во многих СУБД таблица не может иметь более 254 столбцов, и если предложить таблицу с большим числом столбцов, то также возникнет причина для разделения такой таблицы на две. Обычно такие таблицы могут понадобиться только в следующих случаях:
приложение полностью проектируется на базе унаследованной системы, и каждая таблица строится как точная копия файла унаследованной системы. При этом наследуется и структура, и все реляционные свойства в ней отсутствуют;выполняется слияние двух таблиц путем формирования в одной из них повторяющейся группы;речь идет о хранилище данных, в котором принято решение выполнить массовую нисходящую денормализацию. В этом случае следует создавать таблицы с максимальным для СУБД числом столбцов, так как любое другое решение, вероятно, обусловит необходимость массовых соединений "один к одному".
Согласно мнению известного специалиста в области проектирования реляционных баз данных Д. Энсора, "Хорошей мерой степени нормализации является число столбцов на таблицу. Эмпирическое правило гласит, что очень немногие первичные ключи имеют более двадцати действительно зависимых от них атрибутов".
Денормализация методом слияния таблиц
Денормализация методом слияния таблиц - это процесс объединения одной или более нормализованных таблиц с целью устранения операций соединений или уменьшения в некоторых случаях числа операций вставки.
Возникает резонный вопрос: после того как мы вложили столько сил в нормализацию структур данных и разбивку сущностей, нам предлагается начать слияние таблиц? Фактически слияние оправдано в очень немногих случаях.
Один из примеров обоснованного применения слияния - наличие повторяющейся группы, которая гарантированно состоит из фиксированного числа элементов. Хорошими кандидатами на такое объединение являются таблицы со строкой для каждого месяца года или каждого дня недели. Единственный случай, где фиксированные группы надежны, - это когда они соответствуют абсолютно постоянным вещам, например дням недели.
Будет ли какое либо преимущество от такого слияния заранее сказать трудно. Подход к денормализации должен определяться требованиями к обработке данных.
Альтернатива данному способу денормализации - физическое размещение таблиц в кластере (например, как в СУБД Oracle). Это позволяет хранить рядом строки логически связанных отдельных таблиц.
Таким образом, на практике денормализация представляет собой набор приемов преобразования таблиц с целью повышения производительности обработки запросов. С точки зрения реляционной теории мы как бы не принимаем в рассмотрение наличие некоторых функциональных зависимостей в таблицах. Основным критерием проведения денормализации нормализованных таблиц являются требования к обработке данных. В реляционных базах данных следует избегать неоправданной денормализации таблиц.
Длинные строки в таблицах хэширования
Во многих реляционных СУБД поддерживаются так называемые хэш-кластерные индексы (clustered hashed index). Такие объекты правильнее называть таблицами хэширования, а не индексами. Таблица хэширования представляет собой таблицу реляционной базы данных, доступ к строкам которой осуществляется с помощью преобразования ключа. Значения колонок, которые объявлены ключевыми, преобразуются в позиции строк таблицы (и при их вставке там и размещаются) - хэшируются. Такую функцию называют хэш-функцией. Ключ таблицы, который подвергается преобразованию, называется хэш-ключом. Данные, которые обрабатываются таким образом, размещаются в специальных таблицах, называемых еще хэш-кластерами или просто хэш-таблицами. В настоящем курсе предполагается, что проектировщику известны общие методы организации физического доступа к данным, поэтому мы не будем детально обсуждать вопрос, как устроены такие таблицы.
Отметим только, что хэширование является очень эффективным методом доступа по первичному ключу к записи за один доступ. Если значения ключа равномерно распределены, то в среднем это будет так. В противном случае производительность доступа будет резко падать из-за коллизий, т.е. случая, когда для двух различных значений первичного ключа хэш-функция дает одинаковые числа, т.е. позиции записи. В худшем случае придется просканировать всю таблицу, чтобы получить одну запись!
Несмотря на то, что построены динамические таблицы хэширования (изменяющие свой размер во время существования), в большинстве СУБД поддерживаются статические таблицы хэширования, размер которых определяется при их создании. В большинстве случаев таблица хэширования формируется случайным образом по отношению к порядку следования значений ключа, хотя известны функции преобразования ключа, которые поддерживают лексикографический порядок на значении ключа таблицы.
Хэш-индекс обычно применяется, если ключ полностью представлен в предложении WHERE и используется операция равенства для колонок ключа. Нас интересует проблема увеличения производительности в хэш-таблицах, когда длина строки превышает размер физической страницы на жестком диске.
Чтобы лучше понять проблему, рассмотрим, как определяется хэш-таблица в SQL.
Такая таблица создается при помощи команды, например
CREATE CLUSTERED HASHES INDEX CHXNAME ON EMPLOYEE (EMPNO) SIZE 2000 ROWS;
Предложение SIZE задает вероятное количество строк в индексе, а ROWS определяет число строк для хранения индекса. Размер можно задавать в блоках (BUCKETS). Таким образом, по значению первичного ключа адресуется блок, содержащий целое число строк, или строка, если ее размер сопоставим с размером физического блока. В последнем случае считается, что блок содержит одну строку.
Для таблицы хэширования определяется параметр "число строк на странице" (rows per page) или "кластеризация страницы" (page clustering), или коэффициент блокировки, равный
Как видно из определения таблицы хэширования, размер строки и размер физического блока должны быть согласованы. Проблема длинной строки в таблице хэширования состоит в том, что если строка занимает несколько блоков, то возрастает частота коллизий и вместо одного физического доступа для получения строки требуется 4-6, что уже сопоставимо с использованием индексов другого типа.
Цель разбиения таблицы хэширования состоит в том, чтобы попытаться достигнуть такого значения параметра blocking factor (коэффициент блокировки), которое содержало бы по крайней мере как можно больше строк, которые будут выбираться вместе в большинстве транзакций к этой таблице в базе данных.
Поскольку в современных СУБД размер физического блока фиксирован для каждой операционной платформы, то единственным способом влияния на величину этого параметра является подгонка размера строки. Если нельзя пересмотреть спецификацию типов колонок таблицы и свести их размеры до минимума, то единственной возможностью выбрать подходящий коэффициент блокирования является разбиение таблицы хэширования.
Горизонтальное разбиение таблиц
На практике горизонтальное разбиение применяется для изоляции одной группы строк таблицы от другой, когда такие группы строк редко используются в одной транзакции. Наиболее типичный пример, когда этот метод оказывается полезным, есть изоляция текущих данных от архивных данных. Рассмотрим систему обработки заказов. Менеджеры и продавцы работают с текущими заказами. Обработка выполненных заказов (архивные данные) выполняется при подготовке разного рода отчетов. Даже если готовится ежедневный отчет с использованием архивных данных, то в организациях среднего размера частота использования текущих данных все равно превышает частоту использования архивных данных на 2-3 порядка, а отношение объема текущих данных к архивным данным может составлять менее 0,001.
Одним из практических критериев в данном случае может служить классическое правило 80-20. Если активно работают с 20% данных, то, вероятнее всего, остальные 80% можно перенести в архивную таблицу.
Пример. Для нашей учебной базы данных таблицей - кандидатом на такое горизонтальное разбиение является таблица PROJECT, поскольку в ней имеются архивные данные - выполненные проекты. Предположим, что число выполненных проектов в год в организации где-то около 1000. Данные в таблице нужно хранить 10 лет (10000). Средняя продолжительность проекта равна 2 месяцам, т.е. число незавершенных проектов в каждый момент времени не превышает 200. Через 5 лет отношение числа текущих проектов к архивным проектам достигнет 0,04. Следовательно, проектировщик данных может рассмотреть вопрос о горизонтальном разбиении этой таблицы.
CREATE TABLE PROJECT ( PROJNO char(8) NOT NULL, PNAME char(25), BUDGET dec(9,2), PRIMARY KEY (PROJNO) );
CREATE TABLE PROJECT_OLD ( PROJNO char(8) NOT NULL, PNAME char(25), BUDGET dec(9,2), PRIMARY KEY (PROJNO) );
А для совместного использования двух этих таблиц предусмотреть представление
CREATE VIEW ALL_PROJECT AS SELECT * FROM PROJECT UNION SELECT * FROM PROJECT_OLD; Замечание. Далее под исходной таблицей понимается и сама таблица, и то, что от нее осталось после разбиения.
Методы реализации денормализации: Разбиение таблиц базы данных
Разбиение (splitting) таблиц является одним из общих методов денормализации, который применяется в физическом проектировании реляционных баз данных. Разбиение таблиц бывает двух видов - вертикальное разбиение и горизонтальное разбиение.
Вертикальное разбиение является процессом перемещения некоторых колонок таблицы в другую новую таблицу, которая имеет тот же первичный ключ, что и исходная таблица. Горизонтальное разбиение является процессом перемещения некоторых строк одной таблицы в другую новую таблицу, которая имеет такую же внутреннюю структуру, что и исходная таблица.
Основные причины разбиения таблицы: либо существуют некоторые проблемы с производительностью запросов на этой таблице, либо непересекающиеся подмножества строк таблицы имеют значительное различие в производительности обработки. Т.е. подмножества строк таблицы редко встречаются в одном и том же запросе.
Нисходящая денормализация
Рассмотрим принципы денормализации на уровне логической модели реляционной базы данных. Нисходящая денормализация предлагает перенос атрибута из одной (родительской) сущности в подчиненную (дочернюю) сущность. Из рисунков 10.1 и 10.2 видно, что в денормализованной логической модели мы переместили фамилию клиента из сущности Customer (Клиент) в сущность Order (Заказ). Что дает введение избыточности (перенос атрибута) в данном случае? Единственный выигрыш заключается в том, что мы исключаем операцию соединения, если захотим вместе с заказом увидеть фамилию клиента.
Рис. 10.1. Сущности Customer и Order до денормализации
Рис. 10.2. Сущности Customer и Order после денормализации
Таким образом, нисходящая денормализация - это процесс введения избыточных колонок в подчиненных таблицах с целью устранения операций соединения.
Однако устранение соединений посредством нисходящей денормализации редко оправдывает затраты на сопровождение дублирующей колонки в таблице ORDER. Такие соединения, как правило, не являются глобальной проблемой, а выполнение нисходящей денормализации может привести к возникновению дорогостоящих каскадных обновлений, дающих небольшую реальную выгоду. Например, если клиент меняет фамилию, то нам приходится обновлять все заказы, чтобы отразить это изменение. А нужно ли это делать? Следует ли обновлять старые заказы, которые выполнены или закрыты? Если бы не была проведена денормализация, то эти вопросы никогда бы и не возникли.
Нисходящая денормализация оправдана лишь в приложениях, где необходимо устранять операции соединения таблиц. Это имеет место в базах данных большого объема, таких как хранилища данных. При этом проблемы с каскадными обновлениями не возникает потому, что данные в хранилищах данных - архивные.
Понимание типа приложений базы данных
Прежде чем обсуждать основные типы приложений баз данных, уточним термины транзакция и запрос. В теории баз данных, вообще говоря, под транзакцией понимают одну из команд SQL - SELECT, INSERT, UPDATE, DELETE. Однако в зависимости от типа приложений термин транзакция трактуется более свободно как элементарная логически завершенная единица работы (так называемая бизнес-транзакция), которая может включать несколько команд вставки, удаления или модификации. В зависимости от того, какие команды SQL используются, транзакции разделяют на транзакции только для записи (write-only), только для модификации (modify-only), только для чтения (read-only), только для удаления (delete-only). Транзакции только для чтения называют запросом.
Исходя из такого деления транзакций по типам обработки с учетом частоты транзакций каждого типа, можно выделить три основных классических типа приложений баз данных:
OLTP-системы (On-Line Transaction Processing). OLTP-система - это такое приложение, которое содержит в основном транзакции вставки, обновления и удаления, с высокой частотой преимущественно транзакций обновления. Классическим примером этих систем являются системы резервирования авиабилетов или обслуживания гостиниц. Для таких систем характерен высокий уровень параллелизма (high concurrency), который в данном случае означает, что много пользователей используют базу данных одинаковым образом.DSS-системы (Decision Support System). DSS-система - это такое приложение, которое работает с очень большой базой данных в режиме "только чтение". Обычно используется набор фиксированных простых запросов или нерегламентированные запросы пользователей. Хорошим примером такой системы является корпоративная информационная система организации.BATCH-системы. BATCH-системы - это такое приложение, которое работает с базой данных в не интерактивном режиме. Обычно оно использует много транзакций вставки, удаления и обновления, имеет низкий уровень параллелизма, что означает небольшое число пользователей, использующих базу данных одинаковым образом.
Существенным фактором для этих систем является отношение запросов к транзакциям обновления. Классическим примером таких систем является обслуживание базы данных продукции организации.
Можно выделить еще несколько типов приложений, появившихся в последние два десятилетия.
OLAP-системы (On-Line Analytical Processing). OLAP-система - это приложение, которое обеспечивает аналитическую обработку данных, включающую математический, статистический или иной анализ данных. Такие системы нельзя отнести полностью либо к OLTP-, либо к DSS-системам. Они располагаются где-то между ними. В рамках OLAP систем выделяют так называемые ROLAP системы (Relational OLAP), т.е. OLAP-системы, использующие реляционные базы данных. Типичные OLAP-системы разрабатываются обычно под многомерные модели данных.VCDB-системы (Variable Cardinality Database). VCDB-система - это такое приложение обработки данных, для которого база данных растет или сжимается в размерах периодически в зависимости от характера обработки данных. Обычно размер этих баз данных постоянно растет. Кардинальность относится к числу строк в таблицах базы данных в текущий момент времени. Типичным примером такой системы является база данных по обеспечению безопасности (Security Authorization Database), для которой характерна короткая по времени активность записей в таблицы.
Проектировщик базы данных должен оценить тип приложений, для которых он разрабатывает базу данных. Это позволит ему оценить (в случае отсутствия явно прописанной в документации информации):
тип транзакций (какие);частоту транзакций каждого типа (сколько);количество одновременно работающих с базой данных пользователей.
Далее эта информация понадобится проектировщику базы данных для анализа транзакций.
Понятие о денормализации
Начиная с этого раздела мы переходим к рассмотрению методик настройки физической структуры реляционной базы данных с целью удовлетворения требования к производительности базы данных. Эти методики представляют собой набор рекомендаций и эвристических правил по изменению физической структуры базы данных, которая была получена проектировщиком базы данных в результате создания первой итерации физической модели базы данных. Ясно, что использование этих методик носит опциональный характер.
В этом разделе будут описаны различные типы денормализации и методы реализации этого процесса. Кроме того, мы рассмотрим, как использовать для поддержки денормализации триггеры и как обеспечить целостность данных, не прибегая к созданию дополнительного кода.
Под денормализацией понимают процесс достижения компромиссов в нормализованных таблицах посредством намеренного введения избыточности в целях увеличения производительности.
В большинстве случаев необходимость денормализации становится очевидной лишь на этапе проектирования модуля. Другими словами, обычно нельзя принять решение о денормализации на основании одной только модели данных. Когда проектировщик принимает решение о денормализации, то должен господствовать здравый смысл. Обычно стараются найти в приложении базы данных критичные процессы и принимать решения о денормализации в основном в пользу этих процессов. Критичные процессы обычно определяют по высокой частоте, большому объему, высокой изменчивости или явному приоритету. Если проектировщик базы данных прописал все транзакции базы данных, то он, вероятно, сможет определить наличие таких критических процессов.
Замечание. Использовать денормализацию только для упрощения SQL-запросов при обращении к базе данных является неправильным решением. Если вы хотите упростить SQL-запросы на уровне приложения или пользователя, то, наверное, лучше использовать представления, а не вводить избыточность. Чтобы повысить производительность запроса, можно ввести индексы. Как оптимизировать запрос, будет рассмотрено в последней лекции этого курса.
Как правило, денормализация уменьшает время запроса за счет DML-операций. Денормализацию следует рассматривать как расширение нормализованной модели данных, которое повысит производительность запросов. При принятии решения о денормализации определите, что является наиболее важным для приложения - избыточность данных или высокая производительность. Если проектировщик базы данных ведет журнал проектирования (некоторый внутренний документ произвольной формы, в котором фиксируются все принятые в процессе проектирования базы данных решения), то в него необходимо занести обоснованное решение о денормализации. Помните, что кроме денормализации существуют и другие пути повышения производительности. Денормализацию таблиц можно выполнять как на уровне логической модели данных, так и на уровне физической модели.
Разбиение таблиц и ссылочная целостность
Если до разбиения таблица была нормализована, то первичные ключи будут идентичны для таблиц, полученных в результате разбиения. Следовательно, взаимосвязи новых таблиц с другими таблицами базы данных могут остаться такими же, как и с первоначальной таблицей. Однако проектировщик базы данных должен рассмотреть как действие правил удаления для исходной таблицы, так и участие новой таблицы в существующих взаимоотношениях ссылочной целостности, когда будет решать вопрос об определении поддержки ссылочной целостности в новых таблицах. В некоторых случаях может быть проще не определять ссылочную целостность вовсе, так как фактическое удаление строк из новой таблицы будет поддерживаться параллельно с исходной таблицей каким-либо программным способом в приложении базы данных.
Пример. Для нашей учебной базы данных для разрешения отношения "многие-ко-многим" между таблицами EMPLOYEE и PROJECT была введена связывающая таблица EMP_PRJ, которая имеет ограничения ссылочной целостности с таблицей PROJECT. Но у нас появилась еще таблица PROJECT_OLD.
Предположим, что для реализации проекта организация нанимает сотрудников на временной основе (на время выполнения проекта), и вопрос, кто, какие проекты и когда делал, мало интересует руководство организации. В этом случае взаимосвязь между исполнителями и проектами, которые уже завершены, не интересует руководство, и, следовательно, во внутренней схеме больше ничего менять не нужно. Однако если взаимосвязь между исполнителями и завершенными проектами должна отслеживаться (например, руководство будет изучать вопрос, кто, когда и какой проект делал), ее следует распространить и на таблицу PROJECT_OLD. Для этого достаточно внести ограничение внешнего ключа в таблицу EMP_PRJ, как показано ниже:
CREATE TABLE EMP_PRJ ( EMPNO integer NOT NULL, PROJNO char(8) NOT NULL, WORKS number, PRIMARY KEY (EMPNO, PROJNO), FOREING KEY (EMPNO) REFERENCES EMPLOYEE, FOREING KEY (PROJNO) REFERENCES PROJECT, FOREING KEY (PROJNO) REFERENCES PROJECT_OLD );
При разбиении таблицы, для которой были определены ограничения ссылочной целостности, можно также рассмотреть возможность поддержки ссылочной целостности установлением взаимосвязи "один-ко-одному" в сторону исходной таблицы от новой таблицы. В этом случае новая таблица будет иметь внешний ключ, идентичный первичному ключу таблицы. Если определено каскадное правило удаления для этого внешнего ключа, то СУБД будет автоматически удалять строки новой таблицы, когда соответствующая строка исходной таблицы будет удалена, хотя управление вставкой строк придется перенести в приложение базы данных.
Пример. Для нашего примера окончательный код может быть следующим:
CREATE TABLE PROJECT ( PROJNO char(8) NOT NULL, PNAME char(25), BUDGET dec(9,2), PRIMARY KEY (PROJNO) );
CREATE TABLE PROJECT_OLD ( PROJNO char(8) NOT NULL, PNAME char(25), BUDGET dec(9,2), PRIMARY KEY (PROJNO) FOREING KEY (PROJNO) REFERENCES PROJECT );
CREATE TABLE EMP_PRJ ( EMPNO integer NOT NULL, PROJNO char(8) NOT NULL, WORKS number, PRIMARY KEY (EMPNO, PROJNO), FOREING KEY (EMPNO) REFERENCES EMPLOYEE, FOREING KEY (PROJNO) REFERENCES PROJECT, );
При принятии решения о разбиении таблицы следует придерживаться следующего алгоритма:
Определить, какие колонки исходной таблицы в какие новые таблицы будут перемещены.Создать новые таблицы с первичным ключом, идентичным первичному ключу исходной таблицы.
Если СУБД будет управлять ссылочной целостностью для новых таблиц таким же образом, как и для исходной таблицы, в зависимости от ее статуса как дочерней таблицы во взаимосвязи, то вы должны добавить колонку внешнего ключа каждой родительской таблицы во взаимосвязи в новую таблицу, т.е. новая таблица должна содержать ограничение внешнего ключа, идентичное родительской таблице для каждой взаимосвязи.
или
Можно установить связь "один-ко-одному" между новой таблицей и исходной таблицей, определив внешний ключ обратно к исходной таблице тождественным первичному ключу.
Если СУБД будет управлять ссылочной целостностью для новых таблиц таким же образом, как и для исходной таблицы, в зависимости от ее статуса как родительской таблицы во взаимосвязи, то вы должны добавить внешний ключ в каждую дочернюю таблицу исходной таблицы, чтобы идентифицировать эту новую таблицу как дочернюю.
или
Если поступить, как в первой части пункта 3, то новые таблицы не следует объявлять как родительские в других взаимоотношениях. Исходная таблица поддерживает все взаимосвязи, в которых она выступает родителем.
Следует прописать для разработчиков приложений все команды INSERT для полученных в результате разбиения таблиц, или указать правила, которым должна следовать вставка строк в эти таблицы.Следует изменить все представления, которые основывались на исходной таблице, и, если нужно, рассмотреть создание новых представлений для доступа к новым таблицам.
Литература: [7], [20], [23], [42], [45].
Спецификация транзакций
Итак, транзакция базы данных - это логическая единица работы, которая переводит базу данных из одного завершенного состояния в другое завершенное состояние. Под завершенным состоянием базы данных здесь понимается такое состояние данных в базе данных, которое не нарушает целостности этих данных - все данные в таблицах базы данных правильны, а ссылки между таблицами корректны. В дополнение, транзакция группирует операции над данными таким образом, чтобы все обращения к базе данных были успешно завершены или, в случае сбоя, база данных возвратилась в предыдущее завершенное состояние (откат транзакции).
Поскольку любая операция изменения данных в базе данных несет в себе потенциальную возможность нарушения целостности данных, необходимо строго определять транзакции и идентифицировать информацию, которая обычно включается в определение транзакции.
Определение транзакции может иметь различные формы. Иногда для определения транзакций используется репозиторий данных CASE-средств проектирования базы данных. Очень часто определение транзакций выполняется посредством текстовых описаний. Независимо от выбранного подхода любое хорошее определение транзакции включает несколько важных элементов. К таким элементам относятся:
имя транзакции;номер транзакции;описание транзакции;характер транзакции и ее сложность;объем транзакции;требования к производительности транзакции;относительный приоритет;время выполнения транзакции.
Первым шагом в определении транзакции является уникальная идентификация каждой транзакции базы данных. Это можно сделать назначением имени и номера каждой транзакции базы данных. Имена транзакций должны позволять пользователям отличать их друг от друга. Описание транзакций включает перечень операций предметной области, которые выполняются транзакцией. Что касается описания транзакций, то оно должно быть выполнено в терминах предметной области, понятных пользователю. Здесь нужно иметь в виду следующее: а) описание транзакции должно описывать, что транзакция делает для пользователя, а не как она выполняется; и б) описание должно быть понятно пользователю, что не исключает использование технологического жаргона.
Пример. Спецификация транзакции
Номер транзакции: 001
Имя транзакции: Назначить работу служащему.
Описание транзакции: Транзакция проверяет, не назначена ли уже данная работа данному служащему, затем проверяет, располагает ли служащий рабочим временем для выполнения данной работы. Если результаты проверки положительны (данная работа не назначена данному служащему, и служащий располагает временем для ее выполнения), то происходит назначение данной работы данному служащему.
В OLTP-системах большинство транзакций известны заранее, поэтому между спецификацией транзакции и транзакцией базы данных существует взаимно однозначное соответствие. В DSS-системах транзакции часто не известны заранее, и, следовательно, невозможно в принципе описать все транзакции. В этом случае спецификация транзакции лишь в общих чертах описывает транзакции базы данных, поэтому проектировщику базы данных важно уметь предсказать тип транзакций, которые пользователь вероятнее всего будет выполнять в базе данных.
Для каждой транзакции может быть определен характер транзакции как онлайновая транзакция или пакетная транзакция, а также указана ее сложность. Обычно сложность указывается в терминах "высокая", "средняя", "низкая". Эта информация нужна проектировщику базы данных для оценки транзакций базы данных в целом. Количество транзакций той или иной сложности влияет на время проектирования физической модели базы данных - чем больше в базе данных транзакций высокой сложности, тем больше время проектирования физической модели. Сложность транзакции является условной мерой трудоемкости работы проектировщика базы данных при достижении требований производительности базы данных.
Высокая сложность приписывается обычно транзакции, которая имеет две из следующих характеристик:
содержит от 8 до 10 команд SQL;содержит предложение WHERE с большим количеством предикатов;содержит предложение WHERE с более чем тремя соединениями или под запросами;обрабатывает более чем 100 строк.
Низкая сложность приписывается транзакции со следующими характеристиками:
содержит до трех команд SQL;содержит предложение WHERE с одним или двумя предикатами;обрабатывает менее чем 25 строк.
Транзакция со средней сложностью имеет характеристики между нижней и высокой сложностью.
Пример. Спецификация транзакции
Продолжая приведенный выше пример, можем указать следующее:
Характер транзакции: онлайновая транзакция.
Сложность: средняя.
Объем транзакции (Transaction volume statistics) включает обычно два параметра: среднюю частоту транзакции (например, 50 тр./ч) и пиковую частоту транзакции (например, 70 тр./ч). Оценка частотных характеристик транзакций базы данных очень важна для проектирования физической модели базы данных: настройка физической структуры базы данных для транзакций с высокой частотой существенно отличается от настройки ее для транзакции с низкой частотой использования.
Для модернизации существующих систем информация о частотах транзакций может быть представлена во входной документации на основе анализа статистики эксплуатации системы. А вот для вновь создаваемых баз данных такую информацию получить практически невозможно, и приходится пользоваться приблизительными оценками.
Пример. Дополняя наш пример, мы можем указать, что Средняя частота транзакции: до 10 в день. Пиковая частота: 10 в час.
Спецификация транзакции должна включать требования по производительности транзакции. Производительность базы данных в целом складывается из производительности каждой транзакции в отдельности и их распределения во времени. Это очень важная информация для проектировщика данных при решении им задачи настройки физической структуры базы данных. Если проектировщик базы данных не имеет такой информации, он не может решить задачу достижения производительности путем подбора соответствующих конструкций базы данных. Требования по производительности обычно задаются в виде параметра время реакции, т.е. количества секунд, требуемых для выполнения транзакции. Например, во многих банковских системах транзакция для снятия денег со счета клиента не должна занимать более 5 секунд.
Другой формой задания требования на производительность транзакций базы данных является указание их характера и сложности. Например:
онлайновые транзакции высокой сложности должны выполняться не более 15 с;онлайновые транзакции средней сложности должны выполняться не более 7 с;онлайновые транзакции низкой сложности должны выполняться не более 4 с;пакетные транзакции высокой сложности должны выполняться не более 1 часа;пакетные транзакции средней сложности должны выполняться не более 0,5 часа;пакетные транзакции низкой сложности должны выполняться не более 15 мин.
В какой форме проектировщик базы данных получит требования на производительность транзакций, сказать сложно. Будет очень хорошо, если он их вообще получит. Это является одним из самых узких мест, как в исходной документации, так и в проектировании физической модели. На практике проектировщики баз данных "отпускают" базу данных в опытную эксплуатацию таким образом, чтобы получить временные характеристики выполнения транзакций и удовлетворить требованиям по производительности в рамках решения задач обратного влияния.
Желательно в спецификации транзакции указать ее относительный приоритет, который показывает, насколько важна настоящая транзакция для предметной области по сравнению с другими. Относительный приоритет транзакции позволяет проектировщику базы данных сгруппировать транзакции по этому параметру, что даст возможность последовательно оценить все транзакции базы данных и их вклад в производительность базы данных.
Далее в процессе создания физической модели данных проектировщик базы данных будет настраивать физическую структуру так, чтобы каждая в отдельности выполнялась быстрее. Выполнение какой-либо транзакции в базе данных с большим приоритетом отрицательно сказывается на производительности других транзакций. Ранжирование транзакций по относительному приоритету позволяет сбалансировать влияние транзакций друг на друга в базе данных в целом.
Задание приоритета транзакций может иметь различные формы.
Обычно такое действие сводится к субъективной оценке в виде числа от 1 до 10.
Каждая спецификация транзакции должна содержать команды SQL, которые задают операции с базой данных. Указание команд SQL в контексте создания физической модели базы данных позволяют оценить время выполнения транзакций (execution time), т.е. фактическое количество секунд, необходимое для завершения транзакции в режиме эксплуатации базы данных. Для проектировщика базы данных этот параметр важен еще и с точки зрения разработки спецификаций модулей приложений базы данных для разработчиков приложений.
Помимо собственно команды языка манипулирования данными, желательно включить некоторый комментарий к каждой команде, в котором указать: а) что команда делает, б) почему это требуется, и в) количество строк в базе данных, которое захватывается командой. Время выполнения команды SQL непосредственно зависит от числа обрабатываемых командой строк. Обычно время выполнения транзакций можно оценить на стадии опытной эксплуатации и тестирования базы данных.
Пример. Продолжая наш пример, можно составить следующую таблицу.
| Select works from project where empno=:1 and works=:2 | Возвращает информацию о назначении данной работы данному служащему. По крайней мере, одна строка возвращается. Число строк, которые могут обрабатываться командой, равно текущему размеру таблицы PROJECT |
| Select works from project where empno=:1 | Возвращает список работ данного служащего, чтобы оценить его загруженность. Число строк, которые могут обрабатываться командой, равно текущему размеру таблицы PROJECT |
| Insert into project empno, works values(:1,:2) | Назначает данного служащего на данную работу, если это необходимо |
Вертикальное разбиение длинных строк
Проблемы с производительностью выполнения запросов на длинных строках таблицы являются наиболее частой причиной для выполнения вертикального разбиения таблицы. Критериями того, что строка является длинной, могут быть:
длина строки больше, чем длина физической страницы базы данных (> 1 Кб);использование так называемого индекса хэширования (cluster hashed index).
Рассмотрим случай, когда длина строки больше размера физической страницы базы данных. Таблица в этом случае либо имеет слишком много колонок, либо некоторые колонки имеют большую длину. В этом случае СУБД при вставке строки в таблицу будет использовать одну или более дополнительных физических страниц для сохранения строки. Следовательно, при выборке строки из таблицы потребуется больше операций ввода/вывода на число дополнительных физических страниц. Производительность запроса при этом будет ухудшаться. Для увеличения производительности выборки можно разбить таблицу на одну или несколько таблиц с длиной строки подходящего размера.
Метод вертикального разбиения принципиально прост, если вспомнить, что разбиение эквивалентно реляционной операции проекции на таблице. Ясно, что некоторые колонки просто переносятся в новую таблицу так, чтобы длина оставшейся строки была подходящей (< 1 Кб). Разбиение не должно нарушать функциональных зависимостей между колонками. Поскольку мы предполагаем, что исходная таблица нормализована, в частности все неключевые колонки функционально полно зависят от первичного ключа, то первичный ключ новой таблицы является точной копией первичного ключа исходной таблицы.
Критичным вопросом является вопрос, какие колонки следует переносить в новую таблицу. При разбиении таблицы увеличение производительности будет достигаться только при раздельном доступе к полученным таблицам. При соединении полученных таблиц производительность такого запроса может быть ниже, чем с тем же запросом к исходной таблице из-за дополнительного ввода/вывода. Поэтому проектировщик данных, до того как принять решение о разбиении таблицы, должен проанализировать все транзакции к исходной таблице с высоким приоритетом и при разнесении колонок по таблицам руководствоваться принципом "частота совместного использования колонок в запросах, размещенных в каждой таблице разбиения, должна быть достаточно высока".
Пример. Обратимся к нашей учебной базе данных. Предположим, что в таблице EMPLOYEE необходимо дополнительно сохранять фотографию сотрудника и его автобиографию. Эти два новых поля имею достаточно большой размер, и длина строки таблицы заведомо превысит 1 Кб. Далее предположим, что существует 60 транзакций, которые обращаются к этой таблице. Только четыре из них обращаются ко всем колонкам: при вводе данных о сотруднике при приеме на работу, при внесении изменений, при удалении информации о сотруднике при его увольнении, и запрос руководителя, который имеет высокий приоритет. Все транзакции, кроме одной указанной выше, имеют средний и низкий приоритеты. Частота транзакции с высоким приоритетом ожидается не превышающей двух раз в неделю. Поэтому разбиение таблицы на две не сильно повлияет на производительность транзакций с высоким приоритетом в базе данных в целом.
Частота использования полей в транзакциях приведена в таблице 10.2.
| 1. | Номер личной карточки | EMPNO (PK) | 60 |
| 2. | Фамилия | ENAME | 60 |
| 3. | Имя | LNAME | 50 |
| 4. | Страховка | SSECNO | 15 |
| 5. | Номер подраздления | DEPNO (FK) | 50 |
| 6. | Должность | JOB | 20 |
| 7. | Возраст | AGE | 4 |
| 8. | Стаж | HIREDATE | 4 |
| 9. | Доплаты | COMM | 50 |
| 10. | Зарплата | SAL | 50 |
| 11. | Штрафы | FINE | 50 |
| 12. | Автобиография | Biog | 4 |
| 13. | Фотография | Foto | 4 |
Таким образом, проектировщик базы данных имеет основание для принятия решения о разбиении таблицы EMPLOYEE на две. Скажем, EMPLOYEE и EMP_ADD. Полученный фрагмент скрипта приведен ниже.
CREATE TABLE EMPLOYEE ( EMPNO integer NOT NULL, ENAME char(25), LNAME char(10), DEPNO int, SSECNO char(10), JOB char(25), SAL dec(9,2), COMM dec(9,2), FINE dec(9,2), PRIMARY KEY (EMPNO) );
CREATE TABLE EMP_ADD ( EMPNO integer NOT NULL, AGE date, HIREDATE date NOT NULL WITH DEFAULT, BIOG varchar(254), FOTO long varchar, PRIMARY KEY (EMPNO) );
Внутритабличная денормализация
Внутритабличная денормализация выполняется в пределах одной таблицы, т.е. это процесс введения избыточных колонок в одной таблице с целью увеличения производительности запроса строки по производному значению. Например, если строка содержит две числовых колонки, X и Y, то значение Z, равное произведению X и Y (Z = X*Y), легко вычислить во время выполнения. Однако предположим, что есть запросы, в которых необходимо осуществить поиск по Z (например, Z принадлежит диапазону от 10 до 20). Сохранив избыточные значения Z в столбце, можно построить индекс по Z, и запросы будут использовать этот индекс. Если индекс по Z строить не надо, то решение о его хранении в отдельном столбце зависит от того, что является более приемлемым - увеличение времени загрузки, вызванное необ ходимостью постоянно пересчитывать Z, или увеличение времени сканирования, обусловленное удлинением строк таблицы за счет хранения дополнительной колонки.
Приведем еще один часто встречающийся пример внутритабличной нормализации. Допустим, что одинаковый текст хранится в двух видах: с символами в верхнем и в нижнем регистре - для отображения и ввода данных с символами в верхнем регистре. Это бывает необходимо для обеспечения работы ускоренных запросов без учета регистра.
Примечание. Обеспечить приемлемую производительность для таблиц умеренного размера (до 10000 строк) в последнем случае можно и без внутритабличной деномализации, переработав запрос с использованием встроенной функции UPPER.
Восходящая денормализация
Восходящая денормализация предлагает перенос атрибута из подчиненной (дочерней) сущности в родительскую сущность, обычно в форме итоговых данных. На рисунках 10.3 и 10.4 показано, как это можно сделать для сущностей Order и Order Item (Позиция заказа).
Рис. 10.3. Сущности Customer и Order до денормализации
Рис. 10.4. Сущности Customer и Order после денормализации
Например, если в вычисление общей суммы заказа в системы обработки заказов (суммирование колонок Item_Price в таблице Order Item) приводит к снижению производительности, то мы можем повысить производительность этой операции, поместив сумму заказа в избыточном столбце таблицы ORDER. В нашем примере в избыточном столбце хранится сумма значений, но эти приемы применимы к максимальным, минимальным и средним значениям, а также к другим агрегатным показателям.
Таким образом, восходящая денормализация - это процесс введения избыточных колонок в родительских таблицах с целью устранения операций соединения с операциями агрегирования.
Чтобы представить последствия введения денормализации, рассмотрим процедуру сопровождения денормализованных таблиц Order и Order Item, которые сводятся к поддержке следующих бизнес-правил:
Когда в таблицу Order Item добавляется новая строка, то цена заказа (колонка Order_Price) в таблице Order увеличивается на цену новой позиции заказа (Item_Price).Когда строка удаляется из таблицы Order Item, то цена заказа в таблице Order уменьшается на цену старой позиции заказа (Item_Price).Когда изменяется цена в таблице Order Item, то цена заказа в таблице Order должна быть откорректирована на разницу между старой и новой ценами позиции заказа (Item_Price).
Поддержка перечисленных выше бизнес-правил создает дополнительную нагрузку на процессы, выполняющие DML-операции в таблице Order Item. Это и есть цена, которую мы вынуждены заплатить за повышение производительности запросов.
Одной из основных целей настоящей
Одной из основных целей настоящей лекции является формирование навыков решения следующей профессиональной задачи проектирования баз данных этапа создания физической модели базы данных: учета влияния транзакций.
Решая задачу создания первой итерации физической модели базы данных, проектировщики базы данных преследовали достижение следующих целей:
удовлетворить потребности в хранении данных предметной области в рамках реляционной модели данных, т.е. были созданы базовые таблицы для хранения информации обо всех сущностях предметной области;удовлетворить требования целостности данных, т.е. были определены типы колонок и наложены ограничения на значения колонок базовых таблиц, которые следовали бизнес-правилам предметной области;удовлетворить требования ссылочной целостности, т.е. в случае принятия решения о поддержке ссылочной целостности встроенными средствами СУБД были наложены ограничения ссылочной целостности на таблицы, исходя из бизнес-правил ссылочной целостности предметной области;частично удовлетворить требования независимости представления данных для конечного пользователя от характера физического хранения данных, т.е. была построена первая итерация внешней схемы.
Вторая главная цель физического проектирования реляционной базы данных состоит в том, чтобы дать гарантию того, что база данных обеспечивает требуемый уровень производительности. Таким образом, следующей профессиональной задачей проектировщика базы данных является борьба за производительность базы данных, т.е. удовлетворение требований по обеспечению требуемого уровня производительности базы данных. Обычно производительность базы данных измеряется в терминах производительности транзакций (transaction performance).
На предыдущем этапе проектировщик реляционной базы данных решил первую главную задачу физического проектирования - в рамках требований реляционной модели создал объекты хранения данных. Он отобразил сущности и взаимосвязи логической модели реляционной базы данных в физические объекты СУБД - таблицы, индексы, представления.
Критерием этой работы проектировщика базы данных выступает удовлетворение требований по производительности транзакций. Решающим фактором для проектировщика базы данных в борьбе за производительность является решение задачи выбора между физическими конструкциями СУБД, которые могут быть использованы для повышения производительности транзакций.
Чтобы успешно решить данную задачу, проектировщик базы данных должен иметь хорошо определенные транзакции, которые могут существовать в базе данных. Однако в большинстве практических случаев получить изначально полностью прописанные транзакции к базе данных является весьма сложной организационной задачей. В условиях недостаточных сведений о транзакциях проектировщику приходится зачастую полагаться на свой собственный опыт проектирования, выполнять выборочную настройку полученной первой итерации физической модели базы данных и переадресовывать окончательное решение данной задачи администратору базы данных. К тому же часть проблем, связанная с производительностью базы данных, может быть выявлена лишь на стадии тестирования и опытной эксплуатации базы данных. Совокупность таких отложенных задач проектирования автор называет задачами обратного влияния, которые будут изучаться нами в последних лекциях этого курса.
Отметим, что при решении задач этого этапа нельзя опираться только на знание стандарта SQL, как мы делали это в предыдущей лекции. В действие вступают конструкции конкретной СУБД, выбранной для реализации базы данных. Основными механизмами промышленных СУБД для решения настоящей задачи повышения производительности являются денормализация, индексы, кластеризация и разделение. Индексы, кластеризация и разделение (секционирование на уровне возможностей СУБД) будет рассматриваться в следующей лекции.
Хэш-секционирование
Хэш-секционирование (hash partitioning) означает равномерное распределение строк таблицы по назначенным табличным пространствам в зависимости от значения ключа секционирования, который в данном случае хэшируется. Этот вид секционирования удобно применять для строк, у которых распределение значений ключа секционирования неравномерно или плохо группируется. Если проектировщик базы данных принимает решение о создании хэш-секционированной таблицы, то он должен достаточно точно представлять размер этой таблицы, поскольку встроенные в СУБД Oracle алгоритмы хэширования используют этот размер для вычисления позиции строки на физической странице базы данных. Неверное определение размера таблицы может привести к большому числу коллизий, т.е. к попаданию строк с различными значениями ключа на одну и ту же страницу, что приводит к поддержке цепочек переполнения и дополнительному вводу/выводу.
Пример. Рассмотрим ту же таблицу Sales, что и в предыдущем примере, и ту же схему (рис. 11.2) табличных пространств. Однако используем в качестве ключа секционирования идентификацию клиента. Отметим, что распределение значений этой колонки может быть очень неравномерно. Фрагмент кода SQL для создания хэш-секционированной таблицы Sales можно написать так:
CREATE TABLE Sales ( s_customer_id number(6), s_amt number(9,2), s_date date) PARTITION BY HASH (s_customer_id) (PARTITION q01 TABLESPACE ts_01, PARTITION q02 TABLESPACE ts_02, PARTITION q03 TABLESPACE ts_03, PARTITION q04 TABLESPACE ts_04 );
Предложение PARTITION BY HASH (s_customer_id) указывает СУБД Oracle выполнить секционирование таблицы по ключу секционирования - s_customer_id. Предложения вида (PARTITION q01 TABLESPACE ts_01 определяют имя секции st_q01 и ее размещение в соответствующем табличном пространстве ts_01.
Индекс со структурой B-Tree
Индекс на основе сбалансированной иерархической структуры, или индекс B-Tree (Balanced Tree structured object), используется как индекс по умолчанию в СУБД Oracle. Эта структура напоминает дерево (если смотреть снизу вверх), в котором сначала считывается самый верхний блок - корневой узел (root), затем блок на следующем уровне - блок-ветвь (branch) и так до тех пор, пока не будет извлечен блок-лист (leaf) с идентификатором строки. Значения ключа сохраняются в индексе (рис. 11.1). Такая структура позволяет сократить до минимума число операций ввода/вывода. Для получения идентификатора строки обычно требуется одно посещение блок-листа, т.е. физической страницы базы данных, отведенной под индекс.
Рис. 11.1. Концептуальная организация B-Tree индекса
Замечание. Следует отметить два случая, когда после выборки идентификатора строки из индекса может понадобиться несколько посещений физической страницы индекса: 1) когда строка имеет длину более одной физической страницы, так называемая расщепленная строка; 2) когда строка за время своего существования в базе данных увеличилась и была перемещена из исходной страницы в другую, так называемая мигрировавшая строка.
Индекс B-Tree характеризуется количеством уровней в индексе (height). Чем меньше уровней, тем выше производительность.
Индекс B-Tree - это физический объект реляционной базы данных, организованный по принципу сбалансированной иерархической структуры и обладающий набором свойств. Сформулируем некоторые свойства индексов со структурой B-Tree.
Количество операций ввода/вывода, необходимых для получения идентификатора строки, зависит от числа уровней ветвления дерева. По мере увеличения индекса в результате добавление новых данных, СУБД добавляет в него новые уровни, чтобы обеспечить сбалансированность дерева. Однако в действительности таких уровней редко бывает более четырех.Корневой узел и узлы - ветви индекса сжимаются и поэтому содержат ровно столько начальных байтов значения ключа, сколько нужно для того, чтобы отличить его от других значений.
Узлы-листья содержат полное значение ключа.Значения в индексе упорядочиваются по ключевому значению, а физические страницы индекса организуются в двунаправленный список. Это обеспечивает последовательный доступ к индексу и позволяет использовать индекс для выполнения операции ORDER BY в запросе.Индекс можно использовать для поиска и точного соответствия, и для диапазона значений.Индексы могут быть построены для нескольких колонок таблицы (так называемый составной индекс). СУБД использует составные индексы для выполнения тех запросов, в которых задана лидирующая часть составного ключа. Например, составной индекс {Ename, Job} для обработки запроса SELECT * FROM EMPLOYEE WHERE Job='Инженер'; применяться не будет.СУБД обычно само принимает решение, использовать индекс или нет.Значения колонок NULL не индексируются. Если для таких колонок строится индекс, то СУБД будет отказываться примерять его в некоторых операциях, например ORDER BY.
Индексы создаются командой SQL CREATE INDEX. В предыдущих лекциях мы уже создавали индексы на основе B-Tree. При создании индекса опционально можно задать ряд параметров. Для получения полного списка параметров следует обратиться к документации по СУБД. Применение некоторых параметров будет показано в следующих разделах.
Пример. В нашей учебной базе создадим для таблицы EMPLOYEE составной индекс по колонкам Ename и Job. При этом проектировщик базы данных не уверен, что этот индекс будет использоваться эффективно, поэтому он задал опцию для сбора статистики для этого индекса.
CREATE INDEX emp_ndx2 ON EMPLOYEE (Ename, Job) COMPUTE STATISTICS;
В этом подразделе мы рассмотрели наиболее часто используемый тип индексов. В последующих подразделах и разделах мы рассмотрим другие типы индексов реляционных баз данных.
Индексирование
Индексирование (indexing) - это способ обеспечения быстрого доступа к значениям колонки или комбинации колонок. Физически новые строки добавляются в конец таблицы, результатом чего становится неупорядоченное размещение значений в колонках. Без использования каких-либо методов упорядочения данных единственным способом просмотра значения колонки со стороны СУБД является последовательный просмотр каждой строки от начала таблицы к ее концу, так называемое сканирование таблицы. Производительность такого сканирования пропорционально размеру таблицы, размеру физической страницы базы данных и длине строки. Одним из способов внесения отношения порядка в значения колонок без нарушения физического расположения строк таблицы является создание объекта реляционной СУБД - индекса (index). Индекс - это объект в реляционной базе данных, который предназначен для организации быстрого доступа к строкам таблицы по значениям одной или более колонок этих строк.
О создании индекса мы уже говорили при обсуждении ограничений первичного ключа и внешнего ключа. Тогда индексы предназначались для поддержки целостности первичного ключа и поддержки ограничения ссылочной целостности. В этом разделе пойдет речь об использовании индекса как инструмента повышения производительности обработки запросов.
Концептуально действие индекса состоит в следующем. В индексе содержится упорядоченный список значений колонки или комбинации колонок, а также сведения о местонахождении на жестком диске соответствующих этим значениям строк таблицы. Значения колонки в индексе упорядочены. Несмотря на то, что порядок строк в таблице случаен, индекс можно быстро просмотреть, чтобы найти конкретное значение. Упорядоченный индекс можно просмотреть во много раз быстрее, чем неупорядоченную таблицу. Чем выше степень различия значений ключа в колонке, тем быстрее будет выполняться доступ к строкам этой таблицы.
Так при вставке новой записи в таблицу проверка уникальности первичного ключа реализуется не реальным просмотром индекса, а тем, что требование уникальности предъявляется к значениям колонки первичного ключа в индексе.
Таким образом, индекс - это объект базы данных, который может существенно сократить время поиска нужных строк в таблице.
Замечание. После того как вы создали индекс, оптимизатор СУБД, о котором пойдет речь в последней лекции, будет использовать его всякий раз, когда это ускоряет считывание данных. Обратите внимание на то, что созданный вами индекс может ни разу не использоваться!
Индексы, несомненно, занимают место в базе данных. При вводе новых данных или удалении данных СУБД приходится обновлять и таблицы, и индексы. Это может замедлить выполнение операций модификации данных, особенно для таблиц с большим числом строк. Таким образом, может возникнуть проблема, суть которой состоит в возникновении конфликта между скоростью обновления данных в таблице и скоростью ее считываний. При разрешении этой проблемы следует придерживаться следующего эмпирического правила: создавайте индексы для колонок первичных ключей и других колонок, часто используемых в тех запросах, в которых для выборки данных применяются логические критерии. Если в результате скорость обновления данных ухудшается, то можно рассмотреть вопрос об удалении некоторых индексов.
Каждая таблица базы данных может иметь один или несколько индексов. Индексы могут создаваться по одной колонке или нескольким колонкам таблицы. Колонки, входящие в индекс, принято называть ключевыми полями (key fields) или ключами. Индексы могут быть уникальными и неуникальными. Неуникальный индекс может иметь несколько ключей с одинаковыми значениями.
В СУБД Oracle и SQLBase каждая строка таблицы обладает уникальным идентификатором ROWID - идентификатором строки, который представляет собой псевдоколонку с информацией о точном расположении строки в базе данных и содержит еще некоторую идентифицирующую информацию (идентификатор объекта базы данных, файла данных, блока и строки). Идентификатор строки хранится в индексе вместе со значениями ключевых полей.
Зачем создавать индекс для колонки или группы колонок? Это важный вопрос, и мы на него можем ответить следующим образом:
чтобы ускорить поиск в таблицах ( об этом мы только что говорили выше);чтобы обеспечить уникальное значение в колонках (это мы обсуждали в разделе об ограничениях);чтобы извлекать строки в заданном порядке на основании значений индексированных колонок (эта мысль оправдана только для очень больших таблиц, когда использование предложения ORDER дает ухудшение производительности).
На этапе физического проектирования реляционной базы данных проектировщику необходимо принять ряд важных решений о том, что и как индексировать. В данном случае важно четко сформулировать правила индексирования. Для чего нужны правила индексирования? Для каждого ИТ-проекта с базами данных проектировщику необходимо создать и оформить в письменном виде правила индексирования, как часть общих правил обеспечения производительности. Поддержка и сопровождение индексов в процессе эксплуатации базы данных является в основном задачей администратора базы данных. Решая задачи обеспечения производительности базы данных, администратор базы данных будет ставить вопрос о перепроектировании физической структуры базы данных (обратные задачи проектирования), в том числе и вопрос об удалении и создании новых индексов. Он может решать эти задачи самостоятельно. Тем более важно знать, по каким правилам и из каких соображений создавались индексы того или иного типа. Разработка таких правил значительно повысит качество эксплу атации базы данных с точки зрения обеспечения ее производительности.
Чтобы решать эти задачи, проектировщик базы данных должен знать, как работает индекс, какие типы индексов поддерживает СУБД, а также понимать смысл методов индексирования.
Сначала мы опишем типы индексов вместе с методами индексирования для каждого типа, затем разберем вопрос о том, как работает индекс, и в заключение дадим некоторые рекомендации по созданию и использованию индексов.
Исключительно индексные таблицы
Индексы могут создаваться на основе значений одной или нескольких колонок. Если требования к данным в запросе удовлетворяются на основе информации из связанного с этими данными индекса, то доступ к базовой таблице не осуществляется. Это обстоятельство привело к идее создания исключительно индексной таблицы (index-organized table). Исключительно индексная таблица является индексом типа B-Tree базы данных, который одновременно исполняет роль таблицы. Все данные такой таблицы хранятся в индексе. Преимуществом создания полностью индексированных таблиц состоит в экономии места хранения на диске и сокращения объема ввода/вывода, поскольку ключевые колонки нет необходимости сохранять еще раз в таблице. Результат выполнения запроса будет получен на основе данных, сохраненных в индексной таблице. Исключительно индексная таблица создается с помощью команды SQL CREATE TABLE, как показано в примере ниже.
Пример. Предположим, что в нашей учебной базе требуется в отдельной таблице сохранять и отслеживать проблемы, возникающие по выполнении всех проектов, и частоту возникновения проблемы. Создадим исключительно индексную таблицу для этой таблицы, как показано ниже:
CREATE TABLE Proj_Index ( projno char(8) NOT NULL, t_person char(32) NOT NULL, t_frequency integer, t_problem varchar2(512), CONSTRAINT pk_ndx PRIMARY KEY( projno, t_person) ) ORGANIZATION INDEX TABLESPACE ts_ndx1 PCTTHRESHOLD 20 INCLUDING t_frequency OVERFLOW TABLESPACE ts__of_ndx1;
Команда CREATE TABLE не отличается ничем от других команд создания таблиц до тех пор, пока не встретится предложение ORGANIZATION INDEX, которое указывает СУБД на создание исключительно индексной таблицы. Для размещения индекса на диске указывается табличное пространство. Параметр PCTTHRESHOLD указывает, что оставшуюся часть строки нужно сохранять в заданном табличном пространстве - сегменте переполнения, если данная строка превышает размер физической страницы базы данных на указанное число процентов. Параметр INCLUDING определяет имя колонки, с которой строка индексной таблицы делится на две части: индексную и переполнения. Эта колонка может быть частью первичного ключа таблицы или неключевой колонкой. Все неключевые колонки, которые следуют за указанной колонкой, размещаются в сегменте переполнения, который определяется ключевым словом OVERFLOW.
О некоторых параметрах проектирования индексов
Когда проектировщик базы данных приступает к проектированию индексов, то он должен иметь некоторый способ оценки качества создаваемого индекса. Введем несколько понятий, с помощью которых проектировщик может грубо оценить качество потенциального индекса.
Кардинальностью колонки (cardinality) таблицы называется число дискретных различных значений колонки, которые встречаются в строках таблицы. Например, если в таблице EMPLOYEE мы заводим колонку для указания пола - SEX, то кардинальность этой колонки есть 2, так как в природе у людей существует только два пола - мужской и женский. Для колонки первичного ключа кардинальность будет равна числу строк в таблице.
Причиной, по которой кардинальность колонки важна для проектирования индексов, состоит в том, что кардинальность индексируемой колонки определяет число уникальных входов, которые должны сохраняться в индексе, т.е. число записей в индексе. Так, для индексируемой колонки SEX будет существовать два уникальных входа, которые будут повторяться много раз в индексе. При предположении равновероятного распределения пола сотрудников на 100000 строк в таблице EMPLOYEE каждый вход индекса будет повторяться 50000 раз. СУБД вряд ли будут принимать решение об использовании такого индекса при построении плана запроса.
Определить кардинальность потенциальной колонки индексирования в существующей базе данных достаточно просто:
SELECT COUNT (DISTINCT колонка) FROM таблица
При проектировании новой базы данных проектировщик должен оценить кардинальность всех потенциальных индексируемых колонок во всех таблицах базы данных, исходя из имеющейся документации.
Способ, с помощью которого СУБД оценивает действие кардинальности, состоит в использовании фактора селективности выборки (selectivity factor). Фактора селективности выборки индекса определяется как величина, обратная кардинальности индексной колонки:
Фактор селективности оценивает потенциальный объем операций ввода/вывода. Чем меньше фактор селективности, тем меньше требуется операций ввода/вывода для получения результирующего множества строк таблицы.
СУБД оценивает эту величину, чтобы решить, использовать индекс для доступа к строкам таблицы или нет. Какие формулы используются для оценки фактора селективности в СУБД, мы рассмотрим в последней лекции этого курса.
В заключение раздела мы приведем список правил для определения колонок, которые являются хорошими и плохими кандидатами для индексирования. Эти правила могут быть использованы проектировщиком базы данных при принятии решения о построении индексов реляционной базы данных.
Хорошими кандидатами для индексирования обычно являются:
колонки первичного ключа. По определению, колонки первичного ключа должны иметь уникальный индекс;колонки внешнего ключа. Они дают хороший индекс по двум причинам. Во-первых, они часто применяются для выполнения соединений с родительскими таблицами. Во-вторых, они могут быть использованы СУБД при поддержке ссылочной целостности в операциях удаления строк родительской и дочерних таблиц;любые колонки, которые содержат уникальные значения;колонки, запросы или соединения по которым захватывают от 5 до 10% строк таблицы;колонки, которые часто входят как аргументы в функции агрегирования;колонки, которые часто используются для проверки правильности ввода данных в программах ввода/редактирования.
Факторы, влияющие на низкую эффективность индексов:
Таблицы маленького размера. Одним из общих эмпирических правил является правило "не создавать индексы для таблиц размером менее пяти физических страниц". Для таких страниц стоимость поддержки индекса больше, чем стоимость сканирования всей таблицы. Конечно, уникальный индекс требуется для первичного ключа и поддержки ссылочной целостности.Интенсивные обновления таблиц в пакетном режиме. Такие таблицы обычно имеют проблемы с переполнением индекса при интенсивной модификации таблицы. Если индекс необходим для такой таблицы, то целесообразнее его удалять перед обновлением и создавать после него.Асимметрия значений ключей (Skewness of keys). Если распределение значений ключа имеет значительную асимметрию, то кардинальность индекса может оказаться достаточно высокой и СУБД из-за низкого фактора селективности будет часто использовать этот индекс.
СУБД оценивает эту величину, чтобы решить, использовать индекс для доступа к строкам таблицы или нет. Какие формулы используются для оценки фактора селективности в СУБД, мы рассмотрим в последней лекции этого курса.
В заключение раздела мы приведем список правил для определения колонок, которые являются хорошими и плохими кандидатами для индексирования. Эти правила могут быть использованы проектировщиком базы данных при принятии решения о построении индексов реляционной базы данных.
Хорошими кандидатами для индексирования обычно являются:
колонки первичного ключа. По определению, колонки первичного ключа должны иметь уникальный индекс;колонки внешнего ключа. Они дают хороший индекс по двум причинам. Во-первых, они часто применяются для выполнения соединений с родительскими таблицами. Во-вторых, они могут быть использованы СУБД при поддержке ссылочной целостности в операциях удаления строк родительской и дочерних таблиц;любые колонки, которые содержат уникальные значения;колонки, запросы или соединения по которым захватывают от 5 до 10% строк таблицы;колонки, которые часто входят как аргументы в функции агрегирования;колонки, которые часто используются для проверки правильности ввода данных в программах ввода/редактирования.
Факторы, влияющие на низкую эффективность индексов:
Таблицы маленького размера. Одним из общих эмпирических правил является правило "не создавать индексы для таблиц размером менее пяти физических страниц". Для таких страниц стоимость поддержки индекса больше, чем стоимость сканирования всей таблицы. Конечно, уникальный индекс требуется для первичного ключа и поддержки ссылочной целостности.Интенсивные обновления таблиц в пакетном режиме. Такие таблицы обычно имеют проблемы с переполнением индекса при интенсивной модификации таблицы. Если индекс необходим для такой таблицы, то целесообразнее его удалять перед обновлением и создавать после него.Асимметрия значений ключей (Skewness of keys). Если распределение значений ключа имеет значительную асимметрию, то кардинальность индекса может оказаться достаточно высокой и СУБД из-за низкого фактора селективности будет часто использовать этот индекс.
Параметры индексирования
СУБД Oracle предусмотрено еще несколько параметров индексирования, которые позволяют улучшить традиционные для всех СУБД индексы со структурой B-Tree. К таким модификациям, помимо исключительно индексных таблиц, относятся битовые индексы, индексы с обращением ключа, индексы на основе значения функций.
Каждый бит так называемого битового (bitmap) индекса относится к идентификатору строки ROWID в табличном объекте. Если некоторая строка содержит данное ключевое значение, то в индексе для этого значения сохраняется единица. Такая организация индекса может в некоторых случаях значительно повысить производительность выборки данных, т.к. для извлечения строк с определенным значением индекса СУБД нужно лишь найти все единицы, отвечающие ключу. Физически такой индекс организован на основе структуры B-Tree, но задача сводится к поиску данной строки за счет одной операции чтения битовой индексной структуры. Этот тип индекса очень эффективен для индексирования колонок с небольшим кардинальным числом - пол, цвет и т.д. Если значений у колонки буде много, то объем ввода/вывода будет возрастать.
Пример. Для нашей учебной базы данных можно построить битовый индекс для таблицы EMPLOYEE по колонке DEPNO, как показано ниже:
CREATE BITMAP INDEX emp_ndx ON EMPLOYEE (DEPNO);
В индексе с обращением ключа (reverse-key index) применяется обращение байтов индексируемой колонки числового типа. Этот прием позволяет получать равномерное распределение значений колонок среди блок-листов индекса со структурой B-Tree. Этот индекс хорошо подходит для индексирования колонок с последовательной нумерацией или нумерацией с заданным шагом. Заметим, что такие индексы применяются только для возвращения отдельных строк, и с их помощью нельзя выполнить поиск значений в некотором диапазоне. Вы не можете применить опцию REVERSE к битовым индексам и исключительно индексным таблицам.
Пример. В нашей учебной базе данных числовые ключи, содержащие последовательные числа, есть, в частности, в таблице PLOYEE - EMPNO.
Мы можем определить для этой таблиц дополнительный индекс с обращением ключа для извлечения записи о сотруднике. Заметим, что для этой колонки уже есть индекс первичного ключа.
CREATE INDEX dep_ndx ON EMPLOYEE (EMPNO) REVERSE;
В процессе эксплуатации администратор базы данных может перестроить этот индекс с помощью команды ALTER INDEX, как показано ниже
ALTER INDEX EMPLOYEE REBUILD NOREVERSE;
Если в предложении WHERE используется функция по индексированной колонке, то обычно СУБД не применяют этот индекс при организации доступа к строкам таблицы. Но при создании индекса на основе значения функции (function-based index), которая является той же функцией, что и в предложении WHERE, то СУБД использует такой индекс для считывания строк, удовлетворяющих критерию отбора. Индексы на основе значений функции могут быть битовыми индексами.
Пример. Обратимся к нашей учебной базе. Предположим, что при поиске сотрудников по фамилии таковая вводится на верхнем регистре, как в примере ниже:
SELECT * FROM EMPLOYEE WHERE UPPER(:ENAME) ORDER BY UPPER(:ENAME);
Тогда, даже при наличии индекса по колонке ENAME, СУБД будет сканировать таблицу, не обращаясь к этому индексу. Проектировщик базы данных, учитывая, что частота таких транзакций будет очень высокой, может предусмотреть создание индекса на основе значений функции от колонки EMANE, как показано ниже:
CREATE INDEX emp_ndx_e ON EMPLOYEE UPPER(:ENAME);
При наличии в базе данных такого индекса СУБД Oracle будет его использовать при обработке вышеприведенного запроса.
Повышение производительности запросов: Кластеры
Самой медленной операцией, выполняемой СУБД, является операция чтения данных с диска или запись данных на диск. Если существует возможность уменьшить в несколько раз число таких операций, то общая производительность базы данных может заметно увеличиться.
Следует помнить, что СУБД считывает с диска или записывает на диск за один раз одну физическую страницу данных, размер которой колеблется в зависимости от аппаратной платформы от 512 байт до 4 Кб. Таким образом, если можно физически хранить данные, к которым часто происходит совместное обращение, на одной и той же странице диска или на страницах, физически близко расположенных друг к другу, то скорость доступа к этим данным повышается.
Кластеризация (Clustering) - это способ физического размещения рядом, на одной физической странице данных, строк, доступ к которым осуществляется при помощи одинакового значения колонки (ключа) с целью увеличения производительности. Такой ключ называется кластерным ключом. Значением кластерного ключа являются значения одинаковых по смыслу колонок строк кластеризуемых таблиц. Ключ может быть либо хэш-ключом, либо индексным ключом. Если ключ является хэш-ключом, то физическое размещение определяется функцией преобразования ключа (хэширования) и мы имеем дело с уже известной нам из предыдущих разделов таблицей хэширования или хэш-кластером. Если это индексный ключ, то для идентификации страницы данных в кластере используется индекс со структурой B-Tree, в котором сроки, имеющие одинаковые значения ключа, размещаются либо в одной странице, либо в смежных стран ицах индекса. Такой кластер называется индексным кластером. Строки, которые хранятся в индексном кластере, не обязательно должны принадлежать одной таблице. Таким образом, кластеры являются одним из методов хранения таблиц данных, поддерживаемых СУБД. Кластер - это группа таблиц, которая разделяет общие физические страницы данных при совместном использовании в запросах общих колонок этих таблиц.
На практике индексный кластер создается для совместного хранения строк, связанных ограничением внешнего и первичного ключей.
Совместное хранение строк родительской и дочерней таблиц может значительно ускорить выполнение соединения этих таблиц.
Пример. Рассмотрим таблицы DEPARTAMENT и EMPLOYEE нашей учебной базы данных. Они некластеризованы и хранятся каждая на своих физических страницах. Предположим, что анализ запросов показывает, что в 80% запросов эти таблицы используются совместно, при этом соединение выполняется по колонке DEPNO. Проектировщик базы данных может решить построить кластер для этих двух таблиц. На рисунке ниже показана концептуальная сторона такого решения.
До кластеризации строки из таблиц сохраняются отдельно в своих физических областях на диске.
| DEPARTMENT | |||
| DEPNO | DNAME | LOC | … |
| 10 | Торговля | Москва | |
| 20 | Консалтинг | Черноголовка | |
| EMPLOYEE | |||
| EMPNO | ENAME | LNAME | DEPNO |
| 996 | Козырев | Сергей | 10 |
| 997 | Сапегин | Алексей | 20 |
| CLUSTER | ||||
| DEPNO | ||||
| 10 | DNAME | LOC | … | |
| Торговля | Москва | … | ||
| … | … | … | ||
| EMPNO | ENAME | LNAME | … | |
| 996 | Козырев | Сергей | … | |
| … | … | … | ||
| 20 | DNAME | LOC | … | |
| Консалтинг | Черноголовка | … | ||
| EMPNO | ENAME | LNAME | … | |
| 997 | Сапегин | Алексей | … | |
| … | … | … | … | |
Кластеризация может существенно ускорить работу с соединениями. Однако, планируя использование кластеров, проектировщик базы данных должен учитывать следующие факторы:
кластеризация затрагивает физическое размещение данных в файлах базы данных. Поэтому рекомендуется выполнить кластеризацию таблицы только по одной колонке или комбинации колонок;кластеризация замедляет выполнение операций, в которых нужно просканировать всю таблицу, так как она может вызвать разброс строк одной таблицы по множеству физических страниц;кластеризация может замедлить ввод данных;кластеризация может замедлить модификацию данных в тех колонках, которые помещены в кластер.
В силу вышеперечисленных обстоятельств кластеры не рекомендуется создавать для таблиц с интенсивным обновлением данных. Для того чтобы таблица была хорошим кандидатом для ее кластеризации, должны выполняться по крайней мере следующие условия:
Значения колонок кластерных ключей распределены равномерно и плотно, а их размер почти всегда меньше размера физической страницы (иначе будут образовываться кластерные цепочки).В случае индексного кластера на каждый кластерный ключ приходится больше одной выбираемой строки, а для хэш-кластера - одна строка. Альтернативное решение - индексация таблицы.Все данные для заданного кластерного ключа выбираются при каждом доступе по кластерному ключу. Альтернативное решение - индексация таблицы.Интенсивность обращений операций вставки, обновления и удаления не очень велика, иначе общая производительность базы данных может уменьшиться. Обратный отрицательный эффект.
В силу вышеперечисленных обстоятельств кластеры не рекомендуется создавать для таблиц с интенсивным обновлением данных. Для того чтобы таблица была хорошим кандидатом для ее кластеризации, должны выполняться по крайней мере следующие условия:
Значения колонок кластерных ключей распределены равномерно и плотно, а их размер почти всегда меньше размера физической страницы (иначе будут образовываться кластерные цепочки).В случае индексного кластера на каждый кластерный ключ приходится больше одной выбираемой строки, а для хэш-кластера - одна строка. Альтернативное решение - индексация таблицы.Все данные для заданного кластерного ключа выбираются при каждом доступе по кластерному ключу. Альтернативное решение - индексация таблицы.Интенсивность обращений операций вставки, обновления и удаления не очень велика, иначе общая производительность базы данных может уменьшиться. Обратный отрицательный эффект.
Из этого следует, что существуют две основные причины использования кластеров: это необходимость а) обеспечить прямой доступ к строке за одну операцию чтения; и б) сократить число операций ввода/вывода при доступе к часто совместно используемым данным путем размещения их в близко расположенных физических страницах базы данных.
С физической точки зрения кластер находится отдельно от таблиц. Он создается с указанием параметров хранения, а затем в нем последовательно создаются кластеризованные таблицы. При описании кластера нужно указать колонки или колонку, для которых СУБД сформирует кластер, и таблицы, которые будут включены в его состав. При обработке данных СУБД будет размещать строки, содержащие одинаковые значения в колонках кластера, физически максимально близко. В результате строки таблицы могут быть распределены среди нескольких дисковых страниц, но первичные и внешние ключи обычно располагаются на одной странице.
Пример. Вернемся к нашей учебной базе данных и напишем фрагмент скрипта для создания кластера для таблиц DEPARTAMENT и EMPLOYEE. Для создания кластеров используется команда SQL CREATE CLUSTER, которая в нашем случае будет иметь вид
Хэш- кластер является альтернативной техникой создания таблиц данных по отношению к индексному кластеру или некластеризованной таблице.
Пример. Рассмотрим нашу учебную базу данных с целью создания хэш-кластера для таблицы EMPLOYEE. На рис. 11.3 ниже показано, как будет выполняться доступ к записям таблицы до и после кластеризации.
Рис. 11.3. Доступк строке таблицы EMPLOYEE через индекс по колонке EPMNO
SELECT * FROM EMPLOYEE WHERE EMPNO= 997;
До кластеризации по колонке EPMNO доступ будет выполняться через индекс, и согласно рисунку 11.3 потребуется 4 операции ввода/вывода, чтобы получить результирующую строку.
После кластеризации по колонке EPMNO строки таблицы EMPLOYEE будут сохраняться в структуре, которая условно приведена на рисунке ниже. После хэширования ключа потребуется одна операция ввода/вывода, чтобы получить результирующую строку, если нет цепочек переполнения.
| CLUSTER | ||||
| Хэш-ключ | Кластерный ключ | |||
| 110 | EMPNO | ENAME | LNAME | … |
| 996 | Козырев | Сергей | … | |
| … | … | … | ||
| 120 | EMPNO | ENAME | LNAME | … |
| 997 | Сапегин | Алексей | … | |
Пример. Создадим хэш-кластер для таблицы 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, интенсивно использует кластеры для доступа к системным таблицам базы данных, автор настоящего курса рекомендует проектировщикам базы данных проявлять осторожность при принятии решения о кластеризации таблиц при создании новой базы данных. Выигрыш в производительности может быть не слишком высок по сравнению с другими проектными решениями. Проектирование кластеров - штучная работа. Очень полезно знать статистику использования аналогичного кластера при эксплуатации аналогичной базы данных, чтобы построить высокопроизводительный кластер. Придерживайтесь следующих эмпирических правил:
До 1000 записей СУБД не имеет больших преимуществ перед последовательным файлом.От 1000 до 10000 записей это преимущество незначительно.От 10000 до 100000 записей между настольными и промышленными СУБД не ощущается разницы в производительности.От 100000 до 1000000 записей промышленные СУБД обеспечивают приемлемую производительность без специальных способов ее повышения.От 1000000 записей надо начинать думать о повышении производительности.
Литература: [7], [14], [20], [23], [45].
Секционирование
Во многих базах данных в таблицах хранится огромное количество данных. Чем больше размер таблицы, тем больше времени потребуется как для некоторых операций по выборке строк таблицы, так и для выполнения некоторых функций администратора базы данных. Например, для резервного копирования и восстановления. Большие по размеру индексированные таблицы имеют также большие индексы, которые требуют много времени СУБД для работы с ними.
Секционирование (partitioning) - это способ физического распределения таблиц и индексов среди двух или более табличных пространств в зависимости от значений ключевых колонок таблиц с целью повышения производительности операций ввода/вывода. Фрагмент таблицы, расположенный в отдельном табличном пространстве, будем называть секцией таблицы. Секционирование также повышает эффективность резервного копирования и восстановления за счет выполнения этих задач с участием меньшего объема данных. Обсуждению создания табличных пространств далее будет посвящен отдельный раздел следующей лекции. Для понимания данного материала нам достаточно знать, что это предопределенный поименованный фрагмент памяти на одном или нескольких дисках, к которому можно обращаться в предложениях SQL по имени.
В осуществлении секционирования одним из важных понятий является колонка таблицы, относительно значений которой СУБД будет делать физическое разнесение таблицы по различным табличным пространствам на жестких дисках. Эти колонка называется ключом секционирования (partition key).
В СУБД Oracle поддерживается несколько видов секционирования: секционирование по диапазону, хэш-секционирование, составное секционирование, а также различные виды секционирования индексов.
Секционирование индексов
В СУБД Oracle предусмотрено секционирование индексов (index partitioning), которое означает преднамеренное распределение индексов таблиц по назначенным табличным пространствам в соответствии с ключом секционирования. Секционирование индексов может быть глобальным и локальным. Локально секционированный индекс имеет такой же ключ секционирования, количество табличных пространств и правила секционирования, что и отвечающая ему базовая таблица. Глобально секционированный индекс содержит предложение PARTITION BY RANGE, в котором задаются параметры секционирования, отличные от параметров секционирования соответствующей базовой таблицы. Секционированные индексы могут быть префиксными или непрефиксными. В случае префиксного секционированного индекса секционирование производится по ключу секционирования, который содержит основную часть индексного ключа. В случае непрефиксных секционированных индексов ключа секционирования секционирование вып олняется по значениям, отличным от значений колонки индексирования.
Индексы могут быть секционированы и в случае, когда индексируемая таблица не секционируется. В этом случае по умолчанию предполагается, что индекс является глобальным секционированием индексов. В Oracle не предусмотрена поддержка глобальных непрефиксных секционированных индексов.
В локально секционированном индексе ключевые значения одной секции индекса соответствуют строкам таблицы из одной ее секции.
Пример. Создадим локальный секционированный индекс для таблицы Sales (рис. 11.2). Ключом секционирования этой таблицы является колонка s_date. Фрагмент кода создания индекса приведен ниже.
CREATE INDEX sales_ndx ON Sales (s_date) LOCAL (PARTITION st_i_q01 TABLESPACE ts_01, PARTITION st_i_q02 TABLESPACE ts_02, PARTITION st_i_q03 TABLESPACE ts_03, PARTITION st_i_q04 TABLESPACE ts_04 );
Локально секционированный индекс называется равносекционированным (equi-partitioned), если он имеет то же число секций и те же правила секционирования, что и его базовая таблица. Обратите внимание, что в примере при создании индекса не использовалось предложение PARTITION BY RANGE.
Oracle автоматически берет структуру секционирования для индекса из структуры секционирования базовой таблицы Sales. Также можно опустить и предложения типа PARTITION st_i_q02 TABLESPACE ts_02. Если опущено PARTITION, то Oracle автоматически создаст имена секций. Если пущено TABLESPACE, то Oracle автоматически разместит секции в тех же табличных пространствах, в которых находятся соответствующие секции базовой таблицы.
Глобально секционированный индекс имеет структуру секций, отличную от структуры секций базовой таблицы данного индекса. В качестве примера создадим глобально секционированный индекс для таблицы Sales из наших предыдущих примеров.
Пример. В качестве ключа секционирования для индекса используем колонку s_customer_id. Во фрагменте кода ниже для секций индекса используются другие индексные пространства ts_i_01, ts_i_02, ts_i_03. Число секций индекса не совпадает с числом секций базовой таблицы для этого индекса:
CREATE INDEX sales_ndx ON Sales (s_customer_id) GLOBAL PARTITION BY RANGE (s_customer_id) (PARTITION st_i_q1 VALUES LESS THAN (10000) TABLESPACE ts_i_01, PARTITION st_i_q2 VALUES LESS THAN (20000) TABLESPACE ts_i_02, PARTITION st_i_q3 VALUES LESS THAN (MAXVALUE) TABLESPACE ts_i_03, );
Локально секционированный индекс может быть создан по колонке, отличной от ключа секционирования базовой таблицы индекса. В примере ниже создается такой непрефиксный индекс для таблицы Sales.
Пример. В качестве колонки секционирования для индекса выбрана колонка s_customer_id, а для секций индекса выбраны другие табличные пространства ts_i_01, ts_i_02, ts_i_03, ts_i_04, чем для секций базовой таблицы индекса.
CREATE INDEX sales_ndx_1 ON Sales (s_customer_id) LOCAL (PARTITION st_i_q01 TABLESPACE ts_i_01, PARTITION st_i_q02 TABLESPACE ts_i_02, PARTITION st_i_q03 TABLESPACE ts_i_03, PARTITION st_i_q04 TABLESPACE ts_i_04 );
При принятии решения о секционировании индексов проектировщик базы данных должен иметь в виду следующее:
Локальное префиксное секционирование индекса является наиболее эффективным методом секционирования индекса.Поскольку строки одной секции базовой таблицы будут индексироваться в одной секции индекса, СУБД не придется сканировать все секции при выборке данных по запросу.Локальное непрефиксное секционирование индекса требует от СУБД выполнения большего объема работы, так как для поиска данных требуется сканировать все секции индекса. Этот тип следует принимать во внимание при параллельной обработке данных.Глобальное префиксное секционирование индекса является наиболее эффективным методом секционирования индекса при обработке данных, когда необходимо сканирование диапазона. Этот тип секционирования группирует строки в одной секции, и СУБД знает, в какой секции искать значения из заданного диапазона.
Секционирование по диапазону
Секционирование по диапазону (range partitioning) означает распределение строк таблицы на различные предопределенные табличные пространства в зависимости от значения ключа секционирования. Доступ к такой таблице, как и к любой другой, осуществляется по ее имени, причем доступ к секциям, расположенным в каждом табличном пространстве, можно получить отдельно. Например, таблицу, содержащую финансовые квартальные отчеты организации, можно секционировать по дате таким образом, что отчеты по каждому кварталу будут храниться в отдельном табличном пространстве. При такой организации секций данные только по одному кварталу будут выбираться из одного табличного пространства, что повысит эффективность работы базы данных в целом.
Секционирование по диапазону базируется на упорядочении строк таблицы в секциях (табличных пространствах) на основе значения колонок ключа секционирования. Концептуально таблица, секционированная по диапазону, устроена как на рис. 11.2 в примере ниже. Для создания секционированных таблиц используется команда SQL CREATE TABLE с предложением PARTITION. В СУБД Oracle ключ секционирования не может иметь тип LONG.
Рис. 11.2. Пример секционирования подиапазону
Пример. Рассмотрим систему обработки заказов. Предположим, что в ней есть таблица Sales, в которой сохраняются данных о количестве, времени и цене продаж для каждого клиента. Проектировщик базы данных может использовать секционирование по диапазону, а именно - по кварталу, для представления этой таблицы в базе данных. Предположим, что мы имеем четыре определенные ранее табличных пространства c именами ts_01, ts_02, ts_03, ts_04, распределенные по четырем дискам, как показано на рисунке ниже.
Фрагмент скрипта ниже определяет таблицу Sales с физическим размещением секций, как на рисунке выше:
CREATE TABLE Sales ( s_customer_id number(6), s_amt number(9,2), s_date date) PARTITION BY RANGE (s_date) (PARTITION st_q01 VALUES LESS THAN ('01-apr-2002') TABLESPACE ts_01, PARTITION st_q02 VALUES LESS THAN ('01-jul-2002') TABLESPACE ts_02, PARTITION st_q03 VALUES LESS THAN ('01-oct-2002') TABLESPACE ts_03, PARTITION st_q04 VALUES LESS THAN (MAXVALUE) TABLESPACE ts_04 );
Предложение PARTITION BY RANGE (s_date) указывает СУБД Oracle выполнить секционирование таблицы по ключу секционирования - s_date. Предложения вида (PARTITION st_q01 VALUES LESS THAN ('01-apr-2002') TABLESPACE ts_01 определяют имя секции st_q01 и ее размещение в соответствующем табличном пространстве ts_01.
Чтобы получить доступ к строкам таблицы, расположенным в определенной секции, узнать о продажах в третьем квартале, можно использовать команду SELECT, как показано ниже:
SELECT s_customer_id, s_amt FROM Sales PARTITION (st_q03);
Как мы можем увидеть, для этого нужно указать опцию PARTITION (имя секции) после имени таблицы в предложении FROM.
Администратор базы данных может легко удалять, добавлять, перемещать, расщеплять, усекать и изменять секции с помощью команды ALTER TABLE. Удалить отдельную секцию можно также, удалив соответствующее ей табличное пространство.
Секционирование представлений
В Oracle есть возможность секционировать представления. Основная идея секционирования представлений проста. Пусть физическая таблица разбита на несколько таблиц (необязательно с помощью методов секционирования таблиц) в соответствии с критерием разбиения, который делает обработку запроса более производительной. Критерий разбиения будем называть предикатом секционирования. Тогда мы можем создать и настроить представления таким образом, чтобы с их помощью обращение к данным этих таблиц было проще для пользователя. Секция представления определяется в соответствии с диапазоном значений ключа секционирования. Запросы, которые используют диапазон значений для выборки данных из секций представления, будут получать доступ только к тем секциям, которые соответствуют диапазонам значений ключа секционирования.
Секции представления могут быть определены предикатами секционирования, заданными либо при помощи ограничения CHECK, либо с использованием предложения WHERE. Покажем, как могут быть применены оба приема на примере несколько модифицированной таблицы Sales, которую мы рассматривали в предыдущем разделе. Допустим, что данные о продажах для календарного года размещаются в четырех отдельных таблицах, каждая из которых соответствует кварталу года - Q1_Sales, Q2_Sales, Q3_Sales и Q4_Sales.
Пример. Секционирование представлений с помощью ограничения CHECK. С помощью команды ALTER TABLE мы можем добавить ограничения на колонку s_date каждой таблицы, чтобы ее строки соответствовали одному из кварталов года. Созданное затем представление sales дает возможность обращаться к этим таблицам - как к одной, так и по отдельности:
ALTER TABLE Q1_Sales ADD CONSTRAINT C0 CHECK (s_date BETWEEN 'jan-1-2002' AND 'mar-31-2002'); ALTER TABLE Q2_Sales ADD CONSTRAINT C1 CHECK (s_date BETWEEN 'apr 1-2002' AND 'jun-30-2002'); ALTER TABLE Q3_Sales ADD CONSTRAINT C2 check (s_date BETWEEN 'jul-1-2002' AND 'sep-30-2002'); ALTER TABLE Q4_Sales ADD CONSTRAINT C3 check (s_date BETWEEN 'oct-1-2002' AND 'dec-31-2002');
CREATE VIEW sales_v AS SELECT * FROM Q1_Sales UNION ALL SELECT * FROM Q2_Sales UNION ALL SELECT * FROM Q3_Sales UNION ALL SELECT * FROM Q4_Sales;
Преимуществом такого секционирования представлений является то, что предикат ограничения CHECK не оценивается для каждой строки запроса. Такие предикаты исключают вставку в таблицы строк, не соответствующих критерию предиката. Строки, соответствующие предикату секционирования, извлекаются из базы данных быстрее.
Пример. Секционирование представлений с помощью предложения WHERE. Создадим представление для тех же таблиц, что и в примере выше:
CREATE VIEW sales_v AS SELECT * FROM Q1_Sales WHERE s_date BETWEEN 'jan-1-2002' AND 'mar-31-2002' UNION ALL SELECT * FROM Q2_Sales WHERE s_date BETWEEN 'apr-1-2002' AND 'jun-30-2002' UNION ALL SELECT * FROM Q3_Sales WHERE s_date BETWEEN 'jul-1-2002' AND 'sep-30-2002' UNION ALL SELECT * FROM Q4_Sales WHERE s_date BETWEEN 'oct-1-2002' AND 'dec-31-2002';
Второй метод имеет некоторые недостатки. Во-первых, критерий секционирования проверяется во время выполнения для всех строк во всех секциях, которые охватываются запросом. Во-вторых, пользователи могут ошибочно вставить строку не в ту секцию, т.е. вставить запись, относящуюся к первому кварталу, в третий квартал, что приведет к неправильной выборке данных для этих кварталов.
У этого приема есть и достоинство по сравнению с использованием ограничения CHECK. Вы можете разместить секцию, соответствующую предикату WHERE, на удаленной базе данных. Фрагмент определения преставления приведен ниже:
SELECT * FROM east_sales@icp.ac.ru WHERE LOC = 'EAST' UNION ALL SELECT * FROM west_sales@ioc.ac.ru WHERE LOC = 'WEST';
Проектировщик базы данных при принятии решения о создании секционированных представлений должен принимать во внимание следующие факторы:
Секционирование представлений позволяет операциям DML, таким, как загрузка данных, создание индексов и удаление данных, работать на уровне секции, а не целой базовой таблицы.Доступ к одной из секций не оказывает никакого действия на данные в других секцияхСУБД Oracle облагает необходимыми встроенными возможностями для распознавания секционированных представлений.Секционирование представлений очень полезно при работе с таблицами, содержащими большое количество исторических данных.
В этом разделе мы рассмотрели некоторые приемы увеличения производительности обработки транзакций, основанные на секционировании объектов реляционной базы данных - базовых таблиц, индексов базовых таблиц и представлений. Основная задача секционирования: с помощью встроенных команд СУБД разбить таблицы большого объема на ряд физических фрагментов в соответствии с некоторым критерием секционирования, чтобы сократить объем ввода/вывода при обработке фрагментов. Секционирование очень часто используется при работе с таблицами большого объема. Проектировщику базы данных, если он определил наличие в проектируемой базе данных сохраняемых объектов большого объема (более 1 Гб), обязательно следует рассмотреть возможность использования техники секционирования.
Составное секционирование
Составное секционирование (composite partitioning) является комбинацией секционирования по диапазону и хэш-секционирования. Это означает, что таблица сначала распределяется среди табличных пространств на основе диапазона значений ключа секционирования, далее каждая из полученных секций диапазонов делится на подчиненные секции или подсекции, и затем строки равномерно распределяются среди подчиненных секций по значению хэш-ключа.
Пример. Рассмотрим ту же, что и в предыдущем примере таблицу Sales и ту же схему (рис. 11.2) табличных пространств. В качестве ключа секционирования по диапазону используем дату продажи. В качестве ключа хэш-секционирования -идентификацию клиента. Однако теперь каждая секция по диапазону будет разделена на предопределенное число подсекций. Фрагмент кода SQL для создания таблицы Sales с составным секционированием можно написать так:
CREATE TABLE Sales ( s_customer_id number(6), s_amt number(9,2), s_date date) PARTITION BY RANGE (s_date) SUB PARTITION BY HASH (s_customer_id) SUB PARTITION 4 STORE IN (ts_01, ts_02, ts_03, ts_04) (PARTITION q01 VALUES LESS THAN ('01-apr-2002'), PARTITION q02 VALUES LESS THAN ('01-jul-2002'), PARTITION q03 VALUES LESS THAN ('01-oct-2002'), PARTITION q04 VALUES LESS THAN (MAXVALUE) );
Секции q01, q02, q03, q04 будут содержать строки с диапазоном дат, которые определены в предложениях типа PARTITION q02 VALUES LESS THAN ('01-jul-2002') и будут распределены в табличных пространствах ts_01, ts_02, ts_03, ts_04. Предложение SUB PARTITION 4 предписывает СУБД Oracle разбиение каждой секции на четыре логические единицы, а предложение SUB PARTITION BY HASH (s_customer_id) распределяет строки заданного диапазона среди этих четырех подчиненных секций.
в предыдущей лекции, одна из
Как уже отмечалось в предыдущей лекции, одна из важнейших задач физического проектирования реляционной базы данных состоит в том, чтобы дать гарантию того, что база данных обеспечивает требуемый уровень производительности. Там же мы рассмотрели основной подход к решению этой задачи - денормализацию отношений в рамках реляционной модели. В настоящей лекции мы продолжим обсуждать методы борьбы за производительность базы данных и сконцентрируем свое внимание на СУБД-ориентированных средствах для решения этой задачи: индексах, секциях, кластерах.
