Arquitetura de consulta e recuperação

Também em 19 idiomas

English · Español · 日本語 · Português (Brasil) · Français · Deutsch · Italiano · 한국어 · 简体中文 · 繁體中文 · Bahasa Indonesia · Türkçe · Polski · Tiếng Việt · ไทย · Українська · Español (México) · Français (Canada) · Deutsch (Schweiz)

An open sketchbook on a drafting table at dawn, a building sketched on one page and a data model on the other, with a pencil, an eraser, a cup and a lamp. — Imagem gerada com IA

Guia de estudo do capítulo 8 do curso Data Architect. Leia primeiro os cinco módulos; esta página ajuda-o a conferir o que ficou.

Sobre este guia de estudo. Ele foi construído apenas a partir do conteúdo deste curso e da lista de objetivos publicada no guia do exame da Salesforce. Ele nunca se baseia em questões reais do exame e não afirma saber o que cai na prova. Se já fez o exame, por favor não me envie as questões: o contrato de certificação proíbe partilhá-las, e decorar respostas é, de qualquer forma, a forma errada de se tornar arquiteto.

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

Para o exame

Marque conforme avança. Cada item tem um link para onde é ensinado.

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 short IN list, 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) ⟲

Search (module 5) ⟲

Sei quando usar SOSL em vez de uma pilha de ORs em SOQL. (A consulta começa pela estrutura; a pesquisa, por um termo)
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) ⟲

Além do exame

O exame é o piso, não o teto. Estes são os hábitos deste capítulo de que um arquiteto precisa, quer alguma pergunta trate deles ou não.

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. ⟲
Indexe quando for uma solução limpa e geral; redesenhe em vez de indexar um caso isolado.
Metadata should tell the truth. Don’t mark a field as an External ID just for the index. ⟲
Às vezes a melhor otimização de consulta é uma população ativa menor.
Pesquise para descobrir; consulte para operar. A pesquisa não é um modelo de dados.
Data can deserve to exist without deserving to appear in search. ⟲
Search runs as someone. Check as the user, not as an admin. ⟲

Palavras para conhecer

Termo	Numa linha
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
Índice de duas colunas	Um índice sobre dois campos usados juntos
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	A linguagem de consulta do Salesforce para perguntas estruturadas
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

Teste-se

Cenários curtos no estilo do curso. Decida a sua resposta antes de abrir.

1. Um objeto tem 10 milhões de registos. Um filtro num campo com índice personalizado devolve 400.000. O otimizador vai usar o índice?

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?

⟲

Os dados envelheceram: os casos são fechados, então um filtro que antes correspondia a uma fatia pequena agora corresponde a quase tudo. A seletividade depende da distribuição, que muda com o tempo. Releia A armadilha dos casos fechados.

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?

⟲

Não. O índice de pesquisa atualiza-se de forma assíncrona e pode atrasar 15 minutos ou mais. O processo conhece a estrutura, então uma consulta estruturada é a escolha determinística. Releia A atualização faz parte do design.

Para saber mais

Salesforce Certified Platform Data Architect Exam Guide (abre num novo separador), Salesforce Help, checked 2026-11-01: the official list of objectives and their weights. Always check the current version before you book the exam. ⟲
A secção Para saber mais de cada módulo lista a documentação da Salesforce por trás dos seus factos, com a data de consulta.