Индексы оракл. Обзор индексов Oracle

CREATE INDEX . The following statement creates an index named emp_ename for the ename column of the emp table:

CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75);

Notice that several storage settings and a tablespace are explicitly specified for the index. If you do not specify storage options (such as INITIAL and NEXT) for an index, the default storage options of the default or specified tablespace are automatically used.

Creating a Unique Index Explicitly

Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values.

Create a new temporary tablespace using the CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE statement.

Use the TEMPORARY TABLESPACE option of the ALTER USER statement to make this your new temporary tablespace.

Create the index using the CREATE INDEX statement.

Drop this tablespace using the DROP TABLESPACE statement. Then use the ALTER USER statement to reset your temporary tablespace to your original temporary tablespace.

Using this procedure can avoid the problem of expanding your usual, and usually shared, temporary tablespace to an unreasonably large size that might affect future performance.

Creating an Index Online

You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index build is taking place, but DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an index online.

The following statements illustrate online index build operations:

CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;

Keep in mind that the time that it takes on online index build to complete is proportional to the size of the table and the number of concurrently executing DML statements. Therefore, it is best to start online index builds when DML activity is low.

Creating a Function-Based Index

Function-based indexes facilitate queries that qualify a value returned by a function or expression. The value of the function or expression is precomputed and stored in the index.

In addition to the prerequisites for creating a conventional index, if the index is based on user-defined functions, then those functions must be marked DETERMINISTIC . Also, you just have the EXECUTE object privilege on any user-defined function(s) used in the function-based index if those functions are owned by another user.

Additionally, to use a function-based index:

The table must be analyzed after the index is created.

The query must be guaranteed not to need any NULL values from the indexed expression, since NULL values are not stored in indexes.

CREATE INDEX stores the timestamp of the most recent function used in the function-based index. This timestamp is updated when the index is validated. When performing tablespace point-in-time recovery of a function-based index, if the timestamp on the most recent function used in the index is newer than the timestamp stored in the index, then the index is marked invalid. You must use the ANALYZE INDEX...VALIDATE STRUCTURE statement to validate this index.

To illustrate a function-based index, consider the following statement that defines a function-based index (area_index) defined on the function area(geo) :

CREATE INDEX area_index ON rivers (area(geo));

In the following SQL statement, when area(geo) is referenced in the WHERE clause, the optimizer considers using the index area_index .

SELECT id, geo, area(geo), desc FROM rivers WHERE Area(geo) >5000;

Table owners should have EXECUTE privileges on the functions used in function-based indexes.

Because a function-based index depends upon any function it is using, it can be invalidated when a function changes. If the function is valid, you can use an ALTER INDEX...ENABLE statement to enable a function-based index that has been disabled. The ALTER INDEX...DISABLE statement lets you disable the use of a function-based index. Consider doing this if you are working on the body of the function.

An alternative to creating a function-based index is to add a virtual column to the target table and index the virtual column. See "About Tables" for more information.

Creating a Key-Compressed Index

Creating an index using key compression enables you to eliminate repeated occurrences of key column prefix values.

Key compression breaks an index key into a prefix and a suffix entry. Compression is achieved by sharing the prefix entries among all the suffix entries in an index block. This sharing can lead to huge savings in space, allowing you to store more keys for each index block while improving performance.

Key compression can be useful in the following situations:

You have a non-unique index where ROWID is appended to make the key unique. If you use key compression here, the duplicate key is stored as a prefix entry on the index block without the ROWID . The remaining rows become suffix entries consisting of only the ROWID .

You have a unique multicolumn index.

You enable key compression using the COMPRESS clause. The prefix length (as the number of key columns) can also be specified to identify how the key columns are broken into a prefix and suffix entry. For example, the following statement compresses duplicate occurrences of a key in the index leaf block:

CREATE INDEX emp_ename ON emp(ename) TABLESPACE users COMPRESS 1;

The COMPRESS clause can also be specified during rebuild. For example, during rebuild you can disable compression as follows:

ALTER INDEX emp_ename REBUILD NOCOMPRESS;

Creating an Invisible Index

Beginning with Release 11g , you can create invisible indexes. An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Making an index invisible is an alternative to making it unusable or dropping it. Using invisible indexes, you can do the following:

Test the removal of an index before dropping it.

Use temporary index structures for certain operations or modules of an application without affecting the overall application.

Unlike unusable indexes, an invisible index is maintained during DML statements.

To create an invisible index, use the SQL statement CREATE INDEX with the INVISIBLE clause. The following statement creates an invisible index named emp_ename for the ename column of the emp table:

CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) INVISIBLE ;

Кэри Миллсап, Hotsos Enterprises, Ltd

[От гл.редактора OM/RE А.Бачина : Публикация этой статьи имеет некую предысторию, которую я вкраце хочу поведать нашим читателям.
В журнале Oracle Magazine (весна 1995) Кери Миллсап (Cary Millsap), Грег Шаллхамер (Craig Shallahamer) и Миша Адлер (Micah Adler) опубликовали в статью "Predicting the Utility of the Nonunique Index." [Millsap и Al 1993 ] ("Когда использовать неуникальный индекс"). Эта статья была переведена на русский язык и опубликована в нашем журнале "Мир Oracle", который выходил еще в бумажном издании. Интернета в нашей стране еще не было (верится с трудом, но чистая правда!), поэтому статья сохранилась лишь в архивах и памяти многих наших читателей, как прекрасный источник правильного подхода к использованию индексов. Все последние годы мне хотелось заново перевести и переопубликовать эту статью, чтобы разработчики и АБД нового поколения познакомились с правильным подходом к этой проблеме. Но когда дело дошло до дела, оказалось, что ни у кого из доступных адресатов не сохранился английский вариант этой статьи. Даже у самого автора, Кери Миллсап. Когда я к нему обратился, он посоветовал перевести и опубликовать новый ее вариант, в котором грустно отметил [4 ] возможное невнимание к первоначальному тексту. Я постарался его в этом разуверить, послал ему scan-копию статьи и обложки журнала... Он был нам благодарен и разрешил переводить и публиковать статьи с сайта компании Hotsos Enterprises, чем мы, естестенно, с благодарностью еще не раз воспользуемся. Спасибо, Кери! ]

===***===***===***===

