SQL/Oracle: когда можно использовать индексы на нескольких столбцах

Если я создаю индекс в столбцах (A, B, C), в этом порядке, я понимаю, что база данных сможет использовать его, даже если я ищу только на (A) или (A и B), или (A и B и C), но не если я выполняю поиск только по (B) или (C) или (B и C). Правильно ли это?

Ответ 1

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

i) Сканирование по индексу: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#PFGRF10105

ii) Быстрый сканирование полного индекса: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i52044

iii) Полное сканирование индекса: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i82107

Я часто видел быстрое сканирование полного индекса "в дикой природе", но все это возможно.

Ответ 2

Это неверно. Всегда лучше всего придумать тестовый пример, который представляет ваши данные и сам увидеть. Если вы хотите по-настоящему понять Oracle SQL Optimizer google Jonathan Lewis, прочитайте его книги, прочитайте его блог, посмотрите его сайт, парень потрясающий, и он всегда создает тестовые примеры.

create table mytab nologging as (
select mod(rownum, 3) x, rownum  y, mod(rownum, 3) z from all_objects, (select 'x' from user_tables where rownum < 4)
);

create index i on mytab (x, y, z);

exec dbms_stats.gather_table_stats(ownname=>'DBADMIN',tabname=>'MYTAB', cascade=>true);

set autot trace exp

select * from mytab where y=5000;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=10)
   1    0   INDEX (SKIP SCAN) OF 'I' (INDEX) (Cost=1 Card=1 Bytes=10)

Ответ 3

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

В Oracle 9i была введена функция Пропустить индексный доступ, что позволяет Oracle CBO пытаться использовать индексы, даже если столбец префикса недоступен.

Хороший обзор того, как работает сканирование: http://www.quest-pipelines.com/newsletter-v5/1004_C.htm