INDEX setup on surrogate key

[JohnnyDeep]

Hi,

I am creating an unique index on surrogate key column. The surrogate
key is a generate number always increasing on my dimension table. The
only operation on the table is insert (no update and no delete).

1- Can I specify pctfree=0 for the unique index on the surrogate key
without getting page splits since I am only inserting an increasing
number?

2- Does it make sense to cluster the unique index on the surrogate
key since the only use of this column is to make the join with the fact
table (no range scan, no order by, no group by and no distinct)?

I am working the DB2 V8.2.3 on IBM AIX.

Thanks.

[Mark A]

JohnnyDeep wrote:

Hi,

I am creating an unique index on surrogate key column. The surrogate
key is a generate number always increasing on my dimension table. The
only operation on the table is insert (no update and no delete).

1- Can I specify pctfree=0 for the unique index on the surrogate key
without getting page splits since I am only inserting an increasing
number?

Yes.

2- Does it make sense to cluster the unique index on the surrogate
key since the only use of this column is to make the join with the fact
table (no range scan, no order by, no group by and no distinct)?

I am working the DB2 V8.2.3 on IBM AIX.

Thanks.

May be better to set the table to append mose (always insert at the
end) without a clustering index. You should get 100% cluster ratio
anyway, without the overhead of having to use a clustering index in the
insert rule.

[Mark A]

Mark A wrote:

May be better to set the table to append mose (always insert at the
end) without a clustering index. You should get 100% cluster ratio
anyway, without the overhead of having to use a clustering index in the
insert rule.

Should be: append mode, not append mose