FB3 учитывает неиспользуемые поля при подсчёте длины записи для сортировки
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
WITH T
AS (SELECT
NAME,
SHORTNAME,
NAME_EN
FROM COLOR
UNION ALL
SELECT
NAME,
SHORTNAME,
NAME_EN
FROM COLOR
)
SELECT
NAME
FROM T
ORDER BY NAME_EN
план
1.
2.
3.
4.
Select Expression
-> Sort (record length: 164, key length: 36)
-> Union
-> Table "T COLOR" Full Scan
-> Table "T COLOR" Full Scan
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
WITH T
AS (SELECT
NAME,
-- SHORTNAME,
NAME_EN
FROM COLOR
UNION ALL
SELECT
NAME,
-- SHORTNAME,
NAME_EN
FROM COLOR
)
SELECT
NAME
FROM T
ORDER BY NAME_EN
1.
2.
3.
4.
Select Expression
-> Sort (record length: 140, key length: 36)
-> Union
-> Table "T COLOR" Full Scan
-> Table "T COLOR" Full Scan
собственно почему? Если убрать UNION ALL, то record length одинаков что с полем SHORTNAME что без него.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
WITH T
AS (SELECT
NAME,
SHORTNAME,
NAME_EN
FROM COLOR
)
SELECT
NAME
FROM T
ORDER BY NAME_EN
1.
2.
Select Expression
-> Sort (record length: 120, key length: 36)
-> Table "T COLOR" Full Scan
И почему вообще UNION ALL увеличил длину записи?
