Open
Description
This ticket has a fairly low priority since MERGE joins are generally less efficient, but it would still be useful for testing purposes.
Script for filling tables:
RECREATE TABLE BIG_1 (
ID_1 BIGINT NOT NULL,
F_1 BIGINT,
CONSTRAINT PK_BIG_1 PRIMARY KEY(ID_1)
);
RECREATE TABLE BIG_2 (
ID_2 BIGINT NOT NULL,
F_2 BIGINT,
CONSTRAINT PK_BIG_2 PRIMARY KEY(ID_2)
);
SET TERM ^;
EXECUTE BLOCK
AS
DECLARE I BIGINT;
DECLARE A BIGINT;
BEGIN
I = 0;
WHILE (I < 20000) DO
BEGIN
I = I + 1;
A = NULL;
IF(MOD(I, 19) = 0)
THEN A = I;
INSERT INTO BIG_1(ID_1, F_1)
VALUES (:I, :A);
END
I = 0;
WHILE (I < 15000) DO
BEGIN
I = I + 1;
A = NULL;
IF(MOD(I, 13) = 0)
THEN A = I;
INSERT INTO BIG_2(ID_2, F_2)
VALUES (:I, :A);
END
END^
SET TERM ;^
COMMIT;Specify an explicit plan for the SQL query:
SELECT COUNT(*)
FROM
BIG_1
JOIN BIG_2 ON BIG_2.F_2 = BIG_1.F_1
PLAN MERGE (SORT (BIG_2 NATURAL), SORT (BIG_1 NATURAL))But the SQL query is still executed with the plan:
PLAN HASH (BIG_1 NATURAL, BIG_2 NATURAL)
Select Expression
-> Aggregate
-> Filter
-> Hash Join (inner) (keys: 1, total key length: 8)
-> Table "BIG_1" Full Scan
-> Record Buffer (record length: 33)
-> Table "BIG_2" Full Scan
Metadata
Metadata
Assignees
Labels
No labels