Microstrategy and Teradata Best Practices for Tuning VLDB Optimizations

Overview
The MicroStrategy platform provides VLDB drivers for all supported RDBMS platforms to generate optimized SQL that takes advantage of database specific functionality. The full set of VLDB properties is documented in the MicroStrategy System Administration Guide. Settings that are most relevant to Teradata are discussed below.
Default and Recommended VLDB Settings
MicroStrategy’s VLDB drivers for the Teradata family of databases are designed to use Teradata-specific features when they lead to improved performance or analytical functionality. When a Database Instance is configured to use the “Teradata” database connection type, the recommended values for all VLDB properties will automatically be used for every report executed against that Database Instance. The recommended VLDB optimizations for Teradata and are listed below. Administrators may add or modify VLDB optimizations to MicroStrategy projects, reports, or metrics at anytime so that their queries are specifically optimized for their data warehouse environment.

Selected Default VLDB Settings for Teradata

VLDB Category	VLDB Property Setting	Value
Tables	Intermediate Table Type	Derived Table
Tables	Fallback Table Type	True temporary table
Indexing	Intermediate Table Index	Create Primary Index
Query Optimizations	Sub Query Type	Use Temporary Table; falling back to IN (SELECT col ...) for correlated subquery
Joins	Full Outer Join Support	Supported
Select/Insert	Bulk Insert String	;
Select/Insert	UNION multiple INSERT	Use UNION

Other VLDB Settings for Teradata Discussed Below

VLDB Category	VLDB Property Setting	Alternative Non-default Value
Indexing	Allow index on metric	Allow index on metric
Indexing	Intermediate Table Index	Don’t create an index
Indexing	Max columns in index	<integer>
Pre/Post Statements	Table Post Statement	analyze table ??? estimate statistics

Intermediate Table Type
The ability to generate multi-pass SQL is a key feature of the MicroStrategy SQL Engine. Teradata supports a number of different ways to implement multi-pass SQL.
Derived Tables
Derived Table syntax allows the SQL Engine to issue additional passes as query blocks in the FROM clause of a SQL SELECT statement. Instead of issuing multiple SQL passes that create intermediate tables, the SQL engine generates a single large pass of SQL. This can allow queries to run faster since there are no CREATE TABLE or DROP TABLE statements to catalog, no corresponding locks on the created tables or the system tables, and no logging of records inserted into a physical table.
For Teradata, Derived Tables is the default setting for Intermediate Table Type. When using derived tables, the Teradata optimizer has a complete picture of the entire query and it can optimize for the whole process. MicroStrategy customers have used derived tables successfully for many versions. 

select pa1.SUBCAT_ID SUBCAT_ID, a11.SUBCAT_DESC SUBCAT_DESC, pa1.YEAR_ID YEAR_ID, pa1.WJXBFS1 WJXBFS1, pa2.WJXBFS1 WJXBFS2

from (select a13.YEAR_ID YEAR_ID, a12.SUBCAT_ID SUBCAT_ID,

sum(a11.TOT_UNIT_SALES) WJXBFS1 from ITEM_MNTH_SLS a11

join LU_ITEM a12
on (a11.ITEM_ID = a12.ITEM_ID)

join LU_MONTH a13
on (a11.MONTH_ID = a13.MONTH_ID)

group by a13.YEAR_ID, a12.SUBCAT_ID

) pa1
join (select a13.YEAR_ID YEAR_ID,

a12.SUBCAT_ID SUBCAT_ID,

sum(a11.UNITS_RECEIVED) WJXBFS1 from INVENTORY_ORDERS a11

join LU_ITEM a12
on (a11.ITEM_ID = a12.ITEM_ID)

join LU_MONTH a13
on (a11.MONTH_ID = a13.MONTH_ID)

group by a13.YEAR_ID, a12.SUBCAT_ID

) pa2

on (pa1.SUBCAT_ID = pa2.SUBCAT_ID and pa1.YEAR_ID = pa2.YEAR_ID)
join LU_SUBCATEG a11

on (pa1.SUBCAT_ID = a11.SUBCAT_ID)

Note that not all reports are able to use derived tables. There are two primary scenarios in which temporary tables (either volatile tables or global temporary tables – see below) must be used instead of derived tables:

