Помогите убрать full table scan из плана запроса?
Владимир Муравлев
select * from ord, client
where upper(client.name) like :search_string
and ord.client_id=client.client_id
По upper(client.name) построен индекс,
которым успешно пользуемся
По ord.client_id тоже есть индекс, но получаем full table
scan :(
Если запрос сделать не параметрическим, то есть заменить :search_string на
константу, например 'ИВА%', то индексом по ord.client начинаем пользоваться
В
табличке ord - 300000 записей, а табличке client - 50000.
Вопрос: Как оставить запрос
параметрическим и при этом пользоваться индексом?
Понимаю, что можно явно указать
хинт index, но это не красиво. Что его во всех подобных запросах пихать? Может
на такое
поведение влияют какие-нибудь параметры сервера или сбора статистики?
По
табличкам ord и client собрана статистика for all indexed columns
Oracle 8.1.7.0.0 NT
--
Отправлено через сервер Форумы@mail.ru - http://talk.mail.ru
Aleksandr V. Konakov
Tuesday, February 19, 2002, 2:52:25 PM, Вы писали:
> Запрос:
> select * from ord, client
> where upper(client.name) like :search_string
> and ord.client_id=client.client_id
> По upper(client.name) построен индекс,
> которым успешно пользуемся
> По ord.client_id тоже есть индекс, но получаем full table
> scan :(
> Если запрос сделать не параметрическим, то есть заменить :search_string на
> константу, например 'ИВА%', то индексом по ord.client начинаем пользоваться
В общем случае выражение LIKE может содержать шаблон типа '%A%'. То
есть значение поля может начинаться с любых символов. О каком
использовании индекса здесь может идти речь?
--
С наилучшими пожеланиями,
Александр Конаков
Valery Yourinsky
>
> Здравствуйте, Владимир!
>
> Tuesday, February 19, 2002, 2:52:25 PM, Вы писали:
>
> > Запрос:
> > select * from ord, client
> > where upper(client.name) like :search_string
>
> > and ord.client_id=client.client_id
> > По upper(client.name) построен индекс,
> > которым успешно пользуемся
> > По ord.client_id тоже есть индекс, но получаем full table
> > scan :(
>
> > Если запрос сделать не параметрическим, то есть заменить :search_string на
> > константу, например 'ИВА%', то индексом по ord.client начинаем пользоваться
>
> В общем случае выражение LIKE может содержать шаблон типа '%A%'. То
> есть значение поля может начинаться с любых символов. О каком
> использовании индекса здесь может идти речь?
Томас Кайт (http://asktom.oracle.com)
показал такой вариант:
SELECT * FROM my_table
WHERE ROWID IN
(SELECT /*+ INDEX_FFS(my_table my_index) */
ROWID
FROM my_table
WHERE my_column LIKE '%SUBSTRING%')
Однако следует помнить, что INDEX FAST FULL SCAN может
оказаться достаточно недешевой операцией.
Валерий Юринский
--
Oracle8 Certified DBA
Moscow, Russia
Владимир Муравлев
Ваше
справедливое, на мой взгляд, замечание.
select * from ord, client
where
upper(client.name) like :a
and ord.client_id=client.client_id
Получаем
план:
SELECT STATEMENT, GOAL = CHOOSE
HASH JOIN
TABLE ACCESS BY INDEX
ROWID DBO CLIENT
INDEX RANGE SCAN DBO CLNT_UPPER_NAME
TABLE ACCESS
FULL DBO ORD
Меня волнует другое: как заставить его использовать индекс по
ORD.CLIENT_ID вместо full scan по ORD. Вероятно он думает, что кол-во записей,
выбранных
из CLIENT будет настолько велико, что выгоднее будет сканировать всю таблицу
ORD, чем
использовать индекс.
Повторюсь, что если указать не парметр, а конкретное значение,
он ведет себя по-другому.
Например значение 'И%' дает тот же план запроса (см. выше) а
значение 'ИВА%' приводит к использованию индекса по ORD.CLIENT_ID. Вероятно
оптимизатор по статистике значений в колонке CLIENT.NAME определеяет примерное
количество клиентов с названием на ИВА и считает, что их будет не много -
выгодно
использовать индекс. Так вот, как заставить оптимизатор думать таким же
образом
(оптимистично), когда запрос параметрический?
--
Pavel Kirilovsky
VY> Томас Кайт (http://asktom.oracle.com)
VY> показал такой вариант:
VY> SELECT * FROM my_table
VY> WHERE ROWID IN
VY> (SELECT /*+ INDEX_FFS(my_table my_index) */
VY> ROWID
VY> FROM my_table
VY> WHERE my_column LIKE '%SUBSTRING%')
VY> Однако следует помнить, что INDEX FAST FULL
VY> SCAN может
VY> оказаться достаточно недешевой операцией.
А если Select из нескольких таблиц ?
Как тогда с ROWID ?
--
Pavel Kirilovsky
DBA, IT developer
Valery Yourinsky
>
> Valery Yourinsky <v...@bill.mts.ru> пишет:
> VY> Томас Кайт (http://asktom.oracle.com)
> VY> показал такой вариант:
>
> VY> SELECT * FROM my_table
> VY> WHERE ROWID IN
> VY> (SELECT /*+ INDEX_FFS(my_table my_index) */
> VY> ROWID
> VY> FROM my_table
> VY> WHERE my_column LIKE '%SUBSTRING%')
>
> VY> Однако следует помнить, что INDEX FAST FULL
> VY> SCAN может
> VY> оказаться достаточно недешевой операцией.
>
> А если Select из нескольких таблиц ?
> Как тогда с ROWID ?
Аналогично.
Условие
WHERE my_column LIKE '%SUBSTRING%'
заменяется на
WHERE ROWID IN
(SELECT /*+ INDEX_FFS(my_table my_index) */
ROWID
FROM my_table
WHERE my_column LIKE '%SUBSTRING%')
Валерий Юринский
Vladimir Loskutnikov
так: from client,ord
--
Vladimir Loskutnikov
"Владимир Муравлев" <m...@ipc.ru> wrote in message
news:a4vnsq$nro$1...@host.talk.ru...
Владимир Муравлев
VL> попробуй поменять порядок
перечисления таблиц в
VL> запросе, т.е написать вот
VL> так: from client,ord
Не
помогло
Владимир Муравлев
запроса и
использованием хинтов. А именно настройками оптимизатора.
у меня были установлены
параметры
optimizer_index_caching=90
optimizer_index_cost_adj=10
Изменения
optimizer_index_caching от 1 до 99 ни к чему не привели, а вот после установки
optimizer_index_cost_adj=9 (всего 1 процент разницы!!!) любое выражение LIKE
(кроме
'%') использует индекс по ORD.CLIENT_ID. И параметрический запрос ТОЖЕ!
Даже если
задать значение LIKE '%НОВ', по табличке CLIENT (естественно) делается FTS, а
по ORD -
INDEX RANGE SCAN, что мне и требовалось
Ура! И всем спасибо за помощь.