|
|
ГЛАВА 5 ПОЛЬЗОВАТЕЛЬСКИЕ ОБЪЕКТЫ БАЗЫ
Эта глава описывает структуры, обычно называемые объектами базы. К ним
относятся объекты, созданные пользователем, находящиеся в табличных
пространствах и содержащие фактические данные. Наиболее общие объекты,
являющиеся основными элементами памяти данных, называются таблицами.
В разделах этой главы описываются:
* объекты базы, такие как таблицы, обзоры, кластеры и индексы
* формат блоков, содержащих данные
* формат строк данных
* использование кластеров для запоминания данных таблиц
* создание и использование последовательностей чисел
Помимо DBA, многие пользователи ORACLE найдут эту информацию полезной.
Следующие разделы содержат информацию, относящуюся к содержимому этой главы:
* Глава 3 Файловые структуры ORACLE RDBMS - для обсуждения сегментов
данных и индексов
* Глава 4 Табличные пространства и сегменты
* Глава 16 Управление памятью
* Глава 19 Оптимизация обработки приложений
Таблицы
Таблицы - это основной элемент памяти данных для базы данных ORACLE. Любая
таблица определяется (CREATE TABLE ...) именем и набором столбцов (допустимо
до 254 столбцов). Каждый столбец имеет имя, тип данных и ширину. Ширина может
определяться неявно на основании типа данных (например - DATE) или указываться.
Столбцы типа CHAR должны иметь максимальную ширину, а типа NUMBER - масштаб и
точность. (Различные типы данных обсуждаются в Главе 6).
Для каждого столбца можно указать, обязательны ли для него значения или
допустимы пустые (null) значения. Вы можете указать также значения по
умолчанию и эталонные ограничения, хотя эта информация просто записывается в
словарь данных и еще не применяется.
После создания таблицы в нее можно заносить данные.
Кто может создавать таблицы ?
Если у Вас есть привилегия RESOURCE для табличного пространства, Вы можете
создавать внутри него объекты базы. Только DBA может давать привилегии
RESOURCE; кроме того он имеет различные возможности, например - наложение
ограничений на память, которую пользователь может распределить (называемых
"квота").
Смотрите Главу 17 и Приложение G для информации об операторах GRANT и ALTER
USER, используемых для задания и изменения привилегий.
Как таблицы хранятся в базе
Когда создается некластеризованная таблица, автоматически резервируется
сегмент данных для последующего занесения данных таблицы. (Глава 4 описывает
структуру сегментов и их распределение).
Таблица располагается либо в умалчиваемом табличном пространстве, либо по
указанию имени пространства в операторе CREATE TABLE.
-- 55 --
Набор параметров памяти определяет размер распределяемого сегмента данных.
В частности, параметры INITIAL и MINEXTENTS опредесяют, сколько экстентов
(блоков данных) будет первоначально распределено таблице. Действительные
параметры памяти либо берутся по умолчанию, либо определяются в операторе
CREATE TABLE. (См. Главу 4).
В процессе добавления таблицу новых строк, при необходимости автоматически
распределяются дополнительные экстенты. (Если в таблице создаются индексы,
выделяются специальные отдельные экстенты, по одному на каждый индекс). При
создании кластеризованной таблицы распределением управляют параметры памяти,
специфицированные в операторе CREATE CLUSTER; параметры, указанные в CREATE
TABLE в этом случае не действуют.
Формат блока данных
Формат блока ORACLE в основном одинаков независимо от того, какие данные он
содержит: таблицу, индекс или кластеризованные данные. Рисунок 5-1
иллюстрирует формат блока данных.
Ъ------------------------------------------------Дї
і Заголовок (постоянная и переменная части) і
Г------------------------------------------------Дґ
і Справочник таблиц (только для кластеров) і
Г------------------------------------------------Дґ
і Справочник строк і
Г------------------------------------------------Дґ
і Свободная память і
Г------------------------------------------------Дґ
і Данные строк і
А------------------------------------------------ДЩ
Рисунок 5-1 Формат блока данных
заголовок (общая Заголовок содержит общую информацию блока,
и переменная такую как адрес блока, тип сегмента (данные,
части) индекс, кластер, откат). В то время как некоторые части
заголовка фиксированного размера, полный размер заголовка
имеет переменную длину. В среднем фиксированная и перемен-
ная части заголовка блока лежат в диапазоне от 85 до 100
байтов.
справочник таблиц Для кластеризованного блока данных эта часть блока содержит
информацию о кластеризованных таблицах и ключах кластера,
имеющих строки в этом блоке (по 2 байта на таблицу). В
некластеризованных блоках данных эта часть отсутствует.
справочник строк Эта часть блока содержит информацию о строках для
действительных строк блока (включая адрес каждой строки
внутри области данных строк). По 2 байта на строку; для
кластеров одна до- полнительная "строка" требуется для
каждого ключа кластера в блоке.
свободная память Свободная память используется для вставки но- вых строк и
для изменения существующих, требующих дополнительную память
(например - при замене пустого значения на непустое
(null). Проведутся ли реально изменения, зависит от
значений PCTFREE и PCTUSED а также текущей величины
свободной памяти. Если свободная память ниже процента,
установленного PCTFREE, свободная память используется
только для изменения и не используется для вставки строк.
Элементы транзакций также используют свободную память.
-- 56 --
Элемент транзакции требуется для каждой транзакции,
обращающейся к блоку. Каждый элемент транзакции требует
около 23 байтов. Обратитесь к "Справочному руководству по
языку SQL" за описанием опций INITRANS и MAXTRANS
операторов CREATE.
данные строк Эта часть блока содержит данные таблицы или индекса.
Строки могут перекрывать блоки. Фор- мат строки описан ниже
в этой главе.
Когда блок может быть использован для добавлений и изменений ?
Два параметра управления памятью позволяют Вам управлять использованием
свободного пространства для вставки строк и изменений. Эти параметры, PCTFREE
и PCTUSED, определяют, когда блок будет рассматриваться для включения новых
строк. Опции PCTFREE и PCTUSED могут быть указаны в нескольких операторах SQL:
* CREATE TABLE
* CREATE CLUSTER
* ALTER TABLE
* ALTER CLUSTER
Во вновь распределенных блоках доступное для вставки пространство равно
размеру блока минус общая часть и PCTFREE. Изменения в блок могут вноситься
как в этом случае, так и при свободной памяти меньше PCTFREE (память,
зарезервированная под изменения).
ORACLE RDBMS поддерживает для каждой таблицы список блоков, распределенных
экстентам данной таблицы и имеющих свободную память превышающую PCTFREE. Эти
блоки доступны для вставки строк.
Во время выполнения INSERT, ORACLE RDBMS просматривает этот список и берет
первый блок. Если свободное пространство в этом блоке не больше достаточного
для выполнения INSERT, и оно как минимум равно PCTUSED, блок выводится из
списка свободной памяти.
После выполнения операций DELETE и UPDATE ORACLE RDBMS проверяет, не меньше ли
свободная память блока, чем PCTUSED и если так, блок помещается в начало
списка свободных блоков и будет использован первым, если возникнет
необходимость.
Назначение PCTFREE
Если данные в таблице будут часто меняться, необходимо резервировать некоторую
память для изменений, которые могут вызвать удлинение строки. Если Вы не
зарезервировали память по изменения, то строки будут распространены на два или
несколько блоков и некоторые строки могут принадлежать многим блокам. В этом
случае доступ к строке будет требовать больше операций ввода/вывода, нежели чем
при нахождении строки в одном блоке.
Используйте параметр PCTFREE оператора CREATE TABLE для установки процента
памяти блока, зарезервированной для изменений существующих строк.
Например, если для некластеризированных блоков PCTFREE был 20%, а блок заполнен
на 80%, запись новых строк в него производиться не будет. Однако, изменения
существующих строк будут выполняться даже если свободное пространство блока
станет ниже 20%.
-- 57 --
Если для кластеризованного блока данных PCTFREE был 20% и блок заполнился
до 80%, новые строки в него заноситься не будут, однако изменения существующих
строк будут выполняться даже если свободное пространство блока станет ниже
20%. Новые строки с тем же ключом кластера будут заноситься в новый блок,
который сцепляется по существующему ключу кластера.
Умолчание для PCTFREE - 10% причем приемлемо любое число от 0 до 99, если
сумма PCTFREE и PCTUSED не превышает 100. (Если PCTFREE установлен
равным 99, ORACLE будет вставлять в каждый блок как минимум одну строку
данных. Если же строки очень малы а блок велик, возможно помещение в него и
более одной строки.
Низкое значение PCTFREE:
* резервирует меньше места для изменения существующих строк
* позволяет более полно использовать пространство блока при вставке строк
* может экономить память, так как данные для одной таблицы полностью
запоминаются в меньшем количестве блоков (больше строк в каждом блоке)
* увеличивает накладные расходы на обработку, так как блоки требуют частой
реорганизации из-за того, что их свободное пространство заполняется новыми
или измененными данными
* потенциально увеличивает накладные расходы на обработку и требуемую память,
если изменение строк приводит к удлинению строки и распространению ее на
несколько блоков и в результате командам UPDATE и SELECT придется прочитать
большее количество блоков для указанной строки.
Высокое значение PCTFREE:
* резервирует больше памяти для будущего изменения существующих строк
* может потребовать большее количество блоков для того же количества
добавляемых строк (вставляется меньше строк на блок)
* уменьшает накладные расходы на обработку из-за того, что блоки реже
требуют реорганизации при нехватке свободного пространства.
* может увеличивать производительность при изменении, так как системе ORACLE
нет необходимости обрабатывать связанные цепочкой части строки.
Выбирая значение PCTFREE, необходимо понимать природу данных таблицы.
Изменения могут привести к увеличению размеров строки. Новые значения м.б.
того же размера, что и заменяемые, но могут и не быть. Если предполагается
много изменений, приводящих к удлинению строки, PCTFREE должен быть увеличен.
Если же изменения не влекут за собой увеличение длины строк, PCTFREE может
быть снижен.
Вашей целью должно быть нахождение оптимума между плотностью упаковки данных
(низкий PCTFREE, полные блоки) и хорошей производительностью при внесении
изменений в существующие строки (высокий PCTFREE, менее полные блоки).
PCTFREE также влияет на выполнение запросов к таблицам, для которых у других
пользователей существуют незавершенные транзакции. Для достижения
согласованного чтения может потребоваться реорганизация области свободной памяти
Назначение PCTUSED
Как только процент свободного пространства в блоке опускается до PCTFREE,
ORACLE прекращает вставлять новые строки в этот блок до тех пор, пока
-- 58 --
процент используемой памяти не упадет ниже PCTUSED. ORACLE RDBMS пытается
заполнить блок как минимум до PCTUSED. Этот процент вычисляется после
вычитания размера верхней части блока из общей памяти блока.
Предположим, например, что PCTUSED = 40% и строки добавлялись к блоку, пока
PCTFREE не выключил возможность новой вставки. Затем строки удалялись или
измененные строки были меньше существовавших, освобождая таким образом память
в блоке. Пусть освободилось достаточно места и блок стал заполнен менее чем на
40%. В этой точке ORACLE вновь начнет вставлять в блок новые строки. Если
процент заполнености блока не опустился ниже PCTUSED, ни одной новой строки не
может быть вставлено даже когда свободная память превышает PCTFREE.
Умолчание для PCTUSED составляет 40 процентов; допустимо любое число от 0
до 99 включительно, пока сумма PCTUSED и PCTFREE не превышает 100.
Низкое значение PCTUSED:
* в среднем заполняет блоки менее полно, чем высокое значение PCTUSED
* снижает накладные потери при операциях INSERT и UPDATE на перемещение
блоков в список свободной памяти при заполнености ниже процента использования
* увеличивает не используемую память в базе
Высокое значение PCTUSED:
* в среднем заполняет блоки полнее, чем низкое значение
* улучшает эффективность использования памяти
* увеличивает накладные расходы во время операций INSERT и UPDATE
Выбор значений PCTUSED и PCTFREE
Если Вы хотите изменить значения PCTUSED и PCTFREE, имейте в виду следующие
соображения:
* Сумма PCTUSED и PCTFREE должна быть меньше или равна 100.
* Если сумма меньше 100, идеальная величина разницы от 100 - это процент
доступной памяти, равный средней длине строки. (См. пример 1.)
* Если сумма равна 100, ORACLE RDBMS будет пытаться жестко заполнять блоки
(См. пример 2).
* Фиксированный заголовок блока не входит в расчет PCTUSED и PCTFREE.
* незначительная разница между PCTFREE и PCTUSED (например PCTUSED = 75, а
PCTFREE = 20), улучшает использование памяти це- ной некоторых накладных
расходов.
Пример 1. Предположим, размер блока ORACLE 2048 байтов минус 100 байтов
заголовка, остается 1948 байтов, доступных для данных. В среднем строка
требует 195 байтов (или 10% от 1948), тогда любая комбинация PCTUSED и
PCTFREE, дающая в сумме 90, будет наилучшим образом использовать память базы.
Пример 2. Допустим, PCTUSED установлен в 60, а PCTFREE - в 40, что в сумме дает
100. Допустим также, что если свободно 5 байтов, свободная память составляет
около 43%. Вам надо вставить строку из 20 байтов, но первый блок в
списке свободных блоков имеет только 5 байтов свободной памяти. PCTUSED не
может быть достигнут, так как PCTFREE = 43 (следовательно PCTUSED = 57),
значит блок не может быть убран из списка свободных. Если все строки имеют
длину около 20 байтов, то этот блок будет оставаться в списке свободных и
при каждой операции INSERT будет рассматриваться, но безрезультатно.
Пример 3. Если сказанное ниже верно, то PCTUSED = 75 и PCTFREE = 0 будет
вполне приемлемой комбинацией:
-- 59 --
1. Память очень ценна и не может транжириться
2. Большинство транзакций к таблице - INSERT, а количество UPDATE очень
мало (или UPDATE не меняет длины строки), так что маловероятно увеличение
размеров существующих строк.
3. Велико также и количество удалений строк
Однако будет некоторая потеря производительности из-за перемещения блоков
при заполнении на 75%. Модификации, приводящие к расширению строки,
обойдутся дорого, так как любое расширение приведет к появлению цепочки из
частей строки.
Если стопроцентное PCTFREE много больше PCTUSED, блоки не будут часто
помещаться и удаляться из списка блоков, доступных для вставки строк. Однако
если стопроцентное PCTFREE близко к PCUSED, некоторые блоки никогда не будут
удалены из списка свободных (См. пример 2).
Описанные соотношения иллюстрируются Рисунком 5-2.
В списке свободных блоков -
память, используемая для операций
INSERT и UPDATE
і
Ъ---->--------Е----Д>------Дї
^ і v 100 - PCTFREE
PCTUSED і і і Процент
--------------Е------------ДЕ------------ДЕ-------------->исполь-
і і і зования па-
^ і v мяти в блоке
А------<------Е----Д<------ДЩ
Память освобождается і
в результате операций і
UPDATE, DELETE і
Не в списке свободных блоков - память используется только для
операций UPDATE
Рисунок 5-2 Доступность блоков в зависимости от соотношения PCTUSED и PCTFREE.
Формат строки
Строка запоминается в базе одной или несколькими частями. Строка может
перекрывать блоки данных, в этом случае строка будет состоять из нескольких
частей. Если вся строка помещается в блоке, она состоит из одной части.
Всякий раз, когда это возможно, строки вставляются полностью, однако в
результате добавлений и изменений строк они могут быть разбиты на несколько
частей.
Блок содержит не более одной части строки. Каждая часть строки содержит
заголовок строки и данные некоторых столбцов. Столбцы могут также перекрывать
части строк и, соответственно, блоки.
Если строка перекрывает блок, части строки сцепляются вместе с помощью ROWID.
Столбцы запоминаются в порядке, определяемом RDBMS и одинаковом для всех
строк данной таблицы. Значениям столбцов предшествует их длина.
Конечные пустые (NULL) столбцы не запоминаются (см. Главу 6). Однако, если
строка модифицируется так, что после пустого столбца идет не пустой, значение
NULL хранится.
На рисунке 5-3 представлена строка.
Ъ------------ДВ----------ДВ--------------ДВ--------------------ї
і Подзаголовокі число і Идентификатор і ROWID для цепочек і
і і і ключа кластераі частей і...
-- 60 --
і строки і столбцов і (для кластери-і строк і
і і і зованных) і і
А------------ДБ----------ДБ--------------ДБ--------------------Щ
Заголовок строки
Ъ----ДВ----------В------В--------------В----В------------------В--Д
іДлинаі іДлина і і і і
істол-і Данные істол- і Данные і і і...
ібца і столбца ібца і столбца і і і
і і і і і і і
А----ДБ----------Б------Б--------------Б----Б------------------Б--Д
Данные столбцов
Рисунок 5-3 Формат строки.
Данным в строке предшествует заголовок строки, содержащий следующую информацию:
* информация о части строки
* для сцепленных частей строк - соответствующая информация
* информация о столбцах, содержащихся в этой части строки
* для кластеризованных данных - информация о ключе кластера
Некластеризованная строка, полностью содержащаяся в одном блоке, имеет
трехбайтовый заголовок строки. После информации заголовка каждая строка
содержит информацию о столбце и данные.
Каждая строка имеет заголовок как минимум 3 байта и дополнительно 2 байта для
справочника строк. Каждый столбец имеет подзаголовок в 1 байт, содержащий
длину столбца, предшествующий собственно данным столбца (для данных LONG - 3
байта).
Идентификаторы строк (ROWID)
Каждая часть строки идентифицируется своим местоположением и адресом,
называемым ROWID. ROWID для первой части строк могут задаваться в операторах
SQL и использоваться для выполнения модификаций данных в строке. ROWID для
следующих частей строки пользователю недоступны, но используются ORACLE.
Для более подробной информации о формате и использовании ROWID обратитесь
к Главе 6.
Обзоры
Реально обзоры не являются таблицами, но упоминаются здесь так как во многих
случаях они трактуются как таблицы. Обзоры - это "запомненные запросы",
которые представляются в формате таблиц. Таким образом, обзор может содержать,
как и таблица, до 254 столбцов. Ссылки на обзоры и таблицы во многих SQL
-операторах идентичны (особенно в операторах DML и запросах), в других же
случаях использование обзоров запрещено. Обзоры, скрывая физическое
представление данных, обеспечивают их независимость от приложений, которе
заботит только о логическая структура данных.
Обзоры полезны во многих смыслах, включая удобство и безопасность. Например -
обзоры, используемые для изменения и созданные с опцией WITH CHECK, могут
дополнять проверки данных в столбцах, подразумеваемые при создании таблицы с
помощью оператора CREATE TABLE. Использование обзоров для безопасности
дополнительно обсуждается в Главе 18, а более полно обзоры описываются в
"Справочном руководстве по языку SQL".
Обзоры не требуют памяти кроме строк в словаре данных, используемых для
запоминания определения обзора (сохраненный запрос).
-- 61 --
Если удаляются таблицы, на которых определены обзоры, на последние это не
влияет исходя из предположения, что таблица может быть создана заново или
импортирована. Однако попытка использовать обзор при отсутствии таблицы
приведет к ошибке. Если структура вновь созданной таблицы отличается от
предыдущей, результат использования обзора непредсказуем.
Индексы
Индексы - это необязательные структуры, связанные с таблицами и кластерами.
Существует два основных преимущества использования индексов:
Индексы ускоряют выполнение запросов. Также как индекс в этом руководстве
помогает Вам быстрее отыскать необходимую информацию, индекс системы ORACLE
дает более быстрый путь доступа к данным. Правильно используемые индексы
снижают активность дискового ввода/вывода.
Индексы необязательно гарантируют уникальность. Если индекс создается с опцией
UNIQUE, это гарантирует, что каждая строка таблицы уникальна. Основной ключ
таблицы обычно индексируется с уникальным индексом для гарантирования
уникальности.
Индексы создаются на одном или нескольких столбцах. Например, индекс может
гарантировать уникальность страхового полиса; то же самое может быть сделано с
помощью комбинации кода района и номера телефона.
Наличие или отсутствие индекса не требует изменения работающих SQL -
операторов. Индекс - это просто быстрый способ доступа к данным, он влияет
только на скорость выполнения и уникальность проверки. Получив значение
индексированных данных, индекс непосредственно указывает на местоположение
строки (или строк), содержащих эти данные.
ORACLE RDBMS не требует обязательного использования индексов. Автоматически
создаются только индексы в таблицах словаря данных. Однако настоятельная
рекомендация использования индексов диктуется преимуществами их использования.
Например, большинство правильно определенных таблиц имеет столбец (или
столбцы) основного ключа и эти столбцы обычно проиндексированы уникально.
Индексы настоятельно рекомендуются также и для ускорения обработки цепочек.
Индексы логически и физически не зависят от данных. Они могут создаваться и
отменяться в любой момент, не оказывая влияния на таблицы базы и другие
индексы. Если индекс отменен, прикладные программы будут продолжать
работать, но значительно медленнее. Индексы, как самостоятельные структуры,
требуют определенную па- мять (по одному индексному сегменту на индекс).
Однажды созданный, индекс автоматически поддерживается и используется системой
ORACLE. Изменения в данных, такие как создание, модификация или добавление
строк автоматически вносятся в соответствующий индекс без какого-либо
вмешательства пользователя. Скорость выборки остается практически постоянной
даже при добавлении новых строк, хотя присутствие в таблице многих индексов
влияет на скорость изменений и удалений, так как на корректировку индексов
тоже тратится время.
Индексы, ключи и внешние ключи
Хотя эти термины обычно используются как взаимозаменяемые, между индексами и
ключами существует различие. Индексы - это реально записанные в базе
структуры, которые пользователь создает используя SQL - операторы.
-- 62 --
Ключи - это строго логические понятие. Основной ключ - это столбец (или
комбинация столбцов), который может использоваться для уникальной
идентификации строки в указанной таблице. Внешний ключ - это столбец (или
группа столбцов) в одной таблице, которые соответствуют основному ключу в
другой таблице. Внешние ключи полезны при объединении данных из нескольких
таблиц, как и в цепочках. Рисунок 5-4 приводит пример использования этих
концепций на нескольких простых таблицах.
Таблица EMP
Ъ------Дї Ъ------Дї
і Ъ----v--------------------------------------------Дv------їі
^ іEMPNO ENAME JOB MGR HIREDATE SALARY COMM. DEPTNO іі
і Г--------------------------------------------------------Дґі
і і 7329 SMITH CLERK 7902 17-DEC-80 800.00 300.00 20 іі
і і 7499 ALLEN SALES 7698 20-FEB-81 1600.00 300.00 30 іі
і А--------------------------------------------------------ДЩі
ЪБ------------Дї ^
і Основной ключГ------------Дї Таблица DEPT і
і(ключ по одно-і Ъ----Дv------------------------------Дї і
іму столбцу) і і DEPTNO DNAME LOC і і
А--------------Щ Г------------------------------------Дґ і
і 20 RESEARCH DALLAS і і
Ъ----------------Дї і 30 SALES CHICAGO і і
і Внешний ключ і А------------------------------------ДЩ і
і(указывает на пері і
івичный ключ в друГ------------------------------------>------ДЩ
ігой таблице) і Таблица EMP_PROJ
А----------------ДЩ Ъ--------------------------------------ї
і ENAME PROJNO PROJ_TASK і
Ъ----------------Дї Г--------------------------------------ґ
і Составной ключ і і ALLEN 100 PRESENT і
і(ключ может со- і і ALLEN 100 APPROVAL і
ідержать более од-і і і
іного столбца Гї А--ДЕ------------Е------------Е--------Щ
А----------ДВ--ДВДЩі ^ і і
і і А------ДЩ ^ ^
і А----------------------ДЩ і
А----------------------------------------Щ
Рисунок 5-4 Пример индексов, ключей и внешних ключей
Уникальные и неуникальные индексы
Если Вы при создании индекса использовали ключевое слово UNIQUE, тогда
каждое индексируемое значение должно быть уникальным. Это единственный путь
гарантировать, что значения столбца (или общее значение связанных столбцов)
будет уникальным. Создание уникального индекса на столбце или группе
столбцов, определенных как NOT NULL, будет гарантировать уникальность
каждой строки в таблице.
Как только будет создан уникальный индекс, ни одна строка не может быть
вставлена или изменена в случае, если нарушается это ограничение. И наоборот,
уникальный индекс не может быть создан, если индексируемая строка содержит
дублирующиеся значения.
Не уникальный индекс также полезен. Если индексируемый столбец имеет широкий
диапазон значений, индекс будет обеспечивать более быстрый путь доступа к
данным, нежели такой же запрос в отсутствие индекса.
Составной индекс
-- 63 --
Составной индекс - это индекс, созданный более чем на одном столбце таблицы.
Так можно проиндексировать до 16 столбцов. Столбцы в составном индексе
могут появляться в любом порядке и не обязательно должны быть соседними в
таблице базы. Составные индексы очень полезны для запрещения дублирования
по нескольким столбцам, как по одной (например - комбинация кода района и
телефонного номера). Иногда их называют составными основными ключами. И если
они делают уникальными записи по нескольким столбцам, Вам, возможно, захочется
создать уникальный составной индекс.
Составные индексы могут ускорять выборку данных в операторах SELECT, для
которых фраза WHERE ссылается на все (или на начальную часть) столбцы в
составном индексе. Некоторое предложение можно сделать относительно порядка
столбцов в составном индексе: столбцы, к которым наиболее часто обращаются
должны быть первыми.
Когда я могу создавать индекс ?
Кроме двух указанных ниже исключений индексы предпочтительнее создавать после
того, как данные будут загружены, чем наоборот. Более эффективно будет
загрузить таблицу без индексов, а затем их создать; в противном случае
индекс модифицируется всякий раз, когда вставляется строка.
Упомянутые исключения следующие:
* Индекс кластера должен быть создан до того, как данные будут вставляться в
кластер.
* Если Вы хотите убедиться, что некоторое значение уникально для каждой
загружаемой строки, можете создать индекс до вставки или загрузки данных. Хотя
за это придется заплатить некоторым снижением производительности, дублирующиеся
строки будут отвергнуты.
Какие столбцы я должен индексировать ?
Некоторые типы таблиц и столбцов - явные кандидаты на индексирование. Правило
состоит в том, что индекс надо создавать в случае, если Вы часто выбираете
менее 30% данных из большой таблицы. Индексы могут значительно увеличить
производительность при объединении нескольких таблиц и запросах, содержащих
ключевые столбцы таблиц. Небольшие таблицы не требуют индексации.
"Хорошие" строки для индекса удовлетворяют одному и нескольким из приведенных н
* каждое значение уникально (наилучший вариант)
* существует широкий диапазон значений
* столбец состоит из большого количества пустых значений и относительно
небольшого количества значений и Ваши запросы обычно выбирают все
столбцы, имеющие значения. В этом случае фраза
WHERE COL_X > 0
предпочтительнее, чем WHERE COL_X IS NOT NULL так как она будет
использовать индекс (и предполагает столбец -цифровой).
Менее подходящие строки имеют следующие характеристики:
* Столбец имеет мало вариантов значений (например - пол служащих)
* Существует множество пустых (NULL) значений в столбце и Вы не ищете
непустые значения
Столбцы типа LONG и LONG RAW индексироваться не могут.
Создание индекса
-- 64 --
Пользователь может создавать индексы на таблицах с помощью SQL - операторов
CREATE INDEX. Любой пользователь ORACLE, которому дана привилегия доступа INDEX
к таблице, может также создавать индекс. Индекс используется для увеличения
производительности и проверки на уникальность любым пользователем, независимо
от того он или нет создавал индекс.
Создатель индекса может указать:
* имя индекса
* будет ли индекс уникальным
* какие столбцы будут индексироваться и в каком порядке
* в каком табличном пространстве будет располагаться сегмент с индексом
* сколько памяти будет распределено под данные индекса
Имя индекса используется при создании, изменении или отмене индекса; оно
не используется ни в каких других операторах SQL. Если Вы попытаетесь
создать индекс, идентичный одному из существующих, то получите сообщение об
ошибке.
Формат блоков индекса
Каждый индекс занимает один индексный сегмент. Доступная для индекса память
равна размеру блока ORACLE минус длина заголовка, минус один байт длины на
каждое значение индекса. Количество байтов для заголовка зависит от
операционной системы.
Когда выдается команда CREATE INDEX, выбираются столбцы, подлежащие индексации,
сортируются и ROWID добавляется к каждой строке. Затем индекс загружается.
Таким образом, для оператора:
CREATE INDEX IN_EMP_EMPNO ON EMP(EMPNO)
выполняется сортировка для EMPNO и ROWID для всех строк таблицы EMP, а затем
индекс загружается. Хотя ORACLE воспринимает ключевые слова: ACS, DESC,
COMPRESS и NOCOMPRESS, они не влияют на данные индекса, которые запоминаются
с использованием сжатия в узлы ветвей (но не в узлы листьев).
Смотрите ниже раздел "Внутренняя структура индексов", где описывается
внутренняя структура индексных В* - деревьев.
Сколько индексов может иметь таблица ?
Таблица может иметь любое количество индексов, однако чем больше индексов,
тем больше заголовков содержится как часть таблицы, подлежащей изменению.
Имейте в виду, что когда строки добавляются или изменяются, должны быть
изменены все индексы в таблице. Далее, если изменяется столбец, то должны быть
изменены все индексы, содержащие этот столбец.
Таким образом, повышение скорости выборки для запросов в таблице противоречит
скорости изменений той же таблицы. Например, если таблица в основном только
читается, может быть полезно иметь больше индексов, если же она активно
меняется, предпочтительнее уменьшение числа индексов.
В каком порядке располагать столбцы в индексе ?
Порядок, в котором столбцы появляются в индексе не зависит от их расположения в
таблице, однако порядок имеет значение, так как скорость выполнения запроса от
него зависит. В общем случае необходимо помещать столбцы, которые будут
использоваться чаще, ближе к началу индекса.
Например предположим, что таблица PART содержит следующие столбцы:
-- 65 --
VEN_ID PART_NO UNIT_COST
------ ------- ---------
1012 10-440 .25
1012 10-441 .39
1012 457 4.95
1010 10-440 .27
1010 457 5.10
1220 08-300 1.33
1012 08-300 1.19
1292 457 5.28
и для каждого из 5 продавцов существует около 1000 партий. Вы можете создать
связанный индекс, поместив в начало наиболее различные столбцы (содержащие
наибольшее количество значений):
CREATE UNIQUE INDEX IND_VEND_ID
ON VENDORS (PART_NO, VEND_ID)
Если бы Вы поместили столбец продавцов первым, каждый запрос должен
просмотреть около 1000 значений индекса. Если столбец VEND _ID также часто
просматривается, его можно сделать дополнительным отдельным индексом.
Индекс IND_VEND_ID имеет смысл, если часто встречаются запросы следующего типа :
SELECT *
FROM VENDORS
WHERE VEND_ID = '1292'
AND PART_NO = 457
Индексы будут ускорять выборку для любого запроса, использующего начальную
часть индекса. Так, для предыдущего примера, запросы с фразой WHERE,
использующей столбец PART_NO, будут давать заметный выигрыш в скорости
выполнения.
В каком табличном пространстве я должен создавать индексы ?
Индексы могут создаваться в любом табличном пространстве. Они могут создаваться
в том же табличном пространстве, где и таблица, которую они индексируют или в
другом пространстве.
Если для таблицы и индекса Вы используете одно табличное пространство,
сопровождение базы упростится ( такое как резервное копирование табличного
пространства или файла, экспорт, доступность прикладным средствам) и Вы
будете уверены, что все необходимые данные находятся в режиме online.
Если Вы используете различные табличные пространства (на разных дисках) для
таблицы и ее индексов, запрос будет выполняться быстрее, чем при одном
табличном пространстве (из-за конкуренции за ввод/вывод).
При использовании двух различных табличных пространств в случае нахождения в
offline пространства, содержащего данные либо индекс, нет гарантии, что
операторы SQL будут работать.
За информацией о том, как такие операторы будут работать, обратитесь к разделу
о выведении в offline табличных пространств в Главе 4.
Внутренняя структура индексов
-- 66 --
ORACLE использует для индексов В* - деревья, сбалансированные для
эквивалентности времени доступа по всем строкам. Обсуждение теории В* -
деревьев выходит за рамки данного руководства; для более подробной
информации обратитесь к научным разработкам по структурам данных.
Рисунок 5-5 иллюстрирует структуру В* - дерева.
Ъ--------Дї
Ъ------------ґ Г----------Дї
і і M і і
і А--------ДЩ і
v v
Ъ--Д<----ДВ----Б----ї Ъ----Д<--------ДВ--ДБ----Дї
і і B і і і MI і
і Ъґ J Г--ї і Ъ----ґ T і
і іА--------ДЩ і і і А------В--Щ
v v v v v v
і Ъ------Дї і і і і
Ъ--Б----Дї і BLAKE і Ъ----Б--ї ЪБ------ї ЪБ------Дї ЪДБ----Дї
і ADAMS і і CLARK і і JAMES і і MARTINі іMILLER і іTURNER і
і ALLEN і і FORD і і JONES і і і іSCOTT і іWARD і
А------ДВЩ АВ----ДВЩ АВ----ДВЩ АВ----ДВЩ АВ------ВЩ АВ------Щ
^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^
А--ДЩ і А--ДЩ А----Щ А----Щ А--ДЩ
Ъ----------ДБ------------ї
і(Содержание блока) і
і BLAKE - ROWID і
і CLARK - ROWID і
і FORD - ROWID і
А------------------------Щ
Рисунок 5-5 Пример индекса на В* - дереве.
Верхние блоки (блоки ветвей) В* - дерева содержат индексные данные, указыва-
ющие на нижние уровни индексных блоков. Самый нижний уровень индексных блоков
(блоки листьев) содержит все значения индексированных данных и соответствующие
ROWID, для размещения (и поиска) реальных строк. Блоки листьев связаны
в двунаправленый список. Для уникального индекса существует только одно ROWID
для каждого значения данных. Для неуникального индекса ROWID добавляется в
конец ключа индекса, так что неуникальные индексы запоминаются как с ROWID,
так и с ключом индекса. Ключи, содержащие пустые (null) значения столбца, не
запоминаются в индексе (если это не индекс кластера). Таким образом, две
строки, обе содержащие пустой ключ, не нарушают уникальности индекса.
Преимущества В* - дерева заключаются в следующем:
* блоки листьев находятся на одной глубине, так что выборка записи в любом
месте индекса требует приблизительно одного времени
* блоки В* - дерева заполнены в примерно не три четверти и пространство
памяти последовательно освобождается и распределяется
* вставка и удаление строк производится эффективно, поддержи- вая порядок
ключей для быстрой выборки.
Несмотря на то, что существуют некоторые дополнительные потери по поддержанию
индекса В* - дерева сбалансированным, они минимальны в сравнении со временем
выборки. В большинстве баз данных максимальные потери времени получаются в
момент выборки данных, а не в процессе манипулирования с ними; В* - дерево
работает быстро как при последовательной выборке записей, так и при одиночной.
Проверка индексов
-- 67 --
Для проверки непротиворечивости отдельного индекса используется SQL - оператор
VALIDATE INDEX. Кроме того, возможно проверить индекс, созданный другим
пользователем. Синтаксис команды следующий:
VALIDATE INDEX [username.]indexname
Например:
VALIDATE INDEX IN_EMPTNO
VALIDATE INDEX SCOTT.ENAME
Если Вы получите сообщение, отличное от
Index validated.
значит индекс неправильный. Это ненормальное условие, которое необходимо
задокументировать. Диагностическая информация записывается в файл трассировки,
который возможно придется направить в Oracle Customer Support (служба
сопровождения пользователей фирмы ORACLE). После документирования ошибки можно
пересоздать индекс заново.
Проверка индекса работает только с ним и может определить:
* неправильный формат индекса или индексной структуры
* элементы индекса, которые не сравниваются со строкой данных или не имеют
соответствующей строки.
Проверка индекса не определяет строк данных, не имеющих соответствующих
элементов индекса.
Как индексы трактуют пустые (null) значения
Элемент индекса создается для каждой строки при условии, что не все
индексируемые столбцы имеют значение null. Для примера предположим, что
индекс создается на четырех столбцах. Строка А имеет значения null во всех
кроме одного столбцах, значит для него будет создан элемент индекса. Строка В
имеет значения null во всех четырех столбцах и не будет иметь элемента индекса
(если это не индекс ключа кластера).
Кластеры
Кластеры - это необязательный способ хранения данных. Кластеры - это группы
таблиц, хранящиеся вместе, так как они разделяют группы столбцов и обычно
используются вместе. В результате того, что связанные строки и физически
хранятся вместе улучшается время доступа и может сокращаться требуемая память.
Кластеры в зависимости от распределения данных и наиболее часто выполняемых
операторов SQL могут увеличивать производительность. Особенно выигрывают при
использовании кластеров объединения. Время поиска и вычислений уменьшаются,
потому что данные в кластере хранятся замкнутой группой. Однако, использование
кластеров не улучшает ни обработку операторов DML, ни сканирование единст-
венной таблицы кластера. Этот недостаток связан с использованием памяти и
количеством блоков, обработанных при сканировании таблицы: так как несколько
таблиц содержат данные внутри одного физического блока, для хранения таблицы
потребуется большее, по сравнению с некластеризованной таблицей, количество
блоков памяти. Предыдущие рассуждения помогут Вам принять решение об
использовании кластеризованных таблиц.
Кластеры могут хранить данные таблиц более эффективно, чем если бы они
хранились индивидуально (не кластеризовано), так как данные, общие для
нескольких таблиц, запоминаются только один раз. Например, если ключ
кластера ORDER_NO и имеется в среднем 30 элементов на каждый заказ (order),
-- 68 --
тогда вместо запоминания каждого ORDER_NO по 30 раз (по одному разу на каждый
заказанный элемент), каждое уникальное значение ORDER_NO будет храниться только
один раз.
Как и индексы, кластеры не оказывают влияния на разработку
приложений. Данные, хранящиеся в кластеризованной таблице, обра-
батываются SQL точно так же, как и в не кластеризованной.
Для идентификации данных, которые лучше можно хранить в кластеризованной
форме, надо обратить внимание на таблицы, имеющие один или несколько общих
столбцов (обычно столбцы, составляющие основной ключ) и которые обычно
выбираются совместно (обычно в объединении). Таблицы "главный - детальный"
являются хорошим примером, где каждой одной главной записи соответствует
несколько различных детальных записи.
Если Вы кластеризовали таблицы по столбцам, используемых в предложении с
объединением, то число блоков данных, обработанных для выполнения запроса,
уменьшится. Все строки, необходимые для объединения на ключе кластера,
находятся в одном блоке. Этим Вы улучшите выполнение запроса.
Кластеризованные блоки данных имеют такой же формат, что и не кластеризованные
за исключением того, что в блоке появляется дополнительный справочник (См.
Рисунок 5-1).
Ключ кластера
Ключ кластера - это столбец или столбцы, общие для кластеризованных таблиц.
Эти столбцы именуются в предложении CREATE CLUSTER. Затем они именуются во
фразе CLUSTER предложения CREATE TABLE для каждой добавляемой к кластеру
таблицы. Значения ключа кластера запоминаются один раз на блок данных.
Для каждого столбца в операторе CREATE CLUSTER каждая таблица в кластере
должна содержать столбец, равный по размеру и соответствующего типа.
Максимальное число столбцов, на которых можно создать ключ кластера, равно 16.
Значение ключа кластера может быть изменено. Другими словами, могут быть
изменены данные, входящие в ключ кластера. Так как расположение данных
зависит от значения ключа кластера, изменение ключа кластера для строки обычно
означает физическое перемещение строки. Таким образом, столбцы, которые часто
обновляются, не будут хорошими кандидатами в ключи кластера.
Индекс кластера
Вы можете создать индекс на ключевых столбцах кластера сразу же за
созданием кластера. Этот индекс содержит элемент для каждого значения ключа
кластера в блоке. Этот индекс должен быть создан до выполнения любого
оператора DML (включая INSERT), выполняемого над кластеризованными таблицами.
Таким образом, кластер не может быть загружен, пока не создан индекс кластера.
Оператор, используемый для создания индекса, следующий:
CREATE INDEX indexname ON CLUSTER cluster
Как и обычный индекс, индекс кластера создается и запоминается в индексном
сегменте а значит - можно создать кластер в одном табличном пространстве, а
индекс кластера - в другом пространстве.
-- 69 --
Индекс кластера отличается от индекса таблицы следующим:
* Пустые (null) значения ключа будут иметь элемент индекса
* Элементы индекса указывают на первый блок в цепочке для указанного
значения ключа кластера
* отсутствие индекса оказывает влияние на пользователей
(кластеризованные данные без индекса не доступны).
Если индекс кластера отменен, данные кластера становятся недоступными, пока
не будет создан новый индекс кластера. Вам может потребоваться отменить индекс
кластера, чтобы перенести индекс в другое табличное пространство или изменить
характеристики памяти, но после выполнения этих операций индекс надо
пересоздать для обеспечения доступа к данным кластера.
Создание кластеров
За описанием команд CREATE CLUSTER и DROP CLUSTER обратитесь к "Справочному
руководству по языку SQL". Некоторые полезные замечания приведены ниже.
Кластер должен быть создан до создания таблиц, в него входящих.
На использование кластеров не влияет создание дополнительных индексов на
кластеризованных таблицах; они могут создаваться и удаляться по мере
необходимости.
Выбор таблиц для кластера
Обычно кластеры включают в себя несколько таблиц, в особенности - таблиц,
часто сцепляемых вместе.
Иногда имеет смысл кластеризовать единственную таблицу. Это верно, если
таблица часто сцепляется сама с собой или таблица большая (много строк и,
возможно, много столбцов) и ее строки содержат общие значения в одном или более
столбцов. Примером может служить кластеризация первых трех столбцов
таблицы, имеющей столбцы: DIVISION, DEPT, PROJ и TASK.
Хотя кластеризованные таблицы могут содержать данные типа LONG, они не
могут входить в индекс кластера.
Выбор столбцов для кластера
Аккуратно выбирайте столбцы для индекса кластера; если более одного столбца
общие для кластеризованных таблиц, сделайте ключ кластера связанным. В общем
случае - характеристики столбцов, привлекательные для индексации, применимы и
к ключам кластера. (Смотрите выше раздел "Какие столбцы я должен индексировать
?") Хороший ключ должен генерировать достаточно уникальные значения, где
каждое значение имеет соответствующую группу строк и каждая группа строк
занимает приблизительно один блок.
Малое количество данных, хранящихся для каждого ключа (мало строк на ключ
кластера) приводит к потерям памяти и лишь незначительной выгоде. Кластерные
ключи, которые настолько спцефичны, что только несколько строк разделяют
общие значения, транжирят дисковую память в случае, когда при создании
кластера указано маленькое значение SIZE.
Слишком много данных на ключ может привести к расширенному поиску при
поиске строк по этому ключу. Кластерные ключи на значения, являющиеся слишком
общими, например - MALE (мужчина) и FEMALE (женщина), могут привести к
расширенному поиску и в результате снизить производительность по сравнению с
некластеризованными данными.
-- 70 --
SIZE
Оператор CREATE CLUSTER имеет необязательный аргумент SIZE, оценивающий
максимальное количество байтов в блоке, которые будет занимать ключ кластера
вместе с соответствующими строками данных. SIZE не ограничивает память,
распределяемую под ключ кластера, а используется ORACLE RDBMS для вычисления
оценки числа ключей кластера (и связанных строк), которые будут помещены в
кластеризованный блок данных.
По умолчанию ORACLE будет помещать только один ключ кластера и соответст-
вующие строки в каждый блок. Этот размер варьируется при перенесении с машины
на машину, но отмечается в словаре данных, чтобы правило "один ключ на блок"
поддерживалось при импортировании кластеризованной таблицы в другую базу (или
на другую машину).
Значение SIZE надо выбирать несколько больше, чем средний ожидаемый размер,
требуемый каждому ключу кластера вместе с соответствующими строками по всем
кластеризованным таблицам. Например, если в среднем ключ кластера имеет пять
соответствующих строк по 100 байтов каждая, надо выбрать SIZE равным 600
(500 байтов плюс значение ключа плюс небольшой запас). Однако если ожидается,
что 75% ключей кластера будет иметь по 10 строк, значение SIZE надо увеличить.
Если все строки для данного ключа кластера не входят в один блок, то для
быстрого доступа ко всем значениям с указанным ключом блоки будут объединены в
цепочку. Индекс кластера указывает на начальный блок цепочки блоков, каждый
из которых содержит ключ кластера и связанные с ним строки. Если параметр SIZE
такой, что в один блок помещается более чем один ключ, блоки могут
принадлежать более чем одной цепочке.
Выбор PCTUSED и PCTFREE
Два параметра поддержания памяти - PCTFREE и PCTUSED, применяющиеся к
некластеризованным таблицам, аналогично применяются и к кластерам. И для
таблиц и для кластеров значения этих параметров определяют, когда новые строки
могут быть добавлены к блоку. Новые строки для существующего значения ключа
кластера должны быть добавлены в блок, уже содержащий этот ключ или в
сцепленный блок, если достигнут PCTFREE.
zПодробнее о PCTFREE и PCTUSED можно прочитать в предыдущих разделах этой
главы.
Генератор ПОСЛЕДОВАТЕЛЬНОСТЕЙ
Генератор последовательностей может быть использован для генерирования
последовательных номеров для строк таблиц; генератор можно использовать для
автоматического генерирования уникальных основных ключей для данных, затем
можно использовать ключи для согласования нескольких строк или таблиц.
Генерация последовательностей в ORACLE аналогична выдаче номерка для
обслуживания в магазине; никто не ожидает своего номера и каждый пользователь
обслуживается в нужном порядке. В результате избежания последовательной
обработки, когда несколько пользователей ожидают друг друга для генерации и
использования последовательного номера, производительность обработки
транзакций возрастает и ожидание соответственно снижается.
-- 71 --
Последовательные номера генерируются после первого определения последовательно-
сти, которое выполняется с помощью SQL - оператора, указывающего общую инфор-
мацию такую как имя последовательности, направление (возрастание или убывание),
шаг между последовательными номерами и так далее. Все последовательности
хранятся в одной таблице словаря данных. После создания последовательность
может при необходимости использоваться различными пользователями для
генерации реальных последовательных номеров.
Последовательные номера - это целые числа ORACLE размерностью до 28 цифр;
опции позволяют Вам указывать минимальные и максимальные последовательные
номера, интервал между значениями и порядок, в котором будут выдаваться и
использоваться номера. Вследствие того, что последовательности независимы
от таблиц, один генератор последовательностей может использоваться для работы
с одной или несколькими таблицами.
Отдельные последовательные номера могут быть пропущены, если они
генерировались и использовались в транзакции, для которой был выполнен откат.
Приложения могут при необходимости получать и повторно использовать
последовательные номера.
Просмотр последовательностей
Все определения последовательностей хранятся в одной таблице словаря
данных. Для просмотра последовательностей, которые Вы предполагаете
использовать, запросите следующие обзоры словаря данных: USER_SEQUENCES,
ALL_SEQUENCES, DBA_SEQUENCES.
Создание последовательности
Для создания последовательности Вы должны иметь привилегию RESOURCE для
любого табличного пространства или быть DBA. Синтаксис соответствующей команды
приведен ниже, а полное описание приведено в "Справочном руководстве по языку SQ
CREATE SEQUENCE seq_name
[ INCREMENT BY int ]
[ START WITH int ]
[ MAXVALUE int | NOMAXVALUE ]
[ MINVALUE int ] | [ NOMINVALUE ]
[CYCLE ] | [ NOCYCLE ]
[ CACHE int ] | [ NOCACHE ]
[ ORDER ] | [ NOORDER ]
Синтаксис для умалчиваемой последовательности эквивалентен команде:
CREATE SEQUENCE default
INCREMENT BY 1 START WITH 1
NOMAXVALUE NOCYCLE CACHE 20 NOORDER
Генерирование последовательностей номеров с NEXTVAL
После того, как последовательность создана, она может быть использована
для генерирования уникальных последовательностей номеров. В следующих примерах
предполагается, что пользователь ELLY создала последовательность с именем ESEQ:
-- 72 --
CREATE SEQUENCE ESEQ
START WITH 10
INCREMENT BY 10
NOMAXVALUE NOCYCLE NOCACHE
Каждый новый последовательный номер генерируется на основании ключа
последовательности NEXTVAL. Генерируемый последовательный номер может
использоваться в последующих SQL - операторах через ключ CURRVAL. Эти слова не
являются зарезервированными или ключевыми; так же, как и ROWID - слова CURRVAL
и NEXTVAL могут использоваться как имена псевдостолбцов в SQL - операторах
SELECT, INSERT или UPDATE.
Для генерирования (и использования) последовательного номера необходимо
ссылаться к seq_name.NEXTVAL. К нему можно обращаться в списке значений
(values), например:
INSERT INTO EMP VALUES ( ESEQ.NEXTVAL, 'LEWIS', 'TIMOTHY');
или во фразе SET оператора UPDATE, например:
UPDATE EMP SET EMPNO=ESEQ.NEXTVAL WHERE ENAME='NATHANIEL';
Первое обращение к ESEQ.NEXTVAL вернет значение 10. Каждое последующее
обращение к ESEQ.NEXTVAL будет генерировать следующий последовательный номер. К
именам NEXTVAL (CURRVAL) можно ссылаться столько раз, сколько потребуется;
для одной строки каждая ссылка будет выдавать одинаковое значение, например:
SELECT ESEQ.NEXTVAL A ESEQ.NEXTVAL B ESEQ.CURRVAL C
A B C
--- --- ---
30 30 30
Независимо от того, выполняются ли команды COMMIT или ROLLBACK, а также от
работы других пользователей, последовательный номер будет получать очередное
значение. Если два пользователя обращаются к одной последовательности в одно и
то же время, то каждый из них может увидеть промежутки в нумерации, так как
последовательные номера генерируются и другим пользователем.
Последовательные номера могут быть пропущены, если пользователь не выполнил
COMMIT для транзакции или транзакция завершилась аварийно.
Использование последовательных номеров с CURRVAL
Чтобы использовать значение уже сгенерированного последовательного номера,
сошлитесь на seq_name.CURRVAL. CURRVAL можно использовать только в случае,
когда NEXTVAL генерировался в текущем сеансе пользователя (НЕ ТРАНЗАКЦИИ !).
Таким образом, для использования последовательности ELLY (имя пользователя)
можно выдавать такие операторы SQL:
INSERT INTO EMP VALUES (ESEQ.NEXTVAL, 'LEWIS', 'TIMOTHY')
INSERT INTO EMP_PROJ VALUES (ESEQ.CURRVAL, 101)
(Предполагается, что таблица EMP имеет три столбца (EMPNO, LAST, FIRST), а
таблица EMP_PROJ содержит два столбца (EMPNO и PROJNO).
Ограничения на NEXTVAL и CURRVAL
-- 73 --
Придерживайтесь следующих правил для использования CURRVAL и NEXTVAL:
* Их нельзя использовать в выражениях WHERE, на любой глубине вложенности и
с любыми внешними функциями или предложениями
* Они не могут быть использованы в запросах, содержащих ORDER BY, CONNECT BY,
GROUP BY или DISTINCT.
* Они не могут использоваться в обзорах
CURRVAL и NEXTVAL можно использовать в следующих местах оператора SELECT:
* списке VALUES оператора INSERT
* в правой части фразы SET оператора UPDATE
* во внешних операторах SELECT в запросе
* во внешних SELECT оператора INSERT внутри вложенного SELECT или CREATE TABLE
AS ...
* во внешних SELECT в подзапросе, используемом во фразе SET оператора UPDATE
Дополнительные команды для последовательностей
Последовательности трактуются подобно таблицам. Пользователь последовательности
может предоставить другим привилегии для последовательностей ALTER или SELECT;
фраза WITH GRANT OPTION также приемлема. К примеру, чтобы дать возможность
пользователю Matilda генерировать новые номера в последовательности, созданной
пользователем Humphery, первая должна иметь привилегию SELECT для
последовательностей Humphery.
Любой пользователь с привилегией ALTER может изменить определение
последовательности с помощью команды:
ALTER SEQUENCE seq_name ...
Эта команда позволяет (с некоторыми проверками) изменить все начальные
опции, кроме стартового значения. Например, новое значение MAXVALUE не может
быть установлено ниже текущего значения номера последовательности; допустимо
только большее значение.
Последовательности могут быть изменены или отменены подобно другим
объектам базы. Изменяемые опции для последовательностей следующие:
ALTER | AUDIT | GRANT | SELECT
Последовательности могут быть импортированы и экспортированы аналогично
таблицам. Экспорт сохраняет значение последовательного номера так что после
импорта номер будет станет таким, как будто экспорт/импорт не выполнялся.
Если последовательные номера кешированы в процессе экспорта (во время создания
последовательности была использована опция CACHE, а не NOCACHE), последова-
тельность будет восстанавливать в словаре метку высокого уровня, не
обязательно текущее значение.
Параметры INIT.ORA, влияющие на последовательности
Параметр SEQUENCE_CACHE_ENTRIES устанавливает число последовательностей,
которые могут быть кешированы одновременно. Если контроль установлен,
необходимо разрешить одну последовательность. Эта последовательность
создается для идентифицирования номеров сеанса контроля.
Если значение для SEQUENCE_CACHE_ENTRIES слишком мало, возможен пропуск
значений последовательностей по следующему сценарию: допустим у нас есть 4
NOORDER/CACHE последовательностей, кеш полон и SEQUENCE_CACHE_ENTRIES
-- 74 --
установлен в 4. Если в текущий момент используется 4 последовательности, пятая
последовательность заменит наименее часто используемую последовательность в
кеше и все оставшиеся значения для замененной последовательности потеряются.
ГЛАВА 6 ТИПЫ ДАННЫХ ORACLE
Character is simply habit long continued.
Plutarch: On Moral Virtue
If you want to be witty, work on your character and say
what you think on every occasion.
Stendal
Эта глава содержит несколько разделов, относящихся к типам данных ORACLE :
* определение и использование данных различных типов
* CHAR, VARCHAR (существенно идентичны)
* NUMBER
* DATE
* LONG
* RAW
* LONG RAW
* как они хранятся, используются и преобразовываются
* как они соответствуют или отличаются от других типов данных
* псевдо-тип данных ROWID
ORACLE RDBMS хранит данные в виде строк в таблицах. Таблица может содержать
до 254 столбцов, каждый столбец специфицируется одним из типов данных ORACLE.
Далее следует описание отдельных типов данных.
CHAR и VARCHAR
Типы данных CHAR и VARCHAR эквивалентны. Эти типы данных используются для
хранения символьных (алфавитно-цифровых) строк. Слова CHAR и VARCHAR
взаимозаменяемы и имеют одинаковый эффект.
Данные хранятся как строки переменной длины в кодах ASCII или EBCDIC - в
зависимости от символьного набора компьютера, на котором работает база.
Использование нестандартных символов возможно для совместимости между машинными
архитектурами.
Максимальное число символов, которое можно запоминать в столбцах, определенных
как CHAR и VARCHAR, равно 255. Максимальная длина столбца типа CHAR или VARCHAR
определяется при создании таблицы и может в дальнейшем модифицироваться.
Столбцы, описанные как CHAR и VARCHAR, в словаре данных описаны как VARCHAR.
В версии 6.0 ORACLE RDBMS описания CHAR и VARCHAR трактуются как эквивалентные,
однако предполагается в будущих версиях трактовать CHAR как тип данных с
фиксированной длиной, а VARCHAR - как строки переменной длины не более
описанной.
NUMBER
Тип данных NUMBER используется для хранения чисел (с фиксированной и
плавающей точкой). Возможно хранить данные практически неограниченной
размерности (точностью до 38 цифр). Возможно хранение чисел до 9.99 * 10 в 124
степени, то есть единица и за ней 125 нулей.
Для числовых столбцов можно просто указать NUMBER, например:
( colname NUMBER )
-- 75 --
или можно указать точность (общее число цифр) и масштаб (число цифр справа от де
( colname NUMBER (precision, scale) )
например:
CREATE TABLE MINI ( weenumbers number (2, 1));
Нельзя указать масштаб более 38 цифр. Можно указать масштаб и не указывать
точность, задав:
(colname number (*,2) )
Использование масштаба и точности
При задании числовых полей следует указывать максимальное количество цифр и
позиций после десятичной точки. Это обеспечивает возможность дополнительной
проверки данных на входе. На рис. 6-1 приведены примеры хранения данных при
использовании различных показателей масштаба.
Если масштаб отрицательный, данные округляются справа от десятичной точки
до указанного количества цифр. Например, спецификация (10,2) означает
округление до сотен.
7,456,123.89 NUMBER 7456123.89
7,456,123.89 NUMBER(*,1) 7456123.9
7,456,123.89 NUMBER (9) 7456124
7,456,123.89 NUMBER (9,2) 7456123.89
7,456,123.89 NUMBER (9,1) 7456123.9
7,456,123.8 NUMBER (6) не воспринимается
превышена точность
7,456,123.89 NUMBER (7,-2) 7456100
Рисунок 6-1 Примеры масштаба и точности для данных типа NUMBER
Внутренний числовой формат
ORACLE использует собственный внутренний формат числа по различным
соображениям, включая совместимость и точность (отсутствие ошибок округления).
Числовые данные хранятся в формате переменной длины, начиная с байта,
содержащего степень числа и знак, за которым следуют байты данных. Максимально
может быть использован 21 байт (22 вместе с байтом длины), каждый содержит
две десятичных цифры.
[знак/степень] цифра1 цифра2 цифра3 ... цифра19
Знаковый бит - старший в байте показателя степени числа. Если он установлен
в 1, число положительное, в противном случае - отрицательное. Степень числа
занимает семь младших битов этого байта. Основание степени 100; оно может
иметь значение от 0 до 127 со смещением на 64. Результат вычитания 64 из сте-
пени числа показывает, на сколько байтов должно быть сдвинуто число, причем
сдвиг означает умножение или деление числа на 100. Если результат вычитания
отрицательный, число должно быть сдвинуто вправо относительно точки, если
положительный,число должно быть сдвинуто влево.
Каждая цифра внутреннего формата - это двоичное число от 0 до 99, пердставляю-
щая 100 - ричную цифру. К каждой цифре прибавляется 1, чтобы предотвратить
существования двоичного нуля как цифры. Таким образом, действительное
внутреннее представление каждой цифры внутреннего формата - это двоичные
числа от 1 до 100.
-- 76 --
Первая цифра никогда не бывает равной нулю, так как числа нормализованы.
Точка располагается слева от первой цифры. Отрицательные числа преобразуются в
положительные. Преобразование отрицательных чисел в положительные гарантирует,
что все числа в системе ORACLE будут сравниваться правильно.
Преобразование осуществляется следующим образом. Отрицательный байт показателя
степени, включая знаковый бит, преобразуется в свое положительное дополнение.
Каждая отрицательная цифра является дополнением до 100 соответствующей
положительной цифры. Это означает, что положительная цифра вычитается из 100
для получения соответствующей отрицательной цифры. Если отрицательное число
имеет количество цифр меньше максимального, то к нему добавляется байт,
содержащий 102.
Нуль - это однобайтовое число, содержащее 128. Это байт показателя степени
со знаковым битом, установленный в минимальное значение экспоненты (-64).
Это гарантирует, что нуль будет больше всех отрицательных чисел и меньше всех
положительных.
Положительная бесконечность представляется двумя байтами [555,101];
отрицательная бесконечность представляется тремя байтами: [1,1,102]. Такое
представление выбрано для того, чтобы обе эти величины сравнивались корректно.
Тип данных DATE
Данные дат хранятся в фиксированных полях длиной семь байтов. Для каждой
даты запоминается следующая информация:
БАЙТ: 1 и 2 3 4 5 6 7
Год Месяц День Час Минута Секунда
Первые два байта хранят значение года (включая век). Годы до нашей эры
хранятся в форме дополнения до 2. Время хранится в 24 - часовом формате. Если
не введено значение времени, по умолчанию предполагается полночь (12:00 pm).
Только время вводится по умолчанию как часть системной даты.
Стандартный формат даты ORACLE: DD-MON-YY, например 13-NOV-88.
Для ввода данных в формате, отличном от стандартного, используйте функцию
TO_DATE с маской формата.
ORACLE может хранить даты в диапазоне от 1 января 4712 года до н.э. до
31 декабря 4712 года нашей эры. Если в маске формата не указано BC (до н.э.),
предполагается по умолчанию наша эра.
Для того, чтобы ввести в дату время, необходимо это указать в маске формата
функции TO_DATE, как например:
INSERT INTO BIRTHDAYS (BNAME, BDAY) VALUES ('ANNIE',
TO_DATE('13-NOV-85 10:56 A.M.', 'DD-MON-YY HH:MI A.M.')
Для игнорирования времени при сравнении дат можно использовать SQL -
функцию TRUNC.
Использование дат по Юлианскому календарю
Для преобразования дат в даты Юлианского календаря в функциях
преобразования даты (TO_DATE, TO_CHAR) может быть использована маска формата
"J". Например по запросу
-- 77 --
SELECT TO_CHAR (HIREDATE, 'J') FROM EMP;
все даты будут выданы в Юлианском формате. Чтобы использовать Юлианские
даты в вычислениях необходимо использовать также функцию TO_NUMBER.
Юлианские даты могут вычисляться и интерпретироваться по-разному. Метод,
используемый в ORACLE, представляет дату в виде семизначного числа (наиболее
часто используемый метод). Например, 23 января 1982 года будет представлено в
виде 2444993. Юлианская дата вводится для того, чтобы производить отсчет
времени, начиная с какой-либо точки. ( За точку отсчета принимается
приблизительно 4000 г. до н.э., поэтому текущая дата имеет порядок 2,4
миллиона. Вообще говоря, Юлианская дата не является целой и дробная часть
определяет время суток. В системе ORACLE используется упрощенное
преобразование в целые значения.
Тип данных LONG
Столбец, описанный как LONG, может содержать строку переменной длины до
65536 символов. Данные типа LONG представляют собою просто неструктурированный
набор байтов. Данные типа LONG могут содержать массивы двоичных данных,
произвольные символы или даже короткие документы.
Тип данных LONG используется в словаре данных для хранения определений
для обзоров. Столбцы, описанные как LONG, могут использоваться в списках
операторов SELECT, предложениях SET операторов UPDATE а также в операторах INSER
Данные LONG отличаются от LONG RAW тем, что последние не рассматриваются как
символьные и не подлежат преобразованиям при перемещении между системами.
Ограничения на данные типа LONG
Хотя столбцы типа LONG находят много различных применений, на их
использование накладываются некоторые ограничения. Только один столбец типа
LONG может присутствовать в таблице. Дополнительно, данные типа LONG не могут:
* индексироваться
* использоваться во фразах WHERE, GROUP BY, ORDER BY, CONNECT BY и DISTINCT
* использоваться в функциях (таких как SUBSTR или INSTR)
* использоваться в списках SELECT гнездованных запросов
* использоваться в выражениях
* присутствовать в списке SELECT блока запроса, связанного с другим блоком
запроса с помощью UNION, INTERSECT и MINUS
* использоваться в распределенных запросах (хотя они могут входить в
команду COPY SQL*Plus).
Некоторые продукты ORACLE не обеспечивают буферов, достаточных для обработки
очень длинных данных (например - SQL*Plus может работать с данными не более
определенного предельного значения).
Типы данных RAW LONG и RAW
Данные типа RAW и LONG RAW используются для байтоориентируемых данных,
которые не интерпретируются системой ORACLE. RAW аналогичен данным типа CHAR
(соответственно LONG RAW - LONG ), за тем лишь исключением, что не делается
никаких предположений относительно значения байтов.
-- 78 --
Эти типы данных предназначаются для двоичных данных или байтовых строк,
например - для хранения последовательностей графических символов. Данные типа
RAW эквивалентны CHAR (соответственно LONG RAW - LONG), кроме того, что при
при передаче с помощью SQL*Net данные типа CHAR преобразовываются при
необходимости в другой набор символов, а RAW - нет.
Когда ORACLE автоматически преобразует данные RAW и LONG RAW в/из CHAR
(например - в случае ввода этих данных с клавиатуры с использованием
SQL*Plus), они представляются как одна шестнадца- теричная цифра на каждые 4
бита данных. Однобайтовое данное типа RAW с битами 11001011 будет вводиться и
выводиться как 'BA'.
Данные типа LONG RAW не могут быть индексированы.
Как хранятся значения NULL
Значение NULL означает отсутствие данных в столбце. Это должно обозначать
"ничто" и использоваться для указания отсутствующих, неизвестных или
неприменимых данных. Значение NULL не должно быть использовано для обозначения
какого-либо действительного значения, например - нуля. Столбец может содержать
значение null, если при создании таблицы не был определен как NOT NULL. (В
этом случае ни одна строка с пустым столбцом, определенным как NOT NULL не
сможет быть вставлена в таблицу.
Пустые столбцы "запоминаются" в базе, если находятся между столбцами,
содержащими значения. В этом случае им требуется один байт. Пустые значения,
находящиеся в конце строки (на них строка заканчивается), не запоминаются, но
подразумеваются. В таблицах с большим количеством столбцов те столбцы, которые
с наибольшей вероятностью будут содержать пустые значения, желательно
определять в конце строки.
Пустые значения не индексируются за одним исключением. Даже если все значения
в ключе кластера - null, элемент индекса будет создан в индексе кластера.
Типы данных DB2 и SQL/DS
В дополнение к типам данных системы ORACLE с помощью операторов SQL можно
создавать таблицы и кластеры, способные воспринимать данные из продуктов
DB/2 и SQL/DS фирмы IBM и преобразовывать их в данные ORACLE по
следующей таблице соответствия:
SQL/DS и DB/2 ORACLE
------------------------------------------------------------
SMALLINT NUMBER
INTEGER NUMBER
DECIMAL(m,n) NUMBER(m,n)
FLOAT NUMBER
VARCHAR(n) VARCHAR(n)
LONG VARCHAR LONG
CHARACTER(N) CHAR(n)
Из-за отсутствия соответствующих типов данных в ORACLE, не должны
использоваться данные типа GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC. Кроме
того, все типы данных SQL/DS являются зарезервированными словами и поэтому не
могут применяться для именования объектов базы, таких как таблицы и индексы.
Типы данных ANSI
-- 79 --
ORACLE автоматически преобразовывает типы данных ANSI в собственный формат,
как показано в следующей таблице:
ANSI ORACLE
------------------------------------------------------Д
CHARACTER(n) CHAR(n)
CHARACTER, CHAR CHAR(1)
NUMERIC [(p[,s])] NUMBER [(p[,s])]
DEC [(p[,s])] NUMBER [(p[,s])]
SMALLINT NUMBER(*,0)
INTEGER NUMBER(*,0)
FLOAT [(p)] NUMBER
REAL NUMBER(x)
DOUBLE PRECISION NUMBER
Псевдо - данные ROWID
В базе неявно присутствует столбец для каждой строки, ассоциирующийся с ее
адресом в базе. Этот адрес может быть выбран из базы с помощью зарезервиро-
ванного слова ROWID. Как показано в следующем примере, по запросе,
использующем ROWID, будет выдан результат вместе с шестнадцатеричным
значением адреса каждой строки:
SELECT ROWID, ENAME FROM EMP;
ROWID ENAME
------------------ ----------Д
00000938.0000.0001 SMITH
00000938.0001.0001 ALLEN
00000938.0002.0001 WARD
ROWID возвращает элементы информации, необходимые для нахождени строки:
* какой блок в файле (в примере - блок 938)
* какая строка в блоке (первая строка 0, в примере показаны строки 0,1 и 2)
* в каком файле находится строка (первый файл - 1)
Номера строк начинаются с 0. Номера строк относятся к содержащему их файлу, а
не к табличному пространству. Следовательно, две строки с идентичными номерами
блоков могут находиться в двух различных файлах. Номера файлов внутри базы
уникальны.
Для чего используются ROWID ?
ROWID могут использоваться в следующих целях:
* Это наиболее быстрый способ доступа к отдельным строкам
* Их можно использовать для выяснения, как организована таблица
* Они уникально идентифицируют строки в данной таблице
Так как нет гарантии, что ROWID для данной строки останется постоянным
(они могут меняться при экспорте/импорте строки), необходимо проверить ROWID
перед использованием для работы со строкой.
Хотя ROWID могут использоваться наравне с именем столбца (при использовании
в операторах SELECT и предложениях WHERE), они не хранятся в базе, так как
не являются данными. Следовательно над ROWID нельзя выполнить операции UPDATE,
INSERT или DELETE.
Примеры использования ROWID
-- 80 --
Используя некоторые групповые функции, можно реально увидеть, как данные
хранятся а базе ORACLE.
Функция SUBSTR может быть использована для разбиения ROWID на составляющие
части (файл, блок, строка):
SELECT ROWID, SUBSTR(ROWID,15,4) FILE,
SUBSTR(ROWID,1,8) BLOCK,
SUBSTR(ROWID,10,4) ROW
FROM EMP;
ROWID FILE BLOCK ROW
------------------ ----Д -------- ----
000000A8.0000.0001 0001 000000A8 0000
000000A8.0001.0001 0001 000000A8 0001
000000A8.0002.0001 0001 000000A8 0002
000000A8.0003.0001 0001 000000A8 0003
Следующий запрос показывает, сколько блоков реально используется для
хранения данных:
SELECT COUNT( DISTINCT (SUBSTR(ROWID,1,8))) BLOCKS
FROM tablename;
Заметьте, что этот запрос отражает только блоки, содержащие первые части
строки и не включает в себя:
* один блок, необходимый для заголовка таблицы
* блоки, содержащие только части переполнения строк
* блоки, распределенные в экстенте, но пока не содержащие данных.
Следующий запрос показывает, сколько строк, начинающихся в каждом блоке,
принадлежат определенной таблице:
SELECT SUBSTR (ROWID,1,8) BLOCK, COUNT(*)
FROM tablename
GROUP BY SUBSTR (ROWID,1,8);
Преобразование данных
Фразы WHERE и выражения (булевые, арифметические или строковые) могут
работать со смешанными типами данных. В этих случаях ORACLE обычно может для
выполнения оператора использовать преобразование данных. В следующих примерах
ORACLE автоматически использует неявные преобразования данных:
SELECT ENAME FROM EMP WHERE ENAME = 135 ;
SELECT ENAME FROM EMP WHERE EMPNO = '7936' ;
SELECT ENAME FROM EMP WHERE HIREDATE = '12-MAR-86' ;
SELECT ENAME FROM EMP WHERE ROWID = '00002514.0001.0001' ;
Для выполнения преобразований ORACLE может:
* преобразовывать константу в определенный для столбца тип данных
* преобразовывать значение столбца в тип данных константы
* преобразовывать тип данных одного столбца к типу данных другого
Преобразования данных контекстнозависимы. Это означает, что нельзя ожидать от
ORACLE одинаковых преобразований в каждом конкретном случае. Вместо того, чтобы
полагаться на неявные или автоматические преобразования, лучше воспользоваться
функциями SQL, предназначенными для этой цели. Если Вы оставляете все преобра-
зования для выполнения ORACLE RDBMS, это может отрицательно сказаться на
производительности, особенно если данные столбца преобразовываются к типу
данных константы, а не наоборот.
-- 81 --
Алгоритмы преобразований меняются от версии к версии в различных
продуктах ORACLE.
ГЛАВА 7 СЛОВАРЬ ДАННЫХ
-----------------------------------------------------------
No dictionary of living tongue even can be perfect,
since while it is hastening to publication, some words
are budding and some falling away.
Samuel Johnston: Preface to Dictionary
В этой главе описывается создание и назначение основного набора справочных
таблиц и обзоров, общеизвестных под именем "Словарь данных". Разделы включают в
* определение и использование словаря данных
* какие типы объектов составляют словарь данных
* внутренняя структура с схема именования
* создание и модификация словаря данных
Словарь данных не только центральный элемент программного обеспечения
ORACLE RDBMS, но и важный инструмент для пользователей ORACLE, от случайных
пользователей до разработчиков прикладных программ и администраторов базы
данных. Даже начинающие пользователи получат много пользы от как можно более
раннего овладения данными словаря.
Замечание: Ни один пользователь не имеет права изменять (модифицировать,
удалять, вставлять) строки объектов из словаря данных. Такие действия имеют мно
При чтении данной главы Вам может понадобиться обратиться к приложению Е,
содержащему описание отдельных таблиц и обзоров словаря данных.
"Руководство пользователя по инсталяции" описывает шаги инсталяции, связанные с
созданием словаря данных.
Что же такое - Словарь Данных ?
Словарь данных является одной из основных частей ORACLE RDBMS.
Словарь данных - это набор таблиц, используемых для справочного руководства по
базе данных. Например, он расскажет Вам:
* об именах пользователей ORACLE
* правах и привилегиях, им предоставленных
* именах объектов базы (таблиц, обзоров, индексов, кластеров, синонимов и
последовательностей)
* даст информацию об основных и внешних ключах
* умалчиваемые значения для столбцов
* ограничения, поддерживающие непротиворечивость таблиц
* сколько памяти распределено и сколько используется объекта- ми базы,
относящимися к пользователю
* контрольная информация, например кто запрашивал или изменял различные
объекты базы
* другая важная для базы информация.
Словарь данных, как и другая информация базы, структурирован по таблицам
и обзорам. Для доступа к информации словаря данных необходимо использовать
язык SQL. Так как информация об объектах словаря относится к только читаемой,
возможно использовать только запросы (операторы SELECT). Так как многие обзоры
словаря данных доступны всем пользователям базы, новые пользователи могут
изучать язык SQL, формируя запросы по этому словарю.
-- 82 --
Словарь данных создается в процессе создания базы. Затем, уже во время
работы базы, словарь данных модифицируется ORACLE RDBMS в ответ на каждый
оператор DDL. Словарь доступен в любое время любому пользователю независимо
от того, создавал он или нет объекты базы.
Словарь данных - полезный источник информации для конечных пользователей,
разработчиков приложений и администратора базы данных (DBA). Словарь
критичен также и для ORACLE RDBMS, обращающейся к нему для записи, верификации
и управления текущей работой.
Обзор "СЛОВАРЬ" (dictionary)
Словарь отчетов показывает объекты словаря данных в упрощенном виде. Если
Вы забыли имя необходимой таблицы, начните со СЛОВАРЯ.
Обратите внимание на столбец КОММЕНТАРИИ в обзоре DICTIONARY. Когда
создаются новые объекты словаря данных, комментарии загружаются для каждого
обзора и столбца, обеспечивая оперативную информацию, поясняющую эти объекты и
Доступ к Словарю Данных
Словарь данных - это справочник для всех пользователей базы. Все
пользователи базы имеют одинаковый доступ к любому обзору словаря данных,
начиная от USER до ALL.
Некоторые объекты словаря данных предназначаются только DBA. Эти обзоры
имеют префикс DBA и недоступны обычным пользователям.
Словарь данных доступен всегда, когда открыта база. Он располагается в таблич-
ном пространстве SYSTEM, которое при открытой базе всегда находится в online .
Доступ к объектам словаря данных осуществляется с помощью языка SQL.
Таблицы и обзоры словаря данных
---------
База данных создается с двумя пользователями DBA: SYS и SYSTEM (см.
Главу 2). SYS является собственником таблиц и обзоров словаря данных, которые
резюмируют данные о таблицах базы.
Замечание: За единственным исключением НИ ОДИН пользователь ORACLE не может
изменять объекты, принадлежащие SYS (См. "Удаление Элементов Словаря Данных").
Эти обзоры содержат данные в более удобной форме, нежели таблицы базы. К
таблицам базы редко обращаются непосредственно, так как он нормализованы и
большая часть хранящихся там данных использует вместо понятных имен - числовые
ключи. Обзоры декодируют эту информацию в соответствующие значения, такие как
имена пользователей или таблиц, а также используют объединения и фразы WHERE
для упрощения информации. Таким образом, общий доступ к таблицам словаря
осуществляется через обзоры.
Структура Словаря Данных
Словарь данных состоит из наборов обзоров. Каждый, в свою очередь,
содержит логический набор информации, например - существуют обзоры,
соответствующие всем типам объектов базы, которые могут быть созданы
пользователями или DBA.
-- 83 --
Во многих случаях набор состоит из трех обзоров, содержащих подобную
информацию и различающихся префиксами:
Префикс Область применения
----------------------------------------------------------
USER Обзор пользователя (то, что принадлежит ему)
ALL Расширенный обзор пользователя (к чему поль-
зователь может иметь доступ)
DBA Обзор DBA (кратко по всем пользователям)
Этот набор обзоров в основном идентичен, за исключением некоторых исключений:
* Обзоры с префиксом USER обычно не содержат столбца OWNER. Этот столбец
включается в обзоры ALL и DBA, но отсутствует в OWNER, так как это имя
пользователя, выдавшего запрос.
* Некоторые обзоры DBA содержат дополнительные столбцы с ин- формацией,
относящийся только к DBA.
Обзоры с префиксом USER
Эти обзоры скорее всего интересуют обычного пользователя базы и включают в
себя:
* справки по личному окружению пользователя и включают выдачу информации по
таблицам, созданным пользователем, правам, назначенным пользователем
* выдают минимальное число строк (и, следовательно, наиболее управляемые)
* имеют в основном идентичные столбцы с другими обзорами (за исключением
столбца OWNER)
* возвращают подмножество строк, которые выдаются из обзоров с префиксом ALL
* для удобства могут включать в себя сокращенные синонимы PUBLIC
Обзоры с префиксом ALL
Обзоры с префиксом ALL показывают перспективные возможности пользователя в
базе. Они выдают информацию об объектах базы, к которым пользователь может
иметь доступ через общие или явно заданные привилегии (в дополнение к его
личным объектам. Если пользователь с привилегией DBA запрашивает эти обзоры,
он увидит объекты, к которым имеет исключительный доступ.
Обзоры с префиксом DBA
Обзоры с префиксом DBA могут запрашиваться только администраторами базы, так
как содержат глобальный обзор базы данных. Для этих обзоров не создаются
синонимы, так как они должны запрашиваться исключительно DBA. Таким
образом, DBA должен перед именем обзора использовать префикс SYS, например:
SELECT * FROM SYS.DBA_TABLES ;
Для того, чтобы создать синонимы для этих обзоров, DBA должен выполнить файл
DBA_SYNONYMS.SQL; после этого создаются синонимы, доступные текущему
пользователю и имеющего привилегию DBA.
ANSI - совместимые обзоры
Некоторые обзоры имеют альтернативное имя, обеспечивающее совместимость с
ANSI. В этом случае создается и обзор и синоним к нему. Ниже перечислены такие
обзоры и соответствующие синонимы:
Имя обзора Синоним ANSI
----------------------------------------------------Д
ALL_CATALOG ACCESSIBLE_TABLES
-- 84 --
ALL_TAB_COLUMNS ACCESSIBLE_COLUMNS
USER_USERS MYPRIVS
ALL_TAB_GRANTS TABLE_PRIVILEGIES
ALL_COL_GRANTS COLUMN_PRIVILEGIES
Другие обзоры
Некоторые обзоры словаря данных не используют префиксов USER, ALL и DBA. Ниже
они перечислены:
DICTIONARY выдает список таблиц словаря данных, обзоров и синонимов, доступных
текущему пользователю.
DICT_COLUMNS выдает описание столбцов DICTIONARY, доступных текущему
пользователю.
CONSTRAINT_DEFS выдает принудительные определения, введенные для таблиц,
доступных текущему пользователю.
AUDIT_ACTIONS выдает операции, которые м.б. протоколироваться и соответствующий
код.
DUAL это небольшая таблица, на которую ссылаются пользовательские программы и
ORACLE для гарантирования известного результата. Она имеет один столбец и
одну строку.
Собственные (Синоним) Расширенный Глобальный
объекты обзор обзор
пользователя пользователя DBA
Общие ------------------------------------------------------
(DICT) DICTIONARY
DICT_COLUMNS
CONSTRAINT_DEFS
CONSTRAINT_COLUMNS
AUDIT_OPTIONS
DUAL
Объекты пользователя ----------------------------------------Д
USER_CATALOG (CAT) ALL_CATALOG или DBA_CATALOG
ACCESSIBLE_TABLES
USER_OBJECTS (OBJ) ALL_OBJECTS DBA_OBJECTS
USER_TAB_COMMENTS ALL_TAB_COMMENTS DBA_TAB_COMMENTS
USER_COL_COMMENTS ALL_COL_COMMENTS DBA_COL_COMMENTS
USER_CROSS_REFS DBA_CROSS_REFS
USER_TABLES (TABS) ALL_TABLES DBA_TABLES
USER_TAB_COLUMNS (COLS) ALL_TAB_COLUMNS или DBA_TAB_COLUMNS
ACCESSIBLE_COLUMNS
USER_INDEXES (IND) ALL_INDEXES DBA_INDEXES
USER_IND_COLUMNS ALL_IND_COLUMNS DBA_IND_COLUMNS
USER_CLUSTERS (CLU) DBA_CLUSTERS
USER_CLU_COLUMNS DBA_CLU_COLUMNS
USER_VIEWS ALL_VIEWS DBA_VIEWS
USER_SYNONYMS (SYN) ALL_SYNONYMS DBA_SYNONYMS
USER_SEQUENCES (SEQ) ALL_SEQUENCES DBA_SEQUENCES
USER_DB_LINKS ALL_DB_LINKS DBA_DB_LINKS
Пользователи----------------------------------------------Д
USER_USERS или ALL_USERS DBA_USERS
MYPRIVS
USER_TS_QUOTAS DBA_TS_QUOTAS
-- 85 --
Системные объекты ----------------------------------------
DBA_DATA_FILES
USER_TABLESPACES DBA_TABLESPACES
DBA_ROLLBACK_SEGS
Память------------------------------------------------------
USER_SEGMENTS DBA_SEGMENTS
USER_EXTENTS DBA_EXTENTS
USER_FREE_SPACE DBA_FREE_SPACE
Привилегии ------------------------------------------------Д
USER_TAB_GRANTS ALL_TAB_GRANTS DBA_TAB_GRANTS
или TABLE_PRIVILEGES
USER_COL_GRANTS ALL_COL_GRANTS DBA_COL_GRANTS
или COLUMN_PRIVILEGES
USER_TAB_GRANTS_RECD ALL_TAB_GRANTS_RECD
USER_COL_GRANTS_RECD ALL_COL_GRANTS_RECD
USER_TAB_GRANTS_MADE ALL_TAB_GRANTS_MADE
USER_COL_GRANTS_MADE ALL_COL_GRANTS_MADE
Контроль------------------------------------------------------
USER_TAB_AUDIT_OPTS DBA_TAB_AUDIT_OPTS
ALL_DEF_AUDIT_OPTS
USER_AUDIT_TRAIL DBA_AUDIT_TRAIL
USER_AUDIT_CONNECT DBA_AUDIT_CONNECT
USER_AUDIT_RESOURCE DBA_AUDIT_RESOURCE
DBA_AUDIT_DBA
DBA_AUDIT_EXISTS
DBA_SYS_AUDIT_OPTS
Экспорт/Импорт ----------------------------------------------
DBA_EXP_VERSION
DBA_EXP_FILES
Как создается словарь данных ?
Базовые таблицы словаря данных должны быть первыми объектами, которые
должны быть созданы в любой базе, так как они должны присутствовать при
создании любых других объектов. Таблицы словаря данных создаются
автоматически при исполнении SQL - оператора CREATE DATABASE.
Во время создания базы параметр файла INIT.ORA, называемый INIT_SQL_FILES,
читается для нахождения одного или нескольких файлов, которые должны
выполниться (умалчиваемые имена файлов зависят от операционной системы). Файлы
должны прежде всего обеспечить создание словаря данных, остальные файлы создают
начальных DBA (SYS и SYSTEM) или зависящие от системы таблицы. Например, Вы
можете таким образом добавить свои имена файлов после умалчиваемых:
INIT_SQL_FILES = (SQL.BSQ, CATALOG.ORA, ACME_DBA.SQL)
Если Вы используете дополнительные файлы для создания новых объектов базы,
они должны принадлежать либо пользователю SYSTEM, либо вновь созданному
пользователю с привилегией DBA. Они не должны принадлежать пользователю SYS.
Инициирующие SQL - файлы:
1. Определяют табличное пространство SYSTEM и сегмент отката.
2. Определяют базовые таблицы словаря данных.
3. Загружают данные в некоторые таблицы словаря данных.
4. Определяют обзоры словаря данных.
5. Создают общие синонимы для многих обзоров.
6. Дают привилегию PUBLIC для этих синонимов.
-- 86 --
Словарь данных должен создаваться и постоянно находиться в табличном
пространстве SYSTEM. Для обеспечения online - документирования для каждой
таблицы и столбца загружаются также комментарии.
Общие (public) синонимы создаются для обзоров, не принадлежащих DBA, так
что все пользователи ORACLE имели к ним удобный доступ. Во многих случаях
для ускорения ввода создаются сокращенные пользовательские синонимы.
Как используется словарь данных ?
Данные в базовых таблицах словаря данных не только помогают пользователям
и администраторам базы, но и являются необходимыми для функционирования RDBMS.
Таким образом, записывать и менять информацию в словаре данных может только
RDBMS.
Во время работы базы RDBMS читает словарь данных для определения, какие
объекты базы существуют и имеют ли пользователи к ним правильный доступ.
RDBMS постоянно обновляет словарь данных, чтобы он отражал текущее состояние
структур базы, привилегий, контроля и данных.
Например, если пользователь NANCY создает таблицу с именем PARTS, новые
строки добавляются, чтобы отражать новые: таблицу, столбцы, сегмент, экстенты
и привилегии NANCY для этой таблицы. Все эти изменения будут видны при
последующем запросе обзоров словаря данных.
Кеширование данных словаря для ускорения доступа
Так как ORACLE RDBMS постоянно обращается к словарю данных для проверки доступа
пользователей и состояния объектов базы, много данных из словаря кешируется
в SGA. Вся информация запоминается на основании алгоритма LRU ( Least Recently
Used) (наиболее старая информация записывается).
Сумма используемой памяти для кеширования словаря данных определяется парамет-
рами INIT.ORA, начинающимися с DC (Dictionary cache - кеш словаря). В
приложении D обсуждается как вся группа параметров, так и индивидуально каждый.
Обычно кешируется информация, требуемая для разбора. Столбцы комментария, описы
вающие таб-цы и столбцы не кешируются, пока не требуются для текущего доступа.
Другие программы и словарь данных
Другие продукты ORACLE также могут создавать дополнительные объекты словаря
данных или обращаться к существующим обзорам. Если Вы пишете программы,
обращающиеся к словарю данных, они должны обращаться к общим синонимам, а не к
таблицам, так как синонимы менее чувствительны к изменениям программного
обеспечения ORACLE от версии к версии.
Добавление новых элементов в словарь данных
К словарю данных разрешено добавлять совершенно новые таблицы и обзоры. Если
Вы добавляете новые объекты к словарю данных, их собственником должен быть
либо пользователь SYSTEM, либо либо третий пользователь с привилегиями DBA.
Не создавайте новых объектов, принадлежащих пользователю SYS.
Удаление элементов словаря данных
Так как все изменения в словаре данных выполняются ORACLE в ответ на
запросы операторов DDL, никакие данные из таблиц словаря данных не могут быть
удалены или изменены пользователями.
-- 87 --
Единственным исключением из этого является таблица SYS.AUD$. В зависимости от
того, какие опции протоколирования работают, эта таблица может расти безгра-
нично. Хотя Вы и не можете удалять таблицу AUDIT_TRAIL, Вам разрешено
удалять из нее данные. Строки этой таблицы только информационные и не
необходимы для работы RDBMS.
Общие синонимы для обзоров словаря данных
Общие синонимы создаются для многих обзоров словаря данных для удобства
доступа к ним пользователей. Очень полезно создавать дополнительные синонимы
для широко используемых объектов базы. Надо, однако, избегать создания своих
собственных объектов с именами, используемыми в общих синонимах.
Таблицы динамической эффективности
В процессе работы ORACLE RDBMS поддерживает набор "виртуальных" таблиц, в
которых отмечает текущую активность базы. Эти таблицы называют "таблицами
динамической эффективности" (или иногда - фиксированными таблицами).
Информация из этих таблиц используется в различных выходных экранах средства
MONITOR.
Так как таблицы динамической эффективности не являются реальными таблицами
базы, к ним не имеет доступа большинство пользователей. Однако DBA может
запрашивать информацию из этих таблиц а также создавать обзоры на эти таблицы и
давать право доступа к ним другим пользователям базы.
Таблицы динамической эффективности принадлежат пользователю SYS, а их имена
начинаются с V_$. Во всех базах для этих таблиц создаются обзоры а для
последних - синонимы. Имена синонимов начинаются с V$.
Приложение Е содержит описание синонимов и их столбцов, а также -дополнительную
информацию об этих таблицах.
|
|