Here are the previous articles comparing architecture, database objects, data types, data modeling, statements and features, and indexing of Oracle with Couchbase. This post will focus on the optimizer.
To database what Yoda is to Star Wars, optimizer is. – Yoda
Separating the HOW (logical, physical representations, access methods) from WHAT (the SQL query) was the genius of the relational model. SQL has been unreasonably effective for relational models and beyond. A good optimizer is critical for SQL irrespective of the data model. The job of an optimizer is to produce an efficient execution algorithm, commonly known as the query plan or simply the plan.
Oracle is an RDBMS, that used a rule-based optimizer (RBO) for many years before switching to Selinger style cost-based optimizer. Couchbase N1QL is a declarative query language that is SQL for JSON. Couchbase N1QL also started with a rule-based optimizer. In 2021, we added a cost-based optimizer (CBO) for the query service. CBO for the analytics service is on the roadmap. JSON is flexible, supports complex objects, arrays in documents and predicates.
Couchbase’s patented cost-based optimizer builds on and extends the traditional CBO concepts to JSON’s flexible, nested model. Just like Oracle, RBO and CBO coexist in Couchbase. This article describes the query optimizer in the query service of Couchbase. Couchbase analytics service still uses the rule-based optimizer.
*See the full intro to Couchbase for the Oracle developer series here.
Below is a typical flow of query execution:
The job of the optimizer is to transform the query into a query plan:
To achieve that, at a high level, the query optimizer does the following steps:
Rewrite a query to its logical equivalent to make the optimizer easier/better.
Eg, Expression rewrites, subquery flattening.
Join reordering (aka join enumeration).
Determine the most efficient join order.
Choose an efficient access method for each data source and join method.
Choose the right index, predicate pushdowns, join type (eg, hash join or nested loop join) for each join.
Create a physical plan for the query engine to execute.
Oracle Resources on Optimizer
SQL Maria has great byte-sized videos explaining the Oracle Optimizer. Much of the Oracle features and descriptions in this article are from them. Oracle’s SQL tuning guide is also a great resource for learning about the Oracle optimizer. Optimizer is the key to the success of SQL and the performance of your queries.
Let’s deep dive into each of the optimizers and compare the features.