Oracle как работает уникальный индекс со значениями null

Обновлено: 06.07.2024

Индексы (от латинского "указатель", обращающий внимание на запрещенные католической церковью книги или их фрагменты) являются хранимыми вспомогательными объектами, используемыми при отработке запросов на SQL. В базах данных смысл индексов — воплотить функцию, позволяющую по значению (или набору значений) получить адреса строк таблицы с этими значениями. В промышленных БД всех типов подобные функции обеспечиваются с помощью индекса как специальной хранимой структуры, хотя умозрительно требования в такой структуре нет, а нужна лишь функциональность.

Наиболее употребимы в Oracle B*-древовидные индексы. Они могут создаваться:

автоматически, СУБД — как средство проверки ограничений целостности "первичный ключ" и "уникальность" в таблицах,
либо вручную, разработчиком — ради ускорения доступа к строкам таблицы.

Во втором случае ("вручную") для создания индексов используется специальная команда SQL . Примеры:

На выбор столбцов для древовидного индекса есть ограничения.

Разрешено создавать индекс не более чем на 32 столбца.
Нельзя индексировать столбцы некоторых типов (например, семейства LOB или же LONG/LONG RAW ).

Влияние индексов на эффективность работы с БД противоречиво. Индексы:

способны сокращать время обращения к строке таблицы;
способны увеличивать время обращения к строке таблицы (при "неудачном" распределении индексированных строк по блокам);
требуют места в БД;
способны замедлять обновления таблиц.

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

Некоторые общие и простые соображения по поводу использования древовидных индексов:

Индекс неэффективен при малом количестве различных индексированых значений (например, пол: "М" и "Ж"), когда они представлены примерно равными количествами.
При отсутствии значений ( NULL ) сразу во всех индексируемых столбцах (если индекс построен по нескольким столбцам) строка не индексируется. Поиск "по отсутствующим значениям" будет игнорировать индекс и выполняться полным просмотром таблицы.

Второй по важности тип индекса появился в версии Oracle 8.1 и существует для Enterprise Edition. Это поразрядный (bitmap) индекс. Он используется исключительно для ускорения доступа к данным таблицы и дает отдачу во вполне определенных обстоятельствах.

Доменный индекс (иначе — прикладной, предметный) программируется разработчиком приложения для конкретного типа объектов, однако несколько видов доменных индексов приходит в готовом виде с ПО Oracle, будучи уже запрограммированными разработчиками СУБД.

Для всех видов индексов допускаются частные случаи конфигурации.

Индексы для проверки заявляемых ограничений целостности

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

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

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

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

Если на необходимые столбцы индекс был заведен ранее, в качестве служебного можно взять уже имеющийся:

Это позволяет заметно снизить затраты на создание указанных ограничений для больших таблиц.

В последнем предложении конструкцию USING INDEX можно было бы не употреблять. Однако же если бы индекса PK_T заранее не существовало, эту же конструкцию можно было использовать для заведения индекса с желаемыми характеристиками, применив следующую формулировку:

Обратите внимание, что индекс в этом случае не обязан быть уникальным. (Упражнение. Проверьте свойство уникальности у индекса PK_T ). Более того, если ограничение создается как DEFERRABLE , индекс обязан быть неуникальным, и именно таковым он при том создается СУБД автоматически.

Если при заведения ограничения используется существующий индекс, явно создаваемый или же создаваемый ради ограничения с возможностью отложенной проверки ( DEFERRABLE ), то в отличие от автоматического он не будет удаляться вместе с удалением ограничения. (Упражнение. Проверьте это.) В этом есть своя логика, но это же может приводить к недоразумениям, когда по удалению ограничения целостности в БД сохранится "остаточный" индекс, не всегда нужный по делу.

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

Упражнение. Проверьте, что создание индекса на внешний ключ не оказывает влияния на логику поведения последнего.

Решение о создании индекса на столбцы внешнего ключа принимается исходя из конкретных обстоятельств.

