DSO secondary Index
Having read a number of articles/documents, including;
I have a couple of questions about the creation and use of Secondary DSO Indexes.
Take the following example update routines (from a source DSO to a Target DSO):
First Char. Update Routine:
SELECT COUNT(*) FROM /BIC/AZSDDHDUK00 INTO l_num WHERE DOC_NUM = COMM_STRUCTURE-DOC_NUM AND ACT_GI_DTE EQ ' '.
Second Char. Update Routine:
SELECT COUNT(*) FROM /BIC/AZSDDHDUK00 INTO l_num WHERE DOC_NUM = COMM_STRUCTURE-DOC_NUM AND /BIC/ZPOD_STS NE 'C'.
Third Char. Update Routine:
SELECT MAX( /BIC/ZCPODCGL ) FROM /BIC/AZSDDHDUK00 INTO l_date WHERE DOC_NUM = COMM_STRUCTURE-DOC_NUM.
1) Should I create three Indexes on the Source DSO;
020: 0DOC_NUM & 0ACT_GI_DTE
030: 0DOC_NUM & ZPOD_STS
Or (as the above document link leads me to believe) do I only need to create Indexes 020 and 030, because the third example update routine above will use Index 020 as it has 0DOC_NUM first.
2) Is it necessary to 'load' this secondary index after the DSO that I have created the indexes on is loaded? I had understood that it was not, however I have been told that I will need to. Alternatively, is there a benefit in deleting the index before each Delta into the DSO and recreating the index afterwards.
Thanks in advance,
try first to create and Index with the three objects:
0DOC_NUM, 0ACT_GI_DTE and ZPOD_STS.
See if the result is statisfactory
the questions here are:
1. can you have several 0ACT_GI_DTE for one single DOC_NUM?
2 resp ZPO_STS?
If yes and the cardinality of of the index with the three chars would be much higher than an index havong only two them then perhaps the three indexes would be better.
I am also not sure if your index will be used if your where clause is NE...
For the second question, I am not sure but I think that by activating the DSO, the index will be created. You can check this in SE14 with the active table of your DSO / INDEXES.
Whether to DROP/LOAD/CREATE or simply LOAD will depend on how many records are loaded in comparison to the number of records already loaded in the active table. e.g if you are loading 1'000 records and your active table is already 100'000, it doesn't make sense to DROP and RECREATE. I recommend to DROP/RECREATE when this ratio is above 40/50%...
If you don't DROP and RECREATE in your chain, think about dropping and recreated them anyway every one or two month.... This prevent degeneration...
But by the way you're talking about a DSO thus that should be DROP/LOAD/ACTIVATE/RECREATE versus LOAD...
on which DB is your BW running?
hope this helps...
Message was edited by: