SiebelGuide.com/siebelblogs

Indexes are created on the table to improve performance. The purpose of adding index is to help the database locate records much quickly and efficiently. The steps needed in adding an index to a Siebel table are pretty straight forward but this has to be first done on the local client db using Siebel Tools and should never be done straight on the database.

For the purpose of this blog, I will take an example of adding an index to an extension column s_org_ext.X_OLD_ACC_NUM.

This blog assumes that the extension column X_CDC_ACC_NUM has already been created and needs to be indexed so that the query runs faster.

Steps:

1. Open Siebel Tools by connecting to Local DB.
2. Check out the project Table Organization under which this table belongs to.
3. Make a copy of your existing local database found at “C:\Program Files\Siebel\7.7\Tools\LOCAL\sse_data.dbf”. This will ensure a safe back up.
4. Login to Siebel Tools by connecting to your Local Machine
5. Select the Table for which you want index a column. Ex: S_ORG_EXT
6. Under Object Explorer Select Index object type. You will see in the object list editor that lot of indices already exists for this table.
7. Create a new record in the object list editor and give name. Ex: XOLDACCNUM_X (_X in the end of the name will be automatically added if not specified)
8. Type some comments. This will help for future reference
9. Expand the Index object type and select Index Column
10. Create a new record and add Column name Ex: X_CDC_ACC_NUM and step off. If necessary add more columns.
11. Select the table and hit Apply on the Object list editor
12. 
    
13. You will see a pop-up warning you that you are about to connect to the local database. Click ok to continue.
14. A popup appears asking for more information
15. Fill the following information
    1. Tables: Current Row
    2. Privileged User Id: SIEBEL (all CAPS)
    3. Password: this is the password that was used when you extracted the local database EX: if you extracted your local database with username: JMAY and password: INTERNET then you should enter INTERNET. The most common practice is I believe that the password will be your login name as you will be extracting from Development environment.
    4. ODBC data source: This is the name of the System DSN set under Windows Control Panel -> Data Sources (ODBC) -> System DSN. It is most likely be “SSD 77 Local Db default instance” or “SSD Local Db default instance”. The DSN should be pointing to the sse_data.dbf file. This step the most common issue people might face as they might not know the instance or don’t know where to look at. Otherwise it is very simple.
16. Click Apply.
17. You will see a popup that changes are successfully applied.
18. Test and make sure everything looks fine.
19. Now check-in into the server.
20. Object changes would be available on the server but it is not yet been reflected on the database.
21. Login into Siebel Tools connecting to Server
22. Find the table and make sure that the extended column is visible.
23. Select the table and click apply to have server changes reflect in the server database. Fill up required information similar to step 14.
24. After applying changes you should be able to see changes in the database. Login to the server database using your favorite SQL editor like TOAD and verify that the index has been created.
25. Test the server DB and you are all set.

Note:

1. Never add an index to a system column otherwise it will impact performance severely.
2. Create indices only if needed and do it with caution.
3. Never drop an index that is created or already exists instead turn them as inactive.
4. Test thoroughly before deployment.

Hope this document has helped you. Let me know if you have any questions.

I would really appreciate if anyone can participate writing any articles like this and send it to me. Thanks.

Related posts(Auto Generated):

1. Adding Extension Column to Siebel Table - Made Easy
2. Unlock a Project
3. How to Enable Project Check Out
4. Siebel Tools Setup for Debugging
5. Automatic Login to Siebel