Таблицы с временным хранением строк

Отличаются от обычных таблиц БД тем, что время хранения строк в них ограничено концом либо транзакции, либо сеанса связи с СУБД — по выбору разработчика БД. Описания же таких таблиц (метаданные) хранятся в словаре-справочнике БД на общих основаниях с описаниями обычных таблиц, то есть вплоть до выдачи команды DROP TABLE . Эти свойства объясняют выбор фирмой Oracle названия: GLOBAL TEMPORARY в отличие от таблиц LOCAL TEMPORARY , имеющихся со времен SQL-92 (но не в Oracle), полный жизненный цикл которых ограничен программным блоком.

Пример создания таблицы с временем хранения строк, ограниченным транзакцией:

Упражнение. Как изменится результат команды CREATE выше, если в формулировке SELECT опустить фразу WHERE ?

Примеры создания таблиц с явно указанными временами хранения строк — до конца текущего сеанса или же до конца текущей транзакции:

ON COMMIT DELETE ROWS не требует явного указания, так как подразумевается по умолчанию.

Если не считать "короткого" времени жизни строк, по своим потребительским свойствам таблицы с временным хранением строк почти не отличаются от обычных. Например, для них можно строить индекс (напомним: ведь их описание хранится постоянно).

Таблицы обоих видов предоставляют каждому сеансу собственное множество строк, независимое от строк, заведенных в других сеансах (для таблиц, где время хранения строк ограничено сеансом, это неочевидно). Однако выполнение операций DDL с такими таблицами СУБД по понятным причинам увязывает с наличием в них строк (собственных) в других сеансах. Так, построить индекс ( CREATE INDEX ) удастся только, если в данный момент другой сеанс не завел в таблице собственные строки. Таким образом, косвенная связь содержимого таких таблиц в разных сеансах все-таки имеется.

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

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

Применение индексов представляет собой компромисс между ускорением получения результатов запросов и замедлением обновлений и вставок данных. Первая часть этого компромисса – ускорение запросов – довольно очевидна: если поиск выполняется по отсортированному индексу вместо полного сканирования всей таблиц, то запрос проходит намного быстрее. Но всякий раз, когда вы обновляете, вставляете или удаляете строку таблицы с индексами, индексы также должны быть обновлены соответствующим образом. То есть такие операции на таблицах с индексами обходятся дороже.

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

Если только вам не нужно обращаться к большинству сток таблицы, индексированные запросы обеспечивают более быстрое получение результатов, чем запросы, не использующие индексы. Не существует ограничений на количество индексов, которые могут относиться к одной таблице Oracle, но, как упоминалось ранее, от их количества зависит производительность. Индекс полностью прозрачен для пользователя – т.е. оператор SQL пользователя не должен изменяться в результате создания индексов. Однако разработчикам приложений для построения эффективных запросов следует хорошо представлять себе , что такое индексы и как они работают.

Индексы могут относиться к нескольким типам, наиболее важные из которых перечислены ниже:

Уникальные и неуникальные индексы. Уникальные индексы основаны на уникальном столбце – обычно вроде номера карточки социального страхования сотрудника. Хотя уникальные индексы можно создавать явно, Oracle не рекомендует это делать. Вместо этого следует использовать уникальные ограничения. Когда накладывается ограничение уникальности на столбец таблицы, Oracle автоматически создает уникальные индексы по этим столбцам.
Первичные и вторичные индексы. Первичные индексы – это уникальные индексы в таблице, которые всегда должны иметь какое-то значение и не могут быть равны null. Вторичные индексы – это прочие индексы таблицы, которые могут и не быть уникальными.
Составные индексы – индексы, содержащие два или более столбца из одной и той же таблицы. Они также известны как сцепленные индексы (concatenated index). Составные индексы особенно полезны для обеспечения уникальности сочетания столбцов таблицы в тех случаях, когда нет уникального столбца, однозначно идентифицирующего строку.

Руководство по созданию индексов

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

