Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 помогите оптимизировать запрос  [new]
-Евгений-
Member

Откуда: Киев
Сообщений: 225
2 источника данных astlo и askr, в них хранятся населенные пункты. в astlo_str и askr_str находятся улицы. dir_street_type_alias - некий сводный справочник типов.
мне надо найти одинаковые улицы в одинаковых нас.п.
запрос, выводит первые 10 записей секунд за 9-10 (посчитать общее кол-во так и не смог(() :
+
select astlo_str.nplace_id,
       nvl(askr_str.dest_guid, askr_str.alt_guid) fguid,
       case
         when askr_str.dest_guid is not null and
              askr_str.alt_guid is null then
          2
         when askr_str.dest_guid is null and
              askr_str.alt_guid is not null then
          4
       end ftype
--
from   astloaddress astlo
inner  join astloaddress astlo_str on astlo_str.nparent = astlo.nplace_id
inner  join dir_obj_type astlo_d on upper(astlo_d.short_name) = upper(nvl(astlo_str.vplacetype, 'null'))
--
inner  join askraddress askr on nvl(askr.match_guid, askr.alt_guid) = astlo.match_guid
inner  join askraddress askr_str on askr_str.nparent = askr.nplace_id and
                                    upper(astlo_str.name_ukr) = upper(askr_str.name_ukr)
inner  join dir_obj_type askr_d on upper(askr_d.short_name) = upper(nvl(askr_str.vplacetype, 'null')) and
                                   upper(astlo_d.name) = upper(askr_d.name));

в таблице созданы индексы/ключи:
+
astlo:
ASTLO_NPLACE_ID_PK - NPLACE_ID (создался индекс - ASTLO_NPLACE_ID_PK)
ASTLO_NPARENT_FK - NPARENT

ASTLO_HIER_IDX1 - NPLACE_ID, NPARENT
ASTLO_HIER_IDX2 - NPARENT, NPLACE_ID
ASTLO_MATCH_GUID_IDX - MATCH_GUID

askr:
ASKR_NPLACE_ID_PK - NPLACE_ID (создался индекс - ASKR_NPLACE_ID_PK)

ASKR_ALT_GUID_IDX - ALT_GUID
ASKR_DEST_GUID_IDX - DEST_GUID
ASKR_HIER_IDX1 - NPLACE_ID, NPARENT
ASKR_HIER_IDX2 - NPARENT, NPLACE_ID
ASKR_MATCH_GUID_IDX - MATCH_GUID
ASKR_MATCH_IDX - MATCH_STATUS, MATCH_TYPE
ASKR_NPARENT_IDX - NPARENT


ссылка на план

select count(*) from askraddress;  -- 4 879 261
select count(*) from astloaddress; --   514 868


делал обновление, записей по найденным соответствиям, блок выполнялся 6069,421 и обновил 24102 записей (2013.02.05 11:29:36-13:10:46. ok = 24102) сек О_о но хоть закончился))
+
declare
  /*
      2013.02.05 11:29:36-13:10:46. ok = 24102
  */
  d1 timestamp;
  d2 timestamp;
  ok integer;
  --
  cursor updater is(
    select astlo_str.nplace_id,
           nvl(askr_str.dest_guid, askr_str.alt_guid) fguid,
           case
             when askr_str.dest_guid is not null and
                  askr_str.alt_guid is null then
              2
             when askr_str.dest_guid is null and
                  askr_str.alt_guid is not null then
              4
           end ftype
    --
    from   astloaddress astlo
    inner  join astloaddress astlo_str on astlo_str.nparent = astlo.nplace_id
    inner  join dir_obj_type astlo_d on upper(astlo_d.short_name) = upper(nvl(astlo_str.vplacetype, 'null'))
    --
    inner  join askraddress askr on nvl(askr.match_guid, askr.alt_guid) = astlo.match_guid
    inner  join askraddress askr_str on askr_str.nparent = askr.nplace_id and
                                        upper(astlo_str.name_ukr) = upper(askr_str.name_ukr)
    inner  join dir_obj_type askr_d on upper(askr_d.short_name) = upper(nvl(askr_str.vplacetype, 'null')) and
                                       upper(astlo_d.name) = upper(askr_d.name));
