Creating database indexes in ServiceNow database used to be a bit more complicated because you had to raise a ticket in the HI instance. However starting with Fuji you can define the database indexes on your own. In this post, I will show you how to create new indexes and find the existing ones, even using an API. SnowMirror is working with existing ServiceNow indexes to be able to warn users if a table to replicate does not contain an index on the sys_updated_on column.
Reading Existing Indexes
There are several ways how to get a list of existing database indexes on a ServiceNow table.
1. Tables & Columns
Go to System Definition -> Tables & Columns and select a table. In the right column in the Fields section, you can see the columns being part of an index (marked with the Info icon). The Indexes section then contains a little more details about the existing indexes.
Index Viewer in Tables & Columns
2. System Definition – Tables
Go to System Definition -> Tables and select a table from the list. Then right-click the form header and select Configure -> Related Lists and add the Database Indexes -> Reference Table related list. The related table then contains a list of all table indexes. The Column value can contain a comma-separated list of fields in the index.
Database Indexes – Reference List
3. Database Indexes – Virtual Table
Just type v_db_index.list into the Navigation Filter in the Application Navigator. The virtual v_db_index table opens, however, no records are being displayed. The virtual table works correctly only if you filter for indexes in a certain table. So use a filter or a column search to specify the table name. The result then contains a list of indexes, the Column Name(s) column contains the comma-separated list of fields in the index again.
4. Web Service API
The v_db_index table can be queried even using the Direct Web Services SOAP API. Just use the out-of-the-box endpoint https://yourinstance.service-now.com/v_db_index.do?SOAP and its operation getRecords to get the list of indexes for a certain table. As in the previous case do not forget to specify the table name otherwise you get an empty list.
5. Releases Before Eureka
We are not sure in this case if the table v_db_index works in Dublin or not. Anyway, the older ServiceNow versions worked with a similar virtual table called sysx_indexes. You can use it in the same way as described in #4 and #5. Yes, even the SOAP web service works for this table.
Creating Indexes
Starting with Fuji release you can create your own indexes. Please note that it is an advanced procedure and should only be performed by an administrator familiar with database management. Be aware that there is no way, except contacting ServiceNow, to modify or remove the index you create.
1. Tables & Columns
Go to System Definition -> Tables & Columns and select a table. At the very bottom of the page, there is a new form for creating indexes. Just select the columns for your new index and press Create Index. In my experience, the Index Name is not being taken into account at all. A new pop-up appears after pressing the button which allows you to fill in your email address to be notified when the index is ready. ServiceNow is creating the indexes in the background rather than making it a synchronous process.
Notification When Index Is Ready
2. System Definition – Tables
Go to System Definition -> Tables, choose a table and go to the indexes reference list. See #2 from the Read section. By using the New button, you get exactly the same form as in the previous case.
Resources
You can find some more details in the ServiceNow Wiki, however, many features described in this article are not officially documented.
http://wiki.servicenow.com/index.php?title=Creating_a_Custom_Table#Adding_a_Database_Index http://wiki.servicenow.com/index.php?title=Table_Administration#Fuji