• When a report uses a function supported in the MicroStrategy analytical engine that is not supported in Teradata (e.g. many of the functions in the financial and statistical function packages). If these functions are used in intermediate calculations, the MicroStrategy analytical engine will perform calculations and then insert records back into the RDBMS for further processing. Inserting records back into Teradata requires a temporary table.
• When a report uses the MicroStrategy partitioning feature. (Do not confuse this with Teradata partitioning, which is transparent to MicroStrategy.) When using partitioning, the SQL Engine executes a portion of the query in order to determine which partitions to use. The results are then used to construct the rest of the query. Because the full structure of the query is not known prior to execution, the SQL engine must use temporary tables to execute the query in multiple steps.

These situations do not cover 100% of the cases in which temporary tables must be used. The rest of the cases are relatively obscure combinations of VLDB settings, such as certain combinations of Sub Query Type plus outer join settings on metrics plus non-aggregatable metrics.

If the Intermediate Table Type is set to “Derived tables,” then either True temporary tables or Permanent tables should be specified as the “fallback” table type should the specific report requirements not support the use of derived table expressions. For each report, the SQL Engine will follow an “all or nothing” policy in determining whether to use derived table syntax. If the entire report cannot be resolved in a single statement with derived tables, the SQL Engine will automatically revert to the Fallback Table Type syntax and not use derived table syntax at all for the report. 

When using derived tables, the UNION Multiple INSERT setting should be setting to “Use Union.” This allows the engine to use “UNION” statements within derived tables instead of multiple INSERT INTO statements. This setting is relevant for reports that use partitioning and consolidations.
True Temporary Tables (Volatile Tables)
Another way to implement multi-pass SQL is to execute each pass in a separate table. Teradata volatile tables are a good choice because there is low overhead cost for creating and inserting into them. Volatile tables are created in memory and last for only one session; no entry is made in the Teradata Data Dictionary during the creation of these tables. In addition, volatile tables do not incur logging to the transaction journal when the NO LOG option is used.

create volatile table ZZSP00, no fallback, no log(
YEAR_ID SMALLINT, SUBCAT_ID BYTEINT, WJXBFS1 FLOAT)
primary index (YEAR_ID, SUBCAT_ID) on commit preserve rows
;insert into ZZSP00
select a13.YEAR_ID YEAR_ID, a12.SUBCAT_ID SUBCAT_ID,
sum(a11.TOT_UNIT_SALES) WJXBFS1 from ITEM_MNTH_SLS a11
join LU_ITEM a12
on (a11.ITEM_ID = a12.ITEM_ID)
join LU_MONTH a13
on (a11.MONTH_ID = a13.MONTH_ID)
group by a13.YEAR_ID, a12.SUBCAT_ID
create volatile table ZZSP01, no fallback, no log(
YEAR_ID SMALLINT, SUBCAT_ID BYTEINT, WJXBFS1 FLOAT)
primary index (YEAR_ID, SUBCAT_ID) on commit preserve rows
;insert into ZZSP01
select a13.YEAR_ID YEAR_ID, a12.SUBCAT_ID SUBCAT_ID,
sum(a11.UNITS_RECEIVED) WJXBFS1 from INVENTORY_ORDERS a11
join LU_ITEM a12
on (a11.ITEM_ID = a12.ITEM_ID)
join LU_MONTH a13
on (a11.MONTH_ID = a13.MONTH_ID)
group by a13.YEAR_ID, a12.SUBCAT_ID

select

pa1.SUBCAT_ID SUBCAT_ID, a11.SUBCAT_DESC SUBCAT_DESC, pa1.YEAR_ID YEAR_ID, pa1.WJXBFS1 WJXBFS1, pa2.WJXBFS1 WJXBFS2

from ZZSP00 pa1
join ZZSP01 pa2
on (pa1.SUBCAT_ID = pa2.SUBCAT_ID and pa1.YEAR_ID = pa2.YEAR_ID)
join LU_SUBCATEG a11
on (pa1.SUBCAT_ID = a11.SUBCAT_ID)

drop table ZZSP00
drop table ZZSP01

In theory, derived table syntax should result in query performance at least as good as when using volatile tables. Customers may want to experiment with the volatile table option to determine if it is beneficial in their specific environment.
Permanent Tables (Global Temporary Tables)
Global temporary tables are another variation on creating temporary tables for multi-pass SQL available in Teradata. Like volatile tables, global temporary tables can be created so as not to incur transaction journaling for recovery. However, global temporary table definitions are stored in the data dictionary, incurring some overhead.
In general, both volatile tables and derived tables will perform better than global temporary tables. To use Global Temporary Tables, the following VLDB settings should be modified:

