*While struggling through the options to fix the compression issue, we came across 2 specialized Function Modules which are designed to delete the indices on only E fact table.2 – *RSDU_INFOCUBE_INDEXES_REPAIR – *If you want to rebuild the indexes use this function (same parameter as above).
We tested these FMs in our case and it worked wonders. The compression job which was running for more than 3 hrs and was never ending, completed in just 20 min after we dropped the E table Indices.* Here are the steps, one needs to perform: (Cube YTEST has been taken as an example) *1 – Please confirm that secondary indices are there on F fact table for the cube.
.
2 – Then confirm if Secondary indices are there on E fact table of the cube.
3 – Also confirm if the P index, which is exclusively used for compression, is there on E fact table of the cube. The P Index is a consolidated index on all the dimension keys and is exclusively used for compression. It’s not used for reporting.
4 – Once all the above steps are performed successfully, run the FM – +RSDU_INFOCUBE_INDEXES_DROP+ by giving the E table as parameter.The FM will execute successfully within few seconds.
5 – After that check the indices on E table of Cube . They should now be dropped.
6- Check the P index on the E fact table. The P index is not dropped as it’s mandatory for compression.
7- Also cross-check the secondary indices on F fact table. Those should be intact.
8 – After that execute the compression. It will complete in very less time. In my case it just took 20 minutes compared to 3 hrs earlier.
9 – After that rebuild the secondary indices for E fact table of the Cube with FM +RSDU_INFOCUBE_INDEXES_REPAIR+
10 – And finally check the indices on E fact table. Those should now be successfully generated.
All Bitmaps of the used selection keys are merged to do y quick lookup into the underlying F- and E- tables.
They are a compact index type for READING millions of rows , but not for WRITING.
Good practice is to drop the Indexes before loading into cubes (i.e. process chain "DROP INDEX"). Be aware that if you load small deltas this may not be necessary. With full loads
it surer is a killer if you don't drop them in advance.
On E-tables the normal B-Tree P-Index is kept for updating the compressed table. This is no Problem because this index lookup locks only the row that is updated with index information.
Later rebuild the bitmaps (Oracle will do this very fast in parallel).