Using Indexes to Optimize Projections in Queries
OJAI queries that do not use the OJAI Distributed Query Service can use indexes even when there are no filter conditions referencing the fields of an index. This requires a full scan of the index. However, in cases where all fields referenced in the query are fields in an index, the need to read the HPE Ezmeral Data Fabric Database JSON table is eliminated. The referenced fields can be either indexed fields or included fields
The following table provides examples where HPE Ezmeral Data Fabric Database can and cannot use the index for projections. Assume you have an index with the following fields:
- Indexed fields -
IdxField1
,IdxField2
- Included fields -
Field3
,Field4
,Field5
Further assume that the fields referenced in the index are a small subset of the total fields in the HPE Ezmeral Data Fabric Database JSON table. With these assumptions, avoiding reads on the JSON table is beneficial.
OJAI Query Elements | Use of Index for Projections |
---|---|
|
Yes All fields referenced are fields in index. |
|
Yes All fields referenced are fields in index. |
|
No
|
|
No All fields referenced are fields in the index, but the index cannot be used with the WHERE condition. |
|
No All fields referenced are fields in the index, but the index cannot be used with the WHERE condition. |
|
No All fields referenced are fields in the index, but the index cannot optimize the ORDER BY. The query needs the OJAI Distributed Query Service to sort large data sets. |
Projections on Container Field Paths
When your query projects a container field path and the container field path is an included field in an index, then HPE Ezmeral Data Fabric Database can use the index for the projection. It is not enough for the container field path to be an indexed field. See Covering Indexes and Container Field Paths for details.