VLDB Category	VLDB Property Setting	Value
Tables	Intermediate Table Type	Permanent table
Tables	Table Qualifier	global temporary
Tables	Table Option	, no fallback, no log
Tables	Create Post String	on commit preserve rows

create global temporary table ZZSP00 , no fallback, no log (

YEAR_ID SMALLINT, SUBCAT_ID BYTEINT, WJXBFS1 FLOAT)

primary index (YEAR_ID, SUBCAT_ID) on commit preserve rows

;insert into ZZSP00

select a13.YEAR_ID YEAR_ID, a12.SUBCAT_ID SUBCAT_ID,

sum(a11.TOT_UNIT_SALES) WJXBFS1 from ITEM_MNTH_SLS a11

join LU_ITEM a12
on (a11.ITEM_ID = a12.ITEM_ID)

join LU_MONTH a13
on (a11.MONTH_ID = a13.MONTH_ID)

group by a13.YEAR_ID, a12.SUBCAT_ID

create global temporary table ZZSP01 , no fallback, no log (

YEAR_ID SMALLINT, SUBCAT_ID BYTEINT, WJXBFS1 FLOAT)

primary index (YEAR_ID, SUBCAT_ID) on commit preserve rows

;insert into ZZSP01

select a13.YEAR_ID YEAR_ID, a12.SUBCAT_ID SUBCAT_ID,

sum(a11.UNITS_RECEIVED) WJXBFS1 from INVENTORY_ORDERS a11

join LU_ITEM a12
on (a11.ITEM_ID = a12.ITEM_ID)

join LU_MONTH a13
on (a11.MONTH_ID = a13.MONTH_ID)

group by a13.YEAR_ID, a12.SUBCAT_ID

select pa1.SUBCAT_ID SUBCAT_ID, a11.SUBCAT_DESC SUBCAT_DESC, pa1.YEAR_ID YEAR_ID, pa1.WJXBFS1 WJXBFS1, pa2.WJXBFS1 WJXBFS2

from ZZSP00 pa1
join ZZSP01 pa2

on (pa1.SUBCAT_ID = pa2.SUBCAT_ID and pa1.YEAR_ID = pa2.YEAR_ID)
join LU_SUBCATEG a11

on (pa1.SUBCAT_ID = a11.SUBCAT_ID)

drop table ZZSP00 drop table ZZSP01

Common Table Expressions

Common Table Expressions are SQL constructs that serve a similar function to derived tables. Common Table Expression syntax features query blocks in the WITH clause of a SELECT statement, as illustrated below. The table name specified in the WITH clause may be referenced like any other table in the body of the second SELECT statement. 

WITH <table name> AS (<SELECT statement>) <SELECT ststement> 

Index Creation on Intermediate Tables
For any table created in Teradata, the data is distributed across AMPs based on the primary index of the table. That is, any table will be spread across multiple nodes based upon the primary index. When performing join operations, the primary index is crucial for good performance. An important factor in Teradata database design is selecting good primary indexes for tables.
MicroStrategy, like any SQL application, transparently takes advantage of primary indexes defined on tables. Data distribution across AMPs is also relevant in MicroStrategy when creating temporary tables. By default, the SQL Engine generates primary indexes on intermediate tables. If the temporary table is later joined to another table using the same primary index, the join will be done locally (in parallel) on all AMPs. The primary indexes created for intermediate tables consist of all attribute ID columns used in the table and are specified in an order defined by the application architect.
The MicroStrategy engine provides several controls over primary indexes for MicroStrategy temporary tables.

• The application designer can control the order in which columns appear in the primary index. This is done by defining an ordering of the attributes that could be used in temp tables, using the Attribute Weights feature under Project Configuration.
  
• The application designer can control the size of the primary index through the Max columns in index VLDB property.
  
• By default, MicroStrategy generates SQL creating the primary index on all attribute ID columns that are used in the intermediate table. The fact columns can also be included in the primary index, using the Allow index on metric VLDB property. Some queries may benefit from distributing data by the fact columns as well as the attribute ID columns.
  
• Although primary indexes are created by default, they can be turned off altogether using the Intermediate Table Index VLDB property. In this case, Teradata will distribute data according to the first column in the table.

