Hi Rick!

With left joins, the optimizer has to start the plan with the table to
the left side of those joins. Hence, MS_INVENTORY has to be the first
table in the plan. NABP of the view is the only part associated with a
where clause, and since that is MS_INVENTORY.SRVPROVID, the only
possible index to use is I_MS_INVENTORY_SRVPROVID. This index seems to
have lousy selectivity - admittedly I do not understand 'fill
distribution', but 9571 duplicates out of 64583 records is certainly
not good. Possibly that is poor enough selectivity for the optimizer
to decide it is better off without using the index at all.

So, what should you do?

Well, try adding the primary key to the end of that index. That may
change the plan, but I'm not convinced it will execute faster. What I
know will be improved is updating and deleting, though your tables are
so small that I do not know whether you will notice it.

Maybe you will end up deleting all NABP indexes, they all seem to have
been ignored by the optimizer - probably due to low selectivity.
Having said that, your plan with one NATURAL isn't neccessarily bad.

Set

--- In firebird-support@yahoogroups.com, "Rick DeBay" wrote:
> I'm cleaning up a database used as a non-production working area by
> one of our groups. This query produces a plan that doesn't look
> right to me. Every column in the join and where clause has an
> index, so why is the NATURAL still in the plan?
>
> SELECT * FROM V_INVENTORY_OUTSTANDING WHERE NABP = ?
>
> PLAN SORT (JOIN (JOIN (V_INVENTORY_OUTSTANDING MS_INVENTORY NATURAL,
> V_INVENTORY_OUTSTANDING MS_ORDER INDEX (I_MS_ORDER_NDC)),
> V_INVENTORY_OUTSTANDING MS_BUYOUT INDEX (I_MS_BUYOUT_NDC)))
>
> CREATE VIEW V_INVENTORY_OUTSTANDING
> (
> NABP, NDC, OUTS_INV
> ) AS
> SELECT
> MS_INVENTORY.SRVPROVID, MS_INVENTORY.PRODUCTID,
> (SUM(MS_INVENTORY.DECIMALQTY) -
> SUM(COALESCE(MS_ORDER.QTY_SHIPPED,0)) -
> SUM(COALESCE(MS_BUYOUT.BO_QTY,0))) OUTS_INV
> FROM
> MS_INVENTORY
> LEFT JOIN MS_ORDER ON MS_INVENTORY.SRVPROVID = MS_ORDER.NABP AND
> MS_INVENTORY.PRODUCTID = MS_ORDER.NDC
> LEFT JOIN MS_BUYOUT ON MS_INVENTORY.SRVPROVID = MS_BUYOUT.NABP AND
> MS_INVENTORY.PRODUCTID = MS_BUYOUT.NDC
> GROUP BY
> MS_INVENTORY.SRVPROVID, MS_INVENTORY.PRODUCTID;
>
> Index I_MS_BUYOUT_NDC (2)
> Depth: 1, leaf buckets: 1, nodes: 136
> Average data length: 6.00, total dup: 5, max dup: 1
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 1
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 0
>
> Index I_MS_INVENTORY_SRVPROVID (1)
> Depth: 2, leaf buckets: 71, nodes: 64583
> Average data length: 0.00, total dup: 64522, max dup: 9571
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 32
> 60 - 79% = 23
> 80 - 99% = 16
>
> Index I_MS_ORDER_NDC (2)
> Depth: 2, leaf buckets: 7, nodes: 6544
> Average data length: 2.00, total dup: 4250, max dup: 42
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 1
> 60 - 79% = 0
> 80 - 99% = 6