NexusDB V3 Manual

Statement Switches

The following statement switches are NexusDB extensions that can be specified in a statement block to set execution characteristics:

Syntax

<blob switch> ::= #B { + | - }

<index switch> ::= #I { + | - }

<simplify switch> ::= #S { + | - }

<log switch> ::= #L { + | - }

<timeout switch> ::= #T integer-literal

Blob Switch

This switch controls the behavior when copying columns that contain BLOBs. In the default #B- state, the query engine only copies a link to the original BLOB in the result set which can save on both memory and execution time. The exception to this is if the source table is a temporary table, in which case the query engine will copy the full BLOB from the temporary table to the result set.

If switched on (#B+) the query engine will always copy the full BLOB to the result set. Since NexusDB allows for query results drawn on tables that are dropped within the same statement block, it is possible to write a SQL block so that BLOB links would refer to a table which had since been dropped, so understanding the implications of this setting is important.

SELECT * FROM BlobTable;

DROP BlobTable;

In this case, the SELECT statement must be prefixed with #B+ to force BLOBs to be copied from BlobTable into the query result.

Index Switch

Indexing optimization turns the use of indexes on and off for the following statement. This switch defaults to #I+ (on), and is usually only turned off when trying to troubleshoot the query optimizer.

Simplify Switch

#S- turns query simplification off for the statement that follows the switch (default is on). Query simplification is a process where the structure of a parsed query is simplified. E.g. x BETWEEN y AND z would be simplified to x >= y AND x <= z .

As simplification occurs before the SQL statement is bound to the tables it works on, occasionally, simplification can convert a query into a less efficient alternative. For such cases, the #S- switch allows you turn simplification off. When logging is enabled (see #L+), the individual stages in the simplification process are logged.

Log Switch

#L+ turns on logging for the statement block as a whole and should be placed before any other noncomment line. When logging is enabled, (default is off) the query engine collects information about simplifications, index use, and query metrics, and builds a text report which is returned to the client with the query result and status. The report can be accessed through the Log property of the nxQuery component and can be used to analyze the query for opportunities to rephrase statements for better performance.

Timeout Switch

#T sets the query timeout period to the value specified by integer-literal in milliseconds.