The VLDB properties that provide these controls over primary indexes are summarized in the table below.

VLDB Settings controlling Partitioning Keys in Teradata
Teradata introduced partitioned primary indexes. MicroStrategy transparently takes advantage of partitioned primary indexes defined on tables used in queries. Partitioned primary indexes are not allowed for volatile tables or global temporary tables, so the SQL Engine does not generate partitioned primary indexes when creating temporary tables. Primary indexes on temporary tables are sufficient.
Sub Query Type
There are many cases in which the SQL Engine will generate subqueries (i.e. query blocks in the WHERE clause):

• Reports that use Relationship Filters
• Reports that use “NOT IN” set qualification, e.g. AND NOT <metric_qualification> or AND NOT
  <relationship_filter>
• Reports that use Attribute qualification with M-M relationships, e.g. show Revenue by Category,
  filter on Catalog
• Reports that “raise the level” of a filter, e.g. dimensional metric at Region level, but qualify on
  Store
• Reports that use non-aggregatable metrics, e.g. inventory metrics
• Reports that use Dimensional extensions
• Reports that use Attribute to attribute comparison in the filter
  The default setting for Sub Query Type for Teradata is Option 6 – “Use temporary table, falling back to IN for correlated subquery”. This setting instructs the SQL Engine to generate an intermediate pass instead of generating a subquery in the WHERE clause. 

select a11.ITEM_ID ITEM_ID,
max(a12.ITEM_NAME) ITEM_NAME, sum(a11.TOT_DOLLAR_SALES) TOT_DOLLAR_SALES
from ITEM_MNTH_SLS a11
join (select distinct r11.ITEM_ID ITEM_ID from ITEM_MNTH_SLS r11
where r11.MONTH_ID in (200012)
) pa1
on (a11.ITEM_ID = pa1.ITEM_ID)
join LU_ITEM a12
on (a11.ITEM_ID = a12.ITEM_ID)

where a11.MONTH_ID in (200012) group by a11.ITEM_ID

Some reports may perform better with Option 3 – “WHERE (col1, col2) IN (Select s1.col1, s1.col2)...”. This setting instructs the SQL Engine to generate a subquery in the WHERE clause using the IN operator.

select a11.ITEM_ID ITEM_ID,
max(a12.ITEM_NAME) ITEM_NAME, sum(a11.TOT_DOLLAR_SALES) TOT_DOLLAR_SALES
from ITEM_MNTH_SLS a11 join LU_ITEM a12
on (a11.ITEM_ID = a12.ITEM_ID) where (((a11.ITEM_ID)

in (select r11.ITEM_ID from ITEM_MNTH_SLS r11

where r11.MONTH_ID in (200012)))

and a11.MONTH_ID in (200012)) group by a11.ITEM_ID 

[Reports that include a filter with an “AND NOT set qualification” (e.g. AND NOT relationship filter) will likely benefit from using temp tables to resolve the subquery.]
The other settings are not likely to be advantageous with Teradata.

• Option 0 – “WHERE EXISTS (select * ...)”
  IN performs better than EXISTS. This setting is useful for RDBMS platforms that do not support
  any of the other syntax.
  
• Option 1 – “WHERE EXISTS (select col1, col2 ...)”
  IN performs better than EXISTS. This setting is useful for RDBMS platforms that do not support
  other syntax and for which selecting column names performs better than select *.
  
• Option 2 – “WHERE col1 IN (select s1.col1), falling back to [Option 0] for multiple columns”
  Teradata supports multiple columns in an IN subquery. This is setting is useful for RDBMS
  platforms that do not support multiple columns in an IN subquery.
  
• Option 4 – “Use temporary table falling back to [Option 0] for correlated subqueries”
  IN performs better than EXISTS.
  
• Option 5 – “WHERE col1 IN (select s1.col1), falling back to [Option 1] for multiple columns”

Teradata supports multiple columns in an IN subquery. This is setting is useful for RDBMS platforms that do not support multiple columns in an IN subquery.

Additional VLDB Settings

Many of the VLDB properties control string syntax used in SQL queries generated by the MicroStrategy SQL engine. MicroStrategy application developers can further optimize SQL for their specific Teradata environment using these string insertion settings. Possible locations for VLDB optimizations in the query structure are listed below. 

0. Query Structure Illustrating VLDB String Insertion Settings
   

