Features of the Column Storage Engine (HANA)

Both the row and column storage engine each have a built-in query capability optimized for the respective storage method. Consequently each of the storage engines has its strengths when used for differing tasks. The row engine supports all features provided by SAP HANA SQL. The column engine, however, has been optimized for analytics and supports a narrower set of SQL features. These features are much faster than the corresponding functions of the row store. Since not all features provided by SAP HANA SQL are natively supported by the column engine, the query optimizer sometimes creates an execution plan that involves both storage engines even if the data reside completely in the column store. On the other hand it is sometimes desirable to transfer intermediate results from the row store to the column store because subsequent calculation steps can be processed much faster there. As a general rule of thumb we recommend to use the "Explain Plan" feature of the SAP HANA studio to explore execution of critical SQL statements and optimize the data model and SQL statements accordingly.

The following list explains the query features of the column engine. The column engine can natively execute queries that are logically equivalent to queries that can be expressed in the following format.

SELECT

FROM

[ WHERE ]

[ GROUP BY ]

[ HAVING ]

[ ORDER BY

ASC | DESC separated by comma> ]

[ LIMIT [OFFSET ] ]

A column engine FROM specification can be one of the following:

·        

·         ( SELECT * FROM WHERE )

·         INNER JOIN ON

·         LEFT OUTER JOIN ON

·         RIGHT OUTER JOIN ON

·         FULL OUTER JOIN ON

The column engine supports following join conditions.

·         =

§  Data types of two columns should be the same. Decimals with different precisions or scales are also treated as different data types.

·         AND operations between column engine join conditions

§  For outer joins, cyclical joining is not supported. For example, T.a = S.a AND T.b = R.b is not supported if there is already a join condition between S and R.

The column engine supports the following filter conditions

·         Binary comparison (=, <>, <, >, <= and >=) between column engine expressions

·         LIKE, IS NULL and IN predicate on a column engine expression

·         AND, OR, NOT operations over column engine filter conditions

The column engine supports the following scalar expressions.

·         Constant values

·         Binary arithmetic operation (+, -, * and /) between column engine expressions

·        

·         String concatenation (||) between column engine expressions

·         CASE WHEN THEN

·         expression> ... [ELSE ] END

·         CASE WHEN THEN ... [ELSE ] END

·         Functions listed below with column engine expressions as arguments

§  TO_DECIMAL, TO_NUMBER, TO_BIGINT, TO_REAL, TO_DOUBLE, TO_CHAR,

§  TO_NCHAR, TO_DATE, TO_TIMESTAMP and BINTOHEX/HEXTOBIN

§  Format strings are not supported for TO_CHAR, TO_NCHAR, TO_DATE and

§  TO_TIMESTAMP

§  LTRIM | RTRIM | TRIM, LENGTH, SUBSTR, INSTR and LOWER | UPPER

§  WEEKDAY, DAYS_BETWEEN, SECONDS_BETWEEN, ADD_DAYS, UTCTOLOCAL,

§  LOCALTOUTC, ISOWEEK and QUARTER

§  LN | LOG, EXP | POWER | SQRT, SIN | COS | TAN, ASIN | ACOS | ATAN, SINH | COSH

§  and FLOOR | CEIL

§  NULLIF, NVL, NVL2 and COALESCE

§  EXTRACT( YEAR | MONTH FROM )

·         Functions without arguments are evaluated by the SQL parser. The evaluated results are passed to the column engine as constant values.

§  CURRENT_CONNECTION, CURRENT_SCHEMA, CURRENT_USER, SYSUUID, CURRENT_DATE/ CURRENT_TIME/ CURRENT_TIMESTAMP and CURRENT_UTCDATE/ CURRENT_UTCTIME/ CURRENT_UTCTIMESTAMP

§  Expressions equivalent to one of the listed above (e.g. CAST function)

·         The column engine supports the following aggregation expressions.

§  MIN | MAX | COUNT | SUM | AVG ([DISTINCT] )

§   

Since SQL provides multiple possible ways to formulate a query, an SQL query that is not in the above format can be processed natively by the column engine if the query is equivalent to a query that can be represented in the correct format. Below are examples of such equivalence. Equivalence is denoted as 􀑞.

SELECT * FROM T, S WHERE T.a = S.a

􀑞.SELECT * FROM T INNER JOIN S ON T.a = S.a

SELECT DISTINCT a, b, c FROM T

􀑞.SELECT a, b, c FROM T GROUP BY a, b, c

SELECT * FROM T WHERE EXISTS (SELECT * FROM S WHERE T.a = S.a)

􀑞.SELECT DISTINCT T.* FROM T INNER JOIN S ON T.a = S.a

Equivalent only if table T has a primary key.

SELECT * FROM T WHERE NOT EXISTS (SELECT * FROM S WHERE T.a =

S.a)

􀑞.SELECT T.* FROM T LEFT OUTER JOIN S ON T.a = S.a WHERE S.a

IS NULL