[От редакции OM/RE: На сайте корпорации Oracle появилась Oracle ACE (http://www.oracle.com/technology/community/oracle_ace/index.html) - "Аллея славы", то есть галерея наиболее прославленных Oracle-авторов,среди которых заслуженное место занимает автор данной статьи Cary Millsap. Из этой "Аллеи славы" взята публикуемая здесь фотография автора статьи. ]

Резюме

Когда следует использовать индекс? Более десяти лет разработчики приложений Oracle использовали простое rule of thumb (правило большого пальца) - эмпирическое правило для приближенных расчётов, чтобы решить, использовать ли неуникальный индекс (non-unique index). Однако, в повседневной работе мы не редко сталкиваемся с проблемами производительности, вызванными использованием этого эмпирического правила. В этой статье я излагаю следующие результаты наших исследований:

• Правило большого пальца ненадежно, если можно получить процентный баланс селективности строк, чтобы определить, действительно ли нужно создавать индекс.
• Индекс может существенно улучшать эффективность запросов к таблице даже только с одной строкой (a one-row table).
• Доминирующим фактором вашего решения, следует ли создать индекс, должна быть селективность блоков , а не селективность строк .
• Вы можете определить селективность блоков, задавая фразу where , используя SQL-запрос, приведенный в этой статье.
• Значения столбцов обычно кластеризированы (сгруппированы) или естественным образом (naturally clustered), или унифицировано (naturally uniform), то есть единообразно. Вы можете использовать эти сведения, чтобы выработать более правильное решение, действительно ли надо создавать индекс.
• Много новых возможностей Oracle упрощают способность хранить данные в физическом порядке, что обеспечивает превосходную производительность.

Когда использовать индекс: Традиционный Совет

В одном или каком-либо другом виде, но стандартная рекомендация, надо ли использовать индекс, по крайней мере, начиная с версии Oracle 5, звучала следующим образом:

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

Рисунок 1 иллюстрирует понятие, когда некий порог в x% действует как точка баланса производительности Oracle в сравнении диапазонного сканирования индекса и полного сканирования таблицы, осуществляемого по путям доступа. Этот график связывает время ответа R (обычно выражаемое в секундах) в пропорции к Pr строк таблицы, которые возвращаются за данную операцию запроса.

Рисунок 1. Время ответа R в секундах как процентная функция Pr возвращаемых строк таблицы. Пунктирная линия при R = 6.75 (красная линия, если вы видите это в цвете) является временем ответа при полном просмотре таблицы. Непрерывная (синяя) линия - время ответа диапазонного сканирования индекса, который возвращает Pr процентов строк данной таблицы.

Время ответа при плане выполнения, возвращающего r строк при полном просмотре таблицы, является примерно постоянным, независимо от того, r - это одна строка или общее количество строк в таблице. Однако, время ответа диапазонного сканирования индекса увеличивается по мере того, как нарастает объем результирующих исходных строк . Процент pr = x - пороговое значение pr , когда время ответа полного просмотра таблицы и диапазонного сканирования индекса сравниваются. При значении pr < x диапазонное сканирование индекса имеет лучшую производительность. При значении pr > x лучшую производительность предоставляет полный просмотр таблицы.

Тем не менее, в этой линии рассуждения имеется большая проблема. Любое правило типа большого пальца в отношении индексов ненадежно, если существует балансовый процент типа x .

Почему правило большого пальца ненадежно

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

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

Наши испытания и практический опыт показали, что позиция 1) является истинной даже для очень маленьких таблиц. Запрос, возвращающий одну строку, более эффективен, когда выполняется с использованием индекса, чем посредством полного просмотра таблицы, даже если таблица содержит только одну строку. Много людей, с которыми мы обсудили это, выразили удивление таким результатом. Этот результат также противоречит вполне конкретной рекомендации Oracle: "малые таблицы не требуют индексов" [Oracle 2001a ]. Малые таблицы могут не требовать наличия индексов, но индексы на малых таблицах могут сделать вашу систему значительно более эффективной и, следовательно, значительно более масштабируемой [2 ].

Итак, мы принимаем позицию 1), но на позиции 2) начинаются большие проблемы. Иногда намного дешевле прочитать 100 % строк таблицы, используя индекс, чем при полном сканировании таблицы.

Пример: Представим таблицу с именем interface, которая занимает (high-water mark - высшая отметка использования пространства) 10,000 блоков. Хотя в своем историческом прошлом таблица interface содержала сотни тысяч строк, сегодня таблица включает только 100 строк. Эти строки произвольно рассеяны по 30 блокам таблицы. Предположим, что таблица имеет первичный ключ на столбце с названием id, на котором, конечно, построен индекс (с именем id_u1). И далее нам надо выполнить следующий запрос:

Select id, date, status from interface i ;

Если этот запрос выполнять посредством полного просмотра таблицы, то потребуется 10,000 LIO-вызовов Oracle. Мы можем слегка переделать этот запрос, чтобы позволить Oracle выполнять его, используя индекс. Если id - числовой столбец и все значения id - неотрицательные целые числа, то следующий запрос выводит желательный набор строк посредством индекса:

Select /*+ index(i id_u1) */ id, date, status from interface i where id> -1 ;

Этот запрос потребует менее 40 LIO-вызовов Oracle. Время ответа составит примерно 10,000/40, то есть в 250 раз лучше при использовании индекса, чем при выборке 100 % строк из таблицы посредством полного ее просмотра.

Существует много разных крючков и загогулин (all sorts of hooks and crooks), которые можно исследовать на этом примере. Например, если бы фраза select содержала только id или count(id) (что может быть получено из информации индекса даже без обращения к сегменту данных), то просмотр по индексу был бы еще быстрее.

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

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

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

Неравномерно эволюционирующий признак x

Упомянутая большая проблема эмпирического правила индексации состоит в том, что нет четкой ясности, какое значение x должно использовать. Если проследить историю рекомендаций для x в документации Oracle, то вы найдете следующее: [3 ]

Положение даже хуже, чем показано в таблице. Если память мне не изменяет, ранний выпуск производственной документации Oracle7 содержал рекомендацию для x как "1-15 процентов". Я был потрясен тем, насколько широк был диапазон. Если же углубиться в этот вопрос, некоторые из моих друзей из Oracle Applications development очень убедительно говорили, что в своих приложениях они часто наблюдали значение x более 40.

Многие люди полагают, что причина, по которой качается (wiggling) x, состоит в том, что Oracle продолжает совершенствовать работу оптимизатора (optimizer). Но это не всеобщая действительная причина. Причина же того, что значение x стал таким движущимся объектом (moving target), в том, что авторы рекомендаций не сумели выявить истинные параметры, которые дают сбалансированное значение.

Критический параметр - это число блоков Oracle ниже высшей точки заполнения (high-water mark) таблицы, которую можно игнорировать при использовании индекса . Путь построения правила создания индекса, которое превзойдет эмпирическое правило большого пальца и которое сделает жизнь более легкой, должен включать вопрос: "Какой план выполнения потребует меньшее число блоков Oracle, которые должны быть просмотрены? "

Для любого источника строк, с более чем одной строкой, индекс во много раз позволяет Вам сократить PIO-вызовы. Число PIO-вызовов для блоков данных, которое игнорируется при задействовании индекса, зависит от следующего:

• Сколько блоков ниже high-water mark таблицы содержат по крайней мере по одной строке, которая удовлетворяла бы фразе where вашего запроса? Если "интересующие" вас строки распределены однородно по всей таблице, то можно выяснить, когда использование индекса неэффективно даже при невероятно "хороших" значениях селективности строк.

Пример: Мы желаем оптимизировать следующий запрос:

