Defining Primary Key for a View or Table in the Indexes Panel
In Studio, the Indexes panel in the view and table editors enables creation of metadata labels for existing indexes and primary keys already present in the data source. You can mark a column in a view as indexed or as a primary key.
Primary keys are similar to indexes. A column designated as a primary key signifies that every value in that column is unique. So when a column is made a primary key, not only is an index created for it, but typically the native data source ensures that every value in that column is unique and ensures this uniqueness for every update and insert.
Identifying indexes and primary keys enable the TDV Query Engine to use logical algorithms for faster more efficient joins that leverage organization of the data source.
To define and publish an index
1. Open a view or table editor, and click the Indexes tab.
2. Click Add.
3. In the New Index window, supply a name for the index, and click OK.
The index is listed along with the column projections in the view, as shown in the next screen.
4. Select the column for the index and click the right arrow button.
You can add as many columns as you want. When you add columns to the index, you can see the synchronization between the list of available columns and indexed columns.
5. Select Unique or Primary Key box to indicate the type of the index.
If you select Primary Key, the Unique box is automatically selected.
A Unique index is used when you do not want duplicate values to be retrieved. The same is true of Primary indexes. However, in a table you can have only one index marked as Primary key, but you can have more than one index marked as Unique.
6. (optional) Use the Add button to mark more columns as indexes, and use the Remove button to delete any columns improperly marked as existing indexes.
7. Save the resource.
8. Publish the indexed resource to make the index available for external clients.
9. Open the system table ALL_INDEXES (in Data Services/Databases/system).
10. Click Show Contents in the Columns tab to view the details of all the available indexes in the system.
If you have published data source tables, the indexes on those resources would also be listed in ALL_INDEXES.
11. Click a row in the Result panel output to view the result details. For some extremely large results, such as those generated by using EXTEND on an array to generate BIGINT number values, some of the data might be truncated without a visual indication by Studio.