Индекс имеет смысл, если нужно обеспечить доступ одновременно не более чем к 4-5% данных таблицы. Альтернативной использования индекса для доступа к данным строки является полное последовательное чтение таблицы от начала до конца, что называется полным сканированием таблицы. Полное сканирование таблицы больше подходит для запросов, которые требуют извлечения большего процента данных таблицы. Помните, что применение индексов для извлечения строк требует двух операций чтения: индекса и затем таблицы.
Избегайте создания индексов для сравнительно небольших таблиц. Для таких таблиц больше подходит полное сканирование. В случае маленьких таблиц нет необходимости в хранении данных и таблиц, и индексов.
Создавайте первичные ключи для всех таблиц. При назначении столбца в качестве первичного колюча Oracle автоматически создаст индекс по этому столбцу.
Индексируйте столбцы, участвующие в многотабличных операциях соединения.
Индексируйте столбцы, которые часто используются в конструкциях WHERE.
Индексируйте столбцы, участвующие в операциях ORDER BY и GROUP BY или других операциях, таких как UNION и DISTINCT, включающих сортировку. Поскольку индексы уже отсортированы, объем работы по выполнению необходимой сортировки данных для упомянутых операций будет существенно сокращен.
Столбцы, стоящие из длинно-символьных строк, обычно плохие кандидаты на индексацию.
Столбцы, которые часто обновляются, в идеале не должны быть индексированы из-за связанных с этим накладных расходов.
Индексируйте таблицы в которых мало строк имеют одинаковые значения.
Сохраняйте количество индексов небольшим.
Составные индексы могут понадобиться там, где одностолбцовые значения сами по себе не уникальны. В составных индексах первым столбцом ключа должен быть столбец в котором количество строк с одинаковым значением минимально.

Всегда помните золотое правило индексации таблиц: индекс таблицы должен быть основан на типах запросов, которые будут выполняться над столбцами этой таблицы. На таблице можно создавать более одного индекса: например, можно создать индекс на столбце X, или столбце Y, или обоих сразу, а также один составной индекс на обоих столбцах. Принимая правильное решение относительно того, какие индексы следует создавать, подумайте о наиболее часто используемых типах запросов данных таблицы.

Схемы индексации Oracle

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

(B*tree)

В реализации индексов на основе B-деревьев используется концепция сбалансированного (на что указывает буква ‘B’ (balanced)) дерева поиска в качестве основы структуры индекса. В Oracle имеется собственный вариант B-дерева. Это обычные индексы, создаваемые по умолчанию, когда вы применяете оператора CREATE INDEX.

Индексы на основе B-деревьев структурированы в форме обратного дерева, где блоки верхнего уровня называются блоками ветвей (branch blocks), а блоки нижнего уровня – листовыми блоками (leaf blocks). В иерархии узлов все узлы кроме вершины, или корневого узла, имеют родительский узел и могут иметь ноль или более дочерних узлов. Если глубина древовидной структуры , т.е. количество уровней, одинакова от каждого листового блока до корневого узла, то такое дерево называется сбалансированным, или B-деревом.

B-деревья автоматически поддерживают необходимый уровень индекса по размеру таблицы. B-деревья также гарантируют, что индексные блоки всегда будут заполнены не меньше, чем наполовину, и менее, чем на 100%. B-деревья допускают операции выборки, вставки и удаления с очень небольшим количеством операций ввода-вывода на один оператор. Большинство B-деревьев имеет всего три и менее уровней. При использовании B-дерева нужно читать только блоки B-дерева, так что количество операций ввода-вывода будет ограничено числом уровней B-дерева (скажем, тремя) плюс две операции ввода-вывода на выполнение обновления или удаления (одна для чтения и одна для записи). Для выполнения поиска по B-дереву понадоисят всего три или менее обращений к диску.

