Так можно обращаться к небольшой части этой таблицы. Надо обращать внимание на шаг INDEX (RANGE SCAN), после которого идет TABLE ACCESS BY INDEX

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

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

Следующий запрос демонстрирует эту идею:

tkyte@TKE816> select count(*)

2 from T

3 where owmer = USER;

Execution Plan

0 SELECT STATEMENT Optimizer = CHOOSE

1 0 SORT (AGGREGATE)

2 1 INDEX (RANGE SCAN) OF T IDX (NON-UNIQUE)

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

Важно понять различие между этими двумя случаями. Если необходимо выполнить TABLE ACCESS BY INDEX ROWID, надо гарантировать, что так мы обращаемся к небольшой части строк таблицы. При таком способе доступа к слишком большому количеству строк (предел - от 1 до 20 процентов строк) он будет выполняться дольше, чем полный просмотр таблицы. Для запросов второго типа, ответ на которые целиком находится в индексе, ситуация принципиально отличается. Мы читаем блок индекса и выбираем множество строк для обработки, затем, переходим к следующему блоку индекса и т.д., вообще не обращаясь к таблице. По индексам можно также делать быстрый полный просмотр, т.е. благодаря им в определенных случаях работа выполняется еще быстрее. Быстрый полный просмотр - это когда сервер читает блоки индекса не в определенном порядке - он их просто читает. Индекс уже не используется как индекс, скорее - как таблица. При быстром полном просмотре записей индекса строки выдаются неупорядоченными.

Обычно индекс на основе В*-дерева я создаю по столбцам, часто используемым в условии запроса, если предполагается, что по запросу будет возвращаться небольшая часть строк. В простенькой таблице с небольшим количеством или размером столбцов эта часть может быть очень маленькой. Запрос, использующий индекс, обычно должен извлекать не более 2-3 процентов строк таблицы. В массивной таблице с большим количеством или размером столбцов эта доля может доходить до 20-25 процентов таблицы. Этот совет не каждый сразу воспримет; он интуитивно не понятен, но правилен.

Индекс хранится отсортированным по ключу. Индекс будет просматриваться в порядке сортировки ключей. Блоки, на которые указывает индекс, хранятся в случайном порядке, в виде кучи. Поэтому при доступе к таблице по индексу придется выполнять множество разрозненн1х, случайн1х операций ввода/вывода. Разрозненность объясняется тем, что по индексу придется читать блок 1, блок 1000, блок 205, блок 321, блок 1, блок 1032, блок 1 и т.д., а не последовательно блок 1, затем 2, 3 и т.д. Придется читать блоки случайным образом. В этом случае ввод/вывод одного блока может выполняться крайне медленно.

В качестве упрощенного примера возьмем несложную таблицу, читаемую по индексу, при условии, что должно быть прочитано 20 процентов строк. Предположим, в таблице 100000 строк. Двадцать процентов от этого составляет 20000 строк. Если строки в среднем имеют длину 80 байт, в базе данных с размером блока 8 Кбайт в нем будет помещаться около 100 строк. Это означает, что в таблице - около 1000 блоков. Теперь рассчитать все будет несложно. Мы собираемся прочитать по индексу 20000 строк, другими словами, выполнить 20000 операций TABLE ACCESS BY ROWID. Для выполнения этого запроса придется обработать 20000 блоков таблицы. Хотя во всей таблице всего лишь около 1000 блоков! В конечном итоге окажется, что мы прочитали и обработали каж-д1й блок в таблице в среднем 20 раз! Даже если увеличить размер строки на порядок (до 800 байт), что дает 10 строк в блоке, - такая таблица займет 10000 блоков. При доступе по индексу к 20000 строк нам придется прочитать каждый блок в среднем дважды. В данном случае полный просмотр таблицы будет намного эффективнее, чем доступ по индексу, поскольку каждый блок будет просматриваться только один раз. Запрос, использующий этот индекс для доступа к данным, не будет выполняться эффективно, если в среднем обращается более чем к 5 процентам данных для столбца размером 800 байт (в этом случае мы обратимся примерно к 5000 блоков) или к еще меньшей части (менее 0,5 процента) для столбца размером 80 байт.

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

select * from T where primary key between :x and :y

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

tkyte@TKYTE816> create table colocated (x int, у varchar2(2000)) pctfree 0; Table created.

tkyte@TKYTE816> begin

2 for i in 1 . . 100000

3 loop

4 insert into colocated values (i, rpad(dbms random.random, 75, *));

5 end loop;

6 end;

PL/SQL procedure successfully completed.

tkyte@TKYTE816> alter table colocated

2 add constraint colocated pk primary key(x); Table altered.

Эта таблица соответствует приведенному выше описанию: около 100 строк в блоке при размере блока 8 Кбайт. Весьма вероятно, что в этой таблице строки со значением х = 1, 2, 3 попадут в один блок. Теперь мы умышленно дезорганизуем эту таблицу. В представленной выше таблице COLOCATED мы создали столбец Y со случайными значениями, и теперь используем его для дезорганизации данных, так что они уж точно не будут больше упорядочены по первичному ключу:

tkyte@TKYTE816> create table disorganized nologging pctfree 0

2 as

3 select x, у from colocated ORDER BY у

4 / Table created.

tkyte@TKYTE816> alter table disorganized

2 add constraint disorganized pk primary key(x); Table altered.

Можно утверждать, что это - одинаковые таблицы. Это же реляционная СУБД, и физическая организация не влияет на результаты (по крайней мере, так учат в теоретических курсах по базам данных). На самом деле характеристики производительности этих двух таблиц отличаются, как небо и земля. Выполняем один и тот же запрос:

tkyte@TKYTE816> select * from COLOCATED where x between 20000 and 40000;

20001 rows selected. Elapsed: 00:00:01.02

Execution Flan

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF COLOCATED

2 1 INDEX (RANGE SCAN) OF COLOCATED PK (UNIQUE)

Statistics

0 recursive calls 0 db block gets