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
  • SELECT IdxField1, Field4
 Yes

All fields referenced are fields in index.
  • SELECT Field3, Field4
 Yes

All fields referenced are fields in index.
  • SELECT IdxField1, Field6
 No

Field6 not included in index.
  • SELECT IdxField1, Field3
  • WHERE condition on IdxField2
 No

All fields referenced are fields in the index, but the index cannot be used with the WHERE condition.
  • SELECT IdxField1, Field3
  • WHERE condition on Field4
 No

All fields referenced are fields in the index, but the index cannot be used with the WHERE condition.
  • SELECT IdxField1, Field4
  • ORDER BY on IdxField2
 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.