Реализация B-дерева от Oracle – всегда сохраняет дерево сбалансированным. Листовые блоки содержат по два элемента: индексированные значения столбца и соответствующий идентификатор ROWID для строки, которая содержит это значение столбца. ROWID – уникальный указатель Oracle, идентифицирующий физическое местоположение строки и обеспечивающий самый быстрый способ доступа к строке в базе данных Oracle. Сканирование индекса быстро дает ROWID строки, и отсюда можно быстро получить к ней доступ непосредственно. Если запрос нуждается лишь в значении индексированного столбца, то конечно, последний шаг исключается, поскольку извлекать дополнительные данные, кроме прочитанных из индекса, не потребуется.

Оценка размера индекса

Для оценки размера нового индекса можно использовать пакет DBMS_SPACE. Процедуре CREATE_INDEX_COST этого пакета потребуется передать оператор DDL, создающий индекс, в качестве атрибута.

Обратите внимание на отличие между атрибутами, касающимися размера, в процедуре CREATE_INDEX_COST:

Used_bytes показывает количество байт, которыми представлены данные индекса;
Alloc_bytes показывает количество байт, которое займет индекс в табличном пространстве после его создания.

Создание индекса

Индекс создается с помощью оператора CREATE INDEX

По умолчанию Oracle допускает дублирование значения в столбцах индекса, которые также называются ключевыми столбцами. Однако можно специфицировать уникальный индекс, что исключит дублирование значений столбца в нескольких строках.

Для создания уникального индекса служит оператор CREATE UNIQUE INDEX.

Специальные типы индексов

Нормальный или типовой индекс, который создается в базе данных, называется индексом кучи (heap index), или неупорядоченным индексом. Oracle также предоставляет несколько специальных типов индексов для специфических нужд.

Битовые индексы (bitmap indexes)

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

Битовые индексы состоят из битового потока (единиц и нулей) для каждого столбца индекса. Битовые индексы очень компактны по сравнению с нормальными индексами на основе B-деревьев.

Индексы B-деревьев	Битовые индексы
Хороши для данных с высокой кардинальностью	Хороши для данных с низкой кардинальностью
Хороши для баз данных OLTP	Хороши для приложений хранилищ данных OLAP
Занимают много места	Используют, относительно мало места
Легко обновляются	Трудно обновляются

Для создания битового индекса используется оператор

Иногда можно наблюдать значительное повышение производительности при замене обычных индексов B-дерева на битовые в некоторых очень крупных таблицах. Однако каждый элемент битового индекса открывает огромное количество строк в таблице, так что когда данные обновляются,вставляются или удаляются из таблицы, то необходимые обновления битового индекса очень велики., и сам индекс может существенно увеличиться в размере. Единственный способ обойти это увеличение размера индекса с последующим падением производительности заключается в регулярной его перестройке. Битовый индекс – не слишком разумная альтернатива для таблиц, подвергающихся большому количеству вставок, удалений и обновлений.

Индексы с реверсированным ключом

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

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

Индексы со сжатым ключом

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

Приведенный выше оператор сжимает все дублированные вхождения индексированного ключа в листовом блоке индекса (на уровне 1).

Индексы на основе функций

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

Ниже показано, как создать индекс на основе функции LOWER

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

Секционированные индексы

Секционированные индексы используются для индексации секционированных таблиц. Oracle предлагает два типа индексов для таких таблиц: локальные и глобальные.

Существенное различие между ними заключается в том, что локальные индексы основаны на разделах таблицы, по которой они созданы. Если таблица секционирована на 12 разделов по диапазонам дат, то индексы также будут распределены по тем же 12 разделам. Другими словами, между разделами индексов и разделами таблиц существует соответствие «один к одному». Такого соответствия нет между глобальными индексами и разделами таблицы, потому что глобальные индексы секционируются независимо от базовых таблиц.

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

Глобальные индексы

Глобальные индексы на секционированных таблицах могут быть как секционированными, так и несекционированными. Глобальные несекционированные индексы подобны обычным индексам Oracle для несекционированных таблиц. Для создания таких индексов применяется обычный синтаксис CREATE INDEX.

