The specified MERGE plan is ignored if the HASH join plan is selected.

[sim1984]

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