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?

(You may notice indexes on the join fields; the next step is to change
the indexes to foreign keys, after I clean out obsolete data).

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_NABP (1)
Depth: 1, leaf buckets: 1, nodes: 136
Average data length: 0.00, total dup: 132, max dup: 72
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0

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_PRODUCTID (2)
Depth: 2, leaf buckets: 73, nodes: 64583
Average data length: 0.00, total dup: 57974, max dup: 542
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 67
80 - 99% = 5

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_NABP (1)
Depth: 2, leaf buckets: 5, nodes: 6544
Average data length: 0.00, total dup: 6487, max dup: 1747
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 5

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

Rick DeBay
Senior Software Developer
RxStrategies.net