Как хранятся строки таблицы в бд oracle если таблица создана как heap organization таблица

Обновлено: 04.07.2024

Индексы 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 можно превратить существующий индекс в невидимый.

Таблицы могут храниться в базе данных несколькими способами. Простейший из них это heap таблица. Таблица может хранить строки различной длины в случайном порядке. Может быть какая-либо корреляция между порядком записи и порядком в котором данные хранятся но на это нельзя рассчитывать. Более сложные структуры таблицы, такие как представленные ниже, могут влиять на порядок и группировку строк или использовать случайное распределение

74

Использование сложных структур таблицы не вляет на SQL. Любой SQL запрос выполненные к таблицам созданным с этими опциями вернёт абсолютно одинаковый результат такой же как будто таблица являются обычными heap таблицами. Но если программист понимает как они работаеют то можно существенно увеличить производительность.

Создание таблицы с указанием столбцов

Для создания таблицы используется следующий синтаксис

CREATE TABLE [schema.]tablename [ORGANIZATION HEAP]

(columnname datatype [DEFAULT expression]

[,columnname datatype [DEFAULT expression]);

Как минимум необходимо указать имя таблицы (по умолчанию таблицы создаются в схеме текущего аккаунта, если вы не указываете другую) и один столбец с типом данных. Практически никто не указывает явно ORGANIZATION HEAP, так как это значение по умолчанию и стандарт SQL. Ключевое слово DEFAULT в определении столбца позволяет указать выражение которое будет использоваться при выполнении команды INSERT если не указано значение для этого столбца.

CREATE TABLE SCOTT.EMP

HIREDATE DATE DEFAULT TRUNC(SYSDATE),

COMM NUMBER(7,2) DEFAULT 0.03);

Создаем таблицу EMP в схеме SCOTT. Этот запрос выполнится если подключен аккаунт SCOTT (и тогда указание схемы необязательно) или другой пользователь у которого есть доступ на создание таблиц в схеме SCOTT. Столбцы

EMPNO число длиной 4 цифры без дробной части. Если будет указана дробная часть при выполнении команды INSERT значение будет округлено к ближайшему целому.

ENAME строка с максимальной длиной в 10 символов

HIREDATE возможно использовать значение любой даты с указанием времени, если время не указано используется значение полночь

SAL предназначен для хранения зарплаты, принимает число длиной 7 цифр. Цифры после семи будут обрезаны а значение округлено

COMM имеет значение по умолчанию 0.03, которое будет использоваться если явно не указанть значение при выполнении команды INSERT

После создания таблицы можно записывать данные и выполнять команду SELECT

75

Обратите внимание что столбцы не указанные при команде INSERT использовали значения указанные в DEFAULT. Если бы не были указаны значения по умолчанию в секции DEFAULT использовалось бы значение NULL. Также обратите внимание что значение SAL было округлено.

The DEFAULT clause can be useful, but it is of limited functionality. You cannot use a subquery to generate the default value: you can only specify literal values or functions.

Создание таблиц с использованием подзапросов

Вместо создания таблиц с нуля и затем вставки строк (как мы только что рассмотрели) таблицы можно создавать используя другие таблицы – с помозью подзапросов. Этот метод позволяет определить таблицу и записать данные используя один запрос. Любой запрос может быть использован как источник для описания столбцов и как набор данных (строк). Синтаксис команды

CREATE TABLE [schema.]tablename AS subquery;

Любой запрос возвращает двумерный набор строк; результат хранится как новая таблица. Просто пример создания таблицы используя подзапрос

create table employees_copy as select * from employees;

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

Рассмотрим более сложный пример

create table emp_dept as select

from employees natural join departments order by dname,ename;

Строки в новой таблице это результат объекдинения двух таблиц-источников, и два столбца изменяют название. Новый столбец SERVICE будет заполнен результатом арифметической операции и получено количество дней с момента приёма на работу. Строки буду вставлены в определённом порядке. Этот порядок не будет управляться последующими DML командами, но предполагая что в схеме HR данные по умолчанию новая таблица будет выглядеть следующим образом

