Index Drop and Recreate

[Former Member]

Dear All,

Please let me know step by step how to do the following activities(SAP recommanded - Right order) in a cube which is having data.

Drop:

1. Drop non-clustered indexes first

2. Drop a clustered index at last

Re-Create:

1. Create the clustered index first {At what basis, can we select the column/s for clustered index? Where & How?}

2. Create the “composite index” next (index over all key columns) {At what basis, can we select the column/s for composite index? Where & How?}

3. Create all other non-clustered indexes afterwards (each of them only on one column) {At what basis, can we select the column/s for non-clustered index? Where & How?}

Note:

BW Version : 3.5

Database : MS SQL Server 2000

Total Char. in Cube : 21

Total Time Char. in Cube: 6

Total KF. in Cube : 38

Please ask me if you need more information.

Appreciate your early "step by step" reply

Thanks,

Guna.

[clas_hortien]

Hello,

for the drop the sequence is correct. For the recreate it is also correct, but

it makes no difference if you create the composite index right after the clustered

index or later, as it is a non-clustered index as all the other indexes as well. So

to more general sequence is

- create the clustered index first

- create the non-cliustered indexes in any order you want

The columns and the order of the columns of the indexes are defined in the SAP Data Dictionary and MUST NOT be changed.

Regards

Clas

[Former Member]

Hi,

Thanks for your reply.

Please let me know how to do drop and recreate indexes in a table.

It would be better if you explain me step by step.

Thanks a lot,

Guna.

[Christoph_G]

Hello Guna,

as Clas already explained, the fields and their order of indexes on SAP tables (including cube tables in BI) is well defined and must not be altered. Also the clustered index on the f-fact table should never be dropped - actually it defines the physical table layout on the database, so dropping and recreating it will execute a costly reorganisation of the table twice. So to help you further you should give us a hint about what result you expect ...

Best regards,

Christoph