## executionHints ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR executionHints_start((START)) executionHints_start --> executionHints_0_0[EXECUTION_HINT_START]:::quoted executionHints_0_0 --> executionHints_0_1[joinSet] executionHints_0_0 --> executionHints_0_2[noJoinSet] executionHints_0_0 --> executionHints_0_3[joinParallelization] executionHints_0_0 --> executionHints_0_4[ods] executionHints_0_0 --> executionHints_0_5[resultSetName] executionHints_0_0 --> executionHints_0_6[resultSetSerialization] executionHints_0_0 --> executionHints_0_7[lowCost] executionHints_0_0 --> executionHints_0_8[httpDiskCache] executionHints_0_0 --> executionHints_0_9[httpMemoryCache] executionHints_0_0 --> executionHints_0_10[materializeResultSet] executionHints_0_9 --> executionHints_0_11[EXECUTION_HINT_END]:::quoted executionHints_0_11 --> executionHints_end((END)) ``` ## Purpose Execution hints allow you to control individually the execution of SQL statements. Whenever possible, the hints will be used. In contrary to other platforms, Invantive UniversalSQL requires a hint to be valid according to the grammar when specified. This reduces the engineering risk that hints become invalid by accident. ## httpDiskCache ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR httpDiskCache_start((START)) httpDiskCache_start --> httpDiskCache_0_0[HTTP_DISK_CACHE] httpDiskCache_0_0 --> httpDiskCache_0_1["("]:::quoted httpDiskCache_0_1 --> httpDiskCache_0_2[booleanConstant] httpDiskCache_0_2 --> httpDiskCache_0_3[","]:::quoted httpDiskCache_0_3 --> httpDiskCache_0_4[booleanConstant] httpDiskCache_0_4 --> httpDiskCache_0_5[","]:::quoted httpDiskCache_0_5 --> httpDiskCache_0_6[intervalConstant] httpDiskCache_0_6 --> httpDiskCache_0_7[")"]:::quoted httpDiskCache_0_7 --> httpDiskCache_end((END)) ``` ## Purpose The http_disk_cache-hint specifies whether messages may be cached on disk when the provider uses HTTP to exchange data with the backing platform. This typically holds only for cloud-based platforms such as Exact Online, Teamleader or Salesforce. The default setting is false. The first parameter is a boolean whether data may be taken from the disk cache, the second parameter is a boolean whether data retrieved must be stored also in the disk cache and the third parameter is an interval that specifies the timespan before a disk cache hit found is to considered stale, such as "interval '4 hours'". The use of the http_disk-cache-hint is recommended for data which is known to change seldom such as seeded or reference data. The contents of the disk cache are persistent across Invantive UniversalSQL sessions. The disk cache is located in the Cache folder of the Invantive configuration folder. ## httpMemoryCache ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR httpMemoryCache_start((START)) httpMemoryCache_start --> httpMemoryCache_0_0[HTTP_MEMORY_CACHE]:::quoted httpMemoryCache_0_0 --> httpMemoryCache_0_1["("]:::quoted httpMemoryCache_0_1 --> httpMemoryCache_0_2[booleanConstant] httpMemoryCache_0_2 --> httpMemoryCache_0_3[","]:::quoted httpMemoryCache_0_3 --> httpMemoryCache_0_4[booleanConstant] httpMemoryCache_0_4 --> httpMemoryCache_0_5[","]:::quoted httpMemoryCache_0_5 --> httpMemoryCache_0_6[intervalConstant] httpMemoryCache_0_6 --> httpMemoryCache_0_7[")"]:::quoted httpMemoryCache_0_7 --> httpMemoryCache_end((END)) ``` ## Purpose The http_memory_cache-hint specifies whether messages may be cached in memory when the provider uses HTTP to exchange data with the backing platform. This typically holds only for cloud-based platforms such as Exact Online, Teamleader or Salesforce. The default setting is false. The first parameter is a boolean whether data may be taken from the memory cache, the second parameter is a boolean whether data retrieved must be stored also in the memory cache and the third parameter is an interval that specifies the timespan before a memory cache hit found is to considered stale, such as "interval '4 hours'". The use of the http_memory-cache-hint is recommended for data which is known to change seldom such as seeded or reference data. The contents in the memory cache are forgotten across Invantive UniversalSQL sessions. The memory cache is located in the Cache folder of the Invantive configuration folder. ## materializeResultSet ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR Start((START)) --> materializeResultSet materializeResultSet -->| | MATERIALIZE:::quoted MATERIALIZE --> End((END)) ``` ## joinParallelization JOIN_PARALLELIZATION(IDENTIFIER, BOOLENACONSTANT, NUMERICCONSTANT) ### Purpose The join_parallelization hint provides configuration of parallel execution of joins between two data sources with the last one evaluated a table function. It is enabled using the join_parallelization hint. The default is no parallel execution of joins. The first argument is the alias of the table evaluated last in the join. The second argument is whether parallel execution is enabled. The third and final argument is the maximum number of threads used to execute the outer join in parallel. Twice the number of processors will be used when the third argument is missing. The maximum number of threads is 128. ### Examples The following example retrieves all companies on Teamleader and per company executes an additional API call in parallel. ```sql select /*+ join_parallelization(p, true, 32)*/ count(*) from Teamleader.V2.Companies l join Teamleader.V2.CompanyById(l.id) p ``` ## ods ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR ods_start((START)) ods_start --> ods_0_0[ODS] ods_0_0 --> ods_0_1["("]:::quoted ods_0_1 --> ods_0_2[booleanConstant] ods_0_2 --> ods_0_3[","]:::quoted ods_0_3 --> ods_0_4[intervalConstant] ods_0_4 --> ods_0_5[")"]:::quoted ods_0_5 --> ods_end((END)) ``` ## Purpose The ods-hint controls the use of the Invantive Data Cache stored in a relational database. The Invantive Data Cache is also the basis of the Operational Data Store managed by Invantive Data Replicator and the data warehouses managed by Invantive Data Vault. The ods-hint specifies the maximum age data from the data cache eligible for use. The boolean specifies whether the Data Cache may be used to answer a query. Set it to false to disable use of Data Cache for the duration of the query. Keep it on the default true to use Data Cache. The interval specifies the period of time during which cached results are considered sufficiently fresh for use, such as '30 minutes'. When no interval is present, the actual platform is consulted. The default with Invantive Data Cache enabled is to always use the data cache contents when not stale according to the metadata of the data cache. In general, that defaults to a maximum age of 7 days. When use of data cache is requested and a filter is applied, all data will be retrieved from the actual platform and filtered on the Invantive UniversalSQL engine. This also applies when a filter is applied that can be forwarded to the actual platform. When the data is requested a limited number of times and the filter severely reduces the amount of data to be transported, it can be faster to not use the data cache. ## resultSetName ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR resultSetName_start((START)) resultSetName_start --> resultSetName_0_0[RESULT_SET_NAME] resultSetName_0_0 --> resultSetName_0_1["("]:::quoted resultSetName_0_1 --> resultSetName_0_2[stringConstant] resultSetName_0_2 --> resultSetName_0_3[")"]:::quoted resultSetName_0_3 --> resultSetName_end((END)) ``` ## Purpose A query returns a result set. A result set has associated metadata, such as statistics, audit information and SQL execution steps used to retrieve the data. The result_set_name-hint specifies a string to be used as name of the result set during further processing. It is for instance included as an attribute in the default XML-output for a SQL batch, such as with Invantive Data Access Point. This attribute can be used in XPath expressions. ## resultSetSerialization ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR resultSetSerialization_start((START)) resultSetSerialization_start --> resultSetSerialization_0_0[RESULT_SET_SERIALIZATION]:::quoted resultSetSerialization_0_0 --> resultSetSerialization_0_1["("]:::quoted resultSetSerialization_0_1 --> resultSetSerialization_0_2[SHOW]:::quoted resultSetSerialization_0_2 --> resultSetSerialization_0_3[HIDE]:::quoted resultSetSerialization_0_3 --> resultSetSerialization_0_4[")"]:::quoted resultSetSerialization_0_4 --> resultSetSerialization_end((END)) ``` ## joinSet ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR joinSet_start((START)) joinSet_start --> joinSet_0_0[JOIN_SET]:::quoted joinSet_0_0 --> joinSet_0_1["("]:::quoted joinSet_0_1 --> joinSet_0_2[<a href="Invantive UniversalSQL/General/Identifiers" class="internal-link">identifier</a>] joinSet_0_2 --> joinSet_0_3[","]:::quoted joinSet_0_3 --> joinSet_0_4[<a href="Invantive UniversalSQL/General/Identifiers" class="internal-link">identifier</a>] joinSet_0_4 --> joinSet_0_5[","]:::quoted joinSet_0_5 --> joinSet_0_6[numericConstant] joinSet_0_6 --> joinSet_0_7[")"]:::quoted joinSet_0_7 --> joinSet_end((END)) ``` ## Purpose Control join approach between two data sources. A column-indexed lookup will be used instead of a full table scan when the number of rows on the left-hand side does not exceed the maximum number of rows specified in the hint. When not specified, a hash lookup will only be used when the number of rows on the left-side does not exceed 5.000. The actual implementation of a hash lookup depends on the platform on which the data container runs. For instance with OData, a number of requests will be made using an in-construct with a limited number of in-values. With a relation database platform, a native SQL 'in' will be used. The first identifier is the alias of the table on the right-hand side of the join. The second identifier is the name of the column used to join upon in the right-hand side. The numeric constant specifies upto what number of rows on the left-hand side of the join will allow the join set hint to be used. When the number of rows exceeds the numeric constant, a full table join is made. The following example takes for instances 5.000 sales invoices from an Exact Online environment with 100.000 sales invoices. Each sales invoice has 4..10 lines. The join does not retrieve all sales invoices nor all invoice lines, but instead fetches the 5.000 sales invoices using the where-clause, and then retrieves the related invoice lines using a column-indexed lookup by invoiceid. Since Exact Online is an OData source, the approximately 30.000 invoice lines will be retrieves in 300 session I/Os each having an in-construct for 100 lines on invoiceid. ``` select /*+ join_set(sil, invoiceid, 10000) */ * from ExactOnlineREST..SalesInvoices sik join ExactOnlineREST..SalesInvoiceLines sil on sil.invoiceid = sik.invoiceid where sik.status = 50 and sik.InvoiceDate between to_date( :P_RECEIPT_DATE_FROM, 'yyyymmdd') and to_date( :P_RECEIPT_DATE_TO, 'yyyymmdd') ``` ## noJoinSet ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR noJoinSet_start((START)) noJoinSet_start --> noJoinSet_0_0[NO_JOIN_SET]:::quoted noJoinSet_0_0 --> noJoinSet_0_1["("]:::quoted noJoinSet_0_1 --> noJoinSet_0_2[<a href="Invantive UniversalSQL/General/Identifiers" class="internal-link">identifier</a>] noJoinSet_0_2 --> noJoinSet_0_3[","]:::quoted noJoinSet_0_3 --> noJoinSet_0_4[<a href="Invantive UniversalSQL/General/Identifiers" class="internal-link">identifier</a>] noJoinSet_0_4 --> noJoinSet_0_5[")"]:::quoted noJoinSet_0_5 --> noJoinSet_end((END)) ``` ## Purpose The no_join_set hint disables the use of hash-joins. It can be enabled using the join_set hint. ## lowCost ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR lowCost_start((START)) lowCost_start --> lowCost_0_0[LOW_COST] lowCost_0_0 --> lowCost_0_1["("]:::quoted lowCost_0_1 --> lowCost_0_2[booleanConstant] lowCost_0_2 --> lowCost_0_3[")"]:::quoted lowCost_0_3 --> lowCost_end((END)) ``` ## Purpose The low_cost-hint specifies that the select with the hint must be considered a select with low execution costs. Low execution costs trigger early evaluation during parsing. By default, select statements using solely in memory storage, dummy and data dictionary are considered low cost and evaluated early. The evaluation of all others is delayed as long as possible. The use of the low_cost-hint is recommended when the select is used with a 'in ( select ... )' syntax and the developer knows beforehand that it will evaluate fast to values and that the use of these values will allow the use of server-side filtering for the outer select.