begin
  --
  ok := 0;
  select current_date into d1 from dual;
  --
  for updater_data in updater
  loop
    ok := ok + 1;
    update astloaddress --
    set    match_type = updater_data.ftype,
           match_guid = updater_data.fguid
    where  nplace_id = updater_data.nplace_id;
  end loop;
  --
  select current_date into d2 from dual;
  --
  dbms_output.put_line(to_char(d1, 'YYYY.MM.DD HH24:MI:SS') || '-' || to_char(d2, 'HH24:MI:SS') || '. ok = ' || ok);
end;


как можно ускорить сие безобразие? а то дальше буду сравнивать дома по этим улицам... а там их.....
5 фев 13, 15:22    [13878379]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34630
-Евгений-,

Такие вот JOIN-ы :

inner  join dir_obj_type astlo_d on upper(astlo_d.short_name) = upper(nvl(astlo_str.vplacetype, 'null'))
inner  join askraddress askr on nvl(askr.match_guid, askr.alt_guid) = astlo.match_guid
inner  join askraddress askr_str on askr_str.nparent = askr.nplace_id and
                                    upper(astlo_str.name_ukr) = upper(askr_str.name_ukr)
inner  join dir_obj_type askr_d on upper(askr_d.short_name) = upper(nvl(askr_str.vplacetype, 'null')) and
                                   upper(astlo_d.name) = upper(askr_d.name));


НИКОГДА не будут работать (быстро т.е.).
Переписывайте, JOIN-ы должны быть только строго по = .
Если ваши данные это не позволяют -- приводите в порядок данные, вы всё равно не сможете с ними работать.

Конечно, можно придумывать всякие функциональные индексы и прочую фигню, но всё это мёртвому припарки.
5 фев 13, 15:28    [13878416]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
-Евгений-
Member

Откуда: Киев
Сообщений: 225
MasterZiv
-Евгений-,

Такие вот JOIN-ы :

inner  join dir_obj_type astlo_d on upper(astlo_d.short_name) = upper(nvl(astlo_str.vplacetype, 'null'))
inner  join askraddress askr on nvl(askr.match_guid, askr.alt_guid) = astlo.match_guid
inner  join askraddress askr_str on askr_str.nparent = askr.nplace_id and
                                    upper(astlo_str.name_ukr) = upper(askr_str.name_ukr)
inner  join dir_obj_type askr_d on upper(askr_d.short_name) = upper(nvl(askr_str.vplacetype, 'null')) and
                                   upper(astlo_d.name) = upper(askr_d.name));


НИКОГДА не будут работать (быстро т.е.).
Переписывайте, JOIN-ы должны быть только строго по = .

вы про UPPER или про сам INNER JOIN ?

сделал так:
select astlo_str.nplace_id,
       nvl(askr_str.dest_guid, askr_str.alt_guid) fguid,
       case
         when askr_str.dest_guid is not null and
              askr_str.alt_guid is null then
          2
         when askr_str.dest_guid is null and
              askr_str.alt_guid is not null then
          4
       end ftype
--
from   astloaddress astlo,
       astloaddress astlo_str,
       dir_obj_type astlo_d,
       --
       askraddress  askr,
       askraddress  askr_str,
       dir_obj_type askr_d
--
where  astlo_str.nparent = astlo.nplace_id and
       upper(astlo_d.short_name) = upper(nvl(astlo_str.vplacetype, 'null')) and
       nvl(askr.match_guid, askr.alt_guid) = astlo.match_guid and
       askr_str.nparent = askr.nplace_id and
       upper(astlo_str.name_ukr) = upper(askr_str.name_ukr) and
       upper(askr_d.short_name) = upper(nvl(askr_str.vplacetype, 'null')) and
       upper(astlo_d.name) = upper(askr_d.name)

тоже самое.. план собственно такой же...
5 фев 13, 15:34    [13878457]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
Я на измене
Guest
-Евгений-,

выложи план сюда. с работы не пускает по твоей ссылке
5 фев 13, 15:50    [13878571]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
-Евгений-
Member

Откуда: Киев
Сообщений: 225
Я на измене
-Евгений-,

выложи план сюда. с работы не пускает по твоей ссылке


К сообщению приложен файл. Размер - 47Kb
5 фев 13, 15:51    [13878586]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
eev
Member

Откуда: -
Сообщений: 1156
MasterZiv
Переписывайте, JOIN-ы должны быть только строго по = .

хе, у него что-то другое чем не "=" написано?

-Евгений-
1. если вы хотите использовать индексы и всякие UPPER, то сюда http://docs.oracle.com/cd/E11882_01/server.112/e16638/data_acc.htm#i9946
вот попробуйте сначала на 1, 2-х таблицах, посмотрите что получиться
2. на картинке у вас lang='uk', но собственно вроде индекса и нет, как и в приведенном запросе.
Да, но это не факт что лекарство
5 фев 13, 15:53    [13878593]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
УкрТелеком
Guest
пока в УкрТелекоме пишут такие запросы он никогда не будет работать хорошо((
5 фев 13, 16:38    [13879007]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
-Евгений-
Member

Откуда: Киев
Сообщений: 225
eev
-Евгений-
1. если вы хотите использовать индексы и всякие UPPER, то сюда http://docs.oracle.com/cd/E11882_01/server.112/e16638/data_acc.htm#i9946
вот попробуйте сначала на 1, 2-х таблицах, посмотрите что получиться
2. на картинке у вас lang='uk', но собственно вроде индекса и нет, как и в приведенном запросе.
Да, но это не факт что лекарство

сначала по второму) сори, лажанулся с картинкой :) не то кинул :)
почитал я мануал. спасибо ;)
в общем сделал индексы:
create index astlo_u_name_ukr_idx on astloaddress (upper(name_ukr))
create index astlo_u_vplacetype_idx on astloaddress (upper(vplacetype))

create index askr_u_name_ukr_idx on askraddress (upper(name_ukr))
create index askr_u_vplacetype_idx on askraddress (upper(vplacetype))

create index dir_ot_u_short_name_idx on dir_obj_type (upper(short_name))

по времени, особой разницы пока не заметил.. но план улучшился...

УкрТелеком
пока в УкрТелекоме пишут такие запросы он никогда не будет работать хорошо((
ай. пля. кто это?))) палюсь)))

К сообщению приложен файл. Размер - 82Kb
5 фев 13, 16:41    [13879031]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
Corner
Member

Откуда:
Сообщений: 1270
вы для начала покажите планы из плюса с предикатами
5 фев 13, 17:04    [13879287]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
-Евгений-
Member

Откуда: Киев
Сообщений: 225
Corner
вы для начала покажите планы из плюса с предикатами

такое вы хотели увидеть ?
SQL> SELECT * FROM TABLE(dbms_xplan.display(null,null,'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2710723858

-----------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | TABLE ACCESS FULL | ASKRADDRESS |
| 3 | HASH JOIN | |
| 4 | HASH JOIN | |
| 5 | TABLE ACCESS FULL | DIR_OBJ_TYPE |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 6 | HASH JOIN | |
| 7 | TABLE ACCESS FULL | DIR_OBJ_TYPE |
| 8 | HASH JOIN | |
| 9 | TABLE ACCESS FULL| ASTLOADDRESS |
| 10 | TABLE ACCESS FULL| ASTLOADDRESS |
| 11 | TABLE ACCESS FULL | ASKRADDRESS |
-----------------------------------------------
5 фев 13, 17:13    [13879362]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
Добрый Э - Эх
Guest
-Евгений-
... но план улучшился...

какой из планов улучшился? Тот что ты куришь или тот, что Оракл построил.
Вроде как по картинкам - точно не второе...
5 фев 13, 17:16    [13879393]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
-Евгений-
Member

Откуда: Киев
Сообщений: 225
Добрый Э - Эх
какой из планов улучшился? Тот что ты куришь или тот, что Оракл построил.
Вроде как по картинкам - точно не второе...

вроде ж cost стал меньше. это разве не есть гуд?)
5 фев 13, 17:18    [13879405]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
-Евгений-,
ой не стоит трогать кост... это такой же мифический план)) вон MasterZiv долго курил прежде чем выдать про индексы на фулсканах)))
5 фев 13, 17:21    [13879429]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
Добрый Э - Эх
Guest
Vint
-Евгений-,
ой не стоит трогать кост... это такой же мифический план)) вон MasterZiv долго курил прежде чем выдать про индексы на фулсканах)))
+1
настолько баян, что уже классика.
5 фев 13, 17:22    [13879441]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
-Евгений-
Member

Откуда: Киев
Сообщений: 225
Vint
-Евгений-,
ой не стоит трогать кост... это такой же мифический план)) вон MasterZiv долго курил прежде чем выдать про индексы на фулсканах)))
так а что можно сделать-то ?
5 фев 13, 17:23    [13879448]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
-Евгений-,
ну... я могу быть не прав и меня можно будет бить палками)) но мне кажется странным что при поиске одинаковых улиц вы находите их намного больше чем есть и в одной и в другой таблице.... я не знаю схему данных не знаю задачи.. поэтому могу только попкорн кушать) и наблюдать)) вот за кардинальностью и наблюдаю)
5 фев 13, 17:25    [13879481]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
Добрый Э - Эх
Guest
-Евгений-
так а что можно сделать-то ?
думать, как переписать запрос в более адекватный вид. Как сейчас написаны условия соединения - так никакие индексы не помогут, что планы тебе и подтвердили.
5 фев 13, 17:26    [13879486]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
-Евгений-,
тут мысль пришла... может intersect? ведь и там и там множество))
5 фев 13, 17:26    [13879494]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
-Евгений-
Member

Откуда: Киев
Сообщений: 225
Vint
-Евгений-,
мне кажется странным что при поиске одинаковых улиц вы находите их намного больше чем есть и в одной и в другой таблице....
не.. нашло 24102 из 42370 (столько их в astlo)
Vint
-Евгений-,
тут мысль пришла... может intersect? ведь и там и там множество))
спасибо, сейчас попробую ;)
5 фев 13, 17:31    [13879548]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
-Евгений-,
ну и чтобы ускорить почитайте про foreach и sql%rowcount, чтобы обрабатывать пачками и не считать количество записей ручками)
5 фев 13, 17:35    [13879595]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
Я на измене
Guest
-Евгений-,

1. сколько возращает запрос:
select count(*)
  from astloaddress astlo_str, askraddress askr_str
 where upper(astlo_str.name_ukr) = upper(askr_str.name_ukr)

2. Поля short_name и name в таблице dir_obj_type уникальны ?
3. Удали свои новые индексы. MasterZiv пошутил
5 фев 13, 17:56    [13879751]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34630
-Евгений-,

"вы про UPPER или про сам"

конечно про UPPER, NVL и прочие элементы выражения в условиях JOIN-на.
В JOIN слева и справа от = должны быть только значения полей таблиц или константы.

Для NVL есть OUTER JOIN, кстати.
5 фев 13, 20:41    [13880454]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34630
Vint
-Евгений-,
ой не стоит трогать кост... это такой же мифический план)) вон MasterZiv долго курил прежде чем выдать про индексы на фулсканах)))


Йа не курю...
5 фев 13, 20:44    [13880467]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34630
Vint
-Евгений-,
ну и чтобы ускорить почитайте про foreach и sql%rowcount, чтобы обрабатывать пачками и не считать количество записей ручками)


А это-то тут при чём ?

Автору надо сделать одно -- если он хочеть JOIN ить по именам, приведённым в верхний регистр, то и ХРАНИТЬ данные в полях
надо приведёнными в верхний регистр.

update astloaddress 
 set nparent = UPPER(nparent);

upadte dir_obj_type 
  set short_name = upper(short_name) ;


и так далее.

Ну и индексы старые дропнуть и создать такие же без функций.
5 фев 13, 20:48    [13880487]     Ответить | Цитировать Сообщить модератору
 Re: помогите оптимизировать запрос  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34630
А, ещё добавить какое-то условие WHERE надо бы, не все ж записи ты обрабатывать будешь
5 фев 13, 20:49    [13880490]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить