INDEX setup on surrogate key

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JohnnyDeep - 10 Jul 2006 20:17 GMT

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.

Reply to this Message

Mark A - 10 Jul 2006 21:36 GMT

> Hi,
>
[quoted text clipped - 5 lines]
> 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
[quoted text clipped - 3 lines]
>
> 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.

Reply to this Message

Mark A - 10 Jul 2006 21:37 GMT

> 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

Reply to this Message