Каждый столбец таблицы в базе данных обладает рядом свойств, которые диктуют, что в нем может храниться и что не может. Этими свойствами являются тип, размер (или ширина) и ограничения, которые могут еще более ограничивать допустимый набор значений столбца. Например, столбец можно описать следующим образом:
amount NUMBER(8,2) NOT NULL CONSTRAINT cc_limit_amnt CHECK (amount > 0)
В этом столбце можно размещать только числовые данные; он должен быть заполнен для каждой строки таблицы; его значение должно быть положительным; точность этого значения — два значащих десятичных разряда. Максимальное значение, которое может храниться в этом столбце, — 999999.99. В этом простом определении столбца мы фактически определили ряд неявных правил, которые Oracle принудительно включает при вводе данных в базу данных.
В концептуальной информационной модели, создаваемой на этапе анализа, среда реализации не учитывается. Аналитик просто определяет атрибуты как строку, число или дату, в идеале он также назначает атрибуту домен. Домен — это просто тип атрибута, например деньги или рабочий день. Аналитик может включить ряд проверок допустимости или правил обработки, например требование, что значение должно быть положительным, ненулевым и иметь максимум два десятичных разряда (это полезно для сумм долларовых тратт, выставляемых банком на другой банк). Использование доменов упрощает задачу обеспечения непротиворечивости. К сожалению, Oracle не поддерживает (пока) абстрактные типы данных (АТД) (есть определяемые пользователем типы столбцов). Следовательно, при проектировании мы должны переводить и описания атрибутов, и описания доменов в определения столбцов. Например, можно определить домен MONEY как NUMBER(8,2) и домен CHASSIS_NO как NUMBER(ll). Ожидается, что в Oracle8 АТД будут поддерживаться; их поддерживают многие CASE-средства.
В этой главе мы попытаемся научить вас определять наиболее приемлемый для столбца тип. Например, какой тип данных лучше использовать для хранения числовых данных, предназначенных только для справки (а не для расчетов): NUMBER, CHAR или VARCHAR2? Выбор конкретного типа данных иногда оказывает едва заметное влияние, причем нежелательных побочных эффектов может и не быть.
Кроме того, в этой главе мы фокусируем внимание на использовании неопределенных значений (NULL). Неопределенные значения кажутся совершенно безопасными, пока вы не начнете изучать их глубже и задавать философские вопросы вроде: "Что в данном случае означает неопределенное значение?". Неопределенные значения очень распространены в большинстве баз данных, с которыми мы встречались, и мы задаемся вопросом, а нужно ли это?
Типы данных
В Oracle7 немного типов данных. Если исключить те, которые используются только для Trusted Oracle, то останется всего восемь:
VARCHAR2 (известный также как VARCHAR)
NUMBER
LONG
DATE
RAW
LONG RAW
ROWID
CHAR
Наиболее распространены три из них — VARCHAR2, NUMBER и DATE. Наличие такого небольшого набора типов данных может показаться недостатком, однако это не так. Типы, популярные в других продуктах и языках баз данных, в Oracle можно создать. Например, тип MONEY — это тип NUMBER с двумя десятичными разрядами, а тип POSITIVE INTEGER — тип NUMBER без десятичных разрядов и с ограничением на ввод отрицательных значений. По крайней мере, при таком положении дел вам не приходится беспокоиться об ограничениях на внутреннюю память, решая, как хранить вещественное число — с использованием типа FLOAT или типа DOUBLE.
Самое главное при выборе типов данных — обеспечение непротиворечивости. Если вы определите номер шасси автомобиля в одной таблице как NUMBER(11), а в другой как VARCHAR(15), то, когда дело дойдет до соединения этих таблиц, неприятности вам обеспечены. Напишите SQL-предложение для сравнения 918273645 и "918-27-36/4/5". Говорите, запросто? Тогда напишите его так, чтобы оно эффективно работало в предложении, выполняющем соединение!
Числовые данные
Существует два типа числовых данных:
1. Целые и вещественные значения (например, сальдо банковского счета или ставка процента). Они являются объектом математической обработки.
2. Строковые данные, в которых единственно допустимыми символами являются цифры (например, номера банковских счетов).
Целые и вещественные значения
Целые и вещественные значения в процессе проектирования обязательно необходимо вычислить, для того чтобы проверить, достаточно ли "ширины" столбца для хранения самого большого (и самого маленького) допустимого для домена числа. Необходимо также задать максимум и минимум, установив ограничение CHECK на столбце. Подбор точности для числового столбца является важным лишь в том случае, если значение представляет собой вещественное число.
Многие проектировщики попросту игнорируют эти требования и не указывают ни систему счисления, ни точность:
, length NUMBER CHECK (length BETWEEN 0 AND 100)
, height NUMBER CHECK (height BETWEEN 0 AND 50)
, width NUMBER CHECK (width BETWEEN 0 AND 100)
Такое определение работает, но может потребовать неожиданно большого объема памяти. Oracle хранит числа в двоично-десятичном формате с плавающей точкой, а не в двоичном формате с плавающей точкой. Кроме того, они хранятся как числа переменной длины. Любое целое число (например, 123226334) можно точно преобразовать из двоичного в десятичное и наоборот, однако с вещественными числами это можно сделать очень редко. Поэтому если в С-программе есть представление числа 10,2 в двоичном формате с плавающей точкой, то оно будет вставлено в Oracle как 38-значное значение, которое будет очень-очень близко к 10,2, но не абсолютно равно этому числу. Этот эффект трудно продемонстрировать без 3GL- программы, потому что все инструменты Oracle широко используют десятичный формат Oracle с плавающей точкой. Эту проблему демонстрирует следующий диалог на SQL*Plus:
SQL> set numwidth 40
SQL> select 1/8 fraction from dual;
FRACTION
-----------------------
.125
SQL> select 1/7 fraction from dual;
FRACTION
-----------------------
.1428571442857142857142857142857143
К счастью, существует давно предложенное, но редко используемое решение. Оно позволяет ограничивать точность числа в Oracle так, чтобы в формате с плавающей точкой оно было представлено определенным числом битов (обычно 32 или 64). Вот этот синтаксис:
, length FLOAT(64) CHECK (length BETWEEN 0 AND 100)
, height FLOAT(32) CHECK (height BETWEEN 0 AND 50)
, width FLOAT(64) CHECK (width BETWEEN 0 AND 100)
Мы рекомендуем всегда ограничивать разрядность целых чисел, используя NUMBER(nn), а для чисел с плавающей точкой задавать требуемую точность при помощи FLOAT(nn). У вас может возникнуть вопрос, а стоит ли экономить несколько байтов, когда диск так дешев. Мы отвечаем на него так:
• Это очень легко реализовать.
• Чем "короче" данные, тем меньше объем операций ввода-вывода при их сканировании. Если таблица состоит в основном из данных с плавающей точкой, то с помощью FLOAT во многих случаях можно сэкономить до 75% дискового пространства и, следовательно, на 75% уменьшить число обращений к блокам при полном сканировании таблицы.
• Вам совсем не нужны вещественные числа в столбцах, которые должны содержать целые — логика может "предположить", что это не вещественные, а целые значения, и давать сбои при появлении дробей.
Примечание
Не рекомендуем использовать типы данных INTEGER, SMALLINT и REAL. Несмотря на то, что Oracle поддерживает эти ключевые слова в DDL-предложениях, в словаре данных они не поддерживаются. Эти типы интерпретируются как NUMBER(38), NUMBER(38) и FLOAT(63) соответственно, и вы, вероятно, понимаете, что 38-значное число никак нельзя назвать как SMALLINT!
Строки цифр
В предыдущем разделе мы говорили о числовых значениях, которые действительно являются таковыми. А как быть со вторым типом числовых данных — например, с номером карточки социального обеспечения, над которым ни одна нормальная система не будет выполнять математические операции? Как быть с суррогатными ключами, которые генерируются из последовательности? Что лучше использовать — столбцы типа NUMBER или VARCHAR2? Здесь следует учесть несколько факторов.
Если число содержит ведущие нули, то при сохранении его в столбце типа NUMBER, они не записываются. Следовательно, если домен номеров счетов включает, например, число 0321234, то база данных примет его, но не сможет отличить от 321234, даже несмотря на то, что найдет при получении запроса на число 0321234. Таким образом, если 321234 и 0321234 допустимые, но разные номера счетов, возникают серьезные проблемы.
Если вы используете фразу ORDER BY с типом VARCHAR, то Oracle отсортирует значения с применением символьной сортировки и значения будут возвращены в следующем порядке: 1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20... . Указав столбец типа VARCHAR как аргумент функции TO_NUMBER, мы решим эту проблему, но не сможем использовать для сортировки индекс, Конечно, если в операторе INSERT мы набьем эти значения слева нулями, то последовательность сортировки будет правильной и индекс можно будет использовать. Однако этот подход сопряжен с интенсивным потреблением дискового пространства, поэтому приходится решать, приемлем ли он для данных. Будут ли данные выглядеть правильно при таком подходе и не измениться ли их смысл?
Если в ограничении CHECK содержится операция сравнения, то ее безопаснее и эффективнее проводить над "настоящими" числами. Например, установка максимума или минимума для столбца ограничением
где используется последовательность сортировки по кодам ASCII (и значение 9 не годится). Если вы все же хотите указать VARCHAR в данном случае, то лучше воспользоваться такой формой:
Мы предпочитаем использовать для строк цифр тип NUMBER. Главным исключением является случай, когда важное значение имеют ведущие нули.
Дата и время
Хранить даты очень просто, не правда ли? Мы используем столбец типа DATE, потому что других возможностей в Oracle не предусмотрено. Однако это не совсем так. В действительности здесь есть несколько вариантов, и от выбора варианта может существенно зависеть надежность и производительность приложения.
Данные в столбцы типа DATE в Oracle заносятся с точностью до секунды. Если требуется хранить значения с более высокой точностью, можно воспользоваться следующими приемами:
1. Хранить их в фиксированном формате в символьном поле (например, "1998 02 19 21 40 57.223456"). В этом случае время регистрируется с точностью до миллисекунд и правильно будет выполнено сопоставление дат, но расчет разницы дат превратиться в довольно утомительную процедуру.
2. Проблему расчета разницы дат можно решить, сохраняя дату и время как число миллисекунд, микросекунд или наносекунд, истекших с фиксированного момента времени. Недостаток этого подхода в том, что потребуется функция, конвертирующая эти значения в формат, приемлемый для отображения и выдачи сообщений, однако с 38-значными числами у вас вряд ли будут проблемы с точностью.
3. Возможен хороший компромисс: хранить дату, усеченную до ближайшей секунды, в столбце типа DATE, а остаток в наносекундах (или выбранных вами единицах) в отдельном столбце типа NUMBER. При этом "дата" обязательно должна быть усечена, а не округлена!
Отметим, что в Oracle7 версии 7.1 и выше, где есть возможность вызывать пользовательские PL/SQL-функции из SQL, несколько проще управлять нерегламентированными отчетами, основанными на таблицах, в которых для хранения дат используются совершенно искусственные методы. Если таблица содержит показания датчика, регистрируемые с точностью до миллисекунд, можно создать представления (необновляемые), в которых эти данные будут содержаться в нужном нам виде. Вот пример определения такого представления:
CREATE VIEW sensor_readings_v1
( sensor_id
, sensor_name
, reading_taken
, reading_value
) AS
SELECT r.sensor_id
, s.sensor_name
, readings.point_in_time_text(r.read_date, r.read_msec)
, reading_value
FROM sensor_readings r
, sensors s
WHERE s.sensor_id = r.sensor_id;
В отличие от некоторых других РСУБД, в Oracle есть только один тип данных для регистрации информации о датах и времени, и многие посчитали бы, что Oracle сейчас в этом отношении немножко не дотягивает до стандарта SQL ANSI-92. В Oracle значение типа DATE всегда содержит временной компонент — независимо от того, хотите ли вы этого и считаете ли вы его значащим. Если время не указано или усечено с помощью функции TRUNC, то оно регистрируется как полночь.
Рекомендуем использовать правила именования, позволяющие отличить столбцы, в которых хранятся только даты, от столбцов, содержащих и дату, и время. Например, APPOINTMENT_DT — столбец, включающий дату и время, a APPOINTMENT_D — столбец, содержащий только дату.
Кроме того, желательно активно противодействовать тому, чтобы в столбцах, предназначенных только для дат, хранился временной компонент. В следующем примере демонстрируются и метод профилактики (отклонение не соответствующих значений при помощи ограничения), и метод лечения (усечение времени при помощи триггера). Выбор за вами. Советуем воспользоваться хотя бы одним из этих приемов, так как при наличии в одном столбце таблицы дат со временем и дат без времени вы можете получить в результате сравнения неожиданный и нежелательные результаты. Если же вас волнуют последствия отключения триггеров (например, во время загрузки большого массива данных), то используйте и профилактику, и лечение.
CREATE TRIGGER event_date BEFOR INSERT OR UPDATE ON events FOR EACH ROW
BEGIN :new.event_date := TRUNC(:new.event_date);
END; - cure
Одна из распространенных причин возникновения проблем с датами — неверное определение разработчиком маски формата в программе. Типичными примерами таких ошибок являются использование ММ вместо MI для обозначения минут (MM — это месяц в числовом формате) и использование НН вместо НН24 для обозначения часов (при 24-часовом формате измерения времени). К несчастью, у разработчика есть только одно средство для предотвращения таких ошибок — внимательно просматривать код! Впрочем, если у вас есть программы проверки кода, они могут найти подозрительные маски формата дат. Так, например, маска DD-MON-YYYY НН:ММ подозрительна вдвойне, потому что:
• дважды содержит месяц и
• часы заданы в 12-часовом формате без указания a.m./p.m.
В связи с неотвратимым приближением 2000-го года может возникнуть масса проблем, связанных с датами. Уже появляются рассказы о том, как из-за серьезности ожидаемых последствий переписываются целые системы. Для минимизации этих проблем в последующие годы будут использоваться see возможные защитные методы кодирования и проверки. (Подробнее этот вопрос рассматривается в приложении Б.)
Обработка дат более подробно освещается в главе 7.
Строковые данные
Большинство символьных, или строковых, данных хранится в Oracle в столбцах типа CHAR или VARCHAR2. Между этими типами есть существенное различие: данные типа CHAR имеют фиксированную длину и могут содержать максимум 255 символов, тогда как данные типа VARCHAR2 имеют действительно переменную длину и могут содержать максимум 2000 символов. Если текстовые данные не имеют действительно фиксированного размера (например, не являются односимвольным значением флага), рекомендуем использовать VARCHAR2, так как это гораздо безопаснее.
Однако что делать, если столбец может содержать свыше 2000 символов? В этом случае можно воспользоваться одним из приведенных ниже методов:
• присвоить столбцу тип LONG (ниже мы укажем, с какими оговорками следует использовать этот тип);
• разбить данные на несколько столбцов (это делать не рекомендуется!);
• поместить их в справочную таблицу (при этом их размер практически неограничен, так как они могут занимать много строк).
В большинстве ситуаций наиболее предпочтителен последний вариант. Для Oracle7 он является основным, если мы хотим ссылаться на эти данные в предикатных предложениях, т.е. в предложениях WHERE и HAVING.
Семантика сравнения строк
Семантика сравнения SQL имеет несколько ловушек. Например, проблемы могут возникнуть при сравнении усеченных значений дат со значениями дат, которые содержат и время, а также при сравнении чисел с разной степенью точности. Кроме того, проблемы могут появиться и при сравнении символьных строк разной длины. Необходимо помнить также о том, что в SQL все строковые литералы считаются (совершенно обоснованно) имеющими фиксированную длину (т.е. имеют тип данных CHAR).
Со строками одинаковой длины проблем нет. Они просто сопоставляются символ за символом. Если же две строки имеют разную длину и какая-то из них является строкой фиксированной длины, то более короткая строка набивается пробелами до длины второй строки. Так, если у нас два столбца типа VARCHAR2, А и В, которые содержат значения "ab" и "ab,", то SQL сообщит, что А не равен В. Если же сравнить ab и ab буквально, как литералы, то SQL скажет, что они равны.
К сожалению, ваше мнение по этому поводу не имеет никакого значения, потому что так построен алгоритм сравнения. Единственная разумная мера — сделать так, чтобы приложение не вставляло в столбцы типа VARCHAR незначащие конечные пробелы. (Средства Oracle, кроме SQL*Plus, обычно автоматически удаляют их независимо от того, значащие они или нет.)
Текст в свободной форме
А как насчет доменов Comments (Комментарии) и Description (Описание)? Их наличие говорит о том, что определение таблицы будет включать описательный столбец. Какой же размер для него установить? При принятии такого решения необходимо учитывать следующие факторы:
• какова процентная доля записей, которые будут снабжены комментарием или описанием;
• будет ли достаточно 2000 символов (максимальное количество символов для типа VARCHAR2);
• всегда ли нужно будет выбирать текст или же (например) он будет проигнорирован при пакетной обработке;
• какие, инструменты пользователь будет иметь в своем распоряжении для редактирования этого текста;
• какие виды запросов будут производиться к этому тексту.
В Oracle версии 5 для большинства проектов просто выбиралось 240-символьное поле. В версии б, где максимальный размер символьной строки был увеличен, эта величина возросла до 255. Для Oracle7 самое простое решение - использовать VARCHAR2(2000). В этом случае можно будет хранить довольно объемные комментарии. * В программе обработки экранной формы можно реализовать механизм, позволяющий пользователю работать с текстом в этом поле как с обычным документом, т.е. добавлять, соединять строки, перемещать их и т.п.
Однако если потребуется определить, сколько строк в поле, какова длина самой длинной строки или сколько имеется пустых строк, то вам вновь нужно будет начать писать функции. Если комментарии хранятся как строки в справочной таблице, то эти значения могут быть получены при помощи простых SQL-запросов.
Если объем комментария к записи увеличиться, то возникнет реальный риск того, что мы столкнемся с проблемой "миграции строк": строка станет слишком большой для блока базы данных, в котором она ранее находилась. Это приведет к снижению производительности при выполнении запросов к строкам.
Если существует вероятность того, что объем комментариев и замечаний со временем увеличиться, можно использовать следующий подход — создать справочную таблицу, в каждой строке которой содержится одна строка комментария или описания. Эта таблица помещается в однотабличный кластер с кластерным ключом, совпадающим с первичным ключом главной таблицы, как показано в следующем примере:
СREATE CLUSTER case_notes_c ( case# VARCHAR2(12) );
СREATE INDEX ON CLUSTER case_notes;
CREATE TABLE case_notes
( case# VARCHAR2(12)
, line# NUMBER(3) NOT NULL
, made_at DATE NOT NULL
, text VARCHAR2(80)
) CLUSTER case_notes_c ( case# );
Этот метод обладает рядом полезных особенностей:
• в таблице CUSTS не потребляется пространство для комментариев о клиентах, не имеющих значения для пакетной обработки, в результате чего сокращается объем операций ввода-вывода;
• вследствие кластеризации все комментарии, сделанные данным клиентом или для данного клиента, будут располагаться в смежных областям на диске, даже если они создаются в разное время;
• при добавлении комментариев можно регистрировать дату и время создания, не встраивая эти сведения в текст.
При этом, однако, возникает и ряд проблем:
• для загрузки кластеров требуется время, поэтому всякая реорганизации или операция EXPort/IMPort будет длиться значительно дольше (фактечески в несколько раз дольше);
• чтобы организовать редактирование комментариев, потребуются утилиты или функции, которые будут запрашивать их, выполнять конкатенацию строк и передачу их в редактор, а затем — удаление всего набора и вставку новых строк.
Неструктурированные данные и
BLOB
Некоторые данные не умещаются в таблицах, содержащих относительно "узкие" и хорошо отформатированные столбцы. Например, может понадобиться база данных для хранения текста, звука, графики, мультимедиа и других типов данных, известных как BLOB (Binary Large Object — большие двоичные объекты). Хранение этих элементов в базе данных выгодно с точки зрения инкапсуляции, но невыгодно вследствие ограничений, которые Оrасle налагает на столбцы типа LONG или LONG RAW (а именно в столбцах этого типа мы обычно храним такие данные). Вот эти ограничения:
• Их использование не позволяет перемещать и копировать строки помощью SQL-предложения
INSERT INTO a SELECT * FROM b;
которое является самым быстрым способом перемещения данных в баз данных Oracle.
• Отсутствует возможность ссылки на столбцы LONG в условии предложения.
• Когда делается ссылка на часть строки, то всю строку приходится собирать в буферном пуле Oracle. Если значение типа LONG требуется не всегда, то лучше разместить его в отдельной таблице — либо с использованием связи "один к одному", либо путем разделения его на произвольное число элементов и хранения их в справочной таблице.
• Если по какой-либо из вышеизложенных причин мы хотим избежать использования столбца LONG, то следует рассмотреть возможность хранения BLOB вне базы данных (вероятно, в файле на хост-компьютере). Тогда строка будет просто содержать полное имя внешнего файла. Приемлемость этого варианта зависит от конкретного приложения. Если вы все же решили его использовать, имейте в виду следующее:
- Содержимое такого файла можно изменить, "не прикасаясь" к базе данных. Это может иметь неприятные последствия с точки зрения аудита и безопасности или же быть крайне желательным.
- Нет механизма, который обеспечивал бы обновление имени файла в базе данных при его перемещении или переименовании. Аналогичным образом, если файл удаляется внешней командой, то в таблице не будет сведений о потере данных.
- Внесенные в файл изменения не являются объектом операции фиксации базы данных, поэтому синхронность содержимого файла и строки в таблице не гарантируется.
- Вам придется ввести правила именования файлов, содержащих BLOB, или средство размещения их в разных каталогах. Это особенно важно, если ожидается, что в таблице будет много строк и, следовательно, много файлов.
Перед тем как отвергать этот подход из-за связанных с ним сложностей, следует рассмотреть проблемы с производительностью, возникающие в текущих версиях Oracle при записи, например, 10 Мбайт видео. Помимо очень интенсивной нагрузки на логику распределения пространства, вы также сгенерируете свыше 10 Мбайт журнала. Если вы удалите такой BLOB, то обнаружите, что он записан в сегменте отката — на случай, если вы решите отменить удаление или другому пользователю понадобится согласованный по чтению последовательный просмотр с точки, предшествующей выдаче команды удаления.
Нас заставляют надеяться, что в следующих версиях Oracle все эти проблемы будут решены, но на данный момент мы должны проявлять осмотрительность, когда речь идет об использовании РСУБД в качестве хранилища больших двоичных объектов.
Другие типы данных
В данном разделе мы рассмотрим еще пять типов данных из имеющихся в Oracle и более подробно опишем тип CHAR. Два типа — MLSLABEL и RAW MLSLABEL — используются только в Trusted Oracle, и их рассмотрение выходит за рамки нашей книги.
CHAR
Как мы уже говорили, данные типа CHAR имеют фиксированную длину. Любой столбец типа CHAR(255), значения которого определены (NOT NULL), занимает 255 байтов дискового пространства в каждой строке содержащей его таблицы (это плохая новость), а также 255 байтов в листьевом наборе любого индекса, в который он включен (это еще худшая новость).
Использование типа CHAR, а не VARCHAR2, очень редко дает преимущество, за исключением случая, когда строка имеет фиксированную длину и вы хотите фиксировать ее как таковую. Oracle в настоящий момент не поддерживает строки нулевой длины, и пустая строка "" рассматривается (ошибочно) как неопределенное (NULL) значение. По этой причине VARCHAR2(1) не является бессмыслицей из-за того, что длина здесь может быть равна только единице. Она не может быть равна нулю из-за реализационного ограничения и не может быть больше единицы по определению. Поэтому мы рекомендуем хранить односимвольные строки (например, флаги "истина/ложь") как CHAR(l).
RAW
Основное различие между типами RAW и VARCHAR2 состоит в том, что данные типа RAW никогда не переводятся из одного набора символа в другой. Кроме того, инструментальные средства Oracle в целом отказываются обрабатывать данные типа RAW. Поэтому, используя этот тип, вы, по сути, принимаете на себя всю ответственность за операции с ними.
Количество допустимых приложений для этого типа данных очень мало, хотя, к примеру, LONG RAW можно использовать при обработке BLOB, как мы видели выше.
ROWID
Этот тип данных позволяет хранить идентификатор строки (rowid) в его внутреннем шестибайтном виде, который включает номер блока ядра, номер строки и номер файла. Хотя тип ROWID и применяется Oracle в некоторых операциях со словарем данных (например, для хранения ссылок на строки, нарушающие ограничения), он необходим только для синтеза внешнего ключа к таблице, не имеющей возможного первичного ключа.
Поскольку в Oracle (в отличие от DB/2) не требуется, чтобы таблица имела первичный ключ, то коллектив разработчиков ядра Oracle вынужден был в описанном выше случае использовать внутренний указатель (rowid), в котором таблица базы данных хранит строку для каждой строки другой таблицы, которая нарушает ограничение.
Мы не рекомендуем применять тип данных ROWID внутри таблицы. Существуют редкие случаи, когда его использование оправдано при определении представления — чтобы представление могло передать идентификатор строки в запрос для последующего использования его в предложении UPDATE или DELETE. Однако следует помнить, что такую операцию перед выдачей запроса необходимо защитить эксклюзивной блокировкой на уровне таблицы.
VARCHAR
Определение этого типа данных в настоящее время совпадает с определением VARCHAR2, однако между этими типами есть важное различие. Корпорация Oracle заявила, что если будущий (а не предлагаемый) стандарт SQL будет содержать определение назначения VARCHAR, то корпорация будет придерживаться этого стандарта. Тем не менее, определение VARCHAR2 они не изменят (если только он тоже не будет назван в стандарте, что весьма маловероятно).
Поэтому мы рекомендуем использовать в текущих версиях Oracle не VARCHAR, a VARCHAR2, так как его определение вероятнее будет стабильным.
Выше, рассматривая тип CHAR, мы говорили, что Oracle поступает неверно, трактуя строки нулевой длины как неопределенные значения. Конечно, на эту тему можно поспорить, но мы рассматриваем строку нулевой длины как аналог числа нуль. Oracle выразила намерение реализовать в Огас1е8 хранение строк нулевой длины и обеспечить их отличие от неопределенных значений. Мы еще не знаем, потребует ли это использования нового типа данных или же эта поддержка ознаменует собой изменение в VARCHAR. Возможно, это нельзя будет применить к VARCHAR2, так как это означало бы серьезное изменение принципов функционирования и испортило бы намерение держать VARCHAR2 на первых ролях.
Неопределенные значения
Неопределенные (NULL) значения являются одним из предметов, вокруг которого уже много лет бушуют дебаты. В частности, больше года в журнале Database Programming and Design ведутся жаркие дискуссии на эту тему с участием таких ключевых в области баз данных фигур, как Крис Дейт. Даже у авторов этой книги есть различия во мнениях по этому вопросу. Мы не будем углубляться здесь в философские размышления, а просто изучим некоторые особенности неопределенных значений и приведем примеры связанных с ними проектных решений.
Смысл неопределенного значения
Неопределенное значение отличается от всех остальных значений следующим:
• Если оно используется в функции или выражении, то результатом всегда будет неопределенное значение — даже несмотря на то, что после конкатенации неопределенного значения со строкой последняя остается" без изменений.
• Неопределенное значение не считается равным любому другому значению, включая другое неопределенное значение.
• Полностью неопределенные ключи никогда не хранятся в индексе Oracle-частично неопределенные ключи хранятся, но на их использование существует ряд ограничений (которые описаны ниже).
• Неопределенное значение может интерпретироваться по-разному.
• В большинстве 3GL-языков и базовых языков неопределенное значение представить невозможно.
Давайте рассмотрим вопрос интерпретации неопределенного значения. Оно может соответствовать как минимум одному из элементов следующего списка и, вероятно, еще массе других вещей, здесь не указанных:
• не применимо;
• не известно;
• не задано;
• на данный момент не известно (будет указано позже);
• было задано, но сейчас устарело (обновлено в неопределенное значение);
• отсутствует (возможно, по соображениям безопасности).
В некоторых случаях неопределенное значение может иметь особый смысл. Например, неопределенное значение как верхняя граница диапазона может означать неограниченность или бесконечность. В равной степени оно может говорить о том, что верхняя граница существует, но мы не знаем, какова она. Мы, конечно, знаем, что она должна быть больше или равна значению, заданному для нижней границы диапазона, поэтому какие-то сведения о ней у нас все-таки есть. Задача проектировщика — выявить такие двусмысленные ситуации и решить, настолько ли они важны, чтобы принимать к ним какие-либо меры.
Одни самозваные эксперты говорят, что реляционная база данных должна поддерживать более одного типа неопределенных значений. Другие вообще выступают против их применения из-за непредсказуемости поведения, вызванной использованием многозначной логики (МЗЛ) вместо двузначной (23Л). Все эти аргументы основаны на том, что булев оператор, обычно возвращающий ответ True (истина) или False (ложь), может возвратить третье значение, если любой из аргументов имеет неопределенное значение. Это третье значение (Unspecified — не задано) в сочетании с NOT может привести к очень туманному четвертому значению.
Некоторые особенности использования неопределенных значений
В этом разделе освещаются некоторые проблемы, возникающие при использовании неопределенных значений.
Куда одно неопределенное значение равно другому неопределенному значению
Давайте обратимся к нашему списку и рассмотрим утверждение, что одно неопределенное значение не равно другому неопределенному значению. Необходимо отметить, что в Oracle существует исключение из этого правила, касающееся использования неопределенного значения в ограничении UNIQUE. Если мы определяем, что комбинация столбцов должна быть уникальной и для одного из них допускается неопределенное значение, то в целях обеспечения ограничения UNIQUE одно неопределенное значение считается равным другому неопределенному значению. Предположим, что таблица Х имеет ограничение UNIQUE на столбцах А и В, где В допускает неопределенное значение. Если вы попытаетесь дважды вставить строку, в которой А=1, а значение В не определено, то вторая операция не выполнится из-за нарушения ограничения UNIQUE. Таким образом, в данном конкретном и необычном случае одно неопределенное значение считается равным другому!
Следствия неравенства неопределенных значений
Давайте рассмотрим одно следствие неравенства двух неопределенных значений (с учетом нашего исключения). Допустим, у нас есть два SQL-запроса:
/* 1. */
SELECT *
FROM t
WHERE t.a = 'X'
UNION
SELECT *
FROM t
WHERE t.a <> 'X'
/* 2. */
SELECT *
FROM t
WHERE t.a = t.a
На первый взгляд кажется, что эти запросы должны возвратить все строки таблицы Т. Однако ни один из них не возвратит строки, в которых значение А не определено. Это надо учитывать при разработке отчета или приложения, принимающего параметр, который будет использоваться как предикат в условии WHERE. Если параметр не определен, то SQL-запрос должен вернуть только те строки, в которых предикатный столбец не определен. В противном случае он должен вернуть строки, совпадающие с параметром.
Это приводит к тому, что нам приходится написать уродливый и потенциально опасный SQL-запрос такого вида:
SELECT *
FROM t
WHERE NVL(:p, NVL(t.a,'$NULL')) = NVL(t.a,'$NULL')
Почему он уродлив? Да потому, что его не так просто понять и из него не ясно, чего мы хотим добиться. Почему он опасен? Потому что мы используем '$NULL' как "волшебное значение" и предполагаем, что столбец А никогда не будет содержать такое значение. Если же это не так, то наша логика разрушится!
Последствия для программистов
Как насчет 3GL-языков? Если мы используем прекомпиляторы Oracle, то можем ввести индикаторные переменные в соответствии со стандартом ANSI для встроенного SQL. Это означает, что для каждой внешней переменной в программе, которая соответствует столбцу, допускающему неопределенное значение, мы вводим вторую переменную, с помощью которой наша программа и Oracle показывают, является ли соответствующая переменная неопределенной. В Рго*С эти переменные объявляются как короткие целые. Вот пример, в котором индикаторные переменные используются во встроенном SQL-предложении:
ЕХЕС
SQL
UPDATE t
SET t.a = :a:a_ind
WHERE t.b = :b:b_ind;
А что произойдет, если мы используем не прекомпилятор, a Visual Basic и драйвер ODBC? Возможны проблемы, поскольку в Visual Basic эквивалента неопределенному значению нет. Работая с символьными строками, можно трактовать пустую строку как неопределенное значение, но числа и даты создают сложности. Часто кодирование и декодирование неопределенных значений выполняют между базой данных и базовым языком. Это, опять-таки, опасно, потому что в таком языке, как Visual Basic, не всегда можно выбрать для представления неопределенного значения число, которое не встречается в столбце базы данных. В таких случаях, может быть, лучше (а иногда и необходимо) иметь в таблице индикаторный столбец, в котором хранится флаг, показывающий, содержит ли главный столбец реальное значение.
Использование закодированных значений вместо неопределенного значения
Как проектировщикам нам нужно просмотреть все атрибуты в логической модели или столбцы в физической модели, для которых допускается неопределенное значение. Следует относиться к таким столбцам с подозрением и свести их количество к минимуму, учитывая проблемы, о которых мы упоминали выше. Необходимо выяснить, может ли неопределенное значение в данном столбце иметь разное толкование и, если может, важно ли различать эти случаи?
Допустим, мы регистрируем сведения о людях и одним из столбцов в таблице PERSONS является столбец SOCIAL_SECURITY_NO. По каким причинам для этого столбца допускается неопределенное значение? Во-первых, если человек — несовершеннолетний, то у него еще может не быть номера социального страхования (значение Not Applicable, "не применимо"). Во-вторых, не каждый может сообщить свой номер сразу же; в этом случае мы не хотим, чтобы это сдерживало процесс регистрации человека (значение Currently Unspecified, "на данный момент не задано"). И наконец, если мы регистрируем бродягу, то у него, несомненно, есть номер, но он может не знать его или не иметь возможности определить (значение Unknown, "не известно").
Если нужно различать эти значения (Not Applicable, Currently Unspecified, Unknown), то следует рассмотреть возможность использования не неопределенного, а закодированного значения. В принципе, мы могли бы применить неопределенное значение для одного из вышеуказанных значений, а остальные закодировать. Конечно, выбирая закодированное значение, вы должны обязательно выбрать такое значение, которое в реальной жизни не встречается. Например, если SOCIAL_SECURITY_NO — численное значение, то для закодированных элементов можно использовать отрицательные значения. Это, помимо прочего, упрощает поиск закодированных значений, если их не нужно дифференцировать, — например, найти записи, у которых SOCIAL_SECURITY_NO меньше нуля. Однако использовать отрицательные числа для кодирования неопределенного значения, которое может использоваться в агрегатной функции, например при вычислении среднего или минимального номера социального страхования, весьма опасно. Преимущество неопределенных значений в том, что они при вычислении агрегатных функций игнорируются. К счастью, арифметические операции с номерами социального страхования приходится выполнять крайне редко.
Международная организация по стандартизации (ISO) разработала стандартные правила кодирования. Например, для кодирования пола или рода ISO предложила следующий стандарт:
0 = Не известен
1 = Мужской
2 = Женский
9 = Не применим
Обратите внимание на то, что "не известен" и "не применим" заданы разными кодами. ** При использовании такой схемы кодирования рекомендуется хранить коды и их значения в базе данных.
Если значению "на данный момент не известно, будет указано позже" соответствует неопределенное значение, можно рассмотреть возможность использования стандартного квалификатора для инициализации данного столбца закодированным значением с этим смыслом.
Как быть, если мы не уверены в том, что значение, выбранное для представления неопределенного значения, не встретится в базе данных? В этом случае мы можем лишь определить новый столбец, который должен заполняться только в случае, если главный столбец имеет неопределенное значение, и содержать значение, показывающее, какой смысл имеет неопределенное значение в данной конкретной строке. Обеспечить проверку того, что новый столбец заполнен в случае, если главный столбец имеет неопределенное значение, и наоборот, можно при помощи ограничений на таблице:
CREATE TABLE persons
(per_id_ NUMBER (8) NOT NULL
.
.
.
,social_security_no CHAR(10)
,ss_null_reason CHAR(l)
CONSTRAINT per_ccl CHECK (ss_null_reason IN (...)
.
.
.
, CONSTRAINT per_cc2 CHECK ((social_secutity_no IS NULL
OR ss_null_reason IS NULL)
AND social_security_no || ss_null_reason IS NOT NULL)
Доминирующее неопределенное значение
Одним из направлений практического применения столбцов, допускающих неопределенные значения, является использование их в качестве индикаторов исключений. Допустим, у нас есть таблица заказов с 100000 строк, но лишь 50—60 из них могут быть предметом споров. Мы хотим быстро находить такие строки, поэтому определяем столбец-индикатор IN_DIS-PUTE и строим по нему индекс. Если сделать этот столбец обнуляемым и ограничить его значения значениями Y и NULL, то данный индекс будет содержать элементы только для строк спорных заказов. Этот прием иногда называют превалированием неопределенных значений (dominant value null).
Рекомендации по использованию неопределенных значений
Давайте подытожим все сказанное нами по поводу неопределенных значений. Итак, обязательно нужно соблюдать следующие правила:
• во всех случаях необходимо анализировать смысл неопределенных значений и выявлять возможную двусмысленность;
• использование неопределенных значений следует свести к минимуму, учитывая их неординарное поведение и вероятную двусмысленность;
• в качестве альтернативного варианта можно рассматривать систему кодирования, но при этом следует избегать кодовых значений, которые могут существовать и как реальные, даже если это кажется совершенно невероятным;
• если использовать кодирование опасно, придется ввести новый столбец, единственное назначение которого — описывать, как следует интерпретировать неопределенное значение основного столбца;
• следует помнить, что неопределенные значения могут быть очень полезны в индикаторах исключений.
* В некоторых версиях Oracle известна проблема, связанная с VARCHAR2(2000): полное наполнение его в одном экземпляре может привести к возникновению ошибки ORA-1467: Sort key too long при сортировке этой таблицы по любому неиндексированному столбцу.
** Получается, что комитет ISO либо никогда не слышал о гермафродитах, либо решил проигнорировать их существование!
Знаете ли Вы, что диаграмма последовательности, Sequence diagram - это методология объектно-ориентированного проектирования, предназначенная для моделирования взаимодействия во времени. Диаграмма последовательности позволяет отслеживать поведение взаимодействующих групп объектов.
