# Selective SOQL and large data volume queries

*Platform Developer II Study Guide · Performance · Last updated October 16, 2026 · https://www.certifyforce.com/study-guide/platform-developer-ii/performance/selective-soql-and-large-data-volume-queries*

How the query optimizer decides whether a filter can use an index, how to rewrite negative, null, OR and formula filters so it can, and which structure (SOQL for loop, Batch QueryLocator, SOSL, OFFSET, hard delete) fits a large data volume scenario.

- Certification: Salesforce Platform Developer II
- Exam section: Performance
- Study guide: https://www.certifyforce.com/study-guide/platform-developer-ii

**Official exam objective:**

> Given a scenario, choose the appropriate logic and query structure to maximize application performance and handle large data volumes.

## What the exam expects

- Decide whether a filter is selective from the field's index type, the operator and the number of matching rows.
- Rewrite negative, null, OR and cross-object formula filters so the optimizer can use an index, and confirm it with the Query Plan tool.
- Choose a SOQL for loop, a Batch Apex QueryLocator or SOSL for large result sets and multi-object text search.
- Know the OFFSET ceiling and why large deletes should bypass the Recycle Bin.

Key terms: **selective filter** (a filter on an indexed field that matches fewer rows than the index threshold), **standard index** (maintained by the platform on fields such as Id, Name, lookups and CreatedDate), **custom index** (on a custom field, from an External ID or Unique flag or a Support request), **Query Plan tool** (Developer Console view of the optimizer's plans and costs), **soft delete** (a deleted row kept in the Recycle Bin that every query must still exclude).

## Key facts

| Fact | Detail | Source |
|---|---|---|
| Custom index threshold | <10% of rows, max 333,333 (LDV guide; the Query Plan FAQ tiers it: 10% of first million + 5% beyond) | [Indexes](https://developer.salesforce.com/docs/atlas.en-us.salesforce_large_data_volumes_bp.meta/salesforce_large_data_volumes_bp/ldv_deployments_infrastructure_indexes.htm), [Query Plan Tool FAQ](https://help.salesforce.com/s/articleView?id=000386864&language=en_US&type=1) |
| Standard index threshold | <30% of first million + <15% of the rest, max 1,000,000 | [Indexes](https://developer.salesforce.com/docs/atlas.en-us.salesforce_large_data_volumes_bp.meta/salesforce_large_data_volumes_bp/ldv_deployments_infrastructure_indexes.htm) |
| OR filters | Every OR field must be indexed for any index to be used | [Indexes](https://developer.salesforce.com/docs/atlas.en-us.salesforce_large_data_volumes_bp.meta/salesforce_large_data_volumes_bp/ldv_deployments_infrastructure_indexes.htm) |
| Default indexes | Id, Name, OwnerId, lookups, CreatedDate, SystemModstamp, RecordType, External ID, Unique | [Working with Very Large SOQL Queries](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm) |
| SOQL rows per transaction | 50,000; a batch QueryLocator returns up to 50 million | [Using Batch Apex](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_batch_interface.htm) |
| Heap size | 6 MB synchronous, 12 MB asynchronous | [Execution Governors and Limits](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_gov_limits.htm) |
| Maximum OFFSET | 2,000 rows, else `NUMBER_OUTSIDE_VALID_RANGE` | [OFFSET](https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_offset.htm) |
| Recycle Bin retention | 15 days; soft-deleted rows still slow queries | [Deleting Data (Techniques)](https://developer.salesforce.com/docs/atlas.en-us.salesforce_large_data_volumes_bp.meta/salesforce_large_data_volumes_bp/ldv_deployments_techniques_deleting_data.htm) |

## Check yourself

- Why does `Promo_Code__c` without an index force a scan even though the other OR field is a lookup?
- When should a search page use SOSL instead of several `LIKE` queries?
- Why does the 42nd page of a 50-row OFFSET pager fail while the 41st works?
- Which tool shows whether a query uses an index before you request one?

## Sources

- [Indexes](https://developer.salesforce.com/docs/atlas.en-us.salesforce_large_data_volumes_bp.meta/salesforce_large_data_volumes_bp/ldv_deployments_infrastructure_indexes.htm)
- [Working with Very Large SOQL Queries](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm)
- [SOQL and SOSL (Large Data Volumes Best Practices)](https://developer.salesforce.com/docs/atlas.en-us.salesforce_large_data_volumes_bp.meta/salesforce_large_data_volumes_bp/ldv_deployments_best_practices_soql_and_sosl.htm)
- [Using SOQL and SOSL](https://developer.salesforce.com/docs/atlas.en-us.salesforce_large_data_volumes_bp.meta/salesforce_large_data_volumes_bp/ldv_deployments_techniques_using_soql_and_sosl.htm)
- [Execution Governors and Limits](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_gov_limits.htm)
- [SOQL For Loops](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_loops_for_SOQL.htm)
- [Using Batch Apex](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_batch_interface.htm)
- [Developer Console Query Editor](https://help.salesforce.com/s/articleView?id=platform.code_dev_console_tab_query_editor.htm&type=5)
- [Developer Console Query Plan Tool FAQ](https://help.salesforce.com/s/articleView?id=000386864&language=en_US&type=1)
- [OFFSET](https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_offset.htm)
- [Deleting Data (Techniques)](https://developer.salesforce.com/docs/atlas.en-us.salesforce_large_data_volumes_bp.meta/salesforce_large_data_volumes_bp/ldv_deployments_techniques_deleting_data.htm)
- [Deleting Data (Best Practices)](https://developer.salesforce.com/docs/atlas.en-us.salesforce_large_data_volumes_bp.meta/salesforce_large_data_volumes_bp/ldv_deployments_best_practices_deleting_data.htm)

## About the full unit

The "Core concepts", "Exam traps" and "Worked scenario" sections of this unit, its 13 linked practice questions, and unlimited timed mock exams are included with the Platform Developer II pack (USD $19): https://www.certifyforce.com/practice-exams