SF-Data-Arch Large Data Volume Considerations Practice Question
A data architect is designing a solution for a custom object Order__c that will contain 30 million records. Users need to frequently query orders by Customer__c (a lookup to Account) and Order_Date__c. The architect plans to create a composite custom index on Customer__c and Order_Date__c. Which consideration is most critical for the index to be used by the query optimizer?
⚠ Common exam trap
The trap here is focusing on the order of fields in the WHERE clause or the number of fields, rather than the selectivity of the leading field in the composite index.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
The leading field in the composite index must be highly selective for the queries being executed.
For a composite index to be used by the query optimizer, the leading field must be selective. In this scenario, queries filter by Customer__c and Order_Date__c. If Customer__c is highly selective (e.g., a specific customer), the index will be used. If Customer__c is not selective, the optimizer may perform a full scan. Therefore, the most critical consideration is the selectivity of the leading field.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
The composite index must include at least three fields to be effective for large data volumes.
Why it's wrong here
Composite indexes can consist of two fields and still be effective. The number of fields is not the primary factor; rather, the leading field's selectivity and the overall filter conditions determine index usage. Requiring three fields is not a Salesforce requirement and does not guarantee better performance.
- ✗
The composite index must be created on fields that are not already indexed by default.
Why it's wrong here
Salesforce automatically indexes certain fields like Id, Name, and lookup fields. However, if a field is already indexed, you cannot create a custom index on it alone, but you can include it in a composite index. The critical factor is not whether fields are already indexed, but the selectivity of the leading field.
- ✗
The fields in the composite index must be in the same order as they appear in the SOQL WHERE clause.
Why it's wrong here
The order of fields in a composite index does not need to match the order in the WHERE clause. The optimizer can use the index as long as the leading field in the index is included in the filter. However, the leading field must be selective for the index to be used, which is a more critical consideration.
- ✓
The leading field in the composite index must be highly selective for the queries being executed.
Why this is correct
For a composite index to be used, the leading field must be selective. In this scenario, Customer__c is likely to be selective if queries filter by a specific customer. If the leading field is not selective, the optimizer may ignore the index entirely. Thus, ensuring the leading field's selectivity is critical.
About these practice questions
This SF-Data-Arch question is part of Courseiva's 222-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Salesforce exam blueprint
This SF-Data-Arch practice question is part of Courseiva's free Salesforce certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the SF-Data-Arch exam.