I had read a lot about indexes here in SDN but I think I don't understand in hole.
Please two questions:
- On the Performance-Tab in InfoCube-Administration what I can check, repair, delete are secondary indexes? I read that primary indexes are build automatically.
- If I just loading normally. One request a day. Are the indexes refreshed every time after load automatically? Primary-Indexes? Secondary Indexes?
I would be very thankfull for a helpful answer.
As far as loading, if the indexes exist when you load the data, the DB takes care of performing all the necessary updating.
When you drop the secondary indexes on the cube, either using the options on the Performance tab, or from a Drop Index process in a Process Chain, the secondary indexs (in Oracle, these are generally bitmap indexes) are dropped on the F fact table.
Data loads faster when the secondary indexes are dropped because there is less work required of the DB. Bitmap indexs have never been very efficient strucutures for Oracle to have to update, and in fact can cause deadlocks in somw cases if they are present when loading a cube.
If you are not using Process Chains to load your data, then you would want to specify that you want to Delete the indexes on the Performance tab. You want to Build the indexes after the load. If you load using Process Chains, then you want to have a Drop process, the Load, and then Create Index process.
There are some scenarios where you might not drop indexes prior to a load. In a 24 hour reporting environment, dropping indexes will result in Infocube quereis running much longer. Also, if you do not compress your Infocubes regularly, the F fact table can become large and the time to rebuild the indexes every day could become significant.