76

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

create table no_emps as select * from employees where 1=2;

Изменение таблицы после создания

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

  • Добавление столбца
    alter table emp add (job_id number);
  • Изменение столбца
    alter table emp modify (commission_pct number(4,2) default 0.05);
  • Удаление столбца
    alter table emp drop column commission_pct;
  • Маркировка столбца как неиспользуемого
    alter table emp set unused column job_id;
  • Переименование столбца
    alter table emp rename column hire_date to recruited;
  • Включение режима только-чтения для таблицы
    alter table emp read only;

Все изменения этих DDL команд содержат встроенный COMMIT. Эти изменения нельзя отменить и они не смогут отработать если имеется активная транзакция к таблице. Также эти операции происходят практически мгновенно за исключением удаления столбца. Удаление столбца может затребовать длительное время так как поскольку удаляется столбец, каждая строка должна быть изменена. Команда SET UNUSED делает столбец недоступным для SQL команд, и часто является более лучшей альтернативой в сочетании с командой

ALTER TABLE tablename DROP UNUSED COLUMNS;

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

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

Удаление и сокращение таблицы (drop и truncate)

Команда TRUNCATE TABLE удаляет вссе строки из таблицы и оставляет определение таблицы. Команда DROP TABLE удаляет также и определение таблицы (саму таблицу вместе с данными). Синтаксис команды

DROP TABLE [schema.]tablename ;

Если схема не указана удалится таблица с этим именем в схеме текущего аккаунта.

SQL не выдает предупреждений перед выполнением команды DROP и как и любая команда DDL содержит встроенный COMMIT. То есть удаление таблицы нельзя отменить. При выполнении определенных условий, удаление может быть отменено используя определенные методики.

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

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

Меня интересует, как повторно использовать пространство в таблице, организованной по индексу (Index Organized Table - IOT) после удаления существенного количества строк.

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

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

Ответ Тома Кайта

Ответ на этот вопрос, на самом деле, достаточно интересный - Oracle8i Release 8.1 позволяет выполнить два новых действия, которые делают ответ интересным:

  • оперативное пересоздание индексов;
  • перенос таблицы.

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

Теперь мы просто удалим примерно половину строк таблицы. Будем удалять строки "через одну".

Итак, у нас в индексе достаточно много удаленных строк (ни один из блоков не стал полностью пустым). Как это "почистить"?

Вот что мы получили - все "вычищено". В качестве теста, можете оставить открытыми другие сеансы, пока выполняется alter table move - просто чтобы убедиться, что таблица доступна для запросов и всех операторов DML.

Комментарий читателя от 23 августа 2001 года

Вы говорили про два метода. Один из них - оперативное пересоздание индекса.

Я попробовал его применить, но ничего не получилось.

Ответ Тома Кайта

Это делается с помощью оператора:

Изменение первичного ключа таблицы, организованной по индексу

Если, скажем, у меня есть организованная по индексу таблица T со столбцами a , b , c , d , причем, столбцы a , b образуют первичный ключ.

Мне же нужно, чтобы первичным ключом были столбцы a , b , c . Нет ли оператора alter table , позволяющего изменить таблицу, организованную по индексу, и добавить еще один столбец в составной первичный ключ?

Ответ Тома Кайта

В Oracle9i можно использовать оперативное пересоздание.

В 8i, придется использовать Create table . as select . , удалить старую таблицу и переименовать новую.

Префикс.

Если, скажем, у меня есть организованная по индексу таблица T со столбцами a , b , c , d . Но теперь первичный ключ образуют столбцы a , b , c , именно в таком порядке.

Я обнаружил, что в большинстве запросов у меня используется условие по столбцам a , c . Будет ли при этом полезен составной ключ по столбцам a , b , c ? По сравнению с составным ключом по столбцам a , c ?

Ответ Тома Кайта

Первичный ключ - это первичный ключ, это его основное свойство.

Если в большинстве запросов обращение к таблице идет по столбцам a , c , то первичный ключ должен быть по столбцам a , c , b

Вопрос вдогонку.

