Selective queries, indexes and skinny tables
How the query optimizer decides whether an index is used, which fields can and can't be indexed, and when two-column indexes, skinny tables, divisions, parent-record summaries and batch Apex keep a multi-million-row org fast.
Given a customer scenario, design a data model that scales considering LDV and solution performance.
~8 min read · 20 practice questions ·
What the exam expects
- Apply the standard and custom index thresholds to a row count to decide whether a filter is selective.
- Fix non-selective filters: nulls, negative operators, non-deterministic formulas, OR clauses, FirstName/LastName.
- Choose a custom index, two-column index, skinny table or divisions, and know which need Salesforce Customer Support.
- Design reports and parents for scale, process millions of rows with batch Apex, and speed Full sandbox copies.
Key terms: selective filter (an indexed filter under the optimizer's threshold); query optimizer (pre-queries index statistics to pick an access path); non-deterministic formula (one whose value can change without the record changing); skinny table (a Support-built table holding a subset of one object's fields); division (a Support-enabled partition of large data sets).
Key facts
- Detail
- Under 30% of the first million records and 15% of the rest, capped at 1,000,000
- Detail
- Under 10% of the first million records and 5% of the rest, capped at 333,333
- Detail
- Auto Number, Email, Number, Text; marking one creates an index
- Detail
- Multi-select picklist, multicurrency currency, long text, binary, some formulas
- Detail
- Gathered nightly after large API data changes
- Detail
- 200 columns, one object only, copied to Full sandboxes (other types need Support)
- Detail
- Over one million records in one object and more than 35 licenses
- Detail
- Bypasses the SOQL row limit, up to 50 million records
Sources for this section (7)
- Indexesdeveloper.salesforce.com (opens in a new tab)
- Query Plan Tool FAQhelp.salesforce.com (opens in a new tab)
- Very Large SOQL Queriesdeveloper.salesforce.com (opens in a new tab)
- Database Statisticsdeveloper.salesforce.com (opens in a new tab)
- Skinny Tablesdeveloper.salesforce.com (opens in a new tab)
- Divisionsdeveloper.salesforce.com (opens in a new tab)
- Use Batch Apexdeveloper.salesforce.com (opens in a new tab)
- Why can't Support add a custom index to a formula that uses TODAY()?
- When is a two-column custom index better than two single-column indexes?
- Why should a report total child amounts from a field on the parent rather than summarizing the children?
- What must happen before an admin can add a field to an existing skinny table?
The questions are free to read. Answers with their sources and self-grading are included with the pack.
Keep reading with the Platform Data Architect pack
- Core concepts
- Exam traps
- Worked scenario
The pack includes:
- All 21 lessons of the Platform Data Architect guide, with reading progress
- Interactive decision walks and step-by-step sequence steppers
- Inline practice questions and one-click topic practice (20 questions for this lesson)
- An official source cited for every claim
- An AI tutor that answers from the guide
- Unlimited timed practice exams