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
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 scanalso includes the subsequent tables access if necessary.
- Matching index scan
Corresponds to Oracle's
INDEX RANGE/UNIQUE SCANplus the subsequent
TABLE ACCESS BY INDEX ROWIDif 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 joindoesn't use indexes at all (possibly repeating full scans on the inner table), the
Loop join with (hash) indexuses an index on the inner table (which might be a hash index in case of equijoin).
- MERGE JOIN
The Index merge join (shown as
MERGE JOINin 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.
- HASH JOIN
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
DESC modifiers in
Top-N queries can be implemented using a session parameter:
SET LIMIT n
SET LIMIT off
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:
SET OPTIMIZEFIRSTFETCH 1
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.
SET OPTIMIZEFIRSTFETCH 0