Половина моих запросов - по a , c , а другая половина - по a , b .

Если создать составной первичный ключ по столбцам a , b , c , будет ли он использоваться для всех этих запросов? По a , b запросов немного больше, чем по a , c .

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

Система относится к классу 24x7, и время простоя надо свести к минимуму.

Как мне заморозить упомянутую таблицу? Нет ли оператора " alter table <имя_таблицы> read only " - не могу его найти в 8i. Или исходная табллица будет заморожена оператором create table . as select . ?

Я собираюсь в периоды минимальной нагрузки сделать следующее:

  1. переименовать таблицу в <temp>- чтобы дальнейшие изменения не происходили
  2. create table <исходная таблица> as select from <temp>

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

Ответ Тома Кайта

а в другом скопируйте ее.

Потом в первом сеансе удалите, а новую таблицу переименуйте.

В 8i, если запросы выбирают данные по a , b и a , c , скорее всего, надо создать индексы по:

Отдельно по столбцу " c " потому, что все индексы таблицы, организованной по индексу, и так включают первичный ключ. Рассмотрим пример:

Видите, как удалось выполнить запрос исключительно по индексу? Для получения значения A вообще не пришлось обращаться к таблице, - оно есть в индексе.

Требуется ли пересоздание индекса?

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

Ответ Тома Кайта

Я НЕ ПРОТИВ пересоздания вообще.

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

Я против выполнения действий, про которые не известно, что они:

  • делают систему лучше;
  • не вызывают негативных последствий.

Индексы на основе битовых карт (bitmap indexes) могут потребовать пересоздания после выполнения некоторого количества операторов DML.

Мой текстовый индекс на сайте asktom - я его время от времени пересоздаю, после существенного изменения данных (фактически, он очень похож на bitmap index).

Индексы на основе b*-деревьев - вряд ли их вообще когда-либо стоит пересоздавать (подсказка: почитайте про COALESCE , - дает большинство тех же преимуществ, а работы намного меньше).

Комментарий читателя от 16 июля 2003 года

Я создал таблицу, организованную по индексу, для использования в качестве выпадающего списка:

Меня интересует следующее:

1. По сути, есть два индекса по одному столбцу таблицы. Эффективно ли это, и как бы обойтись одним индексом?

2. Как задать явное имя для таблицы переполнения (OVERFLOW)?

Хотелось бы также узнать ваши рекомендации по повышению производительности.

Ответ Тома Кайта

1) На самом деле, я вижу только ОДИН индекс, - по функции, который вы создали. Другой "индекс" - это, фактически, сама таблица.

2) Сегмент переполнения в этот случае не нужен и нежелателен. Фактически, я бы сказал, что, если вам нужен сегмент переполнения, то вам вряд ли нужна организация таблицы по индексу (бывают, конечно, и исключения).

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

и просто вставлять в нее имя в верхнем регистре, имя и display_yn .

При этом вы получаете:

  • одну лишь таблицу, организованную по индексу;
  • никаких двусмысленностей, потому что сейчас у вас в качестве значения "первичного ключа" таблицы может быть как ' hello ', так и ' Hello '.

Как сервер находит строку в таблице, организованной по индексу

Использует ли сервер значение первичного ключа для быстрого поиска строки в таблице, организованной по индексу? Он же не хранит rowid , как в обычном индексе на основе b-дерева? Не могли бы вы объяснить, какой механизм используется.

Ответ Тома Кайта

Хранится "rowid", но универсальный, а не физический. И он, фактически, включает значение первичного ключа. Рассмотрим пример:

видите, каким большим может быть rowid .

Ключ индекса содержит.

Значит ли это, что в записи индекса по столбцу (вторичного индекса) будет значение ключа, значение первичного ключа и rowid (логический)?

Ответ Тома Кайта

Да. Обратите внимание, как в следующем примере обращение идет только к индексу T_IDX , а к таблице вообще не обращаются. Хотя индекс - только по столбцу Y .

Размер таблицы, организованной по индексу

Я занимаюсь преобразованием некоторых "больших, но тощих" таблиц из более чем 10000 строк в организованные по индексу и обнаружил, что это существенно повышает производительность.

