Как составить хинт оптимизатору с индексами из разных таблиц?

verter

Дата: 20.02.2007 18:21:58

Хочется узнать стандартную вещь только в толковом, а не сухом и размытом как в документации объяснении.

Допустим есть такие таблицы:

t1  = (f11,f12,...,f1n)
t1  = (f21,f22,...,f2m)
t1  = (f31,f32,...,f3k)

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

в t1 есть ind_f11, ind_f12
в t2 есть ind_f21, ind_f22
в t3 есть ind_f31, ind_f32

Допустим есть такой запрос:

select t1.f11,t1.f12, t2.f21,t2.f22, t3.31,t3.32
from t1, t2, t3
where t2.f22 = t1.f11
   and t3.f32 = t2.f21

Если мы хотим явно указать оптимизатору по какому индексу строить запрос, то пишем хинт:

select /*+ index (t1 ind_f11)*/
          t1.f11,t1.f12, t2.f21,t2.f22, t3.31,t3.32
from t1, t2, t3
where t2.f22 = t1.f11
   and t3.f32 = t2.f21

Как указать компилятору, что надо идти сразу по нескольким индексам из разных таблиц?

Если написать так:

select /*+ index (t1 ind_f11, t2 ind_f22, t3 ind_f32)*/
          t1.f11,t1.f12, t2.f21,t2.f22, t3.31,t3.32
from t1, t2, t3
where t2.f22 = t1.f11
   and t3.f32 = t2.f21

то оптимизатор не поймёт.

Форумчанин

Дата: 20.02.2007 18:23:25

select /*+ index (t1 ind_f11)  index(t2 ind_f22) index(t3 ind_f32)*/
          t1.f11,t1.f12, t2.f21,t2.f22, t3.31,t3.32
from t1, t2, t3
where t2.f22 = t1.f11
   and t3.f32 = t2.f21

verter

Дата: 20.02.2007 19:02:24

Понятно, спасибо. А теперь конкретный пример.

Есть таблицы:

t1 = (id, t2_id, t4_id, amount)
t2 = (id, t3_id)
t3 = (id, text)
t4 = (id, t5_id)
t5 = (id, text)

Есть индексы по всем полям:

в t1 есть pk_t1_id, fk_t1_t2id, fk_t1_t4id
в t2 есть pk_t2_id, fk_t2_t3id
в t3 есть pk_t3_id
в t4 есть pk_t4_id, fk_t4_t5id
в t5 есть pk_t5_id

где pk - primary key, fk - foreign key

есть запрос:

select t3.id, t3.text, t5.id, t5.text, sum(t1.amount)
from t1, t2, t3, t4, t5
where t1.t2_id = t2.id
  and t2.t3_id = t3.id
  and t1.t4_id = t4.id
  and t4.t5_id = t5.id
group by t3.id, t5.id

Таблицы довольно большие. Все индексы, как видно, либо внешние либо первичные ключи.

Как оптимально в данном случае указать оптимизатору по каким индексам идти?

Наверное это зависит от размера таблиц.

oragraf

Дата: 20.02.2007 19:04:55

В данном случае все равно будет FULL SCAN

verter

Дата: 20.02.2007 19:08:12

oragraf

В данном случае все равно будет FULL SCAN

почему? Если в хинте указывать разные индексы, то план запроса показывает что по этим индексам оптимизатор и пойдёт.

oragraf

Дата: 20.02.2007 20:31:31

Угу, пойдет. Прочитает индекс, потом начнет читать таблицу, ведь не все поля есть в индексе.
В итоге выйдет дороже(индекс+таблица).

izali

Дата: 21.02.2007 00:04:57

Практический опыт (не только мой) подсказывает следующее:
Если селективность запроса более 30%, то это явно фул скан.
Если менее 10% - то однозначно индекс.
Если 10%-30% - смотрим доп. условия.
30% взяты не с потолка, подробнее - изучите устройство индекса и посчитайте количество логических чтений требуемых для получения ROWID.

Чтобы не мучаться, я беру 20%. Выше - фулскан, ниже - индексный доступ.

Бабичев Сергей

Дата: 21.02.2007 05:31:19

izali

Да-да-да, но и тут далеко не всё так просто. Я так смотрю, фактор кластеризации в расчет ты и не берешь. Почитай книгу Кайта "Oracle для профессионалов", он там в разделе про индексы хороший пример по этому поводу приводит. Там тоже по индексу выбирается порядка 10% данных. Вот только данные в таблице очень плохо кластеризованы относительно ключа индекса, и поэтому такой способ доступа также остается далеко позади от банального FULL ТABLE SCAN-а

Ростовский Зануда

Дата: 21.02.2007 07:37:09

izali

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

evostr

Дата: 21.02.2007 07:42:41

Ростовский Зануда

izali

"А был ли мальчик?" :)