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

On May 10, 11:53*pm, Troels Arvin <tro... (AT) arvin (DOT) dk> wrote:
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

Hello,

Frederik Engelen wrote:
I was temporarily confused.


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).


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

On May 11, 12:45*pm, Troels Arvin <tro... (AT) arvin (DOT) dk> wrote:
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