Architettura delle query e del recupero dei dati
Disponibile anche in 19 lingue
English · Español · 日本語 · Português (Brasil) · Français · Deutsch · 한국어 · 简体中文 · Português (Portugal) · 繁體中文 · Bahasa Indonesia · Türkçe · Polski · Tiếng Việt · ไทย · Українська · Español (México) · Français (Canada) · Deutsch (Schweiz)
Guida allo studio del capitolo 8 del corso Data Architect. Leggi prima i cinque moduli; questa pagina ti aiuta a verificare cosa ti è rimasto.
Su questa guida allo studio. È costruita solo con i contenuti di questo corso e con l’elenco degli obiettivi della guida all’esame pubblicata da Salesforce. Non si basa mai su domande reali d’esame, e non pretende di sapere cosa c’è nell’esame. Se hai sostenuto l’esame, per favore non mandarmi domande: l’accordo di certificazione vieta di condividerle, e memorizzare le risposte è comunque il modo sbagliato per diventare architetto.
This chapter is the query and search half of the exam’s Large Data Volume Considerations section (20% of the exam). The three checks and the thresholds are where I’d spend the most time: they’re the ideas people find hardest, and the thresholds are worth being able to work out on paper. Platform numbers change, so check the current ones before you rely on them. ⟲
Per l’esame
Sezione intitolata “Per l’esame ”Spuntali man mano. Ognuno rimanda al punto in cui è spiegato.
Inside the engine (module 1) ⟲
- I know Salesforce is multitenant and metadata-driven: many orgs share one platform, kept apart by the org ID, and adding a field adds metadata, not a database column. (One Building, Many Tenants, No New Tables, Just Metadata) ⟲
- I know standard and custom fields are stored separately, so a query that uses both does a join, and that skinny tables avoid it. (The Join You Never Wrote) ⟲
- I know custom fields can’t use a native database index, so Salesforce copies indexed values into index tables. The internal table names aren’t exam material. (Why a Normal Index Doesn’t Fit) ⟲
Indexes (module 2) ⟲
- I can explain a table scan and an index (the library and the card catalog), and that an index is a copy: it makes finding faster and every save slower. (The Advanced Technology: Card Catalogs, Every Copy Has to Be Kept Up to Date) ⟲
- I know SOQL asks by structure and SOSL asks by words. (Two Ways to Ask) ⟲
- I know the standard indexed fields: RecordTypeId, Division, CreatedDate, Systemmodstamp (LastModifiedDate), Name, Email on contacts and leads, lookup and master-detail fields, and the record ID. (Standard Indexes) ⟲
- I know a custom index comes from Salesforce Customer Support or the Metadata API, and that marking a custom field as an External ID (Auto Number, Email, Number or Text) or Unique indexes it. (Custom Indexes) ⟲
- I know which fields can’t have a custom index: long and rich text areas, multi-select picklists, encrypted text, currency in a multi-currency org, and non-deterministic formulas. A formula can be indexed only if it’s deterministic: no TODAY() or NOW(), and no values from another object. (Which Fields Can Be Indexed) ⟲
When Salesforce uses an index (module 3) ⟲
- I know the three checks, the nitro: the field is indexed; I ask in a selective way; and few enough records match. (The Nitro Boost, All Three: the Boost) ⟲
- I know what asking in a selective way means. Selective: equals, starts with (
LIKE 'abc%'), a shortINlist, a range on a date or number. Not selective: contains and ends with (a leading%), negative operators (!=,NOT LIKE,NOT IN) and comparisons with an empty value. (Asking the Index the Right Way) ⟲ - I can work out the thresholds: standard index, 30% of the first million records plus 15% beyond, capped at 1 million; custom index, 10% plus 5%, capped at 333,333. A standard index on 2 million records allows 450,000; a custom index on 1 million allows 100,000. (The Thresholds) ⟲
- I know a filter can stop being selective without the query changing: cases close, history piles up. (The Closed-Cases Trap) ⟲
- I plan with 30% growth a year, and I don’t confuse it with Well-Architected’s 30% reserve under governor limits. (Add 30% Growth) ⟲
How Salesforce executes a query (module 4) ⟲
- I know the four words: query (which records), fetch (how much you carry back), table scan and join. (Four Words to Know) ⟲
- I know the query optimizer runs pre-queries against its statistics, judges each filter with the three checks, drives from the best index or from the table, and orders the joins; and that a user who sees all records can get a different plan from one who sees only their own. (The Librarian Is the Query Optimizer) ⟲
- I know the optimizer runs for SOQL, reports and list views: a contains or not-equal filter slows a list view as much as code. (When Is the Librarian Working? All the Time) ⟲
- I measure the real distribution, then read the Query Plan: the lowest cost wins, and a cost above 1 means the filter isn’t selective. (Measure the Distribution, Then Ask the Optimizer) ⟲
- I know with AND the most selective indexed filter can drive; with OR, each filter must meet the threshold on its own and every field must be indexed. (More Than One Index: Which One Drives?) ⟲
- I know a two-column index: the first column selects, the second sorts or narrows; the order matters; nulls are allowed in the second column. (Two-Column Indexes) ⟲
- I know a skinny table is not an index: Customer Support creates it; up to 200 columns from one object, no fields from other objects; it leaves out soft-deleted records, avoids the join, and is used automatically when a query, report or list view uses only its fields; custom objects plus Account, Contact, Opportunity, Lead and Case; copied to Full sandboxes only. (Skinny Tables) ⟲
- I know divisions partition a large org’s data to reduce the records queries and reports return: more than one million records in one object, more than 35 licenses, enabled by Customer Support. (Divisions: Splitting a Huge Object) ⟲
- I know Salesforce keeps database statistics, gathered nightly, and that after large loads, updates or deletes through the API, queries may not run efficiently until they’re gathered. (Database Statistics: The Librarian’s Notebook) ⟲
- I can choose between index, rewrite, redesign and archive, and I test at realistic volume and distribution. (When It’s Slow: Index, Rewrite, Redesign or Archive, Correct Isn’t the Same as Scalable) ⟲
Search (module 5) ⟲
- So quando usare SOSL invece di una pila di OR in SOQL. (La query parte dalla struttura; la ricerca parte da un termine)
- I know search finds tokens in its own index first, then fetches the records, and that the index is asynchronous: about 15 minutes or more for new text, and hours after a bulk load. (How Search Works: Tokens First, Then the Fetch, Freshness Is Part of the Design) ⟲
- I know SOSL’s limits: at most 2,000 records searched, then permissions applied; 250 for one object unless you add WHERE or ORDER BY; up to 2,000 divided by the number of objects (and no more than 250 each) when you search several; 20 SOSL queries per Apex transaction. (Asking Search Well) ⟲
- I can explain search crowding: the cut at 2,000 comes before sharing, so records a user can’t see take up places, and a record they can see can be left out. (Search Crowding, Step by Step) ⟲
- I know the levers: search groups (NAME, EMAIL, PHONE), RETURNING, specific terms over wildcards, and wildcards only in the middle or at the end. (Asking Search Well) ⟲
- I know how to reduce crowding: decide which objects each profile searches, take noisy fields out of the index, archive history, and check as the user. (Mitigating Search Crowding: A Playbook) ⟲
Oltre l’esame
Sezione intitolata “Oltre l’esame ”L’esame è il pavimento, non il soffitto. Queste sono le abitudini di questo capitolo di cui un architetto ha bisogno, che una domanda le chieda o no.
- Design against the behavior Salesforce documents, not a table layout. Salesforce rebuilt the engine underneath once already. ⟲
- An index is a copy. Every one makes finding faster and saving slower. ⟲
- “Don’t worry, that field is indexed” is never the end of the conversation. Ask the three questions, then do the math. ⟲
- A query can go bad without changing. ⟲
- Measure before you optimize, and test at the zeros you’ll actually have. ⟲
- Indicizza quando è una soluzione pulita e generale; riprogetta invece di indicizzare un caso isolato.
- Metadata should tell the truth. Don’t mark a field as an External ID just for the index. ⟲
- A volte la migliore ottimizzazione delle query è una popolazione attiva più piccola.
- Cerca per scoprire; interroga per operare. La ricerca non è un modello dei dati.
- Data can deserve to exist without deserving to appear in search. ⟲
- Search runs as someone. Check as the user, not as an admin. ⟲
Parole da conoscere
Sezione intitolata “Parole da conoscere ”| Termine | In una riga |
|---|---|
| Multitenant | Many companies’ orgs share one platform, kept apart by the org ID |
| Metadata-driven | Objects and fields are described as data, not built as database tables |
| Table scan | Touching every record to find the ones you want |
| Index | A copy of one field’s values, with each record’s location, for finding them fast |
| Index table | Where Salesforce copies indexed custom-field values |
| Query optimizer | The part of the platform that chooses the cheapest path for every query, report and list view |
| Selective | Asked in a way an index can use, and matching few enough records |
| Selectivity threshold | The most records an indexed filter can match and still be used |
| Query | Finding which records match |
| Fetch | Carrying back the fields of the records found |
| Join | Crossing from one table or object to another |
| Query Plan | A tool showing the optimizer’s possible paths and their relative cost |
| Custom index | An index on a custom field, from Customer Support, the Metadata API or an External ID |
| Indice a due colonne | Un solo indice su due campi usati insieme |
| Skinny table | A copy of selected fields from one object, kept by Salesforce, for fast reads without the join |
| Division | A partition of a large org’s data that reduces the records queries return |
| Database statistics | The counts the optimizer uses to choose a plan, gathered nightly |
| SOQL | Il linguaggio di query di Salesforce per le domande strutturate |
| SOSL | Salesforce’s search language for finding words across objects |
| Search crowding | Matches the user doesn’t want, or can’t see, filling the 2,000 places before the record they need |
Mettiti alla prova
Sezione intitolata “Mettiti alla prova ”Brevi scenari nello stile del corso. Decidi la tua risposta prima di aprirla.
1. Un oggetto ha 10 milioni di record. Un filtro su un campo con indice personalizzato restituisce 400.000 record. L'ottimizzatore userà l'indice?
Probably not. The custom-index threshold is 10% of the first million plus 5% beyond, capped at 333,333; at 10 million the cap applies, and 400,000 is over it. Check with the Query Plan. Reread The Thresholds.
2. An object has 2 million records. A filter on Name, a standard index, returns 500,000. Selective?
⟲No. A standard index allows 30% of the first million (300,000) plus 15% of the next million (150,000): 450,000. At 500,000 the filter is over the threshold, and the optimizer will look elsewhere or scan the table. Reread As the Object Grows.
3. A list view on 8 million cases filters on Status not equal to Closed. Status has a custom index. Why is it slow?
⟲A negative operator isn’t asked in a selective way, so the index can’t help, and the optimizer runs for list views just as it does for code. Ask positively instead, for the open statuses, and check that the open cases stay under the threshold. Reread Asking the Index the Right Way and When Is the Librarian Working? All the Time.
4. A query on IsClosed = true was fast at launch and is slow three years later. Nothing in the code changed. Why?
⟲I dati sono invecchiati: i casi si chiudono, quindi un filtro che una volta corrispondeva a una piccola fetta ora corrisponde a quasi tutto. La selettività dipende dalla distribuzione, che cambia nel tempo. Rileggi La trappola dei casi chiusi.
5. Someone asks for an index for a report run once a year. What do you ask?
⟲Why it’s slow, and whether a permanent index is worth its cost for a one-off. Measure the distribution and read the Query Plan first; consider rewriting the query, redesigning, or archiving history so the active population is smaller. Reread Before You Ask for an Index.
6. An important query needs recent line items for one Retail Partner, but the partner is on the parent work order, and there are 14.5 million line items. Options?
⟲Redesign: copy the partner onto the line item when it’s created (and index it if the filter needs it), so the query filters the child directly. Weigh the benefit against the duplicate data and its upkeep; here the partner never changes, which helps. Reread When It’s Slow: Index, Rewrite, Redesign or Archive.
7. "Let's ask Support for a skinny table index on Account." What's wrong with that sentence?
⟲A skinny table isn’t an index. It’s a copy of selected fields from one object, which avoids the join between standard and custom fields and makes the fetch lighter. It’s used automatically only when a query, report or list view asks for its fields alone. Reread Skinny Tables.
8. Last night the team loaded 2 million records through the Bulk API. This morning some queries are slow and new records can't be found in search. Broken?
⟲Probably not. The optimizer’s statistics are gathered nightly, so they may not reflect a very large load yet; and the search index is asynchronous, so a bulk load can take hours to become searchable. Plan loads with both in mind. Reread Database Statistics: The Librarian’s Notebook and Freshness Is Part of the Design.
9. A user types any text and expects to find the customer by name, email, phone or notes. The developer builds a SOQL query with ten LIKE clauses joined by OR. Better?
⟲It’s a search problem: use SOSL, which is built for words across fields and objects. A long OR chain also needs every filter to be selective and indexed on its own, and contains-style LIKE never is. Reread Query Starts With Structure; Search Starts With a Term.
10. A care agent searches for a customer's last name and gets 1,900 results, but not the case she needs, which she can open from a list view. Why?
⟲Search crowding. The name matched more than 2,000 records, and search keeps the first 2,000 before it checks what she can see: 100 of those she can’t see, so 1,900 come back, and her case may be among the matches left out. Use a more specific term, search one object, and reduce the noise at the source; then check as her, not as an admin. Reread Search Crowding, Step by Step and Mitigating Search Crowding: A Playbook.
11. Automation must find a delivery exception the moment it's created. Use search?
⟲No. L’indice di ricerca si aggiorna in modo asincrono e può restare indietro di 15 minuti o più. Il processo conosce la struttura, quindi una query strutturata è la scelta deterministica. Rileggi La freschezza fa parte del progetto.
Per approfondire
Sezione intitolata “Per approfondire ”- Salesforce Certified Platform Data Architect Exam Guide (si apre in una nuova scheda), Salesforce Help, checked 2026-11-01: the official list of objectives and their weights. Always check the current version before you book the exam. ⟲
- La sezione Per approfondire di ogni modulo elenca la documentazione Salesforce dietro i suoi fatti, con la data di consultazione.