select id, date from shipment where flag="x"

• Загруженная таблица shipment содержит 1,000,000 строк, хранимых в 10,000 блоках Oracle. Только 10,000 строк соответствуют критерию flag="x". Поэтому селективность строк на столбце flag со значением x очень "хорошая" - 1 %. Однако, физическое распределение строк в shipment такова, что каждый отдельный блок в таблице содержит ровно одну строку, для который flag="x" . Следовательно, используем ли мы индекс на столбце flag или нет, чтобы удовлетворить этот запрос, мы должны просмотреть все блоки таблицы. Поэтому полный просмотр таблицы будет более эффективен, чем диапазонное сканирование индекса даже при том, что запрос возвращает только 1% строк из таблицы.
• Может ли Oracle выполнять требования фразы select запроса, используя исключительно данные, хранящиеся в индексе? Если да, то индекс может вообще устранить потребность обращения к таблице. Столбцы в индексе - это обычно небольшой поднабор столбцов индексированной таблицы. Следовательно, число листовых блоков в индексе обычно намного меньше, чем число блоков ниже high-water mark в соответствующей таблице. Поэтому сканирование даже всего индекса может быть дешевле, чем просмотр диапазона блоков в таблице.

Притча об индексаторах

Давайте раскроем важность концепции, называемой селективностью блоков с помощью истории. Речь пойдет о …

• Представим себе книгу с названием Brief History of Humanity (Краткая История Человечества ), резюме на 1,000 страницах фактически обо всем, что наша порода сделала, с тех пор как мы обрели способность все это выражать словами. Представим, что из этой большой книги вы заинтересовались сведениями об Александре Великом. Как вы будете искать их? Конечно, через индекс книги.
• Индекс точно сообщит вам, на каких страницах находится информацию об Александре Великом. Вы, вероятно, отметите индекс, а затем пойдете поиском прямого доступа по номеру страницы с "Александр". Когда вы обработаете одну секцию, то вернетесь назад к отмеченной странице индекса, чтобы узнать, куда нужно далее обратиться, чтобы найти дальнейшие сведения. Наконец, вы сделаете еще один заход в индекс, чтобы удостовериться, что истощен список номеров страниц, которые содержат интересующую вас информацию.
• Теперь представьте себе, что в отличие от обычных книг, каждое отдельное слово этой книги находится в индексе. В индексе такой книги вы сможете найти местоположения даже таких слов, как "the" ("<определенный артикль>"). Теперь давайте скажем, что в Brief History of Humanity нас интересует полный список слов, которые следуют за словом "the". Запрашивая слова, которые следуют за словом "the", по индексу мы не сможем найти все, что ищем; для этого мы должны обратиться к фактическому тексту.

Экстраординарная частота слова "the", вероятно, сделает эту работу абсолютно невыполнимой даже при наличии индекса. "Давайте посмотрим, где есть "the"… Ах да, "the" есть на первой странице." Хорошо, что вы отметили первую "the" страницу в индексе. Тогда щелкнете по индексу для первой страницы. Вы расположите слово после первого "the". Потом вы возвращаетесь к индексу, чтобы найти следующую страницу, на которой появляется "the" - это также страница один. Вы будете ходить вперед и назад, пока вы не посетите каждую страницу всех множеств книжных времен. Вы будете щелкать по книге назад и вперед столько много раз, что ее переплет, вероятно, полностью износится.

Теперь представим, что существует Reader"s Digest Large Print for Easier Reading (Справочник Читателя для Большого Издания для более легкого чтения) этой Brief History of Humanity (Краткой Истории Человечества). Далее представим себе, что основная книга напечатана буквами по 72 пункта. Поэтому Brief History of Humanity содержит только по 20-30 слов на странице. И хотя слово "the" достаточно общее и фактически появляется на каждой странице обычной книги, оно уже не достаточно обычно, чтобы появляться на каждой странице справочника Large Print. В этих новых условиях индекс имеет очень большую полезность для нашего небольшого проекта "find the word after the "the"" ("найти слово после "the""), потому что теперь индекс позволяет нам пропускать большее количество страниц.

Это - 72-пунктовый шрифт. Справочник Large Print for Easier Reading для книги Brief History of Humanity содержит намного меньшее количество ссылок, чем к каждой странице стандартного размера.

Разгадка мифа

Параметры, которые влияют на полезность индекса при диапазонном сканировании, при котором требуется rowid-доступ к таблице, следующие:

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

• Когда создатели документации Oracle писали руководство по настройке Oracle6 (Oracle version 6 tuning guide), они, вероятно, использовали таблицы типа dept в схеме scott/tiger в базе данных Oracle с блоками 2КБ. Когда создавалась документация по Oracle7, они, вероятно, протестировали те же самые запросы, что и прежде. Но, вероятно, использовался "new" ("новый") 4КБ размер блока Oracle, который вошел в моду с Oracle7. Так как большие блоки хранили большее число строк, чем прежде, наблюдаемое значение x было ниже. Индексы, очевидно, оказались менее полезными, чем они были в Oracle6. Выявленный порог снизился с 10-15 до 2-4 %.
• Документация по Oracle8i и Oracle9i намного лучше раскрывает тему полезности индексов. Теперь, как общее положение, Oracle использует x = 15 , но упоминается, что значение "varies greatly" ("существенно варьирует"). Кластеризация (clustering) и скорость полного сканирования (full-scan) упоминаются как изменяющиеся параметры, но не упоминаются ни размер блока, ни размер строки как параметры кластеризации [Oracle 2001a ].
• Вы не забыли наших хороших друзей из Oracle Applications development, которые объявляли хорошими результаты при x> 40 ? Почему они были убеждены в значении, так драматично отличном от всего, что говорила официальная документация Oracle? Не трудно понять их точку зрения, если вы подумаете о среде, в которой они находятся. Во-первых, их таблицы имеют огромные (huge) строки. Таблицы многих приложений включают более 200 столбцов в строке. Во-вторых, по разным причинам Oracle Applications являются "little slow" ("немного медленными") в плане восприятия новых технологий, предлагаемые ядром (kernel). С середины 1990-ых они почти исключительно использовали 2KB-блок базы данных. Разумеется, изменение размера блока в больших базах данных Oracle Applications - это огромная работа, не говоря уже о, по-видимому, непреодолимой работе по проверке правильности планов выполнения SQL-предложений. Коль уж так случалось, комбинация больших строк и маленьких блоков привела к наблюдаемому более высокому значению порога x, чем понаблюдениям многих других групп.

Что же теперь?

Мой вам совет:

Забудьте все о правилах индексации типа большого пальца, основанных на процентах.

