|
|
You're in the
sql server data warehouse
group:Clustered Indexes
sql server data warehouse:
I just gone through a datawarehouse CD seminar for SQL2K
but a little confused on something. The author said not to use clustered indexes with star/snowflake schemas. Is there a reason for this? The author never explained why we should not use clustered indexes in a datawarehouse.
In my not-so-humble opinion, that's absolutely insane. I've very rarely
found a reason not to have a clustered index, and the time I can remember off the top of my head had something to do with strange real-time insert and update patterns causing page splits. As a data warehouse will generally not be loaded in real time (but rather through an ETL process), there is no such concern; query performance should be the primary focus. Clustered indexes are invaluable for tuning certain types of queries. To not use them would be almost to doom your project to failure... Out of curiosity, what CD/seminar is it? Can you post some more about the reasons the author gave? Perhaps there was some context or certain situations mentioned? -- Adam Machanic SQL Server MVP http://www.sqljunkies.com/weblog/amachanic -- [quoted text, click to view] <anonymous@discussions.microsoft.com> wrote in message news:0bfe01c4bbc0$00f66840$a401280a@phx.gbl... > I just gone through a datawarehouse CD seminar for SQL2K > but a little confused on something. The author said not > to use clustered indexes with star/snowflake schemas. Is > there a reason for this? The author never explained why > we should not use clustered indexes in a datawarehouse. > Does anyone have an answer?
Thanks for the reply. The CD was from Learnkey. It
mentions that when you use clustered indexes it will make
Depends on how much you have in the cluster and what your definition of
'huge' is :) -- Adam Machanic SQL Server MVP http://www.sqljunkies.com/weblog/amachanic -- [quoted text, click to view] <anonymous@discussions.microsoft.com> wrote in message news:174d01c4bc4c$4f113280$a601280a@phx.gbl... > Thanks for the reply. The CD was from Learnkey. It > mentions that when you use clustered indexes it will make > the non-clustered index huge.
Let me help.... Although I am an author for Learnkey, I did not do the
current SQL 2k Data Warehouse stuff, but I think I can tell you what the author was thinking about here.. Think about the leaf level of non-clustered indexes. Each entry contains the key and the row locator(pointer the actual data row.) When the non-clustered index is built on a heap ( no clustered index exists for the table) , the row locator is the physical address of the data row ( I think 6 bytes - it is the file#-Page#-Row#onthePage). If you build a clustered index on this same table on the lastname column ( which is perhaps varchar(24) with an average length of 8 characters). The row locator for EACH of the non-clustered indexes is replaced by the clustered index key... We just grew the leaf entry for the row locator from 6 to 8 bytes.... So the size of the clustered index key matters. I do not know if I have the same opinion as the author you mention ( I'll have to think about it.) But now you know why he said it.. -- Wayne Snyder, MCDBA, SQL Server MVP Mariner, Charlotte, NC www.mariner-usa.com (Please respond only to the newsgroups.) I support the Professional Association of SQL Server (PASS) and it's community of SQL Server professionals. www.sqlpass.org [quoted text, click to view] <anonymous@discussions.microsoft.com> wrote in message news:174d01c4bc4c$4f113280$a601280a@phx.gbl... > Thanks for the reply. The CD was from Learnkey. It > mentions that when you use clustered indexes it will make > the non-clustered index huge.
never, ever, ever, ever, ever
base a clustered index on anything other than an integer or date. it slows down all of the other indexes on the table. I prefer using INTs for clustered indexes-- especallyt for hash indexing.. but there are some times that it is really helpful to use a date for a clustered index. [quoted text, click to view] "Wayne Snyder" <wayne.nospam.snyder@mariner-usa.com> wrote in message news:uXp9Z59yEHA.3708@TK2MSFTNGP14.phx.gbl... > Let me help.... Although I am an author for Learnkey, I did not do the > current SQL 2k Data Warehouse stuff, but I think I can tell you what the > author was thinking about here.. > > Think about the leaf level of non-clustered indexes. Each entry contains the > key and the row locator(pointer the actual data row.) When the non-clustered > index is built on a heap ( no clustered index exists for the table) , the > row locator is the physical address of the data row ( I think 6 bytes - it > is the file#-Page#-Row#onthePage). > > If you build a clustered index on this same table on the lastname column ( > which is perhaps varchar(24) with an average length of 8 characters). The > row locator for EACH of the non-clustered indexes is replaced by the > clustered index key... We just grew the leaf entry for the row locator from > 6 to 8 bytes.... > > So the size of the clustered index key matters. > > I do not know if I have the same opinion as the author you mention ( I'll > have to think about it.) But now you know why he said it.. > > -- > Wayne Snyder, MCDBA, SQL Server MVP > Mariner, Charlotte, NC > www.mariner-usa.com > (Please respond only to the newsgroups.) > > I support the Professional Association of SQL Server (PASS) and it's > community of SQL Server professionals. > www.sqlpass.org > > <anonymous@discussions.microsoft.com> wrote in message > news:174d01c4bc4c$4f113280$a601280a@phx.gbl... > > Thanks for the reply. The CD was from Learnkey. It > > mentions that when you use clustered indexes it will make > > the non-clustered index huge. > >
[quoted text, click to view]
"aaron kempf" <aaron_kempf@hotmail.com> wrote in message news:OU$xvnc0EHA.3708@TK2MSFTNGP14.phx.gbl... > never, ever, ever, ever, ever > > base a clustered index on anything other than an integer or date. > > it slows down all of the other indexes on the table. I prefer using INTs for > clustered indexes-- especallyt for hash indexing.. but there are some times > that it is really helpful to use a date for a clustered index. I don't agree, at all. Use whatever is natural to use. Don't constrain yourself with these rules. -- Adam Machanic SQL Server MVP http://www.sqljunkies.com/weblog/amachanic --
[quoted text, click to view]
"Danny" <istdrs@flash.net> wrote in message news:Vq%od.23751$Rf1.15788@newssvr19.news.prodigy.com... > > Lets say I need to be able to get all the people who's last name begins with > a letter. So the clustered index for the table is set to LastNameInitial > char(1). How would this slow down other indexes or am I just choosing a > specific exception to a general rule? There is no rule, general or otherwise. Remember that columns from clustered indexes will be duplicated as the leaves of non-clustered indexes; so try to keep them fairly narrow. I assume that's what Aaron was referring to. However, there are times when a wide clustered index works quite well, and definitely lots of situations in which using non-integers/dates is appropriate. -- Adam Machanic SQL Server MVP http://www.sqljunkies.com/weblog/amachanic --
Aaron,
I agree. Clustered indexes on int, bigint, datetime things that make natural range lookus work best. But exactly how does using other types slow other nonclustered indexes? Lets say I need to be able to get all the people who's last name begins with a letter. So the clustered index for the table is set to LastNameInitial char(1). How would this slow down other indexes or am I just choosing a specific exception to a general rule? Danny [quoted text, click to view] "aaron kempf" <aaron_kempf@hotmail.com> wrote in message news:OU$xvnc0EHA.3708@TK2MSFTNGP14.phx.gbl... > never, ever, ever, ever, ever > > base a clustered index on anything other than an integer or date. > > it slows down all of the other indexes on the table. I prefer using INTs > for > clustered indexes-- especallyt for hash indexing.. but there are some > times > that it is really helpful to use a date for a clustered index. > > > > > "Wayne Snyder" <wayne.nospam.snyder@mariner-usa.com> wrote in message > news:uXp9Z59yEHA.3708@TK2MSFTNGP14.phx.gbl... >> Let me help.... Although I am an author for Learnkey, I did not do the >> current SQL 2k Data Warehouse stuff, but I think I can tell you what the >> author was thinking about here.. >> >> Think about the leaf level of non-clustered indexes. Each entry contains > the >> key and the row locator(pointer the actual data row.) When the > non-clustered >> index is built on a heap ( no clustered index exists for the table) , the >> row locator is the physical address of the data row ( I think 6 bytes - >> it >> is the file#-Page#-Row#onthePage). >> >> If you build a clustered index on this same table on the lastname column >> ( >> which is perhaps varchar(24) with an average length of 8 characters). The >> row locator for EACH of the non-clustered indexes is replaced by the >> clustered index key... We just grew the leaf entry for the row locator > from >> 6 to 8 bytes.... >> >> So the size of the clustered index key matters. >> >> I do not know if I have the same opinion as the author you mention ( I'll >> have to think about it.) But now you know why he said it.. >> >> -- >> Wayne Snyder, MCDBA, SQL Server MVP >> Mariner, Charlotte, NC >> www.mariner-usa.com >> (Please respond only to the newsgroups.) >> >> I support the Professional Association of SQL Server (PASS) and it's >> community of SQL Server professionals. >> www.sqlpass.org >> >> <anonymous@discussions.microsoft.com> wrote in message >> news:174d01c4bc4c$4f113280$a601280a@phx.gbl... >> > Thanks for the reply. The CD was from Learnkey. It >> > mentions that when you use clustered indexes it will make >> > the non-clustered index huge. >> >> > >
so anyone got the best site explaining in detail cluster and non-cluster
index which impact a HUGE system (production). TQ [quoted text, click to view] "anonymous@discussions.microsoft.com" wrote:
> I just gone through a datawarehouse CD seminar for SQL2K > but a little confused on something. The author said not > to use clustered indexes with star/snowflake schemas. Is > there a reason for this? The author never explained why > we should not use clustered indexes in a datawarehouse. > Does anyone have an answer?
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |