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