Ниже приведен пример глобального индекса на таблице ticket_sales:

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

Давайте в качестве примера воспользуемся таблицей ticket_sales, чтобы разобраться, почему это так. Предположим, что вы ежеквартально уничтожаете самый старый раздел, чтобы освободить место для нового раздела, в который поступят данные за новый квартал. Когда уничтожается раздел, относящийся к таблице ticket_sales, глобальные индексы могут стать недействительными, потому что часть данных, на которые они указывают, перестают существовать. Чтобы предотвратить такое объявление недействительным индекса из-за уничтожения раздела, необходимо использовать опцию UPDATE GLOBAL INDEXES вместе с оператором DROP PARTITION:

Если не включить оператор UPDATE GLOBAL INDEXES, то все глобальные индексы станут недействительными. Опцию UPDATE GLOBAL INDEXES можно также использовать при добавлении, объединении, обмене, слиянии, перемещении, разделении или усечении секционированных таблиц. Разумеется, с помощью ALTER INDEX..REBUILD можно перестраивать любой индекс, который становится недействительным, но эта опция также требует дополнительных затрат времени и обслуживания.

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

Локальные индексы

Локально секционированные индексы, в отличие от глобально секционированных индексов, имею отношение «один к одному» с разделами таблицы. Локально секционированные индексы можно создавать в соответствии с разделами и даже подразделами. База данных конструирует индекс таким образом, чтобы он был секционирован так же, как и его таблица. При каждой модификации раздела таблицы база автоматически сопровождает это соответствующей модификацией раздела индекса. Это, наверное, самое большое преимущество использования локально секционированных индексов – Oracle автоматически перестраивает их всегда, когда уничтожается раздел или над ним выполняется какая-то другая операция DDL.

Ниже приведен простой пример создания локально секционированного индекса на секционированной таблице:

Невидимые индексы

По умолчанию оптимизатор «видит» все индексы. Тем не менее, можно создать невидимый индекс, который оптимизатор не обнаруживает и не принимает во внимание при создании плана выполнения оператора. Невидимый индекс можно применять в качестве временного индекса для определенных операций или его тестирования перед тем, как сделать его «официальным». Вдобавок, иногда объявления индекса невидимым можно использовать в качестве альтернативы уничтожению индекса или объявлению его недоступным. Сделать индекс невидимым можно временно, чтобы протестировать эффект от его уничтожения.

База данных поддерживает невидимый индекс точно так же, как и нормальный (видимый) индекс. После объявления индекса невидимым, его и все прочие невидимые индексы можно сделать вновь видимым для оптимизатора, установив значение параметра optimizer_use_invisible_index равным TRUE на уровне сеанса или всей системы. Значением этого параметра по умолчанию является FALSE, а это означает, что оптимизатор по умолчанию не может использовать невидимые индексы.

Создание невидимого индекса.

Чтобы сделать индекс невидимым, к оператору CRETE INDEX нужно добавить конструкцию INVISIBLE.

С помощью команды ALTER INDEX можно превратить существующий индекс в невидимый.

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

Основная особенность NULLа заключается в том, что он не равен ничему, даже другому NULLу. С ним нельзя сравнить какое-либо значение с помощью любых операторов: = , < , > , like … Даже выражение NULL != NULL не будет истинным, ведь нельзя однозначно сравнить одну неизвестность с другой. Кстати, ложным это выражение тоже не будет, потому что при вычислении условий Oracle не ограничивается состояниями ИСТИНА и ЛОЖЬ . Из-за наличия элемента неопределённости в виде NULLа существует ещё одно состояние — НЕИЗВЕСТНО .

Таким образом, Oracle оперирует не двухзначной, а трёхзначной логикой. Эту особенность заложил в свою реляционную теорию дедушка Кодд, а Oracle, являясь реляционной СУБД, полностью следует его заветам. Чтобы не медитировать над “странными” результатами запросов, разработчику необходимо знать таблицу истинности трёхзначной логики. Ознакомиться с ней можно, например, на английской википедии: Three-valued_logic.

