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

Функция
 

Определение оператора создания индекса на столбец таблицы.

Спецификация
   
< создание индекса >::=
< неименованный индекс >::=
< именованный индекс >::=
< функциональный индекс >::=
< номер файла >::=
< функция >::=
UPPER | LOWER
Синтаксические правила
  1. В качестве < имени таблицы > может выступать только базовая таблица.

    create index model on auto by append;
  2. Опция OR REPLACE заставляет удалять существующий в БД индекс и создавать его под тем же именем, но с другими параметрами.

  3. Опция IF NOT EXISTS отменяет выполнение оператора, если указанный индекс уже существует в БД.

  4. Одновременное использование опций IF NOT EXISTS и OR REPLACE запрещено.

  5. Максимальное количество столбцов в составном индексе равно 31.

  6. Максимальное количество составных индексов в таблице – 100, максимальное количество составных индексов для одного столбца – 10.

  7. Для таблиц с одним индексным файлом по умолчанию индекс строится в данном индексном файле.

  8. Для таблиц, имеющих более одного индексного файла, по умолчанию индекс строится во втором индексном файле.

  9. Опция INDEXFILE задает номер основного индексного файла, где должен храниться создаваемый индекс. Если индекс не может полностью поместиться в этот файл, то он будет размещён (полностью или частично) в другом доступном индексном файле.

    create index "Основные характеристики" on auto (model, cylnders, weight, color) indexfile 3;
  10. < Номер файла > не должен превышать количества зарезервированных при создании таблицы индексных файлов.

  11. Длина индексируемого столбца (столбцов) не должна превышать 1024 байт.

    Примечание

    Для каждого индексируемого столбца переменной длины к длине столбца прибавляется 2 байта (длина).

  12. Опция BY APPEND задает режим создания индекса не через сортировку данных (как делается по умолчанию), а через последовательное добавление элементов.

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

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

    Примечание

    Опцию рекомендуется использовать в случае отсутствия большого свободного дискового пространства, появления какой-либо ошибки сортировки или слишком большого времени сортировки.

