Defining Indexes

In addition to common DDL commands, such as CREATE/DROP INDEX, developers can use GridGain’s SQL APIs to define indexes.

GridGain automatically creates indexes for each primary key and affinity key field. When you define an index on a field in the value object, GridGain will create a composite index consisting of the indexed field and the cache’s primary key. In SQL terms, it means that the index will be composed of two columns: the column you want to index and the primary key column.

Configuring Indexes Using Annotations

Indexes, as well as queryable fields, can be configured from code via the @QuerySqlField annotation. In the example below, the GridGain SQL engine will create indexes for the id and salary fields.

public class Person implements Serializable {
    /** Indexed field. Will be visible to the SQL engine. */
    @QuerySqlField(index = true)
    private long id;

    /** Queryable field. Will be visible to the SQL engine. */
    @QuerySqlField
    private String name;

    /** Will NOT be visible to the SQL engine. */
    private int age;

    /**
     * Indexed field sorted in descending order. Will be visible to the SQL engine.
     */
    @QuerySqlField(index = true, descending = true)
    private float salary;
}

class Person
{
    // Indexed field. Will be visible to the SQL engine.
    [QuerySqlField(IsIndexed = true)] public long Id;

    //Queryable field. Will be visible to the SQL engine
    [QuerySqlField] public string Name;

    //Will NOT be visible to the SQL engine.
    public int Age;

    /** Indexed field sorted in descending order.
      * Will be visible to the SQL engine. */
    [QuerySqlField(IsIndexed = true, IsDescending = true)]
    public float Salary;
}

This API is not presently available for C++.

The type name is used as the table name in SQL queries. In this case, our table name will be Person (schema name usage and definition is explained in the Schemas section).

Both id and salary are indexed fields. id will be sorted in ascending order (default) and salary in descending order.

If you do not want to index a field, but you still need to use it in SQL queries, then the field must be annotated without the index = true parameter. Such a field is called a queryable field. In the example above, name is defined as a queryable field.

The age field is neither queryable nor is it an indexed field, and thus it will not be accessible from SQL queries.

When you define the indexed fields, you need to register indexed types.

Indexing Nested Objects with Annotations

Fields of nested objects can also be indexed and queried using annotations. For example, consider a Person object that has an Address object as a field:

public class Person {
    /** Indexed field. Will be visible for SQL engine. */
    @QuerySqlField(index = true)
    private long id;

    /** Queryable field. Will be visible for SQL engine. */
    @QuerySqlField
    private String name;

    /** Will NOT be visible for SQL engine. */
    private int age;

    /** Indexed field. Will be visible for SQL engine. */
    @QuerySqlField(index = true)
    private Address address;
}

Where the structure of the Address class might look like:

public class Address {
    /** Indexed field. Will be visible for SQL engine. */
    @QuerySqlField (index = true)
    private String street;

    /** Indexed field. Will be visible for SQL engine. */
    @QuerySqlField(index = true)
    private int zip;
}

In the above example, the @QuerySqlField(index = true) annotation is specified on all the fields of the Address class, as well as the Address object in the Person class.

This makes it possible to execute SQL queries like the following:

QueryCursor<List<?>> cursor = personCache.query(new SqlFieldsQuery(
  "select * from Person where street = 'street1'"));

Note that you do not need to specify address.street in the WHERE clause of the SQL query. This is because the fields of the Address class are flattened within the Person table which simply allows us to access the Address fields in the queries directly.

Registering Indexed Types

After indexed and queryable fields are defined, they have to be registered in the SQL engine along with the object types they belong to.

To specify which types should be indexed, pass the corresponding key-value pairs in the CacheConfiguration.setIndexedTypes() method as shown in the example below.

// Preparing configuration.
CacheConfiguration<Long, Person> ccfg = new CacheConfiguration<>();

// Registering indexed type.
ccfg.setIndexedTypes(Long.class, Person.class);

var ccfg = new CacheConfiguration
{
    QueryEntities = new[]
    {
        new QueryEntity(typeof(long), typeof(Person))
    }
};

This API is not presently available for C++.

