| Subject | How to specify manual plan for this kind of query? |
|---|---|
| Author | liviuslivius |
| Post date | 2019-05-30T06:35:10Z |
Hi
i need to modify plan generated by Firebird and specify manual plan.
I use Firebird 3.
example on employee.fdb (sample from taken from README.common_table_expressions.txt)
--------------------------------------------------------
WITH RECURSIVE
DEPT_YEAR_BUDGET AS
(
SELECT B.FISCAL_YEAR, B.DEPT_NO, SUM(B.PROJECTED_BUDGET) AS BUDGET
FROM PROJ_DEPT_BUDGET B
GROUP BY B.FISCAL_YEAR, B.DEPT_NO
),
DEPT_TREE AS
(
SELECT D1.DEPT_NO, D1.HEAD_DEPT, D1.DEPARTMENT, CAST('' AS VARCHAR(255)) AS INDENT
FROM DEPARTMENT D1
WHERE HEAD_DEPT IS NULL
UNION ALL
SELECT D2.DEPT_NO, D2.HEAD_DEPT, D2.DEPARTMENT, H.INDENT || ' '
FROM DEPARTMENT D2 JOIN DEPT_TREE H
ON D2.HEAD_DEPT = H.DEPT_NO
)
SELECT D3.DEPT_NO,
D3.INDENT || D3.DEPARTMENT AS DEPARTMENT,
B_1993.BUDGET AS B_1993
FROM DEPT_TREE D3
LEFT JOIN DEPT_YEAR_BUDGET B_1993
ON D3.DEPT_NO = B_1993.DEPT_NO AND B_1993.FISCAL_YEAR = 1993
--------------------------------------------------------
it generate plan
PLAN JOIN (DEPT_TREE D1 INDEX (RDB$FOREIGN6), DEPT_TREE D2 INDEX (RDB$FOREIGN6), SORT (B_1993 B INDEX (RDB$FOREIGN18, RDB$PRIMARY17)))
but if i put this plan witgout any modification to the query
it say:
--------------------------------------------------
Dynamic SQL Error
SQL error code = -104
Invalid command
there is no alias or table named D1 at this scope level.
--------------------------------------------------
How to put manual plan for this query?
In my real situation query plan looks like
PLAN (SORT (JOIN (CTE T T NATURAL, CTE K INDEX (IXA_NAMES_K__NAME))), JOIN (CTE T T NATURAL, CTE K INDEX (IXA_NAMES_K__ID)))
and i need to change it to
PLAN (SORT (JOIN (CTE T T NATURAL, CTE K INDEX (IXA_NAMES_K__NAME))), HASH (CTE T T NATURAL, CTE K INDEX (IXA_NAMES_K__ID)))
regards,
Karol Bieniaszewski