Re: Whatcha' wanta have?????

OK'm cutting the quotes to size here.. Jonathan Leffler wrote: > In this example, yes. And if a part of the key is unique, the index as > a whole is also unique. However, I believe other examples could be > derived where the index does not have to be unique - or the leading part > does not have to be unique. I readily grant you that most plausible > cases probably end up with uniqueness, but I have yet to be convinced > that uniqueness is a pre-requisite. > > Consider a more complex example - one which requires DDL to explain. I'm > adopting the notation outlined below... > > CREATE TABLE OrderItems > ( > OrderNo INTEGER NOT NULL REFERENCES Orders, > ItemNo SMALLINT NOT NULL, > PRIMARY KEY(OrderNo, ItemNo), > PartNo CHAR(12) NOT NULL REFERENCES Parts, > Quantity INTEGER NOT NULL, > UnitCost DECIMAL(10,3) NOT NULL > );> CREATE {non-unique} INDEX i1_orderitems > ON OrderItems(PartNo) INCLUDE(Quantity); -- Rejected by DB2 > > I'm willing to believe that DB2 might not allow this - indeed, chasing > CREATE INDEX from the URL Serge cites indicates that 'INCLUDE' is only > allowed if the index is unique - but I don't see why that is necessary,> as opposed to usually desirable. > > The index is pretty reasonable; the same part number can be sold to many > customers, so the initial part of the index is not unique. The included > information is also not unique, and neither is the combination -- you > can sell 100 Widgets to Customer1 and another 100 Widgets to Customer2 > (or another 100 Widgets to Customer1 in a different order - indeed, > possibly the same order unless there's a constraint to prevent that and > I didn't show such a constraint). > How is CREATE {non-unique} INDEX i1_orderitems ON OrderItems(PartNo) INCLUDE(Quantity); different from: CREATE {non-unique} INDEX i1_orderitems ON OrderItems(PartNo, Quantity); What is the value proposition? In DB2 at least the only feature of the INCLUDE clause is to separate the unique part of the index form the non-unique part. maybe I'm thinking in the box here..... Cheers Serge -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab

Serge Rielau wrote: > OK'm cutting the quotes to size here.. Me too - only more so. > Jonathan Leffler wrote: > >> In this example, yes. [...] > > How is > CREATE {non-unique} INDEX i1_orderitems > ON OrderItems(PartNo) INCLUDE(Quantity); > > different from: > > CREATE {non-unique} INDEX i1_orderitems > ON OrderItems(PartNo, Quantity); It isn't; I was suffering from myopia. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/