Subject Re: [firebird-support] Re: Help on query performing natural scan
Author Bob Murdoch
At 2/18/2004 09:30 AM, Alexander Nevsky wrote:
> > PLAN SORT (JOIN (SC INDEX (SALESCHECK_DEL_DATE_IDX),M INDEX
>(RDB$FOREIGN174)))
>
> Bob, here optimizer decided it is good to filter SC by sc.del_date
>using index and than join result with M using it's foreign key to SC.

Yes, and that was absolutely the correct choice.


>Easiest way to quickly check it - repeat query forcing plan which you
>think is better:
>
>If you are right it is better to rewrite query to
>avoid explicit planning (if for some reason you in the future will be
>forced to drop and recreate constrains or re-build database from
>script, numbers of system indices will/can be changed).

Yes, exactly what I was trying to avoid.


>select
> m.id, m.sale_id, m.sku, m.line_nbr, sum(m.qty) as qty
> from
> salescheck sc
> join sale_mdse m on (m.sale_id = sc.id+0) /*!!!*/
> join sku sk on (sk.sku_nbr = m.sku) and (sk.account_id =
>sc.account_id)
> where
> (sc.del_date = '1/24/2004') and
> (sc.region = 10)
> group by
> m.id, m.sale_id, m.sku, m.line_nbr
>
>introduced expression leads to impossibility to use SC INDEX
>(RDB$PRIMARY105) and optimizer can go back to use SC INDEX
>(SALESCHECK_DEL_DATE_IDX) and wanted sequence of joining tables.

Yes, that's exactly what I needed. It works perfectly.

Thank you,

Bob M..