This method accepts only pairs of types: one for key class and another for value class. Primitives are passed as boxed types.

Group Indexes

To set up a multi-field index that can accelerate queries with complex conditions, you can use a @QuerySqlField.Group annotation. You can add multiple @QuerySqlField.Group annotations in orderedGroups if you want a field to be a part of more than one group.

For instance, in the Person class below we have the field age which belongs to an indexed group named age_salary_idx with a group order of "0" and descending sort order. Also, in the same group, we have the field salary with a group order of "3" and ascending sort order. Furthermore, the field salary itself is a single column index (the index = true parameter is specified in addition to the orderedGroups declaration). Group order does not have to be a particular number. It is needed only to sort fields inside of a particular group.

public class Person implements Serializable {
	/** Indexed in a group index with "salary". */
	@QuerySqlField(orderedGroups = { @QuerySqlField.Group(name = "age_salary_idx", order = 0, descending = true) })
	private int age;

	/** Indexed separately and in a group index with "age". */
	@QuerySqlField(index = true, orderedGroups = { @QuerySqlField.Group(name = "age_salary_idx", order = 3) })
	private double salary;
}

class Person
{
    [QuerySqlField(IndexGroups = new[] {"age_salary_idx"})]
    public int Age;

    [QuerySqlField(IsIndexed = true, IndexGroups = new[] {"age_salary_idx"})]
    public double Salary;
}

This API is not presently available for C++.

Configuring Indexes Using Query Entities

Indexes and queryable fields can also be configured via the org.apache.ignite.cache.QueryEntity class which is convenient for Spring XML based configuration.

Most concepts that are discussed as part of the annotation-based configuration above are also valid for the QueryEntity based approach.

The types whose fields are configured with the @QuerySqlField annotation and are registered with the CacheConfiguration.setIndexedTypes() method are internally converted into query entities.

The example below shows how to define a single field index, group indexes, and queryable fields.

<bean class="org.apache.ignite.configuration.IgniteConfiguration" id="ignite.cfg">
    <property name="cacheConfiguration">
        <bean class="org.apache.ignite.configuration.CacheConfiguration">
            <property name="name" value="Person"/>
            <!-- Configure query entities -->
            <property name="queryEntities">
                <list>
                    <bean class="org.apache.ignite.cache.QueryEntity">
                        <!-- Setting  the type of the key -->
                        <property name="keyType" value="java.lang.Long"/>

                        <property name="keyFieldName" value="id"/>

                        <!-- Setting type of the value -->
                        <property name="valueType" value="org.apache.ignite.examples.Person"/>

                        <!-- Defining fields that will be either indexed or queryable.
                             Indexed fields are added to the 'indexes' list below.-->
                        <property name="fields">
                            <map>
                                <entry key="id" value="java.lang.Long"/>
                                <entry key="name" value="java.lang.String"/>
                                <entry key="salary" value="java.lang.Float "/>
                            </map>
                        </property>
                        <!-- Defining indexed fields.-->
                        <property name="indexes">
                            <list>
                                <!-- Single field (aka. column) index -->
                                <bean class="org.apache.ignite.cache.QueryIndex">
                                    <constructor-arg value="name"/>
                                </bean>
                                <!-- Group index. -->
                                <bean class="org.apache.ignite.cache.QueryIndex">
                                    <constructor-arg>
                                        <list>
                                            <value>id</value>
                                            <value>salary</value>
                                        </list>
                                    </constructor-arg>
                                    <constructor-arg value="SORTED"/>
                                </bean>
                            </list>
                        </property>
                    </bean>
                </list>
            </property>
        </bean>
    </property>
</bean>

CacheConfiguration<Long, Person> cache = new CacheConfiguration<Long, Person>("myCache");

QueryEntity queryEntity = new QueryEntity();

queryEntity.setKeyFieldName("id").setKeyType(Long.class.getName()).setValueType(Person.class.getName());

LinkedHashMap<String, String> fields = new LinkedHashMap<>();
fields.put("id", "java.lang.Long");
fields.put("name", "java.lang.String");
fields.put("salary", "java.lang.Long");

queryEntity.setFields(fields);

