[Из песочницы] Методы доступа к данным в Oracle

Не найдя на хабре статьи, объединяющей в удобном для чтения виде информацию о методах доступа к данным, используемых СУБД Oracle, я решил совершить «пробу пера» и написать эту статью.

Общая информация

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



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

create table t1 (t1_key number, t1_value varchar2(10));
insert into t1 values(1, '1');
insert into t1 values(2, '2');
insert into t1 values(3, '3');
insert into t1 values(4, '4');
insert into t1 values(5, '5');
insert into t1 values(6, '6');
insert into t1 values(7, '7');
insert into t1 values(8, '8');
insert into t1 values(9, '9');

Для анализа плана выполнения запроса будем пользоваться следующими средствами:

explain plan for [query goes here];
select * from table(dbms_xplan.display(null,null,'basic'));

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

drop index index_name;

TABLE FULL SCAN

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

select t1_key, t1_value from t1 where t1_key = 5;
----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T1   |
----------------------------------

create index key_index on t1 (t1_key);
select t1_key, t1_value from t1 where t1_value = '5';
----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T1   |
----------------------------------

TABLE ACCESS BY ROWID, он же ROWID

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

• Мы указали идентификатор строки в предикате where;


• ROWID запрошенной записи был найден в индексе;

Переходим к методам доступа, используемым Oracle в случае наличия индексов.

INDEX FULL SCAN

Данный метод доступа просматривает все листовые блоки индекса для поиска соответствий условиям предиката. Для того чтобы Oracle мог применить этот метод доступа, хотя бы одно из полей ключа должно иметь ограничение NOT NULL, т.к. только в этом случае соответствующая строка таблицы попадет в индекс. Этот метод обычно быстрее чем TABLE FULL SCAN, но медленнее, чем INDEX RANGE SCAN (см. ниже).

INDEX FAST FULL SCAN

Этот метод доступа применяется, когда выполнены все требования для INDEX FULL SCAN, а также все данные, выбираемые запросом, содержатся в индексе и таким образом доступ к самой таблице не требуется. В отличие от INDEX FULL SCAN этот метод может читать блоки индекса в несколько параллельных потоков и таким образом порядок возвращаемых значений не регламентирован. Oracle также не может использовать этот метод для bitmap-индексов.

INDEX RANGE SCAN

Данный метод доступа используется Oracle в том случае, если в предикат where входят столбцы индекса с условиями = (в случае если индексированные значения неуникальны), >,

Пример:

select * from t1 where t1_key = 3;
-------------------------------------------------
| Id  | Operation                   | Name      |
-------------------------------------------------
|   0 | SELECT STATEMENT            |           |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |
|   2 |   INDEX RANGE SCAN          | KEY_INDEX |
-------------------------------------------------

INDEX UNIQUE SCAN

Данный метод доступа применяется когда в силу ограничений UNIQUE/PRIMARY KEY, а также условия предиката, запрос должен вернуть ноль или одно значение.

Пример:

create unique index u_key_index on t1 (t1_key);
---------------------------------------------------
| Id  | Operation                   | Name        |
---------------------------------------------------
|   0 | SELECT STATEMENT            |             |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |
|   2 |   INDEX UNIQUE SCAN         | U_KEY_INDEX |
---------------------------------------------------

INDEX SKIP SCAN

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

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

create table t2 as (select * from all_objects);
--столбец data_object_id должен иметь селективность значительно ниже, чем object_id
create index test_index on t2 (data_object_id, object_id);
--пересоберем статистику для таблицы
begin
  dbms_stats.gather_table_stats(user, 'T2', cascade=>true);
end;
--И наконец запрос
select * from t2 where object_id=370; --не забудьте сменить object_id
--------------------------------------------------
| Id  | Operation                   | Name       |
--------------------------------------------------
|   0 | SELECT STATEMENT            |            |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2         |
|   2 |   INDEX SKIP SCAN           | TEST_INDEX |
--------------------------------------------------

На этом о методах доступа все. Благодарю за внимание :)

This entry passed through the Full-Text RSS service — if this is your content and you're reading it on someone else's site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers. Five Filters recommends: 'You Say What You Like, Because They Like What You Say' - http://www.medialens.org/index.php/alerts/alert-archive/alerts-2013/731-you-say-what-you-like-because-they-like-what-you-say.html