Общие правила
  1. Команда создает простые (не фразовые) индексы. Создание фразовых индексов описано в документе «Полнотекстовый поиск в базе данных», пункт «Создание фразового индекса».

  2. Создавать индекс может владелец таблицы либо пользователь, получивший привилегию INDEX на указанную таблицу.

  3. Если < именованный индекс > создается для одного столбца, то созданный индекс является именованным одностолбцовым индексом.

  4. Если < именованный индекс > создается для нескольких столбцов, то созданный индекс является именованным многостолбцовым (составным) индексом.

  5. Опция UNIQUE задает ограничение целостности UNIQUE для создаваемого индекса. Указание опции UNIQUE эквивалентно выполнению последовательности команд:

    • в случае создания неименованного индекса:

      CREATE INDEX "Имя столбца" ON "Имя таблицы";
      ALTER TABLE "Имя таблицы" ADD UNIQUE ("Имя столбца");
    • в случае создания именованного индекса:

      CREATE INDEX "Имя индекса" ON "Имя таблицы"("Имя столбца"[, ...]);
      ALTER TABLE "Имя таблицы" ADD UNIQUE ("Имя столбца"[, ...]);
    • в случае создания функционального индекса опция UNIQUE неприменима.

  6. Конструкция для отмены ограничения целостности UNIQUE отсутствует. Его можно снять вручную:

    • в случае неименованного индекса:

      ALTER TABLE "Имя таблицы" DROP UNIQUE ("Имя столбца");
      DROP INDEX "Имя столбца" ON "Имя таблицы";
    • в случае именованного индекса:

      ALTER TABLE "Имя таблицы" DROP UNIQUE ("Имя столбца"[, ...]);
      DROP INDEX "Имя индекса" ON "Имя таблицы";

    Примечание

    Отдельно поданная команда удаления индекса

    DROP INDEX "Имя столбца" ON "Имя таблицы";

    или

    DROP INDEX "Имя индекса" ON "Имя таблицы";

    (без предварительной команды ALTER TABLE …) не отменяет ограничение целостности UNIQUE.

  7. Если указана опция UNIQUE, а столбец (столбцы) содержит неуникальные данные, то индекс не создается.

  8. При определении таблиц с одностолбцовыми (многостолбцовыми) ключами создание и именование индексов выполняется автоматически (исключая столбцы с модификатором AUTOROWID).

  9. Имя автоматически созданного индекса имеет вид:

    Index #nnnnnnnnnn#

    где nnnnnnnnnnROWID записи для индекса/ключа в таблице $$$ATTRI.

  10. Индексы удаляются автоматически при удалении ключей.

  11. Создание индекса разрешено для пустых таблиц.

  12. Запрещено создание составных, простых именованных и функциональных индексов для системных таблиц.

  13. Запрещено создание простых и функциональных индексов на столбцы $$$S14, $$$S24, $$$S35 (столбцы системных таблиц $$$SYSRL, $$$ATTRI, $$$USR соответственно).

  14. Для столбцов типа PRIMARY KEY, FOREIGN KEY и UNIQUE индекс создается автоматически всегда.

  15. Для столбцов типа AUTOROWID создавать индекс не имеет смысла, т.к. записи выбираются напрямую по значению столбца.

  16. Составные индексы применяются при обработке < предиката сравнения > (=, <  >,  >=,  >, < =, < ), < интервального предиката > (BETWEEN), < предиката подобия > (LIKE), < предиката неопределенного значения > (NULL), < предиката вхождения > (IN).

    Примечание

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

  17. Функциональные индексы применяются при обработке < предиката сравнения > типа < операция > < константа >, где в качестве < операции > допускаются следующие сравнения: < , >,=,<  >, LIKE, IS NULL, ...), за исключением 'NOT LIKE', 'NOT SIMILAR', 'NOT NULL', 'NOT BETWEEN', 'NOT IN', 'NOT MATCH', 'NAN' и 'NOT NAN'.

    Тестовые данные:
    create or replace table test(id int, j int, ch char(10));
    insert into test values (1,1,'aAa');
    insert into test values (2,2,'abA');
    insert into test values (3,3,'aaA');
    insert into test values (4,4,'aAaA');
    insert into test values (5,5,'a');
    insert into test values (6,6,'AA');
    insert into test values (7,7,NULL);
    create index "AAA" on test(upper(ch));
    
    select rowid, test.* from test where upper(ch)='AAA';
    ROWID       ID          J           CH
     -----       --          -           --
    |          1|          1|          1|aAa       |
    |          3|          3|          3|aaA       |
    
    select rowid, test.* from test where upper(ch) >'AAA';
    ROWID       ID          J           CH
     -----       --          -           --
    |          2|          2|          2|abA       |
    |          4|          4|          4|aAaA      |
    
    select rowid, test.* from test where upper(ch)< 'AAA';
    ROWID       ID          J           CH
     -----       --          -           --
    |          5|          5|          5|a         |
    |          6|          6|          6|AA        |
    
    select rowid, test.* from test where upper(ch) is NULL;
    ROWID       ID          J           CH
     -----       --          -           --
    |          7|          7|          7|          |
    
    select rowid, test.* from test where upper(ch) is not NULL;
    
    ROWID       ID          J           CH
     -----       --          -           --
    |          1|          1|          1|aAa       |
    |          2|          2|          2|abA       |
    |          3|          3|          3|aaA       |
    |          4|          4|          4|aAaA      |
    |          5|          5|          5|a         |
    |          6|          6|          6|AA        |
    
    select rowid, test.* from test where upper(ch) like '_B_';
    
    ROWID       ID          J           CH
     -----       --          -           --
    |          2|          2|          2|abA       |
  18. Функциональные индексы не используются ни для сортировки, ни для вычисления соединений.