Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 ORDER BY по полям из справочника  [new]
Serge N
Member

Откуда:
Сообщений: 158
Добрый день!

Есть 2 таблицы. Одна - справочник с двумя полями. A.ID, A.NAME
Вторая - очень большая таблица с данными, ссылается на справочник. B.ID, B.A_ID, B.DATA
Есть индексы по первичным и вторичным ключам.
Есть собственная функция CONV, переводящая данные в число по своему алгоритму.

Запрос:
select a.name, b.* 
from b,a
where b.a_id=a.id
order by conv(b.data), a.name

Работает очень медленно. Но если отбросить order by, То быстро.
Какой индекс нужно создать, чтобы работало быстро и с Order By?
23 мар 09, 14:45    [6963634]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY по полям из справочника  [new]
Зелебоба
Member

Откуда:
Сообщений: 1121
Serge N
Добрый день!

Есть 2 таблицы. Одна - справочник с двумя полями. A.ID, A.NAME
Вторая - очень большая таблица с данными, ссылается на справочник. B.ID, B.A_ID, B.DATA
Есть индексы по первичным и вторичным ключам.
Есть собственная функция CONV, переводящая данные в число по своему алгоритму.

Запрос:
select a.name, b.* 
from b,a
where b.a_id=a.id
order by conv(b.data), a.name

Работает очень медленно. Но если отбросить order by, То быстро.
Какой индекс нужно создать, чтобы работало быстро и с Order By?


что делает conv(b.data)?
23 мар 09, 14:48    [6963660]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY по полям из справочника  [new]
Bely
Member

Откуда: Москва
Сообщений: 1903
Serge N
Какой индекс нужно создать, чтобы работало быстро и с Order By?
FBI, если возможно будет создать.
23 мар 09, 14:51    [6963696]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY по полям из справочника  [new]
Alexey181
Member

Откуда: default city
Сообщений: 907
как вариант индекс на основе функции создать.
23 мар 09, 14:52    [6963706]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY по полям из справочника  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
еще (кроме fbi) вариант - храните в той таблице то число как атрибут
23 мар 09, 14:56    [6963734]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY по полям из справочника  [new]
Bely
Member

Откуда: Москва
Сообщений: 1903
orawish
еще (кроме fbi) вариант - храните в той таблице то число как атрибут
Ну это тот же FBI, но "на коленках" сделаный :)
23 мар 09, 15:00    [6963774]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY по полям из справочника  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Bely,

конечно! но на коленке - богаче выбор инструментов по части
(если возможно будет создать) :)
23 мар 09, 15:10    [6963864]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY по полям из справочника  [new]
Zloxa
Member

Откуда: СССР ☭
Сообщений: 1033
Вот народ советует построить FBI, да не договаривает.

Построенный индекс не будет использован для сортировки результирующего множества. Оказать влияние на производительность запроса он сможет, лишь если по нему будет производиться доступ, тогда надобность в сортировке отпадет.

Для того, чтобы была возможность производить доступ по новопостроенному индексу, надо тоже постараться. Наверняка придется изменять текст запроса (как минимум добавлять where conv(b.data) is not null)

При этом, если будет производится доступ по индексу, вместо 2х FTS+Hash Join мы получим индекс full scan+скан всей таблицы по rowid + NL, что в общем случае, скорее увеличит время выполнения запроса. Уменьшится лишь время отклика.
23 мар 09, 15:18    [6963919]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY по полям из справочника  [new]
Serge N
Member

Откуда:
Сообщений: 158
Зелебоба,

conv переводит строку в число. причем если в строке встречается нечисловой символ, все что после него не анализируется.

FUNCTION conv(strValue in varchar2)
return integer 
is
  Result integer;
  i integer;
  c char;
  s varchar2(20);
begin
  if strValue is null then
    return 0;
  end if;
  s := '';
  for i in 1..length(strValue) loop
    c := substr(strValue, i, 1);
    EXIT WHEN InStr('0123456789',c)<=0;
    s := s || c;
  end loop;
  
  if (length(s)>0) then
    return cast(s as integer);
  else
    return 0;
  end if;
end conv;

23 мар 09, 16:24    [6964477]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY по полям из справочника  [new]
__vvp_
Member

Откуда: Санкт-Петербург
Сообщений: 1158
Serge N,
ТОП популярных вопросов. Вопрос 10
23 мар 09, 16:31    [6964542]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY по полям из справочника  [new]
__vvp_
Member

Откуда: Санкт-Петербург
Сообщений: 1158
Serge N
select a.name, b.* 
from b,a
where b.a_id=a.id
order by conv(b.data), a.name

Работает очень медленно. Но если отбросить order by, То быстро.
Какой индекс нужно создать, чтобы работало быстро и с Order By?

Дело в функции, не в индексе. Попробуй так
select a.name, b.* 
from b,a
where b.a_id=a.id
order by to_number(replace(b.data, ltrim(b.data, '1234567890'))), b.data, a.name
23 мар 09, 17:45    [6965060]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY по полям из справочника  [new]
Serge N
Member

Откуда:
Сообщений: 158
Если в Order By вообще не использовать функцию, вместо 14 секунд работает 13
order by a.name

Но если вообще отбросить Order By, то меньше секунды

проблема в том, что сортировка без индекса, а не конкретно в функции
26 мар 09, 11:28    [6977407]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY по полям из справочника  [new]
Zloxa
Member

Откуда: СССР ☭
Сообщений: 1033
Меньше секунды вы получаете отклик.
Весь набор данных профетчится, думаю больше чем за 13 сек.
26 мар 09, 11:52    [6977586]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить