Salesforce Docs Portal

Optimize Queries with Secondary Indexes

When you create a secondary index on a data lake object (DLO) or materialized data model object (DMO), Data 360 quickly finds and returns data based on defined secondary field values. A secondary index improves performance and reduces credit usage.

Data 360 automatically indexes objects. For Profile and Other categories, the index is the primary key. For the Engagement category, the index is the Event Time Field. You can use a secondary index to filter and join data by using fields other than the primary key, which can improve query performance.

In Data 360, a secondary index is a batch data transform on the provided DLO or materialized DMO that adds the specified field as the new index.

Data 360 keeps each secondary index current by refreshing it on a schedule. Secondary index refreshes are incremental and only rebuild the portion of the index affected by changed data. You can choose how often each index refreshes to balance data freshness against cost.

Use Cases and Best Practices

Consider this guidance when you create a secondary index.

  • A secondary index can improve query speed for large DLOs or materialized DMOs that you frequently filter on a non-primary field.
  • Secondary indexes work best for queries that filter on fields with many distinct values, where queries return nearly unique objects. For example, customer-id, phone number, email, or “Global ID” fields.
  • Use a secondary index to quickly search a range of data in a large object. For example, use a secondary index to find customers in a defined zip code range.
  • A secondary index can speed up queries on materialized DMOs, such as customer segments or aggregated metrics.
  • You can create multiple secondary indexes on one DLO or DMO so that queries use the most efficient index.
  • Because a DMO can map to multiple DLOs, a DMO query reads from every mapped DLO. For the best performance, create the secondary index on the mapped field in each DLO that is large enough to impact query speed. If you index only some of the mapped DLOs, the query still scans the DLOs that aren’t indexed.
  • Consider packaging your secondary index configurations in standard data kits for easy sharing and consistent configuration.

For information on credit usage, see Billing Considerations for Secondary Indexes.

Secondary Indexes and SQL

  • Data Model Object (DMO) queries that filter on a field mapped to a DLO or DMO secondary index field use the secondary index to improve query performance.
  • In WHERE clauses (filters), secondary indexes only work with a direct match on the indexed field. The indexed field can’t be based on calculations, functions, or fuzzy patterns.
  • When joining two large objects on a non-primary field, results improve when one of the objects has a small number of rows.
  • When filtering on a secondary index and another field, use the AND clause. The secondary index narrows the result set before the other field is evaluated.

For query examples and detailed guidance on writing filters that use a secondary index, see Filter Your Query Results in the Data 360 Query Guide.

Limitations

Consider these limitations when you define your secondary indexes.

  • Secondary indexes don’t support zero-copy Data 360 objects or unstructured DLOs.
  • Secondary indexes don’t support DevOps data kits or managed packages.
  • Only materialized DMOs (Derived, Bridge, or Transform types) support secondary indexes. Non-materialized DMOs (simple views without underlying physical tables) can’t be indexed.
  • You can’t create a secondary index in a companion org. However, queries from a companion org to a DLO in the shared data space of the home org use the defined secondary index.
  • By default, Data 360 refreshes secondary indexes daily. Secondary indexes work best on objects that update at most every 24 hours. For frequently updated objects, refreshing more often can increase cost without improving freshness, because a large portion of the index can be rebuilt on each refresh.

Next Steps

  • Billing Considerations for Secondary Indexes
    Data 360 Secondary Indexes enable faster query performance and more efficient data retrieval with attributes other than the primary identifier. Use of secondary indexes impacts the consumption of credits used for billing in these usage types.
  • Create a Secondary Index
    Create secondary indexes in Data 360 to improve query performance and reduce credit usage on data lake objects (DLOs) or materialized data model objects (DMOs) by optimizing data retrieval beyond primary key fields.
  • Schedule a Secondary Index Refresh
    Set how often Data 360 refreshes a secondary index so that the index stays current with the underlying object. Choose a refresh frequency that keeps the index fresh enough for your queries without refreshing more often than your data changes.