Skip to content

Defining Database Indexes

SQL database indexes can be defined for entity sets, to enable better server performance for OData queries using $filter. This is achieved by use of the SQL.Indexes annotation term within the appropriate EntitySet elements.

Example:

<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
    <edmx:Reference Uri="vocabularies/com.sap.cloud.server.odata.sql.v1.xml">
        <edmx:Include Alias="SQL" Namespace="com.sap.cloud.server.odata.sql.v1"/>
    </edmx:Reference>
    <edmx:DataServices>
        <Schema Namespace="example" xmlns="http://docs.oasis-open.org/odata/ns/edm" Alias="Self">
            <!-- type definitions omitted for brevity ... -->
            <EntityContainer Name="ExampleService">
                <Annotation Term="SQL.TrackChanges"/>
                <EntitySet Name="Customers" EntityType="Self.Customer">
                    <NavigationPropertyBinding Path="Orders" Target="Orders"/>
                    <Annotation Term="SQL.Indexes">
                        <Collection>
                            <Record Type="SQL.Index">
                                <PropertyValue Property="Name" String="DateOfBirthIndex"/>
                                <PropertyValue Property="Properties">
                                    <Collection>
                                        <PropertyPath>DateOfBirth</PropertyPath>
                                    </Collection>
                                </PropertyValue>
                            </Record>
                            <Record Type="SQL.Index">
                                <PropertyValue Property="Name" String="HomeAddressCityIndex"/>
                                <PropertyValue Property="Properties">
                                    <Collection>
                                        <PropertyPath>HomeAddress/City</PropertyPath>
                                    </Collection>
                                </PropertyValue>
                            </Record>
                        </Collection>
                    </Annotation>
                </EntitySet>
                <EntitySet Name="Orders" EntityType="Self.Order">
                    <NavigationPropertyBinding Path="Customer" Target="Customers"/>
                </EntitySet>
            </EntityContainer>
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>

Note

It might not be necessary to define database indexes when using column based data stores. When using SAP HANA, please consult the SAP HANA Performance Guide for Developers.


Last update: August 12, 2020