Re: Whatcha' wanta have?????

Jonathan Leffler wrote: > Serge Rielau wrote: > >> Neil Truby wrote: >> >>> "Serge Rielau" <srielau@ca.eye-be-em.com> wrote: >>> >>>> Andrew Hamm wrote: >>>> >>>>> Mark Denham wrote: >>>>> >>>>>> Some indexing additions: >>>>>> >>>>>> 1) Inclusion of columns in the index that are not part of the index >>>>>> key but help speed processing. This feature is available in a number >>>>>> of other rdbms's. >>>>> >>>>> >>>>> Waaaaaa? Please explain? >>>> >>>> >>>> This feature applies to unique indexes. >>>> Let's presume you have an employee table. The PK is empno. >>>> You want to optimize lookups of employee names by empno. >>>> Without this feature the fastest to do this (assuming a non trivial >>>> table size) is to fetch the rowid from the index and then do an >>>> fetch by >>>> rowid from the table to get the name. >>>> If you INCUDE empname in the unique index then you save the the table >>>> access. [...strike incorrect sentence...] > > > I don't see that it has to apply solely to unique indexes. It basically > allows a key-only scan to also pick up the other critical data without > having to fetch the data page. So, if you've got a table of zip-codes > and state codes, you index on the zip-code - which probably needs to be > unique - and include the state code too, and you never have to go to the > table which contains demographic data stored in blobs for the state code. > The result is a unique index none the less. If a part of the index is unique all is unique. > The Orrible database has a feature along these lines, I believe. No need to look that far: CREATE TABLE T1 (zip CHAR(6) NOT NULL, province VARCHAR(20), area BIGINT); CREATE UNIQUE INDEX i1 ON T1(zip) INCLUDE (province); ALTER TABLE T1 ADD PRIMARY KEY (zip);SQL0598W Existing index "SRIELAU.I1" is used as the index for the primary key or a unique key. SQLSTATE=01550 http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000888.htm >>> How about including *all* columuns in the PK? Then you'd never have to >>> access the data at all :-) > > > An extreme case, but there has been a request for index-only tables. > >> Wouldn't that violate some normal form? ;-) > > > No; normal forms apply to the table, not to the indexes on the table. Reread the PK line ;-) > >> I dimply recall soem passages about a candidate key being a minimum >> set of column which are unique. >> Thsi is all about saving index space. E.g. you want to get the benefit >> of an index + a unique constraint. >> Some RDBMS will pick up existing indexes when a a primary key is added. >> You would create the table, then an index with include columns, then >> alter the table to add primary key. > > > The index has to be unique - but the problem is that normally, the > 'larger key' would not enforce the uniqueness correctly. If you have a > unique index on columns A, B, and C, but A and B alone are the true > primary key, then that unique index does not automatically enforce the > primary key. You could have rows A1, B1, C1 and A1, B1, C2 which are > unique according to the index but have the same primary key. If (a > modified version of) the DBMS is aware that A, B must be unique but that > C should also be indexed, then it can use the index and enforce the > correct uniqueness. We agree violently! Cheers Serge -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab

Serge Rielau wrote: > Jonathan Leffler wrote: >> Serge Rielau wrote: >>> Neil Truby wrote: >>>> Serge Rielau wrote: >>>>> Andrew Hamm wrote: >>>>>> Mark Denham wrote: >>>>>>> Some indexing additions: >>>>>>> >>>>>>> 1) Inclusion of columns in the index that are not part >>>>>>> of the index key but help speed processing. This >>>>>>> feature is available in a number of other rdbms's. >>>>>> >>>>>> Waaaaaa? Please explain? >>>>> >>>>> This feature applies to unique indexes. >>>>> Let's presume you have an employee table. The PK is empno. >>>>> You want to optimize lookups of employee names by empno. >>>>> Without this feature the fastest to do this (assuming a non >>>>> trivial table size) is to fetch the rowid from the index >>>>> and then do an fetch by rowid from the table to get the >>>>> name. If you INCUDE empname in the unique index then you >>>>> save the the table access. [...strike incorrect >>>>> sentence...] >> >> I don't see that it has to apply solely to unique indexes. It >> basically allows a key-only scan to also pick up the other >> critical data without having to fetch the data page. So, if >> you've got a table of zip-codes and state codes, you index on the >> zip-code - which probably needs to be unique - and include the >> state code too, and you never have to go to the table which >> contains demographic data stored in blobs for the state code. >> > The result is a unique index none the less. If a part of the index is > unique all is unique. 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 onlyallowed 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). >> The Orrible database has a feature along these lines, I believe. > > No need to look that far: Oh well, it adds to the ease of persuading the powers that be to make the change if it is in DB2 -- it's a compatibility feature. > CREATE TABLE T1 (zip CHAR(6) NOT NULL, > province VARCHAR(20), > area BIGINT); > CREATE UNIQUE INDEX i1 ON T1(zip) INCLUDE (province); OK - that's a reasonable syntax. And I used it above... > ALTER TABLE T1 ADD PRIMARY KEY (zip);> SQL0598W Existing index "SRIELAU.I1" is used as the index for the > primary key or a unique key. SQLSTATE=01550 > > http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000888.htm > >>>> How about including *all* columuns in the PK? Then you'd never have to >>>> access the data at all :-) >> >> An extreme case, but there has been a request for index-only tables. >> >>> Wouldn't that violate some normal form? ;-) >> >> No; normal forms apply to the table, not to the indexes on the table. > > Reread the PK line ;-) Indexes have no relevance whatsoever to normal forms. An unindexed table can be in any normal form from first through fifth (even sixth if you go with 'Temporal Data and the Relational Model'). >>> I dimply recall soem passages about a candidate key being a minimum >>> set of column which are unique. Roughly, yes - a candidate key is a combination of columns such that at all times, under all circumstances, the combination of values is always unique, but the same is not true for any subset of the columns. This does not mean that some other candidate key on the same table does not have fewer columns; just that you cannot remove any column from this candidate key and retain the uniqueness property. >>> This is all about saving index space. E.g. you want to get the >>> benefit of an index + a unique constraint. Some RDBMS will >>> pick up existing indexes when a a primary key is added. You >>> would create the table, then an index with include columns, >>> then alter the table to add primary key. >> >> The index has to be unique - but the problem is that normally, the >> 'larger key' would not enforce the uniqueness correctly. If you have >> a unique index on columns A, B, and C, but A and B alone are the true >> primary key, then that unique index does not automatically enforce the >> primary key. You could have rows A1, B1, C1 and A1, B1, C2 which are >> unique according to the index but have the same primary key. If (a >> modified version of) the DBMS is aware that A, B must be unique but >> that C should also be indexed, then it can use the index and enforce >> the correct uniqueness. > > We agree violently! Despite what I've said above, I rather suspect that's a correct observation. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

"Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:40305262.3000808@earthlink.net... > >> contains demographic data stored in blobs for the state code. > >> > > The result is a unique index none the less. If a part of the index is > > unique all is unique. > > 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. > Maybe, but the key issue here is how to declare a unique constraint on a subset of the columns contained within the index. If the index is not declared to be a unique index, then there is little functional difference between an 'add-column' index and a multi-column index. However, if the unique constraint is to be applied to a subset of the columns within the index, then the 'add-column' construct allows that definition. If we had a construct such as ... create index xyz on tab1 (col1, col2, col3) unique(col1, col2) .... then the 'add column' construct would not be needed. But the construct ... create unique index xyz on tab1 (col1, col2) include (col3) is what we've got. I guess there might be some benifit in not having to do comparisions on the included columns, but I doubt that the optimization is all that great.

Madison Pruet wrote: > "Jonathan Leffler" <jleffler@earthlink.net> wrote: >>[...] 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. [...] > > If the index is not declared to be a unique index, then there is little > functional difference between an 'add-column' index and a multi-column > index. Thanks for pointing out the obvious bit that I was not spotting! -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/