Indexing and Performance

Indexing is a foundational feature in Oracle Backend with Firebase APIs that enables efficient querying, filtering, and sorting of documents. Without proper indexing, queries may fail or perform poorly. Oracle Backend with Firebase APIs supports both automatic and functional indexing strategies, each tailored to different query patterns and data models.

This chapter explains the types of indexes, their behavior, and how to configure them for advanced use cases like compound filters, collection group queries, and join collections.

Overview

In Oracle Backend with Firebase APIs, every document is stored in a structured format - either as a JSON-style document (Collection Model) or as a relational row (Relational Model). To retrieve documents efficiently, Oracle Backend with Firebase APIs relies on indexes to:

Locate documents that match filter conditions.
Sort documents by specified fields.
Support complex queries across collections or joined tables.

Without the appropriate index, Oracle Backend with Firebase APIs rejects the query with an error indicating that an index is missing.

Types of Indexes

Oracle Backend with Firebase APIs supports two primary types of indexes:

Index Type	Description	Use Cases
Automatic Indexes	Created implicitly for each field in a document.	Simple filters, single-field ordering.
Functional Indexes	Explicitly defined by the developer through the Console.	Compound filters, joins, collection group queries, multi-field ordering.

Index and Security Rule

Every indexed query must be backed by a security rule that:

Matches the document or collection path
Allows the intended method (get, list)
Evaluates conditions using fixed variables like request, resource, auth, and time

If a query is supported by an index but lacks a matching rule, it will be rejected.

Best Practices

Always define indexes before deploying queries in production.
Use the Console to monitor index usage and performance.
Keep indexes minimal — only index fields that are queried or sorted.
Pair every index with a security rule to avoid runtime errors.

Automatic Indexes

Automatic indexes are created for every field in a document when a collection is created. Automatic indexing can be enabled from the Console. When enabled, Automatic indexing creates a search index on each field for any collection present in the project. Developers can exempt specific collections from auto-indexing using the Console.

Automatic indexes support:

Equality (==) and inequality (!=) filters
Range queries (>, <, >=, <=)
Array operations (array-contains, array-contains-any)
Single-field ordering (orderBy("field"))

However, automatic Indexing has these limitations because it:

Cannot support compound queries (for example, where("a", "==", x).orderBy("b"))
Is not usable for collection group or join queries
Cannot be customized or disabled

Note:

Automatic indexing should not be used if application have frequent writes as it will increase the cost of operation.
Exempt all the collections with frequent writes from auto-indexing.

Functional Indexes

Functional indexes are explicitly defined by the developer in the Oracle Backend with Firebase APIs Console. These indexes are required when:

A query involves multiple fields (compound filters)
A where clause is combined with orderBy
A query spans across subcollections (collection group)
A query involves joined relational tables (join collections)

Characteristics

These are user-defined functional indexes using JSON_VALUE(document, '$.attribute').
It can be a btree, bitmap index based on the user input on any attribute of the JSON document.
For a collection based table, application developer can create index using the Oracle Backend with Firebase APIs console.
For a relational based table, the developer must create a manual index using SQL (which is not maintained by Oracle Backend with Firebase APIs).

How to Create Functional Indexes

Functional indexes are created in the Console by:

Navigating to the project’s indexing section
Defining the collection or join path
Selecting the fields to index
Publishing the index

Each functional index must be paired with a corresponding security rule to be usable.

Compound Query Indexing

A compound query filters or sorts on multiple fields. These queries require a functional index that includes all involved fields in the correct order.

Example 19-1 Compound Query

where("category", "==", "Dessert")
  .orderBy("views", "desc")

Required Index:

Fields: category, views
Direction: Ascending or descending as per query

If the index is missing, Oracle Backend with Firebase APIs rejects the query with an error.

Compound query indexes are used to optimize filtering (conditions) and sorting (explicitOrder) in getDocs() queries.

Collection Group Indexing

A Collection Group allows querying across all subcollections with the same name, regardless of their parent document path. Querying a collection group, for example, to query all notes subcollections under different recipes, you require:

Functional index on the notes collection group
Security rule for the group path (For example, /users/{userId}/recipes/{recipeId}/notes/{noteId})

Example 19-2 Security Rule

match /users/{userId}/recipes/{recipeId}/notes/{noteId} {
  allow list: if request.auth != null;
}

Join Collection Indexing

Join Collections are derived from relational tables and allow querying across multiple tables using defined relationships.

The following must be completed to query join collections:

The join must be defined in the Console
A functional index must be created for the join path
A security rule must be published for the join

Example Join

A join between employee and department tables, which is indexed on employee.department_id and department.name

Example 19-3 Security Rule

match /EMP_DEP/_docId {
  allow get: if request.auth != null;
}

Index Management

This section describes how to manage indexes in the Oracle Backend with Firebase APIs Console. Indexes are essential for optimizing query performance, enabling advanced filtering, and supporting collection group queries.

Creating and Dropping Indexes

Indexes allow Oracle Backend with Firebase APIs to efficiently retrieve documents based on field values. You can create indexes manually or rely on automatic indexing. Indexes can be single-field, multi-field, or composite.

Indexes have the following types:

Single-field Index: Indexes one field for basic filtering.
Composite Index: Indexes multiple fields for complex queries.
Collection Group Index: Enables querying across subcollections with the same name.

Console Steps: Creating Indexes:

Go to the Index Management section of your project.
Click Create Index.
Choose the collection or collection group.
Select fields to index.
Specify index type (single/composite).
Click Create.

Console Steps: Dropping Indexes:

Navigate to the Index Management tab.
Locate the index you want to remove.
Click Delete.
Confirm deletion.

Enabling/Disabling Automatic Indexing

Oracle Backend with Firebase APIs supports automatic indexing of fields during document creation. This can be toggled on or off depending on performance and control requirements.

Console Steps:

Open the Index Settings panel.
Locate the Automatic Indexing toggle.
Switch to Enable or Disable.
Save changes.

Note:

When disabled, developers must manually create indexes for queryable fields.
Automatic indexing may be scheduled or overridden manually.

Collection Group Index Setup

To query across subcollections with the same name, a collection group index must be created. This enables cross-document hierarchy queries.

Console Steps:

Go to Index Management.
Click Create Collection Group Index.
Enter the subcollection name (for example, recipes).
Define fields to index.
Click Create.

Security Rule Requirement:

Each collection group must have a corresponding security rule. Example:

match /.*/recipes {
  allow read: if request.auth != null && resource.data.difficulty == "Easy";
}