[Report Pre Statement] [Table Pre Statement]

CREATE [Table Qualifier] TABLE [Table Descriptor] [Table Prefix]<table_name> [Table Option] (

<column_expressions> )

[Table Space]

primary index (<column_expressions>)

[Create Post String]

[Insert Pre Statement]
;INSERT INTO [Table Prefix]<table_name> [Insert Table Option]

SELECT [SQL Hint] <column_expressions> FROM <table_list>
WHERE <filter_expressions>
GROUP BY <column_expressions>

[Insert Post Statement]
CREATE [Index Qualifier] INDEX [Index Prefix]<index_name>

(<column_expressions>) ON [Table Prefix]<table_name> [Index Post String] [Table PostStatement]

SELECT [SQL Hint] <column_expressions> FROM <table_list>
WHERE <joins_and_filter_expressions> GROUP BY <column_expressions>
[Report PostStatement]

Query Structure Key:
[MicroStrategy VLDB Setting]

SQL SYNTAX <query_parameter>

Bulk Inserts
Teradata can optimize performance of INSERT INTO... SELECT statements. If the target table is empty, the only transient journal entry made for the transaction are to note that the table was empty and data will be written in 32K blocks at a time to the target table.
In certain cases, such as when using application partitioning, the SQL Engine will perform multiple inserts into the same temporary table. On the first insert into the table, the table is empty, and the operation is performed quickly. However, on the second insert into the table, single row inserts (rather than 32K blocks) are performed and the transient journal is updates for each row. However, by placing a semicolon in front of subsequent inserts into the same temporary tables, all inserts into the same table are done as if the table was empty and there is only one entry into the transient journal. This syntax is generated by default for Teradata, using the Bulk Insert String VLDB setting.

VLDB Category	VLDB Property Setting	Value
Select/Insert	Bulk Insert String	;

Notice how all of the INSERT statements below include the bulk insert string. 

create volatile table ZZSP00, no fallback, no log( YEAR_ID SMALLINT,
SUBCAT_ID BYTEINT,
WJXBFS1 FLOAT)

primary index (YEAR_ID, SUBCAT_ID) on commit preserve rows

;insert into ZZSP00

select a13.YEAR_ID YEAR_ID, a12.SUBCAT_ID SUBCAT_ID,

sum(a11.TOT_UNIT_SALES) WJXBFS1 from ITEM_MNTH_SLS a11

join LU_ITEM a12
on (a11.ITEM_ID = a12.ITEM_ID)

join LU_MONTH a13
on (a11.MONTH_ID = a13.MONTH_ID)

group by a13.YEAR_ID, a12.SUBCAT_ID

create volatile table ZZSP01, no fallback, no log( YEAR_ID SMALLINT,
SUBCAT_ID BYTEINT,
WJXBFS1 FLOAT)

primary index (YEAR_ID, SUBCAT_ID) on commit preserve rows

;insert into ZZSP01

select a13.YEAR_ID YEAR_ID, a12.SUBCAT_ID SUBCAT_ID,

sum(a11.UNITS_RECEIVED) WJXBFS1 from INVENTORY_ORDERS a11
join LU_ITEM a12
on (a11.ITEM_ID = a12.ITEM_ID)
join LU_MONTH a13
on (a11.MONTH_ID = a13.MONTH_ID)
group by a13.YEAR_ID, a12.SUBCAT_ID
select pa1.SUBCAT_ID SUBCAT_ID, a11.SUBCAT_DESC SUBCAT_DESC, pa1.YEAR_ID YEAR_ID, pa1.WJXBFS1 WJXBFS1, pa2.WJXBFS1 WJXBFS2
from ZZSP00 pa1
join ZZSP01 pa2
on (pa1.SUBCAT_ID = pa2.SUBCAT_ID and pa1.YEAR_ID = pa2.YEAR_ID)
join LU_SUBCATEG a11
on (pa1.SUBCAT_ID = a11.SUBCAT_ID) drop table ZZSP00

drop table ZZSP01 

Statistics on Intermediate Tables
Using VLDB settings, the MicroStrategy SQL engine can create indexes for intermediate tables. The Table Post Statement VLDB setting can be used to collect optimizer statistics on tables and indexes. In some cases, collecting statistics will help improve query performance. Note that collecting statistics can be a costly operation; this setting may hurt performance more than help. Note also that Teradata does not support collecting statistics on volatile tables, although collecting statistics is supported on global temporary tables.

