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 tunes developers for high SQL performance. He also published the book SQL Performance Explained and offers in-house training as well as remote coaching at http://winand.at/