Как вы думаете, имеет ли смысл организовывать по индексу таблицы из менее чем 10000 строк? Даст ли это существенное преимущество по сравнению с обычной таблицей с индексами?

Ответ Тома Кайта

Да, несомненно. При поиске по ключу может потребоваться всего 1/3 операций логического ввода-вывода. Если такой поиск выполняется часто, 100 строк в таблице или 10000 - не важно.

COALESCE или MOVE ONLINE

1) Когда использовать ALTER iot_table COALESCE , а когда - ALTER iot_table MOVE ONLINE ? Есть ли случаи, когда использование COALESCE оправдано?

2) Если по таблице iot_table есть индекс и мы выполняем MOVE ONLINE , надо ли пересоздавать этот индекс?

Ответ Тома Кайта

1) Используйте coalesce для "сжатия" таблицы, организованной по индексу.

Используйте move для ее переноса. При этом происходит полное пересоздание и требуется свободное пространство размером с таблицу.

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

Индексы обычной таблицы надо пересоздавать (изменились физические идентификаторы строк), а индексы таблицы, организованной по индексу - нет.

Индекс с большим размером блока

Как одна запись индекса может иметь размер более 4000 байтов при размере блока 2 Кбайта?

Ответ Тома Кайта

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

если alter move успешно сработает, вы получите:

при попытке вставить очень большую строку.

Как можно обычную таблицу сделать организованной по индексу?

Организация таблицы по индексу кажется подходящей в моем случае. У нас в таблице - 6 миллионов строк, обращение к которым всегда идет по одному индексу; в таблице всего пять столбцов, и по 3 создается индекс, так что, организация таблицы по индексу кажется прекрасным решением. Но у меня есть два вопроса.

Как реально "реорганизовать" эту таблицу по индексу? Если создать новую таблицу и выполнить: insert into . select * . при шести миллионах строк, эта операция рано или поздно закончится неудачно (после того, как будут заняты все сегменты отката). Нет ли более интересного способа изменить организацию таблицы? А если нет, что, если я экспортирую таблицу, удалю ее, создам таблицу с таким же именем, но другой организацией. Сработает ли импорт? Нет ли причины (интенсивные вставки/изменения. ), по которой не стоит использовать организацию таблицы по индексу?

И еще вопрос: можно ли секционировать таблицу, организованную по индексу? В этом, вообще, есть смысл?

Ответ Тома Кайта

Все закончится удачно, если задать размер сегментов отката в соответствии с теми действиями, которые надо выполнить (ради бога, я часто с 10 миллионами и больше "широких" строк работаю на своем ноутбуке. На ноутбуке!)

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

Да, в этом есть смысл (но 6 миллионов строк, все равно, это немного)

то окажется, что используется, примерно, аж один блок в сегменте отката :) Вы беспокоитесь о том, что не должно произойти (а в режиме archive log вы можете выполнять это действие в режиме nologging , согласовав с АБД выполнение резервного копирования этих новых данных как можно скорее, и устранить тем самым любые проблемы с журналом повторного выполнения, которые вы могли себе надумать):

и использован был один блок UNDO!

Секционированная по диапазону, организованная по индексу сжатая таблица

В 9iR2 у меня есть таблица из 3 столбцов, с, примерно, миллиардом строк, да еще и быстро растущая. Я хотел бы секционировать ее по диапазону, сжать и организовать по индексу.

Я не слишком многого хочу? :-)

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

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

Ответ Тома Кайта

3 "узких" столбца = замечтаельно для таблицы, организованной по индексу

1,000,000,000 rows = подходящая кандидатура на секционирование

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

Я не знаю ни про какие "нюансы" и потенциальные проблемы, которые не позволяют это делать.

Чтоы удостоверится.

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

Я хочу использовать ОРГАНИЗОВАННУЮ ПО ИНДЕКСУ, СЕКЦИОНИРОВАННУЮ ПО ДИАПАЗОНУ и СЖАТУЮ таблицу, приметрно такую, как создается ниже (я где-то нашел синтаксис для сжатия) [кстати, спасибо за подсказку о "начальных столбцах с повторяющимися значениями"]

