Global Secondary Index is an index with a partition key and a sort key that can be different from keys in the base table. A global secondary index is said to be “global” because queries on the index can cover all the data in the base table, across all partitions. A global secondary index has no size limitations like that of the base table and has its own provisioned read and write throughput settings that are separate from those of the table.
Let us understand the use of Secondary Global Indexes by taking a base table, named BoardExams, with partition key as StudentID and sort key as Stream.
Base Table
The items in the table will be organized like below:
Base Table
Note: All attributes are not shown in the above diagram.
Now, suppose we need maths marks from each stream. Using both StudentID and Stream would be very efficient. But suppose we need maths marks based only on streams and not on StudentID. Thus, the scan operation would be used and for numerous items, it is inefficient. Therefore, for fast retrieval of results on non-key attributes, we use Secondary Global Indexes.
For example, we create a secondary global index named Stream-index with a partition key as Stream and a sort key as Maths.
Note: The base table’s primary key attributes are always projected into an index, so the StudentID attribute is also present. The following diagram shows the Stream-index index:
Stream-index
Now you can query Stream-index and easily obtain the marks for Maths based on Streams. The results are ordered by the sort key values, Maths.
Query on Index created:
Follow the below steps to query on the above-created index:
- Step 1: Click on the table name, BoardExams, and navigate to the Items tab of the table.
- Step 2: Select Query from the dropdown and against Query, select Index. See the below image.
- Step 3: In Partition Key, enter the stream name for which you want to get Maths marks. In our case, the value is Science and the sort key is Marks that should be greater than 90. See the below image:
In the above image, we see the result set obtained from querying on the secondary global index.