SQLBase Execution Plan Operations

A short reference of the most common SQLBase execution plan operations. That’s basically from the API documentation, which seems to be rather incomplete. The advanced guide has a second on physical operations too, but doesn’t explain how to spot them in the execution plan.

Index and Table Access

Very much like MySQL, SQLBase shows one line per table access in the execution plan. The order of access is, again like in MySQL, top down: the first row in the execution plan corresponds to the first table accessed.

The used index will also be shown in the INDEX column.

SQLBase does not give indications for range vs. unique scans or index-only scans (“Index-only table access”).

The following list gives a short cross-tab of physical operations known to SQLBase to their Oracle counterpart. Unfortunately, these physical operation names are not visible in the execution plan of SQLBase:

Index leaf scan

Corresponds to Oracle’s INDEX FULL SCAN: it reads the index in order. The Index leaf scan also includes the subsequent tables access if necessary.

Matching index scan

Corresponds to Oracle’s INDEX RANGE/UNIQUE SCAN plus the subsequent TABLE ACCESS BY INDEX ROWID if necessary.


Generally join operations process only two tables at a time. In case a query has more joins, they are executed sequentially: first two tables, then the intermediate result with the next table. In the context of joins, the term “table” could therefore also mean “intermediate result”.

SQLBase documentation as well as execution plans refer to temp tables as the intermediate result. The documentation also mentions temp tables on disk—it is unclear to me whether that means the intermediate results are always materialized.

SQLBase supports all three basic joining techniques:

NESTED LOOP / INDEX LOOP / Loop join with hash index

These are basically Nested Loop Joins. They just vary by the indexes being used: Simple loop join doesn’t use indexes at all (possibly repeating full scans on the inner table), the Loop join with (hash) index uses an index on the inner table (which might be a hash index in case of equijoin).


The Index merge join (shown as MERGE JOIN in the execution plan) is a sort-merge join with the prerequisite to have indexes on the join columns for both tables—thus preventing the need for any sort operation.


Is a Hash Join, as the name suggests.

Sorting and Grouping

SQLBase does not indicate sort or aggregate operations in the execution plan. It is, however, able to benefit from an index to optimize sort operations away. Note that SQLBase also supports ASC/DESC modifiers in CREATE INDEX.

Top-N Queries

Top-N queries can be implemented using a session parameter:


This doesn’t seem to affect optimization. To cause the optimizer preferring a pipelined Top-N execution when fetching a small fraction of the full result, use the “Optimize first fetch” option:


The optimizer will now optimize the execution plan so that the first row is returned as fast as possible. Don’t forget to revert to full optimization mode afterwards:


The execution plan does not indicate the presence of a Top-N limit, nor does it indicate the absence of a sort operation to indicate pipelined execution.

About the Author

Photo of Markus Winand

Markus Winand is the SQL Renaissance Ambassador. He is on a mission to introduce developers to the evolution of SQL in the 21st century. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy his Book on Amazon

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy on Amazon
(paperback only)

Paperback and PDF also available at Markus’ store.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of any size.
Learn more »

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

The Use The Index, Luke! mug

Stickers, coasters, books and coffee mugs. All you need for learning.

Shop now

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license