VLDB Category	VLDB Property Setting	Value
Pre/Post Statements	Table Post Statement	collect statistics on ???

Extended Analytics Using Teradata Functions
The MicroStrategy 7i platform includes libraries of mathematical, statistical, and financial functions that can be applied to any report or customized application. More than 240 analytical functions are available out-of-the-box, and additional libraries can be added by plugging in custom functions or proprietary algorithms.
The MicroStrategy platform provides the advantage of determining whether a given function can be performed in Teradata or whether it should be performed in the MicroStrategy engine. This collaboration between MicroStrategy and Teradata allows calculations to be performed in the most appropriate location. In general, the MicroStrategy engine will push as much processing as possible to Teradata, in order to take advantage of the processing power of the database server and database parallelism. Additionally, using RDBMS functions avoids transport of data between the database server and the MicroStrategy Intelligence Server, further improving scalability and system performance.
Ordered Analytical Functions: Functions for OLAP
OLAP functions are a powerful family of functions for business intelligence and data warehousing applications. These functions are collectively called “ordered analytical functions” in Teradata and they provide significant analytical capabilities for many business analysis queries.

MicroStrategy’s OLAP functions will be calculated via SQL in Teradata rather than in the MicroStrategy analytical engine. There is no VLDB setting associated with this option.
Moving average example

<ordered analytic function> ::=
<function_name> OVER ([<partition by phrase>] [<order by phrase> [<row phrase>]])

select a11.DAY_DATE DAY_DATE,
a12.REGION_ID REGION_ID,
max(a13.REGION_NAME) REGION_NAME,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1,
avg(sum(a11.TOT_DOLLAR_SALES)) over(partition by a12.REGION_ID order by
sum(a11.TOT_DOLLAR_SALES) asc rows 2 preceding) WJXBFS1
from DAY_CTR_SLS a11
join LU_CALL_CTR a12
on (a11.CALL_CTR_ID = a12.CALL_CTR_ID) join LU_REGION a13
on (a12.REGION_ID = a13.REGION_ID) group by a11.DAY_DATE,
a12.REGION_ID

Ranking
The RANK function is closely related to other ordered analytical functions. Key distinctions of the RANK function are that it requires an order by clause and does not support a window clause. The MicroStrategy Engine is aware that Teradata supports the Rank function, so the ranking will by done by the database rather than the MicroStrategy Analytical Engine. 

Rank example

select a13.REGION_ID REGION_ID, max(a14.REGION_NAME) REGION_NAME, a11.EMP_ID EMP_ID, max(a12.EMP_LAST_NAME) EMP_LAST_NAME, max(a12.EMP_FIRST_NAME) EMP_FIRST_NAME, sum(a11.TOT_DOLLAR_SALES) WJXBFS1,

rank () over( order by sum(a11.TOT_DOLLAR_SALES) desc nulls last) WJXBFS1

from ITEM_EMP_SLS a11
join LU_EMPLOYEE a12

on (a11.EMP_ID = a12.EMP_ID) join LU_CALL_CTR a13

on (a12.CALL_CTR_ID = a13.CALL_CTR_ID) join LU_REGION a14

on (a13.REGION_ID = a14.REGION_ID) group by a13.REGION_ID,

a11.EMP_ID

Teradata Functions Used by MicroStrategy
MicroStrategy makes use of all the Teradata functions listed below through built-in functions. Additional Teradata functions can also be used via passthrough expressions (i.e. Apply syntax).

Aggregate Functions
AVG
CORR COUNT COVAR_POP KURTOSIS MAX
MIN REGR_INTERCEPT REGR_R2 REGR_SLOPE SKEW STDEV_POP STDEV_SAMP SUM
VAR_POP VAR_SAMP
Case Expressions
CASE

Arithmetic and Trigonometric Functions ABS
ACOS
ACOSH
ASIN
ASINH
ATAN
ATAN2
ATANH
COS
COSH
EXP
LN
LOG
NULLIFZERO
SIN
SINH
SQRT
TAN
TANH
ZEROIFNULL

Date, Time, and String Functions CHAR_LENGTH
EXTRACT
INDEX
LOWER
SUBSTRING
UPPER CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP TRIM
Ordered Analytical Functions
RANK
AVG

COUNT MAX MIN SUM