KB31580: Recommended VLDB Properties for use of MicroStrategy 9 with Teradata 12 (and higher) as a warehouse

• Strategy

The recommended VLDB optimizations for Teradata 12 (and higher) are listed below. These values are set by default when the "Teradata 12" database object is used (set at Configuration Managers > Database Instances > Database Instance > Database connection Type).

Selected Default VLDB Settings for Teradata 12 (and higher)

Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement
Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement
Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement
Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement
Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement
Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement
Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement
Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement
Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement
Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement
Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement
Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement

While the default values should result in the best report performance in most cases, some reports may perform better when certain VLDB Properties are set to non-default values.

Intermediate Table Type
By default, Strategy generates multi-pass SQL using derived tables with Teradata 12 (and higher). The following VLDB settings will enable volatile tables instead of derived tables when reports are complex:

Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement

Integration with Query Bands
The following settings will “tag” each SQL statement with Strategy-specific information and make this information available via query bands.

Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	Tables Intermediate Table Type Derived table Tables Fallback Table Type True temporary table Tables Maximum SQL Passes Before FallBack 0 (no threshold) Tables Maximum Tables in FROM Clause Before FallBack 0 (no threshold) Indexing Intermediate Table Index Create Primary index Joins Full Outer Join Support Support Select/Insert Bulk Insert String ; Select/Insert UNION Multiple INSERT Use UNION Select/Insert Distinct / Group By option Use DISTINCT Query Optimizations Sub Query Type Use temporary table; falling back to IN (SELECT col) for correlated subquery Query Optimizations SQL Global Optimization Level 4: Level 2 + Merge All Passes with Different WHERE Query Optimizations Set Operator Optimization Enable set operator optimization Tables Intermediate Table Type True temporary table Pre/Post Statements Report Pre Statement	SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION; Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)
SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION; Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)	SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION; Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)	SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION; Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)
SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION; Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)	SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION; Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)	SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION; Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)
SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION; Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)	SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION; Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)	SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION; Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)

Sub Query Type
The default setting for Sub Query Type for Teradata 12 (and higher) is Option 6 – “Use temporary table, falling back to IN (SELECT COL) for correlated subquery”. Some reports may perform better with Option 3 – “WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)”

SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION;
Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)

Third Party Software Installation WARNING

The third-party product(s) discussed in this technical note is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.

Comment

0 comments

Details

Knowledge Article

Published:

March 22, 2018

Last Updated:

March 22, 2018