На самом деле не имеется никакого диапазона процентов, который даст вам надежный результат. Имеются запросы, возвращающие 1 % или меньшее число строк таблицы, которые более эффективно выполняются полным просмотром таблицы, нежели с использованием индекса. И существуют запросы, возвращающие все 100 % строк таблицы, которые более эффективно выполняются посредством индекса. Но если вы настаиваете на выборе значения для x , я рекомендую найти такое значение, которое одновременно является меньше 1% и больше или равно 100%. Так как никакое такое число не существует, я рекомендую, чтобы вы полностью переключили свое внимание в другую сторону от правил индексации типа большого пальца, основанных на процентах.

Технология оптимизации Oracle проделала длинный путь, начиная с внедрения основанного на стоимости (cost-based) оптимизатора Oracle (это было весьма хорошо в Oracle8i ). Все что требуется от Вас - это определить, какие создать индексы. Ядро Oracle только тогда будет использовать созданные вами индексы, когда это эффективно. Но создание индекса, который никогда не будет хорошо использоваться, - только трата и места, и времени . Поэтому вы должны сами решать, создавать индекс или нет? Ответ в селективности блоков.

Селективность блоков

Вы, вероятно, уже знакомы с концепцией селективности строк. Вы можете определить селективность строк данного предиката из фразы where, как число строк, возвращенных предикатом (r), разделенное на общее число строк в таблице (R):

- определение селективности строк (row selectivity)

Селективность блоков можно определять, аналогично задавая в фразе where предикат отношения числа блоков данных, содержащих по крайней мере одну строку, отвечающую условию предиката (b), к общему количеством блоков данных ниже high-water mark (B):

Определение селективности блоков (block selectivity)

Различие между селективностью строк и селективностью блоков весьма существенно, потому что селективность блоков почти всегда хуже - часто много хуже - селективности строк. Ранее на примере таблицы shipment мы видели flag ="x" . Для этого предиката получается селективность строк 1 %, а селективность блоков 100 %.

Вы можете вычислить селективность строк и селективность блоков, используя SQL-скрипт из следующего примера, который мы назвали hds.sql [Holt 2002 ].

