Для того, чтобы при создании той или иной таблицы указать требуемый уровень блокировок, в операторе CREATE TABLE надо указать соответствующую опцию: CREATE TABLE … LOCK MODE {PAGE | ROW} Если уровень блокировки для таблицы не указан, то по умолчанию выбирается блокировка на уровне страницы. Примеры создания таблиц с указанием уровня блокировки: CREATE TABLE companies ( company_id SERIAL UNIQUE, name CHAR(40), address CHAR(40)) LOCK MODE ROW CREATE TABLE persons ( person_id SERIAL UNIQUE, company INTEGER, lname CHAR(40), fname CHAR(30), sname CHAR(20), position CHAR(20)) LOCK MODE PAGE 5.11.4. Эффективное построение запросов Производительность сервера базы данных при выполнении доступа к информации зависит не только от наличия или отсутствия индексов, буферизации журнала транзакций и т.д., но и от того, какие запросы исполняются. Не секрет, что одно и тоже действие (например, построение списка сотрудников какой-либо фирмы) можно запрограммировать поразному. Суммарная скорость исполнения данного действия зависит от того, какими операторами на SQL оно представлено. Рассмотрим пример. Допустим, у нас есть таблицы persons и companies (их структура привевдена выше, в пункте 5.11.3). Требуется построить поименный список сотрудников, работающих в фирме “АО Рога и Копыта”. Первый вариант реализации данного действия выглядит так: SELECT lname, fname FROM persons, companies WHERE persons.company = companies.company_id AND companies.name = “АО Рога и Копыта” При исполнении данного запроса при отсутствии индексов сервер базы данных должен будет перебрать все возможные комбинации записей из таблиц persons и companies, и для каждой комбинации проверить, выполняется ли условие из раздела WHERE. Если в таблице persons находится M записей, а в таблице companies - N записей, то всего будет проверено M*N комбинаций. Другой запрос, функцмионально реализующий тоже самое действие, а именно, формирование списка сотрудников АО Рога и Копыта выглядит следующим образом: SELECT lname, fname FROM persons WHERE persons.company IN (SELECT company_id FROM companies WHERE name = “АО Рога и Копыта”) При исполнении этого запроса, сервер вначале просмотрит таблицу companies и найдет одно требуемое значение, затем он просмотрит таблицу persons и сравнит поле company с найденным значением. В итоге, он просмотрит M+N записей. Если значения M и N достаточно велики (порядка сотен или более), то второй запрос будет исполняться много быстрее первого. Естественно, это очень грубая оценка без учета наличия индексов, возможностей сервера по оптимальному исполнению запросов и т.д., но выигрыш в полмиллиона раз раз для таблиц с миллионом записей каждая уже впечатляет. Приведенный выше пример позволяет сформулировать следующее общее правило: уменьшайте число таблиц, задействованных в одном запросе. На основании этого правила можно сделать вывод о том, что иногда выгоднее разбить сложный запрос на несколько простых (с использованием временной таблицы), нежели пытаться уместить какое-либо действие в один запрос. Допустим, вас отправляют в командировку в г. Тверь, и вы хотите посетить директоров всех имеющихся в этом городе фирм-партнеров. Если данный запрос представить на SQL, то первый, неоптимизированный вариант запроса мог бы выгшлядеть следующим образом: SELECT persons.lname, persons.fname, persons.sname, companies.address FROM persons, companies WHERE companies.address MATCHES "*Тверь*" AND companies.companies_id = persons.company AND persons.position = “директор” Привести данный запрос к виду SELECT . . . FROM persons WHERE . . . подобно тому, как мы это делали ранее, невозможно, поскольку поле address из таблицы companies нам нужно в конечном результате. Но число фирм, расположенных в Твери (как и в любом другом городе), по сравнению с общим числом фирм в нашей базе данных невелико. Поэтому следующий набор операторов будет, скорее всего, выполняться быстрее, нежели один первоначальный оператор: CREATE TEMP TABLE local_companies ( company_id INTEGER, name CHAR(40), address CHAR(40)) {наполнение временной таблицы списком фирм, распорложенных в Твери}INSERT INTO local_companies(company_id, name, address) SELECT company_id, name, address FROM companies WHERE address MATCHES "*Тверь*" {получение требуемых данных}SELECT persons.lname, persons.fname, persons.sname, local_companies.address, local_companies.name FROM persons, local_companies WHERE local_companies.companies_id = persons.company AND persons.position = “директор” DROP TABLE local_companies Существуют множество рекомендаций, по написанию эффективных запросов, но практически все они основаны или на разделении одного сложного запроса на несколько простых, или на более аккуратном и тщательном написании условия с тем, чтобы оптимизатор смог понять, какие же данные требуются. 5.11.5. Сортировка и поиск по коротким полям. Классификаторы Сортировка (т.е. использование раздела ORDER BY в операторе SELECT) требует очень больших времЕнных затрат. Особенно это касается больших таблиц. Сортировка, выполняемая по проиндексированным полям, происходит существенно быстрее, чем по непроиндексированным. Если же сортировка все же проводится по полям, для которых нет индекса, то лучше для сортировки использовать короткие поля (типа INTEGER, SMALLINT), нежели длинные (например, символьные). В условии WHERE операторов также предпочтительнее использовать короткие поля. Например, оператор SELECT address FROM companies WHERE name=”АО Рога и Копыта” будет в среднем выполняться дольше, нежели оператор SELECT address FROM companies WHERE company_id=105 Это правило можно распространить и на использование классификаторов. Классификатор - это специально введенная таблица, в которой хранится набор возможных значений какого-либо атрибута. Предположим, для каждой фирмы требуется хранить ее тип (госпредприятие, ООО, АОЗТ и т.д.). Набор типов предприятия ограничен. В принципе, в таблице companies нетрудно предусмотреть на этот случай специальное символьное поле: CREATE TABLE companies( . . . . . type CHAR(20) ) Сортировка и поиск по полю CHAR(20) nбудет происходить значительно медленне, чем по полю INTEGER. К тому же, если записей в таблице companies много, то использование поля INTEGER как ссылки на классификатор с типами предприятий вместо непосредственного задания этого значения в таблице даст экономию памяти. Поэтому более грамотным решением, на наш взгляд, является создание специального классификатора (таблицы types): CREATE TABLE types ( type_id SERIAL UNIQUE, name CHAR(40) ) и введение в таблицу companies ссылки (внешнего ключа) на этот классификатор: CREATE TABLE companies( . . . . . type INTEGER ) В результате процедуру поиска предприятий некоторого типа придется разбивать на две части - вначале должен быть выполнен поиск уникального ключа по его имени, а затем - поиск по таблице companies. Однако, в целом это может дать существенный выигрыш по производительности. Пример запроса, который ищет все ООО (Общества с Ограниченной Ответственностью): SELECT name, address FROM companies WHERE type = (SELECT type_id FROM types WHERE name = “ООО”) 5.12. Объектное расширение SQL в Informix DS/Universal Data Option 5.12.1. Зачем нужна поддержка объектов в серверах БД? Выше была рассмотрена классическая, традиционная реляционная модель данных и ее конкретная реализация - язык SQL. Однако не все задачи могут быть одинаково хорошо и изящно решены с помощью реляционных баз данных вообще и языка SQL в частности. Рассмотрим те ограничения и неудобства, которые заложены в реляционных СУБД и как использование объектно-ориентированной технологии может помочь в преодолении возникающих трудностей. Неравноправие атрибутов Понятие отношения предполагает, что у каждого реального объекта или у каждой реальной сущности, описываемой записью в отношении, есть некоторое количество атрибутов. С точки зрения реляционной модели все эти атрибуты равноправны, находятся на одном уровне абстрации. Это не означает, конечно, что между атрибутами совсем нет никакой разницы. Например, бывают ключевые и не ключевые атрибуты. Суть в том, что атрибуты не могут быть сгруппированы, они не могут быть собраны в иерархию. Все атрибуты в отношении находятся “на одном уровне”. На самом деле, в реальных задачах, некоторые атрибуты более важны, нежели другие. С другой стороны, некоторые атрибуты должны быть сгруппированы, структурированы. Проблема группирования атрибутов может быть решена на понятийном уровне до тех пор, пока этих атрибутов не так много. Когда число атрибутов в таблице переваливает за несколько десятков, в атрибутах уже легко запутаться. Рассмотрим пример. Предположим, нам надо реализовать в реляционном отношении (в таблице) список сотрудников предприятия для местного отдела кадров. В такой таблице должны быть следующие атрибуты - фамилия, имя, отчество, табельный номер, отдел, должность, дата приема на работу и т.д: CREATE TABLE persons ( tabel_num INTEGER, {табельный номер} last_name CHAR(40), {фамилия} first_name CHAR(40), {имя} second_name CHAR(40), {отчество} department INTEGER, {отдел} dolgnost CHAR(20), {должность} emp_from DATE, {работает с…} . . . . . . ) При такой структуре данной таблицы все атрибуты “свалены” в кучу. Более правильно было бы сгруппировать фамилию, имя и отчество в новый составной тип под названием “ФИО”, а отдел и должность - в тип “позиция”: CREATE ROW TYPE fio_t ( last_name CHAR(40), {фамилия} first_name CHAR(40), {имя} second_name CHAR(40) {отчество}) CREATE ROW TYPE position_t ( department INTEGER, {отдел} dolgnost CHAR(20) {должность}) После чего определить таблицу уже с учетом структурированных, упорядоченных атрибутов: CREATE TABLE persons ( tabel_num INTEGER, {табельный номер} fio fio_t, {ФИО} position position_t, {позиция} emp_from DATE, {работает с…} . . . . . . ) Такое группирование, структурирование атрибутов снижает вероятность ошибки, делает и схему базы данных, и программы, работающие с этой базой, более читаемыми и, следовательно, более надежными. Ограниченный набор базовых типов В реляционных СУБД набор типов данных, которые можно использовать для атрибутов, фиксирован. Этот набор включает в себя целые числа, числа с плавающей и фиксированной запятой, типы данных для указания времени и даты, а также символьные строки. Подобный набор характерен для финансовых приложений, приложений складского характера и т.д. Современная тенденция такова, что растет число приложений, где необходимо совместить сервер баз данных и мультимедийные типы данных (видео, звук, форматированные тексты). С другой стороны, появляется необходимость обрабатывать новые типы данных, которые не представлены в SQL, а определяются конкретной предметной областью (отпечатки пальцев, фотографии, схемы и т.д.). Нельзя сказать, что современные реляционные СУБД такой возможности не предоставляют совсем. Например, такие типы данных в Informix Dynamic Server, как BYTE и TEXT предназначены для хранения больших (до 2 ГБт) двоичных и текстовых объектов соответственно. Сервер обеспечивает только хранение таких объектов, он не может сам обрабатывать данные этих типов. Вся обработка должна проводиться на программеклиенте. То есть для того, чтобы, например, просмотреть все имеющиеся в базе данных отпечатки пальцев и отобрать те, которые похожи на заданный образец, программаклиент должна последовательно просмотреть все имеющиеся отпечатки. Проведение такого просмотра самим сервером, а, тем более, наличие индекса по отпечаткам, значительно бы ускорило обработку такого запроса. Могут существовать и специализированные СУБД (или специализированные расширения универсальных СУБД), предназначенные для той или иной конкретной предметной области. Однако, производители СУБД не раскрывают свои внутренние интерфейсы, поэтому для разработчика прикладной системы написать подобное расширение не представляется возможным, а полностью разрабатывать свою собственную специализированную СУБД экономически невыгодно. Следовательно, возможность расширения набора базовых типов, то есть возможность разрабатывать новые типы данных и внедрять их в СУБД увеличит эффективность использования СУБД для широкого класса задач. При введении нового базового типа крайне желательно иметь возможность определять для этого типа внутреннее представление (формат), описывать при необходимости новые методы хранения, доступа, индексирования и т.д. При определении нового базового типа для него имеет смысл доопределять те операции, которые уже существуют в языке доступа к данным (например, в SQL). Другими словами, в серверах БД необходимо обеспечить поддержку концепции абстрактных типов данных. Рассмотрим простейший пример. Предположим, нам надо хранить информацию об адресах и делать выборку по улице. В Российских городах для названия улиц может использоваться дополнительная нумерация (1-я Парковая, 9-я Парковая) или прилагательное “Большой” или “Малый” (Малая Бронная). Соответственно, существует несколько способов записи таких названий. Следующие три названия обозначают одну и туже улицу: М.БроннаяБронная, М.Малая Бронная Очевидно, что если мы будем использовать для хранения названия улиц обычные строковые типы, то придется следить за тем, чтобы в базе данных названия улиц хранились в едином, унифицированном формате. А при запросе на поиск по названию улицы потребуется преобразовывать введенное пользователем название к этому единому формату. Было бы удобнее, если бы можно было определить новый тип данных “Название улицы”, который бы делал это автоматически и, следовательно, минимизировал возможные ошибки. Для данного типа нужно было бы переопределить операцию сравнения, чтобы при сравнении двух значений учитывалось разное написание улиц. Кроме того, для данного типа потребуется переопределить операции сравнения, чтобы сортировка значений данного типа проводилась с учетом разных написаний: SELECT streets FROM table1 ORDER by streets . . . .БауманскаяБ.БроннаяМ.БроннаяБуденного. . . . Можно привести и другие примеры, когда стандартного набора базовых типов недостаточно. Введение в ту или иную СУБД новых предопределенных типов не может решить данную проблему - всегда найдется задачу, которую не предусмотрели. Сложные типы данных В информационной системе для отдела кадров предприятия надо хранить сведения о детях сотрудников. Эта информация учитывается и в бухгалтерии при расчете подоходного налога, начисления разного рода выплат и т.д. Число детей для каждого сотрудника заранее неизвестно, поэтому при реализации схемы базы данных, позволяющей хранить такую информации, обычно заводится подчиненная таблица “Дети”, каждая запись которой содержит ссылку на первичный ключ таблицы с сотрудниками: Таблица “Дети” ID Фамилия Имя Отчество Родитель 7 Иванов Игорь Иванович 106 8 Иванова Анна Ивановна 106 14 Петров Сергей Петрович 107 . . . . . . . . . . . . . . . .. Таблица “Сотрудники”: ID Фамилия Имя Отчество . . . 106 Иванов Иван Иванович . . . . 107 Петров Петр Петрович . . . . 108 Сидоров Сидор Сидорович . . . . . . . . . . . . . . . . . . . .. Наличие таких двух таблиц, безусловно, решает задачу хранения информации о детях сотрудников. Однако, при таком подходе возникают некоторые сложности. Во-первых, требуется предпринять определенные меры по поддержанию целостности базы данных, А именно, при удалении информации о родителе необходимо удалить и информацию о детях, нужно следить за тем, чтобы не было “висящих” ссылок и т.д. Во-вторых, появление лишней таблицы, вообще говоря не представляющей интереса для данной предметной области, а выполняющей только технические, второстепенные функции, усложняет схему базу данных. Любое усложнение, нарушение логики задачи, ведет к повышению вероятности ошибки и к удорожанию разработки. Если бы в таблице можно было описать поле типа множество, а каждый элемент множества представлял бы запись об одном ребенке, то все перечисленные выше проблемы решались бы автоматически: CREATE TABLE persons ( tabel_num INTEGER, {табельный номер} last_name CHAR(40), {фамилия} first_name CHAR(40), {имя} second_name CHAR(40), {отчество} children SET OF ( {дети - множество записей вида} last_name CHAR(40), {фамилия ребенка} first_name CHAR(40), {имя ребенка} second_name CHAR(40), {отчество} was_born DATE {дата рождения} ) . . . . .) Мы рассмотрели один конкретный пример, и убедились, что структура данных “множество” в некоторых задачах может быть очень полезна. Можно привести примеры, когда было бы полезно, если бы в качестве атрибутов в таблице можно было использовать сложные, составные типы данных со структурами “массив”, “список” и т.д. Реляционные СУБД в своей классической теории не позволяют этого сделать. Специальные методы хранения и доступа Современные СУБД практически полностью скрывают от программиста методы физической организации хранения данных (физическую модель). Только администратор сервера базы данных, в силу специфики своих задач, должен и может иметь некоторое представление об используемой физической модели данных. Существующие реляционные СУБД весьма эффективно реализуют способы представлениея, хранения, доступа и поиска традиционных реляционных данных - чисел, текстовых строк, дат и т.д. Для ускорения поиска обычно используются методы индексирования на основе B-деревьев или B+-деревьев. Когда же мы пытаемся выполнить запрос на поиск текстовой строки, содержащей внутри себя некоторую заданную подстроку, например: SELECT name, address FROM companies WHERE name MATCHES “*Рога*” или пытаемся выбрать информацию по продажам в феврале в течении нескольких лет: SELECT price FROM contracts WHERE MONTH(was_signed)=2 то использовать стандартные индексы не получается. Оба указанных выше запроса будут обработаны последовательным сканированием. Существуют специальные методы индексирования для поиска и хранения некоторых типов данных - например, существуют bitmap-индексы, существуют специальные индексы для изображений. Реализовать все возможные типы индексов и все возможные методы хранения на одном сервере БД просто невозможно. Следовательно, надо иметь возможность не просто определять новый тип данных, но и определять методы хранения, доступа и индексирования для данного типа, если стандартные по тем или иным причинам не подходят. Иерархия данных В очень многих случаях, данные в той или иной предметной области, образуют иерархическую структуру. Например, на одном предприятии работают продавцы, инженеры и администрация. С точки зрения отдела кадров, структура информации о каждом из сотрудников идентична и включает в себя фамилию, имя, отчество, должность, дату рождения, дату приема на работу и т.д. Эта структура (набор атрибутов) не зависит от того, на какой должности нааходится тот или иной сотрудник. С другой стороны, способ начисления зарплаты для каждой категории разный. Инженерные работники получают оклад плюс премию как процент от выполнения плана, административные работники получают строгий оклад, а продавцы имеют и оклад, и процент от заключенных сделок. Следовательно, для бухгалтерии при расчете зарплаты требуется в зависимости от позиции человека производить разный расчет и использовать разные атрибуты. Если мы попытаемся объединить требования отдела кадров и бухгалтерии в единой базе данных, то в стандартных реляционных СУБД мы будем стоять перед выбором: или иметь единую таблицу со всеми возможными атрибутами, или иметь три разных таблицы с разным набором атрибутов. Первый подход, когда информация о всех сотрудниках хранится в едином файле, приведет к излишнему расходу памяти (серым цветом показаны неиспользуемые поля в таблице): Фамилия Имя Отчество Должность Оклад Премия(%) Комиссия(%) Суммасделок Иванов Иван Иванович продавец 1000 0.5 500000 Петров Петр Петрович продавец 1000 0.6 400000 Сидоров Сидор Сидорович инженер 1500 20

Матвеев
Матвей
Матвеевич
инженер
1600
20

Степанов
Степан
Степанович
админист.
1700

Кузьмин
Кузьма
Кузьмич
админист.
1800