Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Возможности оптимизации исчерпаны ?  [new]
ora2007
Guest
Добрый день, есть некая функция (func), которая состоит из одного запроса:
 
select ni.id 
  from (select max(ind_n) min, min(ind_k) mik
            from(select ind_n, ind_k, 
                            length(n.ind_n) ln, length(n.ind_k) lk,
                            max(length(n.ind_n)) over() mln,
                            max(length(n.ind_k)) over() mlk
                     from num_index n   
                   where rpad(<НОМЕР ТЕЛЕФОНА>,30,0) between rpad(ind_n,30,0) and rpad(ind_k,14,9)
                   )
           where ln=mln and lk=mlk) ni1,
          num_index ni
where ind_n=min and ind_k=mik and rownum=1;

Эта функция имеет входным параметром <НОМЕР ТЕЛЕФОНА>, а возвращает идентификатор направления, например Ростелеком, Уралсвязьинформ, Межрегиональный Транзит Телеком и т.д.
В таблице num_index заданы диапазоны индексов в полях ind_n(начальный индекс) и ind_k (конечный индекс)

Например:
id |             name            |  ind_n  |  ind_k  |
5  | Краснодарская Сотовая Связь |901490  | 901491 |
15 ноя 07, 07:36    [4918488]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
ora2007
Guest
Так вот в последнем случае, если <номер телефона> = 89014905678, то функция должна вернуть 5.

Для конкретного телефона функция работает достаточно быстро, но я хочу её использовать в запросе со звонками, где миллионы записей:

select data, nomer_telefona, FUNC(nomer_telefona) from ZVONKI;

И вот тут запрос ужасно тормозит, выполняется 3 часа для 3 000 000 записей.

На таблицу num_index завёл индекс по функции (rpad(ind_n,30,0), rpad(ind_k,30,9), id, ind_n, ind_k)

Что ещё можно придумать для ускорения ?
15 ноя 07, 07:41    [4918495]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
https://www.sql.ru/forum/actualthread.aspx?tid=339684&hl=get_pset_id#3154153
15 ноя 07, 07:55    [4918503]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54388
ora2007
Что ещё можно придумать для ускорения ?
Попытаться сделать представление - или вместо функции, или как параллельное дополнение. Имхо, представление - более пригодный механизм для работы с миллионами записей.
15 ноя 07, 08:44    [4918566]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
wdelete
Member

Откуда:
Сообщений: 63
храни даные по диапозонам с лева как ключено а справа нет. для это прибавляй 1 к праваму значению диапазона направлений.

так будут использоваться индексы без всяких индеквом по функциям и лишних операци (rpad)
create table num_index(id,ind_n  , ind_k, primary key(ind_n,ind_k,id))
organization index
as
select 1 id,'901490' ind_n, '901492' ind_k from dual--диапазон соответствует номерам начинающимся с 901490,901491 как в примере
union all
select 2 id,'9014901' ind_n, '9014906' ind_k from dual--диапазон соответствует номерам начинающимся с 9014901-9014905. поддиапазон первого диапазона
union all
select 3 id,'901492' ind_n, '901493' ind_k from dual

ну и запросик немного красивей вишел
select max(id)keep(dense_rank last order by ind_n)
from num_index ni
where ind_n<='9014905678'   and ind_k>'9014905678'
15 ноя 07, 12:41    [4920128]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
ora2007
Так вот в последнем случае, если <номер телефона> = 89014905678, то функция должна вернуть 5.

Для конкретного телефона функция работает достаточно быстро, но я хочу её использовать в запросе со звонками, где миллионы записей:

select data, nomer_telefona, FUNC(nomer_telefona) from ZVONKI;

И вот тут запрос ужасно тормозит, выполняется 3 часа для 3 000 000 записей.

На таблицу num_index завёл индекс по функции (rpad(ind_n,30,0), rpad(ind_k,30,9), id, ind_n, ind_k)

Что ещё можно придумать для ускорения ?
Поверхностные мнения, основанные на опыте решения подобных задач:
1. Индекс не нужен, так как соединение таблиц по условию BETWEEN
лучше выполняется методом MERGE JOIN (по сравнению с NESTED LOOPS)
2. Убрать аналитические функции
3. Переформулировать запрос без INLINE VIEW (без SELECT ... FROM (SELECT ... FROM (SELECT ...))) )
15 ноя 07, 13:14    [4920428]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
ora2007
Так вот в последнем случае, если <номер телефона> = 89014905678, то функция должна вернуть 5.

Для конкретного телефона функция работает достаточно быстро, но я хочу её использовать в запросе со звонками, где миллионы записей:

select data, nomer_telefona, FUNC(nomer_telefona) from ZVONKI;

И вот тут запрос ужасно тормозит, выполняется 3 часа для 3 000 000 записей.

На таблицу num_index завёл индекс по функции (rpad(ind_n,30,0), rpad(ind_k,30,9), id, ind_n, ind_k)

Что ещё можно придумать для ускорения ?
перепишите запрос на чистом sql (делов то;)
15 ноя 07, 13:21    [4920484]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
wdelete
Member

Откуда:
Сообщений: 63
SQL*Plus
ora2007
Так вот в последнем случае, если <номер телефона> = 89014905678, то функция должна вернуть 5.

Для конкретного телефона функция работает достаточно быстро, но я хочу её использовать в запросе со звонками, где миллионы записей:

select data, nomer_telefona, FUNC(nomer_telefona) from ZVONKI;

И вот тут запрос ужасно тормозит, выполняется 3 часа для 3 000 000 записей.

На таблицу num_index завёл индекс по функции (rpad(ind_n,30,0), rpad(ind_k,30,9), id, ind_n, ind_k)

Что ещё можно придумать для ускорения ?
Поверхностные мнения, основанные на опыте решения подобных задач:
1. Индекс не нужен, так как соединение таблиц по условию BETWEEN
лучше выполняется методом MERGE JOIN (по сравнению с NESTED LOOPS)
2. Убрать аналитические функции
3. Переформулировать запрос без INLINE VIEW (без SELECT ... FROM (SELECT ... FROM (SELECT ...))) )


а можно увидеть вариант переписаный без подзапросов и использующий merge_join
15 ноя 07, 15:36    [4921393]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
wdelete
а можно увидеть вариант переписаный без подзапросов и использующий merge_join
Данных подходящих сейчас под рукой нет, но будет это что-то вроде:
SELECT I.* FROM ind I, zvonki Z
WHERE RPAD(Z.nomer_tel,15,'0') BETWEEN RPAD(I.ind_n,15,'0') AND RPAD(I.ind_k,15,'9')
План будет примерно такой
Plan
SELECT STATEMENT  CHOOSE Cost: ...
	5 MERGE JOIN  Cost: ...
		2 SORT JOIN  Cost: ...
			1 TABLE ACCESS FULL MYUSER.IND   Cost: ...
		4 SORT JOIN    Cost: ...
			3 TABLE ACCESS FULL  myuser.ZWONKI   Cost: ...
15 ноя 07, 16:50    [4922032]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
wdelete
Member

Откуда:
Сообщений: 63
этот вариант не решает задачи тут идет просто соединение а надо соединее с направлением наибольшей длины.
15 ноя 07, 16:58    [4922122]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
Mikst
Member

Откуда: Москва
Сообщений: 983
wdelete
этот вариант не решает задачи тут идет просто соединение а надо соединее с направлением наибольшей длины.


