Про ускорение запроса и индексы Oracle.

← →
EternalWonderer (2002-11-18 12:09) [0]

Уважаемые господа!
Задача: ускорить выполнение запроса, выбирающего данные из таблицы t1.

Дополнительные условия:
1) определение таблицы
s1 Integer, o1 Integer, o2 Integer, o3 Integer, ID Integer
Первичный ключ отсутствует (за ненадобностью :).
2) требуемый запрос:
select ID from t1 where s1=1 order by o1,o2,o3
При этом порядок полей в условии order by выбирается при динамическом формировании запроса пользователем.

Вопрос: как построить индексы, чтобы скорость выполнения запроса была наибольшей при любом порядке полей в order by?

Заранее признателен за конструктивные советы.

← →
stone © (2002-11-18 12:52) [1]

построение индексов ускоряет выборку данных с использованием условий (where...), order by тут ни при чем

← →
EternalWonderer (2002-11-18 15:55) [2]

Так. И как же ускорить выполнение запросов?

← →
stone © (2002-11-18 16:04) [3]

индекс строится по полям, которые чаще всего употребляются в where

← →
Prooksius © (2002-11-18 16:11) [4]

2 stone © (18.11.02 12:52)
Странно, так что же Оракл настолько глуп, что при "ордер бай" сортирует данные на клиенте, предварительно их всех выкачав?
Маловероятно.
Я вообще-то в Оракле не силен, может ты и прав...
Но как тогда Оракл работает с "ордер бай"? Что никак?
Мне говорили, что он по оптимизации запросов сильнее будет, чем Интербейз, с которым я работаю, но и IB/FB это дело нормально отрабатывает.

← →
stone © (2002-11-18 16:21) [5]

> Странно, так что же Оракл настолько глуп, что при "ордер
> бай" сортирует данные на клиенте, предварительно их всех
> выкачав?

на клиенте никто ничего не сортирует. при чем тут это

← →
Prooksius © (2002-11-18 16:32) [6]

2 stone © (18.11.02 16:21)
Да я что-то сморозил - на клиенте действительно ничего не сортируется... :( Сорри.

Смотри, у тебя есть запрос с ордер бай.
Как сервер выдаст тебе отсортированный НД, если он не будет использовать индексы по полю в "ордер бай"?
Он будет сортировать все данные в памяти/виртуальной памяти (я это хотел сказать, а не на клиенте - оговорился), что будет ужасно тормозить.
Для планировщика логичнее было бы использовать индексы, как по where, так и по order by.

Ваши коментарии? :)

← →
stone © (2002-11-18 16:41) [7]

2 Prooksius © (18.11.02 16:32)

Ну не знаю, у меня в БД порядка 15 млн. записей и ничего не тормозит при любом order by

← →
Prooksius © (2002-11-18 16:46) [8]

2 stone © (18.11.02 16:41)
Ну, это значит:
1. Оракл все-таки использует индексы по order by.
2. Эти индексы у тебя есть. :)

Либо индексов нет, но запросы короткие (возвращают мало записей)

Ну или такой крутой сервак, что... просто... ну... офигеть! :))))

← →
stone © (2002-11-18 16:50) [9]

> Ну, это значит:
> 2. Эти индексы у тебя есть. :)

только по полям в условии where
в order by эти поля могут использоваться а могут и не использоваться

← →
stone © (2002-11-18 16:53) [10]

> но запросы короткие (возвращают мало записей)

чаще всего да (при работе приложений, там большие рекордсеты как раз и не нужны), но при анализе БД бывают довольно большие наборы строк (до 500000)

← →
EternalWonderer (2002-11-18 17:09) [11]

SELECT STATEMENT Optimizer=CHOOSE
SORT (ORDER BY)
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS (BY INDEX ROWID) OF GR1
INDEX (RANGE SCAN) OF IND1 (NON-UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF LIST_SI
INDEX (UNIQUE SCAN) OF LIST_SI_PK0 (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF CATALOG_PLACES
INDEX (UNIQUE SCAN) OF CATALOG_PLACES_PK0 (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF CATALOG_SI
INDEX (UNIQUE SCAN) OF CATALOG_SI_PK0 (UNIQUE)

Похоже на правду. Получается, что Oracle сначала формирует все строки, в т.ч. вытаскивая данные из присоединённых "дочерних" таблиц (что небыстро :), и только потом задумывается, в каком порядке их вывести.

Заставить Oracle действовать наоборот принципиально невозможно?

← →
petr_v_a © (2002-11-18 18:06) [12]

принципиально возвожно хинтом /*+ use_smj */ но вот нужно ли???
сколько записей возвращает
select ID from t1 where s1=1 order by o1,o2,o3 ?
при разных order by скорость сильно разная?

← →
Внук © (2002-11-18 19:40) [13]

Если Optimizer Goal = Choose, то план выполнения запроса и скорость работы будут существенно зависеть от наличия статистики и ее "свежести". См. analyze table... и dbms_stat package.
Заставить использовать индекс можно, как было сказано выше, директивой оптимизатору, вот только зачем? При правильной постановке дела Oracle сам примет наилучшее решение.
К тому же, вполне возможно, в данном конкретном случае будет достаточно поставить /*+ First_Rows*/.
Однако, таблица без первичного ключа в SQL-ориентированной базе - это, как бы сказать помягче..., неразумно :) Другое дело, что он может быть естественным или искусственным, простым или составным. В будущем весьма вероятны проблемы при малейшем усложнении задачи.
Да, чуть не забыл о главном вопросе :) Наличие order by на самом деле влияет на использование индексов, это нетрудно увидеть экспериментально на планах запросов. Наилучшим с этой точки зрения является, когда строка SORT (ORDER BY) заменяется на FILTER.

Внук © (18.11.02 19:40)
Согласен с Вами, но в данном конкретном случае "сhoose" уже явно сделан в пользу FIRST_ROWS
Другой вопрос, что, возможно, так сложилось, что NESTED_LOOPS оказались, мягко говоря, не самыми оптимальным access path`ом.

Ага. Еще заметьте, что в изначальном вопросе про связанные таблицы речи не было, так что автор лукавит :-))

← →
EthernalWonderer (2002-11-18 20:59) [16]

Да, признаю, вначале вопрос формулировался иначе - я пытался разбить одну большую проблему на мелкие :). Спасибо за советы, буду экспериментировать.

Ну вот, я же знал, что Oracle безбожно крут! :)

2EternalWonderer (18.11.02 17:09)
>Получается, что Oracle сначала формирует все строки..., и только потом задумывается, в каком порядке их вывести.
>Заставить Oracle действовать наоборот принципиально невозможно?
И не только Оракл, ИМХО. Сортировать то чего еще нет крайне проблематично. 8-)

>select ID from t1 where s1=1 order by o1,o2,o3
Для этого запроса прежде всего нужен индекс по s1

>Вопрос: как построить индексы, чтобы скорость выполнения запроса была наибольшей при любом порядке полей в order by?
Тут возможно не только индексами надо играть, но и настройками Оракла. Так для сессий с большими запросами можно попробовать задавать больший SORT_AREA_SIZE для уменьшения сортировок на диске.
А индексы... Индексы для сортировки используются когда порядок полей в order by совпадает(хотя бы частично) с порядком полей в индексе. Иначе нет. Поэтому строить надо или по всем комбинациям, или вообще не строить. 8-) Второе ИМХО правильнее.

2 Sergey13 © (19.11.02 10:41)

>> Получается, что Oracle сначала формирует все строки...,
>> и только потом задумывается, в каком порядке их вывести.
>> Заставить Oracle действовать наоборот принципиально
>> невозможно?
> И не только Оракл, ИМХО. Сортировать то чего еще нет крайне
> проблематично. 8-)

Я бы не был так категоричным. Почиьтай вот это:
http://www.krista.ru/ib/plan-intro.html
От того, как (в какой их сортировке, т.е. какие индексы таблиц будут использованы) ты будешь объединять потоки, зависит производительность.
Это, конечно, по IB/FB, но все равно принцип должен быть тот же, ну или похожим. Уж если у IB так все сложно, то у Orace и подавно...

Про ускорение запроса и индексы Oracle. Найти похожие ветки