queryEntity.setIndexes(Arrays.asList(new QueryIndex("name"),
        new QueryIndex(Arrays.asList("id", "salary"), QueryIndexType.SORTED)));

cache.setQueryEntities(Arrays.asList(queryEntity));

var cacheCfg = new CacheConfiguration
{
    Name = "myCache",
    QueryEntities = new[]
    {
        new QueryEntity
        {
            KeyType = typeof(long),
            KeyFieldName = "id",
            ValueType = typeof(dotnet_helloworld.Person),
            Fields = new[]
            {
                new QueryField
                {
                    Name = "id",
                    FieldType = typeof(long)
                },
                new QueryField
                {
                    Name = "name",
                    FieldType = typeof(string)
                },
                new QueryField
                {
                    Name = "salary",
                    FieldType = typeof(long)
                },
            },
            Indexes = new[]
            {
                new QueryIndex("name"),
                new QueryIndex(false, QueryIndexType.Sorted, new[] {"id", "salary"})
            }
        }
    }
};
Ignition.Start(new IgniteConfiguration
{
    CacheConfiguration = new[] {cacheCfg}
});

This API is not presently available for C++. You can use XML configuration.

A short name of the valueType is used as a table name in SQL queries. In this case, our table name will be Person (schema name usage and definition is explained on the Schemas page).

Once the QueryEntity is defined, you can execute the SQL query as follows:

SqlFieldsQuery qry = new SqlFieldsQuery("SELECT id, name FROM Person" + "WHERE id > 1500 LIMIT 10");

Indexing Nested Objects with Query Entities

Fields of nested objects can be indexed and queried using query entities. For example:

public class Person {
    private long id;
    private String name;
    private int age;
    private Address address;
}
public class Address {
    private String street;
    private int zip;
}

We can index the street and zip from the address by doing the following.

QueryEntity queryEntity = new QueryEntity();
queryEntity.setKeyFieldName("id").setKeyType(Long.class.getName()).setValueType(Person.class.getName());
LinkedHashMap<String, String> fields = new LinkedHashMap<>();
fields.put("id", "java.lang.Long");
fields.put("name", "java.lang.String");
fields.put("Address.street", "java.lang.String");
fields.put("Address.zip", "int");
queryEntity.setFields(fields);
queryEntity.setIndexes(Arrays.asList(new QueryIndex("name"),
        new QueryIndex(Arrays.asList("id", "name"), QueryIndexType.SORTED)));

This makes it possible to execute SQL queries like the following:

QueryCursor<List<?>> cursor = personCache.query(new SqlFieldsQuery(
  "select * from Person where street = 'street1'"));

Creating Indexes Using SQL Command

Refer to the CREATE INDEX section.

Configuring Index Inline Size

Proper index inline size can help speed up queries on indexed fields. Refer to the dedicated section in the SQL Tuning guide for the information on how to choose a proper inline size.

In most cases, you will only need to set the inline size for indexes on variable-length fields without a specified length, such as strings or arrays. The default value in this case is 10.

If the variable-length field length is specified (for example, varchar(20)), the optimal inline size is calculated automatically.

You can change the default value by setting either

inline size for each index individually, or
CacheConfiguration.sqlIndexMaxInlineSize property for all indexes within a given cache, or
IGNITE_MAX_INDEX_PAYLOAD_SIZE system property by specifying the MAX inline size for all indexes in the cluster.

The settings are applied in the order listed above.

You can also configure inline size for each index individually, which will overwrite the default value. To set the index inline size for a user-defined index, use one of the following methods. In all cases, the value is set in bytes.

When using annotations:

@QuerySqlField(index = true, inlineSize = 13)
private String country;

[QuerySqlField(IsIndexed = true, IndexInlineSize = 13)]
public string Country { get; set; }

This API is not presently available for C++.

When using QueryEntity:

QueryIndex idx = new QueryIndex("country");
idx.setInlineSize(13);
queryEntity.setIndexes(Arrays.asList(idx));

var qe = new QueryEntity
{
    Indexes = new[]
    {
        new QueryIndex
        {
            InlineSize = 13
        }
    }
};

This API is not presently available for C++.

If you create indexes using the CREATE INDEX command, you can use the INLINE_SIZE option to set the inline size. See examples in the corresponding section.
```
create index country_idx on Person (country) INLINE_SIZE 13;
```

Custom Keys

If you use only predefined SQL data types for primary keys, then you do not need to perform additional manipulation with the SQL schema configuration. Those data types are defined by the GridQueryProcessor.SQL_TYPES constant, as listed below.

Predefined SQL data types include:

all the primitives and their wrappers except char and Character
String
BigDecimal
byte[]
java.util.Date, java.sql.Date, java.sql.Timestamp
java.util.UUID

However, once you decide to introduce a custom complex key and refer to its fields from DML statements, you need to:

Define those fields in the QueryEntity the same way as you set fields for the value object.
Use the new configuration parameter QueryEntity.setKeyFields(..) to distinguish key fields from value fields.

The example below shows how to do this.

<bean class="org.apache.ignite.configuration.IgniteConfiguration">
    <property name="cacheConfiguration">
        <bean class="org.apache.ignite.configuration.CacheConfiguration">
            <property name="name" value="personCache"/>
            <!-- Configure query entities -->
            <property name="queryEntities">
                <list>
                    <bean class="org.apache.ignite.cache.QueryEntity">
                        <!-- Registering key's class. -->
                        <property name="keyType" value="CustomKey"/>
                        <!-- Registering value's class. -->
                        <property name="valueType" value="org.apache.ignite.examples.Person"/>
                        <!-- Defining all the fields that will be accessible from DML. -->
                        <property name="fields">
                            <map>
                                <entry key="firstName" value="java.lang.String"/>
                                <entry key="lastName" value="java.lang.String"/>
                                <entry key="intKeyField" value="java.lang.Integer"/>
                                <entry key="strKeyField" value="java.lang.String"/>
                            </map>
                        </property>
                        <!-- Defining the subset of key's fields -->
                        <property name="keyFields">
                            <set>
                                <value>intKeyField</value>
                                <value>strKeyField</value>
                            </set>
                        </property>
                    </bean>
                </list>
            </property>
        </bean>
    </property>
</bean>

// Preparing cache configuration.
CacheConfiguration<Long, Person> cacheCfg = new CacheConfiguration<Long, Person>("personCache");

// Creating the query entity.
QueryEntity entity = new QueryEntity("CustomKey", "Person");

// Listing all the queryable fields.
LinkedHashMap<String, String> fields = new LinkedHashMap<>();

fields.put("intKeyField", Integer.class.getName());
fields.put("strKeyField", String.class.getName());

fields.put("firstName", String.class.getName());
fields.put("lastName", String.class.getName());

entity.setFields(fields);

// Listing a subset of the fields that belong to the key.
Set<String> keyFlds = new HashSet<>();

keyFlds.add("intKeyField");
keyFlds.add("strKeyField");

entity.setKeyFields(keyFlds);

// End of new settings, nothing else here is DML related

entity.setIndexes(Collections.<QueryIndex>emptyList());

cacheCfg.setQueryEntities(Collections.singletonList(entity));

ignite.createCache(cacheCfg);

var ccfg = new CacheConfiguration
{
    Name = "personCache",
    QueryEntities = new[]
    {
        new QueryEntity
        {
            KeyTypeName = "CustomKey",
            ValueTypeName = "Person",
            Fields = new[]
            {
                new QueryField
                {
                    Name = "intKeyField",
                    FieldType = typeof(int),
                    IsKeyField = true
                },
                new QueryField
                {
                    Name = "strKeyField",
                    FieldType = typeof(string),
                    IsKeyField = true
                },
                new QueryField
                {
                    Name = "firstName",
                    FieldType = typeof(string)
                },
                new QueryField
                {
                    Name = "lastName",
                    FieldType = typeof(string)
                }
            }
        },
    }
};

This API is not presently available for C++. You can use XML configuration.

© 2024 GridGain Systems, Inc. All Rights Reserved. Privacy Policy | Legal Notices. GridGain® is a registered trademark of GridGain Systems, Inc.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.

Last updated on Dec 04, 2024