Introduction to Couchbase for Oracle Developers and Experts: Part 7: Optimizer

ORACLE

Couchbase

Optimizer Concepts

Optimizer Statistics

Optimizer hints

Advisor

Oracle CBO Book

Rule-Based Optimizer

CBO documentation

CBO Blog and Stats blog

The CBO Talk

Query Transformation (Rewrites) (chapter 5)

INLIST handling

IN list handling.

We’ve seen queries with 25,000 values in the IN list! N1QL optimizer handles various forms of IN list efficiently by using multiple index lookups on both static and dynamic lists by creating a hash map that has O(1) lookup complexity during IN clause evaluation. This was added in Couchbase 6.5.

Subqueries:

Significant subquery optimizations via query rewrites. See chapter 5.4

Subqueries. There are simple optimizations for both correlated and non-correlated subqueries. Subquery unnesting (flattening) or rewriting subqueries as joins aren’t done yet.

Materialized view rewrites. Used mainly in data warehouse workload.

Couchbase does have materialized views (simply called views) that execute map-reduce functions. These views have a separate API to access and are not used by N1QL.  

Star join transformations and others.

Query service is targeted for OLTP-like workload. Many of the transformations like star transformations are useful for data warehousing workload. 

JOIN Methods

Nested loop

Nested loop

Hash join

Hash join. With RBO, you need to specify the build or the probe side of the has joined for one of the collections; CBO determines probe and builds sides based on the statistics.  The query service does limit the number of items for the hash table to 16 million to avoid memory bloat.

Sort-merge join

Unavailable

Join reordering is an important step in optimization; Join order can change a query execution time from hours to minutes and vice versa. It’s based on the cardinality estimates to create a plan with the lowest cost.

Couchbase 7.1 adds cost-based join-reordering optimization (aka, join enumeration). Prior to 7.1, the join order is the same as the order specified in the FROM clause of the SELECT statement.

ACCESS methods

Full table scan; Reads all the rows in the table without any logical order. Rarely done in an OLTP application and use only where index paths are unavailable.

Couchbase collection is a hash distributed table and doesn’t support the exact full table (collection) scan.  However, you can create a primary index that provides equivalent functionality. In addition, you can filter the data based on the document key and you can also exploit the key ordering in the index 

E.g. FROM t ORDER BY META(t).id

The primary index can also be used to efficiently filter and paginate the result sets. 

FROM t 

WHERE META(t).id LIKE “CA::%”

ORDER BY META(t).id

OFFSET 100

LIMIT 10; 

Pro tip: Consider using keyset pagination.

Table access by rowid

Document key is the equivalent of rowid. Index scans return a set of document keys to query service which uses a look-up by rowid to retrieve the document for further processing.  The applications can also retrieve documents by specifying one or more keys

SELECT * FROM t USE KEYS [“k1”]

SELECT * FROM t USE KEYS [“k1”, “k2”, “K8”, “K4”]

Index unique scan

Index scan with limit 1

Index range scan

Index range scan is the main workhorse for the query service to use the indexer. The query specifies the spans for the index to filter and return the results.  This range scan can also skip the keys in the case of a composite index.  E.g. index on (c1, c2, c3) can be used with index spans on c1 and c3 for WHERE c1 = 20 and c3 > 40; 

Index skip scan

The optimizer does a statistical analysis of the leading columns to scan and filter values in non-leading columns.  This can reduce the number of indexes you need in the system. 

Unavailable.

Full Index Scan

Full index scans are used depending on the predicate and cost.  

E.g.   WHERE c1 IS NOT MISSING ORDER BY c1; 

Fast Full Index Scan

[All the columns are in the index and used for order by]

This is simply an ordinary index scan with an optimizer matching the order by expressions with index key order; Additional optimization by pushing down the pagination clauses (OFFSET, LIMIT) to the index scan

Index join

In Couchbase parlance, it’s called a multi-index scan. Multiple indexes are used to evaluate a single collection, results of which can be joined with other indexes. 

Bitmap indexes

Not available

Array indexes are known as multivalue indexes in Oracle. They need to have a special code remove duplicates from the qualified rowids returned from the index scan. 

An array index scan is similar to Couchbase.  For arrays, there could be multiple index entries for a single document. Hence duplicate document keys returned by the index scan need to be removed. Couchbase array indexes can themselves have an array constructor, complex expressions, and therefore is more flexible. 

Statistics Collection

ANALYZE statement

ANALYZE  statement

Same as

UPDATE STATISTICS statement

Automatic statistics collection via DBMS package. 

Unavailable

EXPLAIN

Multiple ways to generate the query plan. The plan is tabular with indentation to represent the order.

1. EXPLAIN PLAN

2. V$SQL_PLAN (Plan for the statement in cursor cache)

See the full guide here.

A single way to generate the query plan. The plan generated is in JSON and therefore the query execution tree is correctly represented without having to guess.

1. EXPLAIN. Explained here and here.

Graphical plan with profile: I searched and searched. I could only see an old graphical explain; Nothing for 21c

Graphical explain plan via query work bench. 

Graphical plan with profile: I searched and searched. I could only see an old graphical explain; Nothing for 21c

When you execute the query, the profile will give the same plan with different shades of color to give you a sense of the expense. Clicking on each box will give you stats on that iterator. 

Hints: Extensive hints are available.

Hints: Extensive hints are available. N1QL hints are similar to the other Oracle database, MySQL than Oracle database.