ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Преобразование запроса с EXEISTS( select * from X where <some_cond_on_X_PK> ) к INNER JOIN
10 сообщений из 10, страница 1 из 1
Преобразование запроса с EXEISTS( select * from X where <some_cond_on_X_PK> ) к INNER JOIN
    #38607555
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
hi all

Дано:
1) две таблицы (master-detail), в мастере 2000 строк, в детали - по 500 строк на каждую мастер-строку.
2) мастер-таблица имеет два доп. идексированных поля: optype_id и state_id, число уник. значений в них = 10 и 3 соотв-но, сами значения распределены равномерно
3) после заполнения тестовыми данными выполнен пересчет статистики по индексам:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
recreate sequence gh;
recreate sequence gd;
recreate table td(id int primary key using index td_pk, doc_id int, qty int, acq int);
recreate table th(id int primary key using index th_pk, optype_id int, state_id int);
commit;
alter table td add constraint td_fk foreign key (doc_id) references th using index td_fk;
create index th_optype on th(optype_id);
create index th_state on th(state_id);
commit;
set term ^;
execute block as
  declare n_docs int = 2000; --20000;
  declare n_rows int = 500; --100;
  declare m int;
  declare v_doc_id int;
begin
  while (n_docs > 0) do begin
    insert into th(id, optype_id, state_id) values( gen_id(gh,1), cast( 0.5+ rand()*10 as int), cast(0.5 + rand()*3 as int) )
    returning id into v_doc_id;
    m=n_rows;
    while(m>0) do begin
      insert into td(id, doc_id, qty, acq) values( gen_id(gd,1), :v_doc_id, 100, 50);
      m = m-1;
    end
    n_docs = n_docs - 1;
  end
end
^set term ;^
commit;
set statistics index th_pk;
set statistics index th_optype;
set statistics index th_state;
set statistics index td_pk;
set statistics index td_fk;
commit;

Задача: подсчитать число записей в детали (d), для которых выполнено следующее условие в мастере (h): h.optype_id = 5 and h.state_id in (2,3) - для соотв-щего d.doc_id = h.id; плюс еще один доп фильтр на неиндексированные поля, но это уже неважно.

Запрос-1 , через inner join .
Выполняется мгновенно:
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
2014-04-07T17:21:59.0440 (6403:0x7f309d8df958) EXECUTE_STATEMENT_FINISH
        oltp30 (ATT_286, SYSDBA:NONE, WIN1251, TCPv4:192.168.43.96)
        C:\MIX\IBE\IBExpert.exe:1984
                (TRA_44824, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)

Statement 22299:
-------------------------------------------------------------------------------
select count(*)
from(
    select d.id,d.doc_id,d.qty,d.acq
    from td d
    join th h on d.doc_id=h.id and h.optype_id = 5 and h.state_id in (2,3)
    where d.qty > d.acq
)

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (H INDEX (TH_OPTYPE, TH_STATE, TH_STATE), D INDEX (TD_FK))
1 records fetched
     64 ms, 140741 fetch(es)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
TD                                            70000
TH                                              140


Запрос-2 , через exists , с указанием в нём ВСЕХ условий на мастер-таблицу.
Выполняется хреново:
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
2014-04-07T17:23:44.9910 (6403:0x7f309d8df958) EXECUTE_STATEMENT_FINISH
        oltp30 (ATT_286, SYSDBA:NONE, WIN1251, TCPv4:192.168.43.96)
        C:\MIX\IBE\IBExpert.exe:1984
                (TRA_44824, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)

Statement 22305:
-------------------------------------------------------------------------------
select count(*) 
from(
    select d.id,d.doc_id,d.qty,d.acq
    from td d
    where
    exists( select * from th h where h.id=d.doc_id and  h.optype_id = 5 and h.state_id in (2,3) )
    and d.qty > d.acq
)

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (H INDEX (TH_PK))
PLAN (D NATURAL)
1 records fetched
   3839 ms, 7015662 fetch(es)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
TD                                1000000
TH                                          1000000

Причина очевидна: ФБ идёт натуралом по всей детали, т.е. она - ведущая.

Что помешало оптимизатору "увидеть" в exists-подзапросе связь по PK-FK и доп. фильтры по полям optype_id & state_id, дабы родить вначале источник данных на основании именно мастера ?
Т.е. проще говоря - преобразовать запрос с exists(), когда в его where-условии идёт запрос по PK, к inner join'у - такое будет когда-нить в ФБ ?
...
Рейтинг: 0 / 0
07.04.2014, 17:32:32
| Ответить | Цитировать | Написать  
Преобразование запроса с EXEISTS( select * from X where <some_cond_on_X_PK> ) к INNER JOIN
    #38607576
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Таблоид,

когда нибудь будет semi-join
1. NESTED LOOP по индексу (почти как сейчас)
2. HASH
3. MERGE

что касается превращения exists в inner join оно может и будет, но там всю производительность может убить сортировка для исключения дубликатов
...
Рейтинг: 0 / 0
07.04.2014, 17:59:12
| Ответить | Цитировать | Написать  
Преобразование запроса с EXEISTS( select * from X where <some_cond_on_X_PK> ) к INNER JOIN
    #38607604
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Симонов Денисчто касается превращения exists в inner join оно может и будет, но там всю производительность может убить сортировка для исключения дубликатовНе будет там никаких дубликатов. И б убликатов тоже :-)
Ибо: "преобразовать запрос с exists(), когда в его where-условии идёт запрос по PK "
...
Рейтинг: 0 / 0
07.04.2014, 18:27:12
| Ответить | Цитировать | Написать  
Преобразование запроса с EXEISTS( select * from X where <some_cond_on_X_PK> ) к INNER JOIN
    #38607966
WildSery
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
WildSery
Участник
Таблоид,

а если не PK, а UNQ, то отдельную ветку?
...
Рейтинг: 0 / 0
08.04.2014, 09:53:22
| Ответить | Цитировать | Написать  
Преобразование запроса с EXEISTS( select * from X where <some_cond_on_X_PK> ) к INNER JOIN
    #38608058
dimitr
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dimitr
Участник
Таблоид,

не надоело задавать риторические вопросы? Уже неоднократно упоминалось, что констрейнты оптимизатором никак не учитываются. Да, могут. Нет, не сейчас.
...
Рейтинг: 0 / 0
08.04.2014, 10:46:07
| Ответить | Цитировать | Написать  
Преобразование запроса с EXEISTS( select * from X where <some_cond_on_X_PK> ) к INNER JOIN
    #38608065
Гаджимурадов Рустам
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гаджимурадов Рустам
Участник
Да ну, лично я двумя руками голосую против всяких оптимизаций
типа разворачивания exist-а в джойн по констрейнту. Нафиг-нафиг.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
08.04.2014, 10:48:36
| Ответить | Цитировать | Написать  
Преобразование запроса с EXEISTS( select * from X where <some_cond_on_X_PK> ) к INNER JOIN
    #38608195
WildSery
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
WildSery
Участник
Гаджимурадов Рустам,

Поддержу. Разруха, она в головах. Наводить порядок нужно в запросе, а не в оптимизаторе.
...
Рейтинг: 0 / 0
08.04.2014, 11:42:58
| Ответить | Цитировать | Написать  
Преобразование запроса с EXEISTS( select * from X where <some_cond_on_X_PK> ) к INNER JOIN
    #38608388
Ivan_Pisarevsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan_Pisarevsky
Участник
WildSeryНаводить порядок нужно в запросе, а не в оптимизаторе.Не ну если по делу, то можно и в оптимизаторе.
Гаджимурадов Рустамтипа разворачивания exist-а в джойн по констрейнту. Нафиг-нафиг.Вот этого точно не надо, я и сам соображу где мне джойн врисовать, а где экзисц.
...
Рейтинг: 0 / 0
08.04.2014, 13:22:02
| Ответить | Цитировать | Написать  
Преобразование запроса с EXEISTS( select * from X where <some_cond_on_X_PK> ) к INNER JOIN
    #38608414
Гаджимурадов Рустам
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гаджимурадов Рустам
Участник
Ivan_Pisarevsky> Не ну если по делу, то можно и в оптимизаторе.
Ivan_Pisarevsky> Вот этого точно не надо, я и сам соображу
Ivan_Pisarevsky> где мне джойн врисовать, а где экзисц.

У тебя два предложения противоречат друг другу.
Вчитайся в них и в топик ещё раз.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
08.04.2014, 13:30:46
| Ответить | Цитировать | Написать  
Преобразование запроса с EXEISTS( select * from X where <some_cond_on_X_PK> ) к INNER JOIN
    #38608493
Ivan_Pisarevsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan_Pisarevsky
Участник
Гаджимурадов РустамУ тебя два предложения противоречат друг другу.Да, как-то нескладно. Оптимизатор надо пилить по делу, предложение в заголовке топика к таковым(требующим изменений оптимизатора) не отношу.
...
Рейтинг: 0 / 0
08.04.2014, 14:09:46
| Ответить | Цитировать | Написать  
10 сообщений из 10, страница 1 из 1
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Преобразование запроса с EXEISTS( select * from X where <some_cond_on_X_PK> ) к INNER JOIN
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=40&gotonew=1&tid=1563724]:
 0ms
get settings:
 9ms
get forum list:
 11ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 165ms
get topic data:
 6ms
get first new msg:
 3ms
get forum data:
 2ms
get page messages:
 28ms
get tp. blocked users:
 1ms
others: 205ms
total:  434ms
созд. / загр.: 434ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]