> As soon as i add the ROWS clause to a CTE FB cannot use indices available in
> the CTEs tables.
> Is this a bug, or as designed?
>
> This:
>
> with low as (
> select igc.issue_id, ia.occasion
> from issues_groups igc
> left join issues_groups igb on (igb.issue_significant_id =
> igc.issue_significant_id)
> left join issues_addressed ia on (ia.issue_id = igb.issue_id)
> where ia."USER" = 'a'
> --order by ia.occasion desc
> --fetch first 1 row only
> )
> select *
> from issues i
> left join low l on (l.issue_id = i.issue_id)
>
> issues_groups, issues_addressed: Indexed reads
>
> remove the comments and issues_groups will have non-indexed reads for
> the join l.issue_id to i.issue_id.
I believe you are incorrectly using CTE instead of simple calculated column, as
in:
select
i.*,
(
select
FIRST 1 ia.occasion
from issues_groups igc
left join issues_groups igb on (igb.issue_significant_id =
igc.issue_significant_id)
left join issues_addressed ia on (ia.issue_id = igb.issue_id)
where
igc.issue_id = i.issue_id
and ia."USER" = 'a'
order by ia.occasion desc
) as occasion
from issues i
Sean
