Как стать автором
Обновить

Методы доступа к данным в Oracle

Время на прочтение4 мин
Количество просмотров81K
Не найдя на хабре статьи, объединяющей в удобном для чтения виде информацию о методах доступа к данным, используемых СУБД 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 входят столбцы индекса с условиями = (в случае если индексированные значения неуникальны), >, <, а также like ‘pattern%’, причем wildcard-символы должны стоять после искомой подстроки. В отличие от TABLE FULL SCAN, при использовании этого метода доступа Oracle не перебирает все листовые блоки и поэтому в большинстве случаев INDEX RANGE SCAN быстрее.
Пример:
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 |
--------------------------------------------------


DISCLAIMER


Утверждения о том, что при определенных условиях cost-based-optimizer (CBO) выберет тот или иной метод доступа, могут быть не совсем справедливыми в отдельных случаях, так как логика определения оптимального метода оптимизатором очень сложна.
Теги:
Хабы:
Всего голосов 17: ↑12 и ↓5+7
Комментарии7

Публикации

Истории

Работа

Ближайшие события

Конференция HR API 2024
Дата14 – 15 июня
Время10:00 – 18:00
Место
Санкт-ПетербургОнлайн
Конференция «IT IS CONF 2024»
Дата20 июня
Время09:00 – 19:00
Место
Екатеринбург
Summer Merge
Дата28 – 30 июня
Время11:00
Место
Ульяновская область