Default Options

setDefaultOptionValue/getDefaultOptionValue

Default only options and default values of $NOOPT, $PREFER_DWH and $ALLOW_CARTESIAN can be changed using the following procedure:

setDefaultOptionValue(<optionname>, <optionvalue>)

and displayed by this procedure:

getDefaultOptionValue(<optionname>)

For more information, please see System Procedures.

Here are some things to keep in mind:

• Default values can be overridden explicitly by the default options of a certain query or the SET Statement Statement for the current session;

• Default option value changed via setDefaultOptionValue will affect the Data Virtuality Server behaviour for sessions which are newly opened after the change. Changing the value for the actual session can be done with the SET Statement Statement;

• Default option value for ALLOW_INDEX_RECOMMENDATION and ALLOW_INDEX_CREATION changed via setDefaultOptionValue will affect the Data Virtuality Server behaviour immediately, including the current session;

• <optionname> for both setDefaultOptionValue() and getDefaultOptionValue() should be passed without the dollar sign ($).

Default Only Options

These options can only be set via the setDefaultOptionValue procedure.

ALLOW_INDEX_RECOMMENDATION 

This option controls how recommended indexes are created when a query is processed. It can have the following values:

ALLOW_INDEX_CREATION 

This option controls how physical indexes are created for the corresponding materialized tables when an optimization is enabled and materialized. It can have the following values:

A combination of values for ALLOW_INDEX_CREATION option can be provided in a CSV format. For example, WHERE, JOIN or JOIN, MANUAL are accepted values. ALL and NONE values cannot be combined to any other value (e.g. ALL, JOIN is not a valid value).

As for the MANUAL indexes, they can be created using the SYSADMIN.createIndex() or SYSADMIN.importIndex() stored procedure. Here is an example:

CALL SYSADMIN.createIndex(3, '', 'test_tables.test_b.c', 'MANUAL', NULL)
CALL SYSADMIN.importIndex(3, '', 'test_tables.test_b.c', 'MANUAL', NULL)

The main difference between the create and import procedures is that the first one creates a recommended index and a physical index in the materialized table, while the second one only creates a recommended index. 

For more information, please see System Procedures.

ALLOW_MAT_AGGR

This option enables/disables the generation and the rewriting of MAT_AGGRs. The default value is FALSE.

ALLOW_MAT_JOIN

This option enables/disables the generation and the rewriting of MAT_JOINs. The default value is FALSE.

SKIP_STRUCTURE_CHECKING

This option controls validation of materialized tables structure and can have the following values:

• TRUE (structure check is disabled; default);
• FALSE (structure check is enabled) 

Structure validation algorithm is used to check the correspondence of materialized table to its source table(s) and whether it is applicable for further usage. This is done by verifying the following:

1. Changes in the structure (one or more columns have been added, removed or renamed);
2. Datatype mismatch (type of one or more columns has been changed)

For changes in the structure, before rewriting of a source table (or a join) with the corresponding materialized table, the system checks if all the columns needed to execute the current query are present in the materialized table. If the system detects incompatible structure changes, an error message is generated providing information about the specific materialized table which should be synchronized with the source.

Example workflow

CREATE TABLE ds.test1 (a integer);
INSERT INTO ds.test1 VALUES(1);
SELECT * FROM ds.test1;
-- materialize the source table ds.test1
-- add the column b to the source table
DROP TABLE ds.test1;
CREATE TABLE ds.test1 (a integer, b integer);
INSERT INTO ds.test1 VALUES(1,1);
-- selecting all the columns from the source table will throw an exception since the column "b" is not found in the mat table
SELECT * FROM ds.test1;
-- selecting only the column "a" from the source will correctly work
SELECT a FROM ds.test1;
-- to fully synchronize the materialized table with the source, a full re-materialization of the optimization is needed

Data type mismatch check is related to data types. The algorithm detects if the data type in the source column is the same as the column data type in the materialized table. If the system detects a mismatch, it throws an exception providing information about the expected and actual data types, the column names in the source and in the materialized table, the materialized and the source table names.

Example workflow

CREATE TABLE ds.test1 (a integer)
INSERT INTO ds.test1 VALUES(1)
-- enable the optimization
DROP TABLE ds.test1;
CREATE TABLE ds.test1 (a double);
INSERT INTO ds.test1 VALUES(1.1);
--  selecting data from the source table will throw an data type mismatch exception
SELECT * FROM ds.test1

Please note that the current version of the structure checking algorithm detects only the first occurrence of structure mismatch. This means that if a query gets data from more than one modified table, the error message will include information only about the first of them. Information about the next issue will be shown only after the previous one has been fixed.

PLAINID_CONFIGURATION

This option enables PlainID authorization policies. It takes several parameters as input, depending on how the service is configured on PlainID side. The following parameters are accepted:

Example:

EXEC SYSADMIN.setDefaultOptionValue("opt" => 'PLAINID_CONFIGURATION', "val" => 'url=http://123.123.123.123/plainid-accesslist/resolution,appId=XXX,appSecret=YYY,entityType=DV_Users,mask="00000",userswithdomain=false') ;;

Note

Please note that resource names should be fully qualified in PlainID and they should exactly match the names of the resources defined in Data Virtuality.