Firebird 3.0.0.30849
Выполняю такой запрос
1.
2.
3.
4.
5.
6.
7.
8.
WITH T
AS (SELECT T1_ID AS T1_ID,
SUM(ID) OVER() AS S
FROM T2)
SELECT COUNT(*)
FROM T
LEFT JOIN T1 ON T.T1_ID = T1.ID
WHERE T.S = 0
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.
COUNT
0
Plan
------------------------------------------------
PLAN JOIN (T T2 NATURAL, T1 INDEX (PK_T1))
Query Time
------------------------------------------------
Prepare : 0,00 ms
Execute : 15,00 ms
Avg fetch time: 15,00 ms
Memory
------------------------------------------------
Current: 279 134 792
Max : 279 554 648
Buffers: 16 384
Operations
------------------------------------------------
Read : 0
Writes : 0
Fetches: 6 015
Marks : 0
Enchanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts | Backouts | Purges | Expunges |
| | Total | reads | reads | | | | | | |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|T1 | 0 | 1000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
|T2 | 0 | 0 | 1000 | 0 | 0 | 0 | 0 | 0 | 0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
Откуда для T1 1000 индексных чтений, если после вот этого WHERE T.S = 0 должно быть 0?
DDL и тестовые данныеCREATE TABLE T1 (
ID INTEGER NOT NULL
);
CREATE TABLE T2 (
ID INTEGER NOT NULL,
T1_ID INTEGER
);
SET TERM ^;
EXECUTE BLOCK
AS
DECLARE VARIABLE I INTEGER = 0;
BEGIN
WHILE (I < 1000) DO BEGIN
I = I + 1;
INSERT INTO T2(ID, T1_ID) VALUES(:I, MOD(:I, 10));
MERGE INTO T1 USING (
SELECT MOD(:I, 10) AS F FROM RDB$DATABASE
) SRC ON T1.ID = SRC.F
WHEN NOT MATCHED THEN
INSERT (ID) VALUES(SRC.F);
END -- WHILE (I < 1000) DO BEGIN
END^
SET TERM ;^
COMMIT;
ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY (ID);
ALTER TABLE T2 ADD CONSTRAINT PK_T2 PRIMARY KEY (ID);
ALTER TABLE T2 ADD CONSTRAINT FK_T2_REF_T1 FOREIGN KEY (T1_ID) REFERENCES T1(ID);
