Operators Guide

A guide to all the operators for query optimization
Links to several additional resources to help you tune your queries further and learn more about SQL++

#operator	Usage	Description
PrimaryScan	Scan	Scans a primary index v1 (Pre CB 5.50)
PrimaryScan3	Scan	Scans a primary index v2 (CB 5.50+)
ParentScan	Scan	Used for `UNNEST`. Treats the parent object as the result of a scan.
IndexScan	Scan	Scans a secondary index v1 (CB pre 5.0)
IndexScan2	Scan	Scans a secondary index v2 (CB 5.0)
IndexScan3	Scan	Scans a secondary index v3 (CB 5.50+)
KeyScan	Scan	Does not perform a scan. Directly treats the provided keys as a scan.
ValueScan	Scan	Used for the VALUES clause of `INSERT` and `UPSERT` statements. Treats the provided values as the result of a scan.
DummyScan	Scan	Used for `SELECT`s with no `FROM` clause. Provides a single empty object as the result of a scan.
CountScan	Scan	Used when the query has no predicate i.e. `SELECT COUNT(*) FROM bucket-name`. Treats the bucket size as the result of a scan, without actually performing a full scan of the bucket.
IndexCountScan	Used when the query has predicates and the predicate can be pushed to the indexer. Count is performed by Indexer. (v1 Pre CB 5.0)
IndexCountScan2	Scan	Used when the query has predicates and the predicate can be pushed to the indexer. Count is performed by Indexer. (v2 CB 5.0)
IndexCountDistinctScan2	Scan	Used when the query has predicates and the predicate can be pushed to the indexer. Count of Distinct values is performed by Indexer. (v2 CB 5.0)
IntersectScan	Scan	A container that scans its child scanners and intersects the results. Used for scanning multiple secondary indexes concurrently for a single query. Intersect of document keys are done
OrderedIntersectScan	Scan	Same as IntersectScan, First scan in the order is maintained (First Scan Index order is exposed becaue it matches query `ORDER BY`)
UnionScan	Scan	`OR` predicate can use multiple indexes. Each Index perform IndexScan document keys are merged as `UNION`
DistinctScan	Scan	Eliminates Duplicate document keys (Indexer can produce duplicate document keys. Array Indexing, Overlap `OR` clauses)
ExpressionScan	Scan	Source (`FROM` clause) is not a key space. It is Expression. Expression Scan will be performed from the in memory data.
Fetch	Fetch	Obtain documents from the data service based on a key
DummyFetch	Fetch	No Fetch Operations performed. Act like dummy fetch
Join	Join	Used for Look-up Join. `a JOIN b ON KEYS`
IndexJoin	Join	Used for Index Join. `a JOIN b ON KEY b.xxx FOR a`
NestedLoopJoin	Join	ANSI JOIN (`a JOIN b ON a.xx = b.yy`). The Join is performed nested loop every row of a, index scan/fetch is performed on b
HashJoin	Join	ANSI JOIN (`a JOIN b ON a.xx = b.yy`). The Join is performed Hash JOIN, In memory hash table will be constructed on a or b. Then a or b scanned/fetched and look up done in memory hash table
Nest	Join	Join operation between a parent and a child with a nested array where parent is repeated for each child array item. Same as Join (`a NEST b ON KEYS a.xxx`)
IndexNest	Join	Same as Index JOIN (`a NEST b ON KEY b.xxx FOR a`)
NestedLoopNest	Join	Same as Nested LOOP JOIN (`a NEST b ON a.xx = b.yy`)
HashNest	Join	Same as Hash JOIN (`a NEST b ON a.xx = b.yy`)
Unnest	Join	Grouping operation between a parent and a child array where child array is embedded into the parent.
Let	Let + Letting	Let and Letting variables evaluation
InferKeyspace	Infer	INFER statement
Filter	Filter	Apply a filter expression e.g. `WHERE X=<value>`
InitialGroup	Group	Initial phase. (Can be executed in parallel with IntermediateGroup)
IntermediateGroup	Group	Cumulate intermediate results. This phase can be chained.
FinalGroup	Group	Compute final aggregate results.
InitialProject	Project	Reduce the stream size to the fields involved in the query processing
FinalProject	Project	Final Shaping of the result into the JSON for the requested fields
IndexCountProject	Project	Project the output of IndexCountScan2 IndexCountDistinctScan2 operators
Distinct	Distinct	Indicates that duplicates are being filtered from the result.
UnionAll	Set Operator	Combine the results of two queries. For `UNION`, we perform `UNION ALL` followed by DISTINCT.
IntersectAll	Set Operator	Intersect all of the result objects
ExceptAll	Set Operator	Except all of the result objects ( i.e Present on LEFT side query but not present on right side query)
Order	Order	Orders the results based on 1 or more keys ASC or DESC
Offset	Paging	Start returning items from a specified item count
Limit	Paging	Limit the number of items returned to N
SendInsert	Insert	When an insert statement is explained
SendUpsert	Upsert	When an upsert statement is explained
SendDelete	Delete	When a delete statement is explained
Clone	Update	Used for UPDATE. Clones data values so that UPDATEs read original values and mutate a clone.
Set	Update	Used for UPDATE
Unset	Update	Used for UPDATE
SendUpdate	Update	When an update statement is explained
Merge	Merge	Merge Statement
Alias	Framework	Alias of Keyspace
Authorize	Framework	Privilege validation (i.e permission RBAC validation on all objects in the query)
Parallel	Framework	A container that executes multiple copies of its child operator in parallel. Used for all data-parallelism.
Sequence	Framework	A container that chains its children into a sequence. Used for all execution pipelining.
Discard	Framework	Discard results
Stream	Framework	Stream results out. Used for returning results.
Collect	Framework	Collect results into an array. Used for subqueries.
CreatePrimaryIndex	Index DDL	When a `Create PRIMARY INDEX` statement is explained
CreateIndex	Index DDL	When a `CREATE INDEX` statement is explained
DropIndex	Index DDL	When a `DROP INDEX` statement is explained
AlterIndex	Index DDL	When a `ALTER INDEX` statement is explained
BuildIndexes	Index DDL	When a `BUILD INDEX` statement is explained
GrantRole	Roles	GRANT statement
RevokeRole	Roles	Revoke Statement
Explain	Explain	EXPLAIN statement
Prepare	Prepare	Prepare Statement

Resources

This tutorial is part of a Couchbase Learning Path:

Contents