30.05.2019 9:35, liviuslivius wrote:
>
> 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.
> --------------------------------------------------
Manual planning is the remainder of old times linear queries. It doesn't
work for nested and/or complex statements.
> How to put manual plan for this query?
You may try putting a sub-plan into particular select expression you
need to optimize. Or better use +0 hints, if possible.
> 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)))
Really? I'd suppose the changed part should be:
HASH (CTE T T NATURAL, CTE K NATURAL))
Dmitry
