set control option

Set an SQL operational parameter value

Syntax

set_option:
          SET DATE FORMAT [TO | =] 
               {"YYYY-MM-DD" | "MM-DD-YYYY" | "DD-MM-YYYY"
              |  "YYYY/MM/DD" | "MM/DD/YYYY" | "DD/MM/YYYY"}
     |    SET TIME FORMAT [TO | =] "HH[:MM[:SS[:F[F[F[F]]]]]]"
     |    SET WILD ALL [TO | =] {"%" | "*"}
     |    SET WILD ONE [TO | =] {"_" | "." | "?"}
     |    SET TIMEOUT [TO | =] integer
     |    SET {DISTINCT | GROUP BY | ORDER BY} CACHE SIZE [TO | =] integer
     |    SET SORT COST FACTOR [TO | =] decimal
     |    SET IGNORE IMPORT ERROR [TO | =] {ON | OFF}

Description

The SET statement is used to set a variety of different RaimaDB SQL operational control options. All of the option settings apply to the connection handle and, thus, all of the statement handles that have been allocated on that connection.

The SET DATE FORMAT statement is used to set the input and character display output format for dates. The default format conforms to the ISO and SQL standards: YYYY-MM-DD. The YYYY is the four digit year, MM is the month number from 1 to 12, and DD is the day of the month number from 1 to 31. Two other formats are supported with the option of changing the separator character from a '-' to a '/'.

The SET TIME FORMAT statement is used to set the character display output format for times which just sets the precision of the time values to be displayed. HH is the hour from 0 to 23, MM is the minute of the hour from 0 to 59, SS is the second of the minute from 0 to 59, and F is the fractional part of a second with up to four decimal places of accuracy.

The SET WILD statements can be used to set the wild card characters that are used in the pattern matching with the LIKE operator. The WILD ALL character matches zero or more characters. It can be set to '%' (default and SQL standard) or '*' (Posix standard). The WILD ONE character matches any single character and can be set to '_' (underscore; default and SQL standard), '.' (Posix), or '?' (Microsoft).

The SET TIMEOUT sets the number of seconds to wait for a locked table to become available. The default is 30 seconds. Setting timeout to -1 will disable timeouts which we do not recommend doing. A timeout value of 0 will cause lock requests to timeout immediately when the requested lock is not available.

The SET {DISTINCT | GROUP BY | ORDER BY} CACHE SIZE statement can be used to change the size of three different caches that are used by SQL to manage 1) distinct processing for aggregate functions, 2) the individual groups (aggregates) during GROUP BY processing, and 3) the size of the sort/merge cache used for ORDER BY sorting. The default value for each cache is 10,000 entries. Each represents the maximum number of rows that will be kept in memory during the operation. Of course, larger cache sizes mean more memory is required to support the operation but the operation will execute faster.

The SET SORT COST FACTOR statement is used to specify a factor which the SQL query optimizer will use to estimate the additional performance cost associated with an ORDER BY clause. The added cost is computed as this value times the optimizer's estimate of the number of rows in query's result set. Hence, it is a cost per row that is incurred by the ORDER BY clause. Note that the cost of doing a sort only matters when the optimizer has to choose between an execution plan that will naturally return the result rows in the specified order (due to the availability of a key) and another which requires that a sort be performed. The default value is 0.000062. More details on how to calculate a sort cost factor for your database and computer can be found in the "How Queries are Processed by RaimaDB SQL" section.

Example

set order by cache size to 5000;
set group by cache size to 10000;
set distinct cache size to 1000;
    ...
set timeout to 5;