все ИМХО: приводить таблицу-справочник к нормализованному виду (т.е. уходить от диапазонов и раскрывать маску до конца:
901490 | 901491
получится две строки
901490*
901491*

и искать по индексу в цикле с постепенным уменьшением длины, пока не будет найдено.
Еще лучше привести все коды к одной длине, но это не всегда возможно
15 ноя 07, 17:05    [4922172]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
wdelete
этот вариант не решает задачи тут идет просто соединение а надо соединее с направлением наибольшей длины.
Разбейте направления на интервалы.
"Направления наибольшей длины" хороши для оценщика, который обрабатывает звонки индивидуально.
15 ноя 07, 17:21    [4922314]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
Shmyg
Member

Откуда: Kiev
Сообщений: 56
Когда-то очень давно писал подобную функцию. Только там тоже поиск был по маске, т.е. телефоны были не в интервалах, а в таком виде:

digits des
1 США
12 Оператор1
123 Оператор2

Выглядит приблизительно так.

CREATE OR REPLACE
FUNCTION best_match
(
i_b_number VARCHAR2
)
RETURN VARCHAR2
AS

TYPE char_tab_type
IS TABLE
OF VARCHAR2(63)
INDEX BY BINARY_INTEGER;

zones_t char_tab_type;

CURSOR zones_cur
IS
SELECT des
FROM zones
WHERE digits = SUBSTR( i_b_number, 1, LENGTH( digits ) )
ORDER BY LENGTH( digits );

v_des zones.des%TYPE;
v_digits zones.digits%TYPE;

BEGIN

OPEN zones_cur;
FETCH zones_cur
BULK COLLECT
INTO zones_t;

CLOSE zones_cur;
IF zones_t.COUNT = 0
THEN
RETURN ('No value' );
ELSE
RETURN( zones_t( zones_t.LAST ) );
END IF;

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE( i_b_number );
END;
/

Конечно, лучше на чистом SQL, но в тот момент решения не нашлось. Возможно, можно добавить колонку в таблицу, в которой хранить длину префикса, по которому идет поиск?

ЗЫ. Я смотрю, эта задача вообще очень популярна в телекоммуникациях :)
15 ноя 07, 17:36    [4922416]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
Shmyg
Когда-то очень давно писал подобную функцию. Только там тоже поиск был по маске, т.е. телефоны были не в интервалах, а в таком виде:

digits des
1 США
12 Оператор1
123 Оператор2

Выглядит приблизительно так.

CREATE OR REPLACE
FUNCTION        best_match
        (
        i_b_number      VARCHAR2
        )
RETURN  VARCHAR2
AS

        TYPE    char_tab_type
        IS      TABLE
        OF      VARCHAR2(63)
        INDEX   BY BINARY_INTEGER;

        zones_t char_tab_type;

        CURSOR  zones_cur
        IS
        SELECT  des
        FROM    zones
        WHERE   digits = SUBSTR( i_b_number, 1, LENGTH( digits ) )
        ORDER   BY LENGTH( digits );

        v_des           zones.des%TYPE;
        v_digits        zones.digits%TYPE;

BEGIN

        OPEN    zones_cur;
        FETCH   zones_cur
        BULK    COLLECT
        INTO    zones_t;

        CLOSE   zones_cur;
        IF      zones_t.COUNT = 0
        THEN
                RETURN  ('No value' );
        ELSE
                RETURN( zones_t( zones_t.LAST ) );
        END     IF;

EXCEPTION
        WHEN    OTHERS
        THEN
                DBMS_OUTPUT.PUT_LINE( i_b_number );
END;
/
Конечно, лучше на чистом SQL, но в тот момент решения не нашлось. Возможно, можно добавить колонку в таблицу, в которой хранить длину префикса, по которому идет поиск?

ЗЫ. Я смотрю, эта задача вообще очень популярна в телекоммуникациях :)

При оформлении кода используйте, пожалуйста, тег SRC данного форума.
15 ноя 07, 17:41    [4922451]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54388
а сколько примерно строк в справочнике направлений?
15 ноя 07, 21:19    [4923196]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
ora2007
Guest
andreymx
а сколько примерно строк в справочнике направлений?


2580




Если отказаться от аналитических функций и вложенных запросов, то надо будет писать group by. А в плане у него cost больше чем у исходного запроса.
16 ноя 07, 08:16    [4923614]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
ora2007
Guest


К сообщению приложен файл. Размер - 0Kb
16 ноя 07, 08:23    [4923629]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
wdelete
Member

Откуда:
Сообщений: 63
было приведено множесто возможных вариантов решения. можно узнать сравнения результатов выполнения. если они конечно проводились.
16 ноя 07, 15:32    [4926670]     Ответить | Цитировать Сообщить модератору
 Re: Возможности оптимизации исчерпаны ?  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54388
Перечитывая Миллсапа:
Время на общее выполнение функции = (время на один раз) * (количество выполнений).
Не думали, можно ли снизить показатель (количество выполнений) ?
Если ли одинаковые телефоны в запросе/ZVONKI и сколько таких повторений?
Возможно ли объединить телефоны в группы (например, отбрасыванием последней цифры)?
16 ноя 07, 16:42    [4927307]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить