ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / План для коррелированного и некоррелированного подзапроса
8 сообщений из 8, страница 1 из 1
План для коррелированного и некоррелированного подзапроса
    #39074144
Фотография CyberMax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CyberMax
Участник
FB 2.5.4.
Уникальный индекс:
Код: sql
1.
ALTER TABLE INF$ABONENT$BT ADD CONSTRAINT UNQ_INF$ABONENT$BT UNIQUE (ID_ABONENT, ID_SERVICE, ID_PERIOD);


Обновляющий запрос с подзапросом:
Код: sql
1.
2.
3.
4.
5.
UPDATE DIR$ABONENT$ABONENT A
SET
    A.BALANCE_FINAL_GAS = COALESCE((SELECT SUM(BT.BALANCE_FINAL) FROM INF$ABONENT$BT BT INNER JOIN DIR$SERVICE$SERVICE S ON BT.ID_SERVICE = S.ID WHERE (BT.ID_ABONENT = :ID_ABONENT) AND (BT.ID_PERIOD = 201510) AND (S.ID_TYPE_SERVICE = 1)), 0)
WHERE
    A.ID = :ID_ABONENT


FB дает план:
Код: plaintext
1.
2.
PLAN JOIN (S INDEX (FK_DIR$SERVICE$SERVICE_TYPE_SER), BT INDEX (UNQ_INF$ABONENT$BT))
PLAN (A INDEX (PK_DIR$ABONENT$ABONENT))

Результат:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |
|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|DIR$ABONENT$ABONENT            |         0 |         1 |           0 |       1 |       0 |       0 |        0 |        0 |        0 |
|DIR$HOUSE$HOUSE                |         0 |         2 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|DIR$SERVICE$SERVICE            |         0 |         3 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|INF$ABONENT$BT                 |         0 |         1 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

Коррелированный подзапрос:
Код: sql
1.
2.
3.
4.
5.
UPDATE DIR$ABONENT$ABONENT A
SET
    A.BALANCE_FINAL_GAS = COALESCE((SELECT SUM(BT.BALANCE_FINAL) FROM INF$ABONENT$BT BT INNER JOIN DIR$SERVICE$SERVICE S ON BT.ID_SERVICE = S.ID WHERE (BT.ID_ABONENT = A.ID) AND (BT.ID_PERIOD = 201510) AND (S.ID_TYPE_SERVICE = 1)), 0)
WHERE
    A.ID = :ID_ABONENT


План
Код: plaintext
1.
2.
PLAN JOIN (BT INDEX (FK_INF$ABONENT$BT_ABONENT, FK_INF$ABONENT$BT_PERIOD), S INDEX (PK_DIR$SERVICE$SERVICE))
PLAN (A INDEX (PK_DIR$ABONENT$ABONENT))

Результат:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |
|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|DIR$ABONENT$ABONENT            |         0 |         1 |           0 |       1 |       0 |       0 |        0 |        0 |        0 |
|DIR$HOUSE$HOUSE                |         0 |         2 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|DIR$SERVICE$SERVICE            |         0 |          7  |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|INF$ABONENT$BT                 |         0 |          7  |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

Как видим, таблицы DIR$SERVICE$SERVICE и INF$ABONENT$BT стали читаться 7 и 7 раз вместо 3 и 1 соответственно.
Вопросы ДЕ: Это баян? И сможет ли оптимизатор подобрать первый план в случае вот такого коррелированного подзапроса?
...
Рейтинг: 0 / 0
12.10.2015, 08:50
| Ответить | Цитировать | Написать  
План для коррелированного и некоррелированного подзапроса
    #39074175
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
CyberMax,

не может. Попробуй проверить что будет если переписать на MERGE

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
MERGE INTO DIR$ABONENT$ABONENT A
USING (
  SELECT 
    BT.ID_ABONENT AS  ID_ABONENT
    COALESCE(SUM(BT.BALANCE_FINAL), 0) AS S    
  FROM INF$ABONENT$BT BT 
  INNER JOIN DIR$SERVICE$SERVICE S ON BT.ID_SERVICE = S.ID 
  WHERE (BT.ID_ABONENT = :ID_ABONENT) AND (BT.ID_PERIOD = 201510) AND (S.ID_TYPE_SERVICE = 1)
) B
ON B.ID_ABONENT = A.ID 
WHEN MATCHED THEN
  UPDATE SET A.BALANCE_FINAL_GAS = B.S
...
Рейтинг: 0 / 0
12.10.2015, 09:27
| Ответить | Цитировать | Написать  
План для коррелированного и некоррелированного подзапроса
    #39074177
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
блин, GROUP BY забыл

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
MERGE INTO DIR$ABONENT$ABONENT A
USING (
  SELECT 
    BT.ID_ABONENT AS  ID_ABONENT
    COALESCE(SUM(BT.BALANCE_FINAL), 0) AS S    
  FROM INF$ABONENT$BT BT 
  INNER JOIN DIR$SERVICE$SERVICE S ON BT.ID_SERVICE = S.ID 
  WHERE (BT.ID_ABONENT = :ID_ABONENT) AND (BT.ID_PERIOD = 201510) AND (S.ID_TYPE_SERVICE = 1)
  GROUP BY ID_ABONENT
) B
ON B.ID_ABONENT = A.ID 
WHEN MATCHED THEN
  UPDATE SET A.BALANCE_FINAL_GAS = B.S
...
Рейтинг: 0 / 0
12.10.2015, 09:28
| Ответить | Цитировать | Написать  
План для коррелированного и некоррелированного подзапроса
    #39074189
dimitr
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dimitr
Участник
Навскидку не могу сказать. Если можешь сделать воспроизводимый пример - высылай, попробую посмотреть.
...
Рейтинг: 0 / 0
12.10.2015, 09:37
| Ответить | Цитировать | Написать  
План для коррелированного и некоррелированного подзапроса
    #39074198
Фотография CyberMax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CyberMax
Участник
Симонов Денис,

Добавил в запрос запятую пропущенную. Результат выполнения как первом запросе:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |
|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|DIR$ABONENT$ABONENT            |         0 |         1 |           0 |       1 |       0 |       0 |        0 |        0 |        0 |
|DIR$HOUSE$HOUSE                |         0 |         2 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|DIR$SERVICE$SERVICE            |         0 |         3 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|INF$ABONENT$BT                 |         0 |         1 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

А так, меня этот вопрос больше с теоретический точки зрения интересует :).
...
Рейтинг: 0 / 0
12.10.2015, 09:44
| Ответить | Цитировать | Написать  
План для коррелированного и некоррелированного подзапроса
    #39074211
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
CyberMax,

с теоретической dimitr лучше скажет. Посмотрел на запросы ещё раз, по идее разницы быть не должно
...
Рейтинг: 0 / 0
12.10.2015, 09:56
| Ответить | Цитировать | Написать  
План для коррелированного и некоррелированного подзапроса
    #39074219
Фотография CyberMax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CyberMax
Участник
dimitr,

На пустых таблицах все воспроизводится.
Код: sql
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.
35.
36.
37.
38.
39.
40.
41.
CREATE TABLE TEST$ABONENT (
    ID       INTEGER NOT NULL,
    BALANCE  NUMERIC(18,2) NOT NULL
);

ALTER TABLE TEST$ABONENT ADD CONSTRAINT PK_TEST$ABONENT PRIMARY KEY (ID);


CREATE TABLE TEST$PERIOD (
    ID  INTEGER NOT NULL
);


ALTER TABLE TEST$PERIOD ADD CONSTRAINT PK_TEST$PERIOD PRIMARY KEY (ID);

CREATE TABLE TEST$SERVICE (
    ID    INTEGER NOT NULL,
    FLAG  INTEGER NOT NULL
);


ALTER TABLE TEST$SERVICE ADD CONSTRAINT PK_TEST$SERVICE PRIMARY KEY (ID);

CREATE TABLE TEST$BT (
    ID          INTEGER NOT NULL,
    ID_ABONENT  INTEGER NOT NULL,
    ID_PERIOD   INTEGER NOT NULL,
    ID_SERVICE  INTEGER NOT NULL,
    BALANCE     NUMERIC(18,2) NOT NULL
);


ALTER TABLE TEST$BT ADD CONSTRAINT UNQ1_TEST$BT UNIQUE (ID_ABONENT, ID_SERVICE, ID_PERIOD);


ALTER TABLE TEST$BT ADD CONSTRAINT PK_TEST$BT PRIMARY KEY (ID);


ALTER TABLE TEST$BT ADD CONSTRAINT FK_TEST$BT_1 FOREIGN KEY (ID_ABONENT) REFERENCES TEST$ABONENT (ID) ON UPDATE CASCADE;
ALTER TABLE TEST$BT ADD CONSTRAINT FK_TEST$BT_2 FOREIGN KEY (ID_PERIOD) REFERENCES TEST$PERIOD (ID) ON UPDATE CASCADE;
ALTER TABLE TEST$BT ADD CONSTRAINT FK_TEST$BT_3 FOREIGN KEY (ID_SERVICE) REFERENCES TEST$SERVICE (ID) ON UPDATE CASCADE;



Запросы:
Код: sql
1.
2.
3.
4.
5.
UPDATE TEST$ABONENT A
SET
    A.BALANCE = COALESCE((SELECT SUM(BT.BALANCE) FROM TEST$BT BT INNER JOIN TEST$SERVICE S ON BT.ID_SERVICE = S.ID WHERE (BT.ID_ABONENT = :ID_ABONENT) AND (BT.ID_PERIOD = 201510) AND (S.FLAG = 1)), 0)
WHERE
    A.ID = :ID_ABONENT


Код: sql
1.
2.
3.
4.
5.
UPDATE TEST$ABONENT A
SET
    A.BALANCE = COALESCE((SELECT SUM(BT.BALANCE) FROM TEST$BT BT INNER JOIN TEST$SERVICE S ON BT.ID_SERVICE = S.ID WHERE (BT.ID_ABONENT = A.ID) AND (BT.ID_PERIOD = 201510) AND (S.FLAG = 1)), 0)
WHERE
    A.ID = :ID_ABONENT
...
Рейтинг: 0 / 0
12.10.2015, 10:02
| Ответить | Цитировать | Написать  
План для коррелированного и некоррелированного подзапроса
    #39074392
dimitr
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dimitr
Участник
на пустых таблицах невозможно оценить качество плана, да и пример не совсем удачный - S NATURAL в первом варианте явно не равнозначен S INDEX (FK_DIR$SERVICE$SERVICE_TYPE_SER). Глядя на исходный пост, я бы навскидку посчитал второй план более оптимальным. И смотрел бы не на число индексированных чтений, а на число фетчей и на время выполнения.
...
Рейтинг: 0 / 0
12.10.2015, 12:53
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / План для коррелированного и некоррелированного подзапроса
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=40&msg=39074175&tid=1562601]:
 0ms
get settings:
 11ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 37ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 40ms
get tp. blocked users:
 1ms
others: 15ms
total:  136ms
созд. / загр.: 136ms
x
x
Закрыть


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