1 rem $Header: /usr/local/hotsos/RCS/hds.sql,v 1.8 2002/01/07 18:12:27 hotsos Exp $ 2 rem Copyright (c) 2000-2002 by Hotsos Enterprises, Ltd. All rights reserved. 3 rem Author: [email protected] 4 rem Notes: Hotsos data selectivity using a full table scan for the row count. 5 6 define v_substr7 = "substr(rowid,15,4)//substr(rowid,1,8)" 7 define v_substr8 = "substr(rowid,7,9)" 8 define v_over = "substr(""&_O_RELEASE"",1,1)" 9 10 col dummy new_value v_substr 11 12 set termout off heading on pause off 13 14 select decode(&v_over, "7", "&v_substr7", "&v_substr8") dummy 15 from dual; 16 17 set termout on verify off feedback off pages 10 18 19 accept p_town prompt "TableOwner: " 20 accept p_tname prompt "TableName: " 21 accept p_clst prompt "ColumnList: " 22 accept p_where prompt "WhereClause: " 23 accept p_pgs prompt "PageSize: " 24 25 variable fblks number 26 27 declare 28 tblks number; 29 tbytes number; 30 ublks number; 31 ubytes number; 32 luefid number; 33 luebid number; 34 lublk number; 35 begin 36 sys.dbms_space.unused_space(37 upper("&p_town"), upper("&p_tname"), "TABLE", 38 tblks, tbytes, ublks, ubytes, luefid, luebid, lublk, null 39); 40:fblks:= tblks - ublks; 41 end; 42 / 43 44 col blks form 9,999,999,999 heading "Table blocks below hwm/(B)" just c 45 col nrows form 999,999,999,999 heading "Table rows/(R)" just c new_value v_nrows 46 47 select:fblks blks, count(*) nrows 48 from &p_town..&p_tname; 49 50 col bs form a17 heading "Block selectivity/(pb = b/B)" just c 51 col nblks form 9,999,999,999 heading "Block count/(b)" just c 52 col rs form a17 heading "Row selectivity/(pr = r/R)" just c 53 col nrows form 999,999,999,999 heading "Row count/(r)" just c 54 55 set pause on pause "More: " pages &p_pgs 56 57 select &p_clst, 58 lpad(to_char(count(distinct &v_substr)/:fblks*100,"990.00")//"%",17) as bs, 59 count(distinct &v_substr) nblks, 60 lpad(to_char(count(*)/&v_nrows*100,"990.00")//"%",17) rs, 61 count(*) nrows 62 from &p_town..&p_tname &p_where 63 group by &p_clst 64 order by bs desc;

Использование скрипта hds.sql очевидно. Однако, получение полной информации о распределении данных в таблице может быть очень дорогим. В зависимости от ваших данных, этот запрос может выполняться и минуты, и часы. Это объясняет, почему стоимостной оптимизатор Oracle полагается на хранимую статистику вместо самостоятельного анализа данных, когда вычисляет или утверждает план выполнения. Следующий пример иллюстрирует, как мы используем данные hds.sql.

Пример: система имеет таблицу с именем po.cs_ec_po_items . Наша цель состоит в том, чтобы оптимизировать несколько подопераций запроса, которые во фразе where используют предикат ec_po_id =:vas . Что получится, если мы создадим индекс на столбце ec_po_id ? Мы можем использовать скрипт hds.sql, чтобы получить истинную информацию о распределении данных по различным значениям ec_po_id :

Выходные данные скрипта hds.sql отсортированы по убыванию селективности блоков. Листинг обычно содержит тысячи строк, но все самые плохие данные (worst-case data) - в данном случае представляющие наиболее интересную часть - находятся наверху. Поэтому мы обычно обрываем листинг hds.sql после выдачи одной-двух страниц.

Заметим, что для этой таблицы имеет место превосходная селективность строк для каждого значения ec_po_id . "Самое плохое" значение селективности строк - только 0.54 % . Это означает, что только полпроцента строк таблицы имеет значение ec_po_id = "8" . Однако столбец селективности блоков представляет нам совсем другую историю. Селективность блоков ec_po_id = "8" составляет 63.50%. Это значит, что почти двух третях блоков таблицы содержится по крайней мере по одной строке, для который ec_po_id = "8" .

Должны ли мы создать индекс на ec_po_id ? Можно потратить полдня или более, вычисляя "back of the envelope" ("быстро и легко определяемый") ответ, пытаясь вычислить по формулам затраты плана выполнения. Но оптимизатор Oracle может сделать эту работу за вас. Наиболее точный и, в конечном счете, наименее отнимающий время метод для определения ответа состоит в том, чтобы выполнить тестирование на фактической базе данных Oracle. Лучший способ определить относительные затраты двух планов выполнения состоит в том, чтобы выполнить их на некоих тестовых данных с установкой опции sql_trace=true . Если нужна большая детальность в части, скажем, использования других (не-CPU) механизмов, которых задействует Oracle в течение выполнения запроса, то протрассируйте выполнение с использованием Oracle-события 10046 на уровне 8 [Hotsos 2002 ]. Если нужно большее количество данных о том, почему оптимизатор выбрал такой план, который сам и сделал, то протрассируйте выполнение с Oracle-событием со случаем 10053 [Lewis 2001 ].

Из листинга hds.sql мы узнаем граничные условия, которые нужно проверить. Например, мы теперь знаем, что при тестировании следует ответить на следующие запросы:

• Выполнится ли запрос select foo from cs_ec_po_item where ec_po_id="8" быстрее с индексом на ec_po_id ?
• Выполнится ли сколько-либо быстрее запрос с индексом для ec_po_id = "45" ?
• Выполнится ли сколько-либо быстрее запрос для ec_po_id , которые имеют селективность блоков меньше чем 1 %? (Поскольку отчет сортируется в нисходящем порядке селективности блоков, значения с лучшей селективности блоков в нем не показываются.)

Ваше окончательное решение о построении индекса, конечно, зависит от того, превышает ли выгода от наличия индекса стоимость его наличия. Эти затраты могут включать:

• Случайная деградация планов выполнения других запросов. В приложениях, которые все еще используют синтаксический оптимизатор Oracle, это представляет очевидный риск. Создание индекса для оптимизации предложения А может случайно деградировать производительность некоторого другого предложения B . К счастью, в стоимостной оптимизации, особенно для гистогамм (histograms) это явление становится все более редким.
• Увеличение времени DML-ответа для конкретной таблицы. Однако я видел, как люди драматически переоценивают важность этого фактора. Не гадайте об этом; спрофилируйте трассовые данные ваших DML-операций, чтобы выяснить их истинную стоимость.
• Увеличение объема пространства для размещения индекса. Когда-то количество места, нужного для индекса, было материально важным фактором при определении, строить ли индекс. С сегодняшними ценами на диски это почти к делу не относится.

Когда используется инструмент подобно скрипту hds.sql, наблюдается один из трех вариантов:

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

Решения в случаях 1 и 2 очевидны. А ситуация 3, вероятно, именно та, в которой вы пребываете наиболее часто. Пользователи стоимостного оптимизатора Oracle до релиза 7.3 стояли перед жестким выбором. Если индекс не создавался, то был велик риск низкой производительности при некоторых значениях во фразе where; если индекс создавался, то был риск низкой производительности для других значений. Новейшие версии стоимостного оптимизатора Oracle делают жизнь намного проще. Если в наши дни вы регулярно исполняете свои обязанности по сбору статистики , подобная ситуация намного менее вероятна, и ошибочное создание мало пригодного индекса причинит экстремальные издержки (torture - пытка) вашим пользователям.

Пример: Представим себе, что секционированная таблица содержит столбец id со следующим распределением данных:

Показанное здесь распределение данных сильно перекошено (highly skewed). Теперь выдадим следующий запрос к этой таблице:

select name from division d where id=:a1

Без гистограмм стоимостной оптимизатор может предположить, что имеются десять различных значений id, каждый id отвечает за примерно 1/10 строк таблицы. Это предположение заставит его вспомнить хорошую идею использовать индекс на столбце id. И так было бы до тех пор, пока :a1 != "01" .

Сила гистограммной (histogram-based) оптимизации состоит в том, что должным образом реализованный [9 ] гистограммный оптимизатор заметит, когда:a1 = " 01" и не будет пытаться использовать индекс на id. Без гистограммной оптимизации разработчик приложений должен или

1. оптимизировать запрос так, чтобы было эффективно, если:a1 = " 01 ", но крайне неэффективно иначе [10 ]; или
2. вы должны написать процедурную логику, которая использует одно SQL-предложение для обычных значений и другое SQL-предложение для редких значений. Oracle General Ledger генерирует динамические SQL-предложения, используя метод 2 для функций Financial Statement Generator. Это умно, но одновременно и беспорядок (a mess).

Значения не часто распределяются произвольным образом

Недавняя документация по Oracle приводит предположение, что "строки в таблице упорядочены произвольно (randomly ordered) в отношении столбца, на котором базируется запрос" . Это предположение немного упрощает написание Oracle-документации, но это делает этот совет Oracle менее полезным, чем он мог бы быть.

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

Пример: таблица shipment имеет столбец состояния, называемый shipped, который принимает значение "y" , тогда и только тогда, когда пункт заказа был отгружен (shipped). Поскольку заказы имеют тенденцию отправляться, грубо говоря, в той же последовательность, что были введены, таблица shipment через какое-то время имеет хорошую естественную кластеризацию по значениям shipped="n" , как это показано на рисунке 3. Кластеризация строк с shipped="n" улучшает полезность индекса при поиске строк с shipped="n" .

Рисунок 3. Значения столбца состояния имеют тенденцию к естественной кластеризации.

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

Пример: таблица address имеет столбец с именем state , который содержит двух литерный код штата или провинции. В приложении, которое использует эту таблицу, нет никаких очевидных отношений между временем, когда была вставлена строка клиента, и значением state клиента. Следовательно, физическое распределение каждого значения state практически однородно. Хотя state ="TX" истинно, возможно, только для одной строки из 30, лишь немногие блоки таблицы не имеют ни одной строки с state ="TX" . Рисунок 4 показывает эту ситуацию.

[Блок содержит по крайней мере одну строка с state = "TX"
Блок не содержит ни одной строки, для который state ="TX" ]

Рисунок 4. Индекс на state имеет низкую полезность для state ="TX ".

Использование здесь индекса по с state , вероятно, было бы неэффективно для поиска любого "известного" ("popular") кода штата. Но если, например, имеется один или более штатов с много меньшим количеством строк, чем наличествует блоков в таблице address , и если Вы часто ищете коды таких штатов и используете гистограммы, тогда создание индекса по state , вероятно, поможет вашему приложению.

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

• Секционирование (partitioning) таблиц и индексов Oracle
• Индекс-организованные таблицы Oracle
• Периодические операционные процедуры обслуживания для удаления строк и затем повторной их вставки в предпочтительном физическом порядке
• Использование кластерных (cluster) сегментов Oracle, вместо табличных сегментов

Без нужды не предполагайте, что распределение ваших данных случайно (random). Выясните это с помощью hds.sql. Любые приемы, предписывающие физическую упорядоченность, принесут как выгоды, так и затраты вашему бизнесу. Если изменение физического распределения данных одновременно помогает максимизировать чистую прибыль вашей компании, поток наличности (cash flow) и возврат инвестиций, тогда делайте это [Goldratt 1992 ].

Заключение

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

Стоимостной (cost-based) оптимизатор Oracle делает более простым ответ на вопрос, надо ли строить индекс, поскольку он вырабатывает более продвинутые решения по использованию индексов, чем это может сделать синтаксический (rule-based) оптимизатор. Но для реализаций, которые все еще полагаются на синтаксический оптимизатор Oracle, понимание важности селективности блоков может быть жизненно важно для производительности приложений Oracle. Как только определятся характеристики селективности блоков, необходимо исключить пассивный подход к физической упорядоченности ваших данных. Много возможностей, привнесенных в СУБД Oracle, начиная с выпуска 7.3, упрощают ваши действия по хранению данных в физической упорядоченности, что обеспечивает превосходную производительность.

Примечания:

Индексы 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, создающий индекс, в качестве атрибута.

SET SERVEROUTPUT ON DECLARE l_index_ddl varchar2(1000); l_used_bytes NUMBER; l_allocated_bytes NUMBER; BEGIN DBMS_SPACE.create_index_cost (ddl => "create index repsons_idx on EMP(ENAME)", used_bytes => l_used_bytes, alloc_bytes => l_allocated_bytes); DBMS_OUTPUT.PUT_LINE ("RESULT:"); DBMS_OUTPUT.PUT_LINE ("used_bytes = " || l_used_bytes || " byte"); DBMS_OUTPUT.PUT_LINE ("alloc_bytes = " || l_allocated_bytes || " byte"); END; /

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

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

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

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

CREATE INDEX employee_id ON employee(employee_id) TABLESPACE MY_INDEXES;

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

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

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

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

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

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

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

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

CREATE BITMAP INDEX gender_dx ON employee(gender) TABLESPACE MY_INDEXES;

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

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

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

SQL> CREATE INDEX reverse_idx ON employee(emp_id) REVERSE;

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

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

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

SQL> CREATE INDEX emp_indx1 ON employees(ename) TABLESPACE MY_INDEXES COMPRESS 1;

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

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

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

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

SQL> CREATE INDEX lastname _idx ON employees(LOWER(l_name));

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

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

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

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

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

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

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

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

SQL> CREATE INDEX tickersales_idx ON ticket_sales(month) GLOBAL PARTITION BY range(month) (PARTITION ticketsales1_idx VALUES LESS THAN (3) PARTITION ticketsales1_idx VALUES LESS THAN (6) PARTITION ticketsales2_idx VALUES LESS THAN (9) PARTITION ticketsales3_idx VALUES LESS THAN (MAXVALUE);

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

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

SQL> ALTER TABLE ticket_sales DROP PARTITION sales_quarter01 UPDATE GLOBAL INDEXES;

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

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

SQL> CREATE INDEX hgidx ON tab (c1,c2,c3) GLOBAL PARITION BY HASH (c1,c2) (PARTITION p1 TABLESPACE tsb_1, PARTITION p2 TABLESPACE tsb_2, PARTITION p3 TABLESPACE tsb_3, PARTITION p4 TABLESPACE tsb_4,);

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

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

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

SQL> CREATE INDEX ticket_no_idx ON Ticket_sales(ticket_no) LOCAL TABLESPACE localidx_01;

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

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

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

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

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

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

ALTER INDEX test_idx INVISIBLE;

И обратная команда

ALTER INDEX test_idx VISIBLE;

Приведенный ниже запрос к представлению DBA_INDEXES показывает состояние видимости индекса:

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

Если вы сомневаетесь в использовании определенного индекса, можете попросить Oracle выполнить мониторинг его применения. Таким образом, если индекс окажется избыточным, его можно уничтожить и сэкономить место в хранилище, а также снизить накладные расходы на операции DML.

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

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

SQL> ALTER INDEX p_key_sales MONITORING USAGE;

Теперь запустите какие-нибудь запросы к таблице sales. Завершите мониторинг, применив следующую команду:

SQL> ALTER INDEX p_key_sales NOMONITORING USAGE;

После этого можно запросить представление словаря данных V$OBJECT_USAGE для определения того, используется ли индекс p_key_sales.

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

Обслуживание индексов

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

ALTER INDEX sales_idx REBUILD;

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

ALTER INDEX sales_idx REBUILD ONLINE;

Оперативную перестройку индекса можно ускорить за счет добавления к показанному выше оператору ALTER INDEX конструкции ONLINE NOLOGGING. После добавления этой конструкции база данных не будет генерировать данные повторного выполнения для операции перестройки индекса.

В Oracle имеется несколько типов индексов:

· древовидные индексы (В-деревья).

· хешированные индексы (hash ).

· индексы на основе битовых карт или битовые индексы (bitmap ).

В-деревья были реализованы в Oracle практически с самого начала ее существования, затем появились хешированные индексы появились, а затем - битовые карты.

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

Оператор создания индекса использует следующий синтаксис:

СREATE INDEX имя_индекса

ON имя_таблицы (имя_столбца, [¼])

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

DROP INDEX <ИМЯ> (удалить)

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

ALTER INDEX<ИМЯ> REBUILD (перестроить индекс)

ALTER INDEX<ИМЯ> UNUSABLE (отключить индекс на время,

чтобы снова включить обратно при помощи REBUILD)

B-деревья

Видимо, наиболее популярным подходом к организации индексов в базах данных является использование техники B-деревьев. B-дерево содержит по одному индексному элементу для каждой строки таблицы, в которой имеется непустое (NOT NULL) индексное значение. С точки зрения внешнего логического представления B-дерево - это сбалансированное сильно ветвистое дерево во внешней памяти (рис.5.3).

Рис. 5.3 - Древовидный индекс по текстовому столбцу

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

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

При этом выдерживаются следующие свойства:

ключ(1) <= ключ(2) <= ... <= ключ(n);

в странице дерева Nm находятся ключи k со значениями ключ(m) <= k <= ключ(m+1).

Листовая страница обычно содержит значение индекса и идентификаторы строк (ROWID) и имеет следующую структуру:

Листовая страница обладает следующими свойствами:

· ключ(1) < ключ(2) < ... < ключ(t);

· сп(r) - упорядоченный список идентификаторов кортежей (tid), включающих значение ключ(r);

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

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

Основной "изюминкой" B-деревьев является автоматическое поддержание свойства сбалансированности. Рассмотрим, как это делается при выполнении операций занесения и удаления записей.

При занесение новой записи выполняется:

· Поиск листовой страницы. Фактически, производится обычный поиск по ключу. Если в B-дереве не содержится ключ с заданным значением, то будет получен номер страницы, в которой ему надлежит содержаться, и соответствующие координаты внутри страницы.

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

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

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

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

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

При удалении записи выполняются следующие действия:

· Поиск записи по ключу. Если запись не найдена, то удалять ничего не нужно.

· Реальное удаление записи в буфере, в который прочитана соответствующая листовая страница.

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

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

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

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

Как видно, при выполнении операций вставки и удаления свойство сбалансированности B-дерева сохраняется, а внешняя память расходуется достаточно экономно.

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

· упреждающие расщепления, т.е. расщепления страницы не при ее переполнении, а несколько раньше, когда степень заполненности страницы достигает некоторого уровня;

· переливания, т.е. поддержание равновесного заполнения соседних страниц;

· слияния 3-в-2, т.е. порождение двух листовых страниц на основе содержимого трех соседних.

Следует заметить, что при организации мультидоступа к B-деревьям, характерного при их использовании в СУБД, приходится решать ряд нетривиальных проблем. Конечно, грубые решения очевидны, например монопольный захват B-дерева на все выполнение операции модификации. Но существуют и более тонкие решения.

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

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

В-деревья можно использовать для поиска данных, как по условиям равенства, так и по условиям неравенства. Это единственный тип индексов, который можно использовать для предикатов неравенства: LIKE, BETWEEN, “>”, “>=”, “<”, “<=”. Исключение представляет случай использования предиката LIKE при сравнении с шаблоном вида ‘%выражение’ или ‘_выражение ’. В-деревья хранят только непустые значения ключей, так что можно построить разреженное В-дерево.

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

Почему индексы необходимы

Индексы это часть механизма ограничений (constraint). Если столбец (или группа столбцов) помечены как первичной ключ таблица, то каждый раз когда вставляется строка в таблицу, Oracle необходимо проверить что не существует строки с такими значениями. Если у таблицы нет индекса дял столбцов – единственный способ проверить это это вычитать всю таблицу. Это может быть приемлимо если в таблице всего несколько строк, но дял таблиц, содержащих тысячи миллионов (или миллиардов) строк это займёт очень много времени и неприемлимо. Индекс позволяет практически мгновенно получить доступ к значениям ключа и проверка на существование происходит моментально. Когда определяется первичный ключ Oracle создаст индекс для столбца(ов) ключа если ещё не существует такого индекса.

Ограничение по уникальности (unique constraint) тоже требует создание индекса. Это ограничение отличается от первичного ключа тем что значение в столбцах ограничения по уникальности могут быть NULL в отличие от первичного ключа, но это не влияет на создание и исопльзование индекса. Внешний ключ (foreign key) соблюдается с помощью индексов, но обязательным является индекс только на родительской таблице. Внешний ключ дочерней таблицы зависит от столбца первичного ключа или уникального ключа родительской таблицы. Когда строка добавляется в дочернюю таблицу, Oracle будет использовать индекс родительской таблицы для проверки существует ли такое значение в родительной таблице или нет, перед тем как позволить записать данные. Как бы то ни было желательно всегда создавать индексы для столбцов дочерней таблицы используемых как внешние ключи из соображений производительности: DELETE для родительской таблицы будет гораздо б ыстрее если Oracle сможет использовать индекс для проверки существуют ли ещё строки в дочерней таблице с этим значением или нет.

Индексы критически важны для производительности. Когда выполняется команда SELECT с директивой WHERE, Oracle необходимо определить строки в таблице которые необходимо выбрать. Если не создано индексов для столбцов используемых в директиве WHERE, то единственным способом сделать это – это вычитать всю таблицу (full table scan).Full table scan проверяют все строки по очереди для поиска нужных значений. Если в таблицы хранятся миллиарды строк, это может занять несколько часов. Если существует индекс для использованного в WHERE столбца, Oracle может искать используя индекс. Индекс это отсортированный список ключей значений структурирвоанных таким образом чтобы операция поиска была очень быстрой. Каждая запись это сслыка на строку в таблице. Поиск строк используя индекс гораздо быстрее чем чтение всей таблицы если размер таблицы больше определённого размера и пропорция между данными которые нужны для запроса и всеми данными в таблице ниже определённого значения. Для маленьких таблиц, или где секция WHERE всё равно выберет большую часть строк из таблицы, полное чтение таблицы будет быстрее: вы можете (обычно) доверять Oracle при выборе решения использовать ли индекс. Это решение осуществляется на основании статистической информации собираемой о таблице и строках в ней.

Второй случай когда индексы могут увеличить производительность это сортировка. Команда SELECT c директивой ORDER BY, GROUP BY или ключевым словом UNION (и несколько других) обязана отсортировать строки в определённом порядке – если не создан индекс, который может вернуть строки без необходимости в сортировке (строки уже отсортированы).

И третий случай это объекдинение таблиц, но опять же у Oracle есть выбор: в зависимости от размера таблиц и наличия свободной памяти, может быть быстрее вычитать таблицы в память и объединять их чем использовать индексы. Метод nested loop join читает строки одной таблицы и использует индекс другой таблицы для поиска совпадений (это обычно нагружает диск). Hash join считывает таблицу в память, преобразует в хеш таблицу и использует специальный алгоритм для поиска совпадений — такая операция требует больше оперативной памяти и процессорного времени. Sort merge join сортиует таблицы по значениям столбца для объединения и затем объединяет их вместе – это компромисс между использованием диска, памятии процессора. Если нет индексов –Oracle сильно ограничен в способах объединения.

Indexes assist SELECT statements, and also any UPDATE, DELETE, or MERGE statements that use a WHERE clause-but they will slow down INSERT statements.

Oracle поддерживает несколько типов индексов с различными вариациями. Два типа, которые мы рассмотрим это B* Tree индекс, который является типом по умолчанию и bitmap индекс. Основное правило – индексы увеличивают производительность для чтения данных но замедляют при DML операциях. Это происходит потому что индексы нужно обновлять и поддерживать. Каждый раз когда строка записывается в таблицу, новый ключ должен быть вставлен в каждый индекс таблицы, что усиливает нагрузку на БД. Поэтому OLTP системы обычно используют минимальное количество индексов (возможно только необходимые для ограничений) а для OLAP систем создаётся столько индексов сколько нужно для быстроты выполнения.

B* Tree индексы (B*=balanced)

Индекс это древовидная (tree) структура. «Корень» (root) дерева содержит указатели на множество узлов второго уровня, которые в свою очередь могут хранить указатели на узлы третьего уровня и так далее. Глубина дерева определяется длинной ключа и количеством строк в таблице.

The B*Tree structure is very efficient. If the depth is greater than three or four, then either the index keys are very long or the table has billions of rows. If neither if these is the case, then the index is in need of a rebuild.

В листьях (узлы нижнего уровня) индекса хранятся значения столбца строк по порядку и указатель на строку. Также листья хранят ссылки на соседние листья. Таким образом чтобы выбрать строку если условие WHERE использует строгое равенство — Oracle исдёт по дереву в лист содержащий искомое значение и затем использует указатель для считывания строки.Если же используется нестрогое равенство (например LIKE, BETWEEN и т.д.) то вначале находится первая строка удовлетворяющая условию а затем считываются строки по порядку и переход между листьями осуществляется напрямую, без нового обхода по дереву.

Указатель на строку – это rowid. Rowid — это псевдостолбец закрытого формата, который имеет каждая строка в каждой таблице. Внутри значения зашифрован указатель на физический адрес строки. Так как rowid не является частью стандарта SQL то он не видим при написании обычных запросов. Но вы можете выбирать эти значения и использовать их при необходимости. Это отображено на рисунке 7-3.

Rowid для каждой строки полностью уникальный. Каждая строка во всей БД имеет свой уникальный rowid. Расшифровав rowid получаем физический адрес строки, и Oracle может рассчитать в каком файле и где внутри файла находится искомая строка.

B* Tree индексы очень эффективны для вычитки строк число которых невелико относительно всех строк таблицы и таблица достаточно большая. Рассмотрим запрос

select count(*) from employees where last_name between ‘A%’ and ‘Z%’;

При использовании такого условия в WHERE запрос вернёт все строки таблицы. Использование индекса при таком запросе будет значительно медленее чем чтение всей таблицы. И вообще – вся таблица это то что нужно в этом запросе. Другим примером будет настолько маленькая таблица где одна операция чтения считывает её полностью; тогда нет смысла считывать вначале индекс. Обычно говорят что запросы, результат которых предполагает вычитку более чем 2-4% данных в таблице обычно работают быстрее используя полное чтение таблицы. Особым случаем является значение NULL в столбце указанном в секции WHERE. Значение NULL не хранится в B* Tree индексах и запросы типа

select * from employees where last_name is null;

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

Мощность (кратность – количество уникальных значений) столбца велика и

Столбец используется в директивах WHERE и операциях объединения

Bitmap индексы

Во многих приложения природа данных и запросы таковы что использование B* Tree индексов не сильно помогает. Расммотрим пример. Есть таблица продаж, в которой набор данных о продажах в супермаркетах за год, которые нужно проанализировать в нескольких измерениях. На рисунке 7-4 показана простая диаграмма сущность-связь для четырёх измерений.

Мощность каждого измерения очень низкая. Преположим

Всего два измерения (DATE и PRODUCT) предполагают селективность лучше чем упомянутые 2-4%, т.е. делают использование индексов оправданным. Но если запросы используют предикаты группы (к примеру месяц в году, или группа товаров в которую входит десять товаров) то и эти измерения не подходят к требованиям. Отсюда следует простой факт: B* Tree индексы часто бесполезны в хранилищах данных. Типичным запросов может быть сравнение продаж между двумя магазинами приходящим покупателям определённой группы товаров за месяц. Можно создать B* Tree индесы для этих столбцов но Oracle проигнорирует их так как они недостаточно селективны. Для таких ситуация созданы bitmap индексы. Bitmap индексы хранят все rowid строк как битовую маску для каждого уникального значения ключа. Битовые маски индекса для измерения CHANNEL может быть к примеру

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

Битовые маски индекса столбца SHOP могут быть

Это значит что первые две продажи были в Лондоне, затем одна в Оксфорде, затем четвертая в Рединге и так далее.

Теперь если приходит запрос

select count(*) from sqles where channel=’WALK-IN’ and shop=’OXFORD’

Oracle может выбрать две битовые маски и объединить их с помощью операции И

Результат логического И показывает что только седьмая и шестнадцатая строки удовлетворяют запросу. Операции над битовыми масками очень быстрые и могут использоваться для сложных булевых операций надо многими столбцами со многими сочетаниями И, ИЛИ или НЕ. Также достоинством bitmap индексов является то, что они хранят значения NULL. С точки зрения битовой маски – NULL просто ещё одно уникальное значение со своей битовой маской.

В общем, bitmap индексы полезны когда

Мощность столбца низкая и

Количество строк в таблице большое и

Столбец используется в операциях булевой алгебры

If you knew in advance what the queries would be, then you could build B*Tree indexes that would work, such as a composite index on SHOP and CHANNEL. But usually you don’t know, which is where the dynamic merging of bitmaps gives great flexibility.

Свойства индексов

Всего доступно шесть свойств которые можно применить при создании индекса

• Уникальность / Unique или nonunique
• Реверсивность / Reverse key
• Сжатие / Compessed
• Составной или нет /Composite
• Основанный на функции или нет / Function based
• Сортировка по возрастанию или убыванию / Ascending или descending

Все шесть свойств можно применить к B* Tree индексам и только три последних можно использовать для bitmap индексов.

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

Реверсивный индекс строится на значениях ключа в которых байты строятся в обратном порядке: вместо индексирования значения к примеру ‘John’ будет использоваться значение ‘nhoJ’. Когда выполнится команда SELECT, Oracle автоматически преобразует строку поиска. Это используется для распределения строк по индексу в мультипользовательских системах. Например если много пользователей добавляют много строк в таблицу с первичным ключом как последовательно-увеличивающийся номер – все строки будут стремиться к концу индекса. Путем реверса ключа строки распределяются по всему индексу. При использовании индекса с реверсированным ключом базы данных не сохраняет ключи индекса друг за другом в лексикографическом порядке. Таким образом, когда в запросе присутствует предикат неравенства, ответ получается медленнее, поскольку база данных вынуждена выполнять полное сканирование таблицы. При индексе с реверсированным ключом база данных не может запустить запрос по диапазону ключа индекса.

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

Составной индекс – это индекс который строится для нескольких столбцов. Нет ограничений на использование столбцов разных типов данных. Если условие WHERE не использует все столбцы, то индекс всё ещё может быть использован, но если не используется самый левый столбец, то Oracle использует skip-scanning метод который гораздо менее эффективный чем если бы левый столбец был включен.

Основанный на функции индекс строится для результата выполнения функции к одному или нескольким столбцам, к примеру upper(last_name или to_char(startdate,’ccyy-mm-dd’). Запросы должны использовать ту же функцию для поиска или Oracle не сможет использовать индекс.

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

Создание и использование индексов

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

CREATE INDEX [ schema.]indexname

ON tablename (column [, column…]) ;

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

Many database administrators do not consider it good practice to rely on implicit index creation. If the indexes are created explicitly, the creator has full control over the characteristics of the index, which can make it easier for theDBA to manage subsequently.

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

create table dept(deptno number,dname varchar2(10));

create table emp(empno number, surname varchar2(10),

forename varchar2(10), dob date, deptno number);

create unique index dept_i1 on dept(deptno);

create unique index emp_i1 on emp(empno);

create index emp_i2 on emp(surname,forename);

create bitmap index emp_i3 on emp(deptno);

alter table dept add constraint dept_pk primary key (deptno);

alter table emp add constraint emp_pk primary key (empno);

alter table emp add constraint emp_fk

foreign key (deptno) references dept(deptno);

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

Когда определяются два ограничения, Oracle определит уже существующие индексы и использует их для ограничений. Обратите внимание что индекс для DEPT.DEPTNO не даст выигрыш с точки зрения происзводительности, но он всё равно необходим для обеспечения ограничения первичного ключа.

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

The Oracle server should make the best decision about index use, but if it is getting it wrong, it is possible for a programmer to embed instructions, known as optimizer hints, in code that will force the use (or not) of certain indexes

Изменение и удаление индексов

Команда ALTER INDEX не может менять свойства индексов интересных с точки зрения программиста: тип, столбцы и всё иное. ALTER INDEX создана для администратора БД и обычно будет использоваться для управления физическими свойствами индекса. Если необходимо изменить логические свойства – то единственным способом будет удаление старого индекса и создание нового. К примеру чтобы изменить индекс EMP_I2 можно выполнить следующие команды

drop index emp_i2;

create index emp_i2 on emp(surname,forename,dob);

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