Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / План для коррелированного и некоррелированного подзапроса / 8 сообщений из 8, страница 1 из 1
12.10.2015, 08:50
    #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, 09:27
    #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:28
    #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:37
    #39074189
dimitr
dimitr
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
План для коррелированного и некоррелированного подзапроса
Навскидку не могу сказать. Если можешь сделать воспроизводимый пример - высылай, попробую посмотреть.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.10.2015, 09:44
    #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:56
    #39074211
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
План для коррелированного и некоррелированного подзапроса
CyberMax,

с теоретической dimitr лучше скажет. Посмотрел на запросы ещё раз, по идее разницы быть не должно
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.10.2015, 10:02
    #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, 12:53
    #39074392
dimitr
dimitr
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
План для коррелированного и некоррелированного подзапроса
на пустых таблицах невозможно оценить качество плана, да и пример не совсем удачный - S NATURAL в первом варианте явно не равнозначен S INDEX (FK_DIR$SERVICE$SERVICE_TYPE_SER). Глядя на исходный пост, я бы навскидку посчитал второй план более оптимальным. И смотрел бы не на число индексированных чтений, а на число фетчей и на время выполнения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / План для коррелированного и некоррелированного подзапроса / 8 сообщений из 8, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 407ms
Закрыть
start [/forum/topic.php?fid=40&mobile=1&tid=1562601]:
 0ms
get settings:
 7ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 48ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 52ms
get tp. blocked users:
 2ms
others: 259ms
total:  407ms
x
x
Закрыть


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