Switch the sequence of Primary Key Columns

[Former Member]

Hi MaxDB experts,

As part of our product upgrade we've made a schema change to 10 of our database tables. Typically these tables contain about 5 million rows in them. The change involves switching the order of columns in the primary key (original order SampleTime, Id; new order would be Id, SampleTime). What's the quickest way to achieve this?

So far I've tried the following approaches without much luck:

1. Use the "alter table alter primary key (new seuqence of columns)". Takes an average of 1 hour per table.

2. Copy original table content into another table with "insert into newTable (select * from existingTable)" command. Takes the same amount of time as #1

3. Tried using "Export Table" option presented by the "loadercli". Unfortunately, exported data can not be imported to a table with a different schema (in our case changed primary key column sequence).

What else can we try? Any advise / direction would be greatly appreciated. Thanks for your time.

Sincerely,

Sameer Apte

[lbreddemann · Accepted solution]

Hi there,

ok, I assume that you figured out that you like to have data entries belonging to a specific ID stored rather nearby than scattered around in the table by TIMESTAMP or that you have more queries that specify the ID but not the TIMESTAMP.

Both would be good reasons to perform such a change.

Concerning the speed: basically things won't get any faster than ALTER TABLE or INSERT (SELECT * FROM)...

The ALTER TABLE approach would have the advantage to be transactional atomic and simple to use.

The INSERT approach would enable the use of the PREFETCHING feature for the read-I/O on the source table.

So if you're just focussing on speed, then I'd recommend to:

• setup a data cache that can hold both source and target tables im memory

• enable prefetching by setting the parameter READAHEAD_TABLE_THRESHOLD to say 128 (be aware that you've to use MaxDB 7.6.05 or higher, but not 7.7.x for that!)

After you've copied each table, make sure to create the secondary indexes one by one since otherwise the internal parallelism won't be used.

regards,

Lars

p.s.

it is possible to export/import into different schemas - it's even supported via DB Studio.

Anyhow, it wouldn't make anything quicker for this case.

[Former Member]

Hi Lars,

Thanks for your reply. It was helpful and your comments were spot on.

Best regards,

samapt0100