Мне на мгновение показалось, если я правильно понял, что вы оправдываете использование секционированной ИЛИ организованной по индексу таблицы, но я хочу и то, и другое, И сжатие, но озабочен вероятностью потери данных и т.п. в долгосрочной перспективе. У нас памяти - вагон, так что время на сжатие меня не беспокоит, хотя, протестировать надо. Меня больше беспокоит повреждение данных и т.п., ПЛЮС если будет выполнен TRUNCATE (как я случайно сделал, пока разбирался, как выполнить усечение для секции) - придется ли мне пересоздавать ВСЮ таблицу или только эту организованную по индексу секцию :-)

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

. (Вопрос про 10g выкинут, поскольку Том на него не ответил, пока. - Прим. В.К. )

Ответ Тома Кайта

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

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

Кучей является таблица без кластеризованного индекса. Для таблиц, сохраненных как куча, может быть создан один или несколько некластеризованных индексов. Данные хранятся в куче без указания порядка. Обычно данные первоначально сохраняются в порядке, в котором строки вставлены в таблицу, но компонент Компонент Database Engine может перемещать данные в куче для более эффективного хранения строк. Поэтому порядок данных нельзя прогнозировать. Чтобы гарантировать порядок строк, возвращаемых из кучи, необходимо использовать предложение ORDER BY . Чтобы указать постоянный логический порядок хранения строк, создайте кластеризованный индекс для таблицы, чтобы таблица не была кучей.

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

Когда следует использовать кучу

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

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

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

Если таблица является кучей и не имеет некластеризованных индексов, требуется прочитать всю таблицу (выполнить сканирование таблицы), чтобы найти любую строку. SQL Server не может выполнить поиск RID непосредственно в куче. Это может быть допустимо, если таблица невелика.

Когда не нужно использовать кучу

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

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

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

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

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

Управление кучами

Чтобы создать кучу, создайте таблицу без кластеризованного индекса. Если в таблице уже содержится кластеризованный индекс, удалите кластеризованный индекс, чтобы преобразовать таблицу в кучу.

Чтобы удалить кучу, создайте кластеризованный индекс в ней.

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

  • Создайте в куче кластеризованный индекс, а затем удалите его.
  • Используйте команду ALTER TABLE . REBUILD для перестроения кучи.

Создание или удаление кластеризованных индексов требует перезаписи всей таблицы. Если у таблицы есть некластеризованные индексы, то все они должны быть созданы повторно при каждом изменении кластеризованного индекса. Таким образом, для перехода с кучи на кластеризованный индекс и обратно может потребоваться продолжительное время и дополнительное место на диске — для переупорядочения данных в базе данных tempdb.

Структуры кучи

Кучей является таблица без кластеризованного индекса. Для каждой кучи существует одна строка в представлении sys.partitionsс index_id = 0 для каждой секции, используемой кучей. По умолчанию у кучи есть одна секция. Если куча имеет несколько секций, каждая из них имеет структуру кучи, содержащую данные для этой определенной секции. Например, если у кучи четыре секции, имеются четыре структуры кучи, по одной на каждую секцию.

В зависимости от типов данных в куче, каждая структура кучи имеет одну или несколько единиц распределения для хранения и управления данными определенной секции. У каждой кучи есть не менее одной единицы распределения IN_ROW_DATA на каждую секцию. У кучи также будет одна единица распределения LOB_DATA на каждую секцию, если в этой секции есть столбцы больших объектов (LOB). Кроме того, для хранения строк переменной длины, превышающих ограничение на размер строки, равное 8060 байтам, для каждой секции требуется одна единица распределения ROW_OVERFLOW_DATA .

Системное представление sys.system_internals_allocation_units зарезервировано только для внутреннего использования SQL Server. Совместимость с будущими версиями не гарантируется.

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

На следующей иллюстрации демонстрируется, как Компонент SQL Server Database Engine использует IAM-страницы для получения строк данных из кучи с одной секцией.

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