Unique index - there can only be on row in the table with this value. Usually this is reserved for the Primary index which usually includes teh document number.
Non-unique - you can have multiple rows with tha same values. This is what a DSO secondary index would normally have.
I wouldn't worry about the naming convention of the index. The name used doesn't have any impact on your query. The DB figures out which index to use in the BW environment.
As to how much it will help the query, that depends on how selective S1 is. If you run a query requesting one specific S1 value from a 1,000,000 row table with only 50 rows having that value, your performance should be very good. If there are 5,000 rows with that value, it obviously will take longer. If you have 100,000, the DB probably won't (and shouldn't) use the S1 index at full, instead opting to scan the entire table.
Another option to get a sense of what kind of performance to expect would be to run the query from RSRT <b>before</b> you create the index using the Exec 7 Debug option with the selection <b>Display Run Schedule</b>. This will display what is known as the Explain or Execution Plan and will provide a query "cost". Then run the query again <b>after</b> you have created the index. Compare teh cost. The Explain Plan should also show that the index was used.
It will probably be necessary to refresh the DB statistics on the DSO after you create the index before you run the query.
Your DBA can explain what the Explain Plan is showing.
If you are ging to create secondary indexes on large DSOs, you should keep your DBA in the llop. They can offer guidance on the appropriate index construction, and they may also need to make sure enough dispace is allocated for the new indexes.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.