Descending indexes
in [DB2]
|
Prev: 64-bit DB2 server and 32-bit application?
Next: DB2 Express-C 9.7 via ODBC from a MAC with Filemaker Pro 11 Advanced
From: Troels Arvin on 10 May 2010 17:53 Hello, Let's say I have a large table which includes an row with values generated from a sequence, i.e. ever-increasing values (BIGINTs); the row is indexed with a clustering index. New rows are regularly added to the table, and sometimes, a few rows are updated. The users are normally only interested in the latest 20% of the rows. Am I right in thinking that the clustering index shoud be created with the DESC attribute, because it will increase the chance of cache (buffer pool) hits when using the index? -- Troels
From: Frederik Engelen on 11 May 2010 04:20 On May 10, 11:53 pm, Troels Arvin <tro...(a)arvin.dk> wrote: > Hello, > > Let's say I have a large table which includes an row with values > generated from a sequence, i.e. ever-increasing values (BIGINTs); the row > is indexed with a clustering index. New rows are regularly added to the > table, and sometimes, a few rows are updated. The users are normally only > interested in the latest 20% of the rows. > > Am I right in thinking that the clustering index shoud be created with > the DESC attribute, because it will increase the chance of cache (buffer > pool) hits when using the index? > > -- > Troels Hello Troels, I don't think there is a difference with regards to caching. Why do you expect to have more cache hits with this cluster index? An ascending index makes more sense to me, actually. With a descending clustering index, DB2 will constantly try to insert records at the beginning of the table. Very quickly, there will be no space left there and he will have to revert to appending to the end of the table. In your case, if you never delete, you could even try to use APPEND mode, you'll be surprised how good your cluster factor on this specific column will remain (don't update too much and use PCTFREE) :-) -- Frederik Engelen RealDolmen
From: Troels Arvin on 11 May 2010 06:45 Hello, Frederik Engelen wrote: > I don't think there is a difference with regards to caching. Why do you > expect to have more cache hits with this cluster index? I was temporarily confused. > An ascending index makes more sense to me, actually. With a descending > clustering index, DB2 will constantly try to insert records at the > beginning of the table. Very quickly, there will be no space left there > and he will have to revert to appending to the end of the table. Good point. But what's the point with DESC indexes, then? In the PostgreSQL documentation, it states that DESC indexes are un-interesting for single- column indexes, but that they may make sense for multi-column indexes. The DB2 documentation doesn't give any advice (or I haven't looked closely enought). > In your case, if you never delete, you could even try to use APPEND > mode, you'll be surprised how good your cluster factor on this specific > column will remain (don't update too much and use PCTFREE) :-) Yes, I thought that it would make a lot of sense, but I also thought that APPEND-only tables could only be used if table changes are strictly always inserts(?) -- Troels
From: Frederik Engelen on 11 May 2010 08:03
On May 11, 12:45 pm, Troels Arvin <tro...(a)arvin.dk> wrote: > Hello, > > Frederik Engelen wrote: > > I don't think there is a difference with regards to caching. Why do you > > expect to have more cache hits with this cluster index? > > I was temporarily confused. > > > An ascending index makes more sense to me, actually. With a descending > > clustering index, DB2 will constantly try to insert records at the > > beginning of the table. Very quickly, there will be no space left there > > and he will have to revert to appending to the end of the table. > > Good point. > > But what's the point with DESC indexes, then? In the PostgreSQL > documentation, it states that DESC indexes are un-interesting for single- > column indexes, but that they may make sense for multi-column indexes. > The DB2 documentation doesn't give any advice (or I haven't looked > closely enought). > > > In your case, if you never delete, you could even try to use APPEND > > mode, you'll be surprised how good your cluster factor on this specific > > column will remain (don't update too much and use PCTFREE) :-) > > Yes, I thought that it would make a lot of sense, but I also thought that > APPEND-only tables could only be used if table changes are strictly > always inserts(?) > > -- > Troels Hello, Since indexes allow reverse scans by default, there is no performance difference for a single column index between ASC and DESC. For multi- column indexes, the "relative order" between different columns does matter as the order of only one column cannot be reversed. It's either everything or nothing. A small example to explain: create table test(a int, b int, c int, d int); create index idx_ascdesc on test(a asc, b desc); select a,b,c from test t order by t.a asc, t.b asc; -> TBSCAN select a,b,c from test t order by t.a asc, t.b desc; -> IXSCAN select a,b,c from test t order by t.a desc, t.b asc; -> IXSCAN (when index allows reverse scans, otherwise TBSCAN) select a,b,c from test t order by t.a desc, t.b desc; -> TBSCAN Hope this clears it up. -- Frederik Engelen RealDolmen |