Subject | Re: [firebird-support] Re: Help on query performing natural scan |
---|---|
Author | Bob Murdoch |
Post date | 2004-02-18T15:19:21Z |
At 2/18/2004 09:30 AM, Alexander Nevsky wrote:
Thank you,
Bob M..
> > PLAN SORT (JOIN (SC INDEX (SALESCHECK_DEL_DATE_IDX),M INDEXYes, and that was absolutely the correct choice.
>(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.
>Easiest way to quickly check it - repeat query forcing plan which youYes, exactly what I was trying to avoid.
>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).
>selectYes, that's exactly what I needed. It works perfectly.
> 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.
Thank you,
Bob M..