19 Indexing and Performance

Indexing is a foundational feature in Oracle Backend for Firebase that enables efficient querying, filtering, and sorting of documents. Without proper indexing, queries may fail or perform poorly. Oracle Backend for Firebase 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
  
• Automatic Indexes
  
• Functional Indexes
  
• Compund Query Indexing
  
• Collection Group Indexing
  
• Join Collection Indexing
  
• Index Management
  

19.1 Overview

In Oracle Backend for Firebase, 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 for Firebase 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 for Firebase rejects the query with an error indicating that an index is missing.

Types of Indexes

Oracle Backend for Firebase 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.

19.2 Automatic Indexes

Automatic indexes are created for every field in a document when a collection is created. Automatic indexing can be enabled from Console. When enabled, Automatic indexing creates a search index on each field for any collection present in the project. Developer can exempt specific collection 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 collection from auto indexing with frequent writes.

19.3 Functional Indexes

Functional indexes are explicitly defined by the developer in the Oracle Backend for Firebase 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 collection based table, application developer can create index using the Oracle Backend for Firebase console.
• For relational based table, the developer has to create a manual index using SQL. (not maintained by Oracle Backend for Firebase)

How to Create Functional Indexes

Functional indexes are created in the Console by:

1. Navigating to the project’s indexing section

2. Defining the collection or join path

3. Selecting the fields to index

4. Publishing the index

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

19.4 Compund 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 Compund 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 for Firebase rejects the query with an error.

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

19.5 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;
}

19.6 Join Collection Indexing

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

The following must be completed for querying on join collections:

• Join must be defined in the Console

• Functional index must be created for the join path

• 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;
}

19.7 Index Management

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

Creating and Dropping Indexes

Indexes allow Oracle Backend for Firebase 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:

1. Go to the Index Management section of your project.

2. Click Create Index.

3. Choose the collection or collection group.

4. Select fields to index.

5. Specify index type (single/composite).

6. Click Create.

Console Steps: Dropping Indexes:

1. Navigate to the Index Management tab.

2. Locate the index you want to remove.

3. Click Delete.

4. Confirm deletion.

Enabling/Disabling Automatic Indexing

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

Console Steps:

1. Open the Index Settings panel.

2. Locate the Automatic Indexing toggle.

3. Switch to Enable or Disable.

4. 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.

1. Go to Index Management.

2. Click Create Collection Group Index.

3. Enter the subcollection name (For example, recipes).

4. Define fields to index.

5. 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";
}