Для удобства сделаем процедуру, печатающую состояние булевого параметра:

Привычные операторы сравнения пасуют перед NULLом:

Существуют специальные операторы IS NULL и IS NOT NULL , которые позволяют производить сравнения с NULLами. IS NULL вернёт истину, если операнд имеет значение NULL и ложь, если он им не является.

Соответственно, IS NOT NULL действует наоборот: вернёт истину, если значение операнда отлично от NULLа и ложь, если он является NULLом:

Кроме того, есть пара исключений из правил, касающихся сравнений с отсутствующими значениями. Во-первых, — это функция DECODE , которая считает два NULLа эквивалентными друг другу. Во-вторых, — это составные индексы: если два ключа содержат пустые поля, но все их непустые поля равны, то Oracle считает эти два ключа эквивалентными.

DECODE идёт против системы:

Пример с составными индексами находится в параграфе про индексы.

Обычно, состояние НЕИЗВЕСТНО обрабатывается так же, как ЛОЖЬ . Например, если вы выбираете строки из таблицы и вычисление условия x = NULL в предложении WHERE дало результат НЕИЗВЕСТНО , то вы не получите ни одной строки. Однако, есть и отличие: если выражение НЕ(ЛОЖЬ) вернёт истину, то НЕ(НЕИЗВЕСТНО) вернёт НЕИЗВЕСТНО . Логические операторы AND и OR также имеют свои особенности при обработке неизвестного состояния. Конкретика в примере ниже.

В большинстве случаев неизвестный результат обрабатывается как ЛОЖЬ :

Отрицание неизвестности даёт неизвестность:

Оператор OR :

Оператор AND :

Для начала сделаем несколько предварительных действий. Для тестов создадим таблицу T с одним числовым столбцом A и четырьмя строками: 1, 2, 3 и NULL

Включим трассировку запроса (для этого надо обладать ролью PLUSTRACE ).
В листингах от трассировки оставлена только часть filter, чтобы показать, во что разворачиваются указанные в запросе условия.

Предварительные действия закончены, давайте теперь поработаем с операторами. Попробуем выбрать все записи, которые входят в набор (1, 2, NULL) :

Как видим, строка с NULLом не выбралась. Произошло это из-за того, что вычисление предиката "A"=TO_NUMBER(NULL) вернуло состояние НЕИЗВЕСТНО . Для того, чтобы включить NULLы в результат запроса, придётся указать это явно:

Попробуем теперь с NOT IN :

Вообще ни одной записи! Давайте разберёмся, почему тройка не попала в результаты запроса. Посчитаем вручную фильтр, который применила СУБД, для случая A=3 :

Из-за особенностей трёхзначной логики NOT IN вообще не дружит с NULLами: как только NULL попал в условия отбора, данных не ждите.

Здесь Oracle отходит от стандарта ANSI SQL и провозглашает эквивалентность NULLа и пустой строки. Это, пожалуй, одна из наиболее спорных фич, которая время от времени рождает многостраничные обсуждения с переходом на личности, поливанием друг друга фекалиями и прочими непременными атрибутами жёстких споров. Судя по документации, Oracle и сам бы не прочь изменить эту ситуацию (там сказано, что хоть сейчас пустая строка и обрабатывается как NULL, в будущих релизах это может измениться), но на сегодняшний день под эту СУБД написано такое колоссальное количество кода, что взять и поменять поведение системы вряд ли реально. Тем более, говорить об этом они начали как минимум с седьмой версии СУБД (1992-1996 годы), а сейчас уже двенадцатая на подходе.

NULL и пустая строка эквивалентны:

непременный атрибут жёсткого спора:

Если последовать завету классика и посмотреть в корень, то причину эквивалентности пуcтой строки и NULLа можно найти в формате хранения varchar`ов и NULLов внутри блоков данных. Oracle хранит строки таблицы в структуре, состоящей из заголовка, за которым следуют столбцы данных. Каждый столбец представлен двумя полями: длина данных в столбце (1 или 3 байта) и, собственно, сами данные. Если varchar2 имеет нулевую длину, то в поле с данными писать нечего, оно не занимает ни байта, а в поле с длиной записывается специальное значение 0xFF , обозначающее отсутствие данных. NULL представлен точно так же: поле с данными отсутствует, а в поле с длиной записывается 0xFF . Разработчики Оракла могли бы, конечно, разделить эти два состояния, но так уж издревле у них повелось.

Лично мне эквивалентность пустой строки и NULLа кажется вполне естественной и логичной. Само название «пустая строка» подразумавает отсутствие значения, пустоту, дырку от бублика. NULL, в общем-то, обозначает то же самое. Но здесь есть неприятное следствие: если про пустую строку можно с уверенностью сказать, что её длина равна нулю, то длина NULLа никак не определена. Поэтому, выражение length('') вернёт вам NULL, а не ноль, как вы, очевидно, ожидали. Ещё одна проблема: нельзя сравнивать с пустой строкой. Выражение val = '' вернёт состояние НЕИЗВЕСТНО , так как, по сути, эквивалентно val = NULL .

Длина пустой строки не определена:

Сравнение с пустой строкой невозможно:

Критики подхода, предлагаемого Ораклом, говорят о том, что пустая строка не обязательно обозначает неизвестность. Например, менеджер по продажам заполняет карточку клиента. Он может указать его контактный телефон (555-123456), может указать, что он неизвестен (NULL), а может и указать, что контактный телефон отсутствует (пустая строка). С оракловым способом хранения пустых строк реализовать последний вариант будет проблемно. С точки зрения семантики довод правильный, но у меня на него всегда возникает вопрос, полного ответа на который я так и не получил: как менеджер введёт в поле «телефон» пустую строку и как он в дальнейшем отличит его от NULLа? Варианты, конечно, есть, но всё-таки…

Вообще-то, если говорить про PL/SQL, то где-то глубоко внутри его движка пустая строка и NULL различаются. Один из способов увидеть это связан с тем, что ассоциативные коллекции позволяют сохранить элемент с индексом '' (пустая строка), но не позволяют сохранить элемент с индексом NULL:

Использовать такие финты ушами на практике не стоит. Во избежание проблем лучше усвоить правило из доки: пустая строка и NULL в оракле неразличимы.

Этот маленький абзац писался пятничным вечером под пиво, на фоне пятничного РЕН-ТВшного фильма. Переписывать его лень, уж извините.

Задача. У Маши до замужества с Колей было неизвестное количество любовников. Коля знает, что после замужества у Маши был секс с ним, Сашей и Витей. Помогите найти Коле точное количество любовников Маши.

Очевидно, что мы ничем не сможем помочь Коле: неизвестное количество любовников Маши до замужества сводит все расчёты к одному значению — неизвестно. Oracle, хоть и назвался оракулом, в этом вопросе уходит не дальше, чем участники битвы экстрасенсов: он даёт очевидные ответы только на очевидные вопросы. Хотя, надо признать, что Oracle гораздо честнее: в случае с Колей он не будет заниматься психоанализом и сразу скажет: «я не знаю»:

С конкатенацией дела обстоят по другому: вы можете добавить NULL к строке и это её не изменит. Такая вот политика двойных стандартов.

Почти все агрегатные функции, за исключением COUNT (и то не всегда), игнорируют пустые значения при расчётах. Если бы они этого не делали, то первый же залетевший NULL привёл бы результат функции к неизвестному значению. Возьмём для примера функцию SUM , которой необходимо просуммировать ряд (1, 3, null, 2) . Если бы она учитывала пустые значения, то мы бы получили такую последовательность действий:
1 + 3 = 4; 4 + null = null; null + 2 = null .
Вряд ли вас устроит такой расчёт при вычислении агрегатов, ведь вы наверняка не это хотели получить. А какой бы был геморрой с построением хранилищ данных… Бррррр…

Таблица с данными. Используется ниже много раз:

Пустые значения игнорируются агрегатами:

Функция подсчёта количества строк COUNT , если используется в виде COUNT(*) или COUNT(константа) , будет учитывать пустые значения. Однако, если она используется в виде COUNT(выражение) , то пустые значения будут игнорироваться.

с константой:

С выражением:

Также, следует быть осторожным с функциями вроде AVG . Поскольку она проигнорирует пустые значения, результат по полю N будет равен (1+3+2)/3 , а не (1+3+2)/4 . Возможно, такой расчёт среднего вам не нужен. Для решения подобных проблем есть стандартное решение — воспользоваться функцией NVL :

Агрегатные функции возвращают состояние НЕИЗВЕСТНО , если они применяются к пустому набору данных, либо если он состоит только из NULLов. Исключение составляют предназначенные для подсчёта количества строк функции REGR_COUNT и COUNT(выражение) . Они в перечисленных выше случаях вернут ноль.

Набор данных только из NULLов:

Пустой набор данных:

NULL в OLAP

Очень коротко ещё об одной особенности, связанной с агрегатами. В многомерных кубах NULL в результах запроса может означать как отсутствие данных, так и признак группировки по измерению. Самое противное, что на глаз эти две его ипостаси никак не различишь. К счастью, есть специальные функции GROUPING и GROUPING_ID , у которых глаз острее. GROUPING(столбец) вернёт единицу, если NULL в столбце измерения означает признак группировки по этому столбцу и ноль, если там содержится конкретное значение (в частности, NULL). Функция GROUPING_ID — это битовый вектор из GROUPING ов, в этой заметке она точно лишняя.

В общем, такая вот краткая и сумбурная информация про дуализм NULLа в многомерном анализе. Ниже пример использования GROUPING , а за подробностями велкам ту Data Warehousing Guide, глава 21.

Удобная фишка sqlplus: при выводе данных заменяет NULL на указанную строку:

В этом учебном материале вы узнаете, как создавать, переименовывать и удалять индексы (create, rename and drop indexes) в Oracle/PLSQL с синтаксисом и примерами.

Что такое indexes в Oracle?

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

Create INDEX

Синтаксис

Синтаксис для создания индекса в Oracle/PLSQL:

CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];

UNIQUE
Указывает на то, что сочетание значений в индексируемых столбцах должны быть уникальными.
index_name
Наименование индекса.
table_name
Имя таблицы для которой создается индекс.
column1 , column2 , . column_n
Столбцы для использования в индексе.
COMPUTE STATISTICS
Это послание Oracle для сбора статистических данных во время создания индекса. Статистические данные затем используются оптимизатором, чтобы выбрать "план выполнения", когда выполняются SQL запросы.

Пример

Рассмотрим пример того, как создать индекс в Oracle/PLSQL.
Например:

В этом примере мы создали index таблицы supplier под названием supplier_idx . Он состоит только из одного поля supplier_name .

Index также можно создать для нескольких полей, как в примере ниже:

При создании index, для сбора статистических данных можно сделать следующим образом:

Создание Function-Based INDEX

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

Синтаксис

Синтаксис для создания function-based index в Oracle/PLSQL:

CREATE [UNIQUE] INDEX index_name
ON table_name (function1, function2, . function_n)
[ COMPUTE STATISTICS ];

UNIQUE
Указывает на то, что сочетание значений в индексируемых столбцах должны быть уникальными.
index_name
Наименование индекса.
table_name
Имя таблицы для которой создается индекс.
function1 , function2 , . function_n
Функции для использования в индексе.
COMPUTE STATISTICS
Это послание Oracle для сбора статистических данных во время создания индекса. Статистические данные затем используются оптимизатором, чтобы выбрать "план выполнения", когда выполняются SQL запросы.

Пример

Рассмотрим на примере того, как создать function-based index в Oracle/PLSQL.
Например:

Читайте также: