Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP and SAP HANA →  Data Aging in SAP HANA 

Dependency Rules for Optimization of Access to Old Data

Dependency rules are SAP HANA database objects that can represent dependencies between semantic columns and technical columns in a database table. The optimizer can then use these dependencies to create extended selection conditions when the table is read.

In database tables with a temperature column, and hence where data aging is enabled, dependency rules can be used to optimize access to old data. In this case, a dependency rule joins the temperature column of a database table with regular date columns in the table.

Other versions: 7.31 | 7.40 | 7.54

Basics of Optimization

In tables with a temperature column, only the current data is located in the main memory of the SAP HANA database and old data is moved to other partitions. By default, the AS ABAP database interface accesses current data only. The relationship between the actual data and the partition limits is defined in data aging runs and is made transparent for application programming. This means that, when old data is accessed, the data in all partitions must first be loaded to the main memory of the SAP HANA database, which can have a negative effect on performance. Old data can be accessed as follows:

  • Disabling data aging in the database interface using the profile parameter abap/data_aging.
  • Configuring a different temperature using the classes CL_ABAP_SESSION_TEMPERATURE and CL_ABAP_STACK_TEMPERATURE.

A dependency rule can be used to add a selection condition for the temperature column to an access of this type. This restricts the temperatures to those that are in the same rows as the data that is actually requested. This means that the SAP HANA database only has to load those partitions to its main memory that contain the required data.

It cannot be guaranteed, however, that the SAP HANA database optimizer actually creates the additional selection condition for the temperature column. This is why this condition must not modify the results set of the original query. This is technically possible, but produces undefined behavior in cases where the SAP HANA database optimizer responds differently depending how a query is formulated.

The following sections demonstrate how dependency rules for optimizing access to old data can be defined.

Filter Dependency Rules for Data Aging

The temperature column has the following important properties for filter dependencies:

  • The temperature column has the data type DATS.
  • For current data, the temperature column has the initial value 00000000.
  • In the case of old data, the temperature column is given a date value created in the data aging run.

This means that filter dependency rules can be defined as follows:

  • For access to old data, filter dependency rules can derive additional selection conditions. These conditions contain comparisons for the temperature column, restricting it to the same rows as the selection conditions for the associated date values.
  • If current data is required alongside old data, filter dependency rules must be defined in such a way that the selection condition restricts the temperature column to the required date values without excluding the current data. In this case, the additional selection condition must contain a relational expression dbtab._dataaging = '00000000' joined using OR.


Note

The actual design of filter dependency rules for optimizing access to old data is determined by the layout of the database tables and the rules applied in the data aging run.


Example

In the following CDS view, data aging is disabled for the database table DAAG_SFLIGHT using the annotation @DataAging.noAgingRestriction:true.

@AbapCatalog.sqlViewName: 'DEMOCDSDAAGSFL'
@DataAging.noAgingRestriction:true
DEFINE VIEW demo_cds_daag_sflight AS
  SELECT FROM daag_sflight
         { carrid,
           connid,
           fldate,
           planetype,
           seatsocc };

When this view is accessed using ABAP SQL, both old and current data is read:

SELECT *
       FROM demo_cds_daag_sflight
       WHERE fldate >= '20160101'
       INTO TABLE @DATA(result).

To avoid loading all partitions, a filter dependency rule can be defined as follows. Here, the assumption is made that the temperature column _DATAAGING for old data has the same value as the column FLDATE.

DEFINE FILTER DEPENDENCY RULE demo_daag_sflight_rule
  ON daag_sflight
    IF { daag_sflight.fldate >= $1 }
      THEN { daag_sflight._dataaging >= $1 OR
             daag_sflight._dataaging =  '00000000' }

The SAP HANA database then adds the following additional selection condition to the WHERE condition of the shown SELECT statement implicitly:

AND daag_sflight~_dataaging >= '20160101'
    OR  daag_sflight~_dataaging =  '00000000'

This does not modify the results set of the query.

Join Dependency Rules for Data Aging

If multiple database tables are joined together in a relational model, data aging must be respected for all these tables. Rows in different database tables that have the same date are usually joined using join expressions. These joins can be in ABAP SQL, Native SQL, or in views.

Join dependency rules can be defined to optimize access to old data for database tables joined like this. As in filter dependency rules, the actual design of the data aging must be known as well as how the tables are dependent on each other.


Example

In the following CDS view, data aging is disabled for the database tables DAAG_SFLIGHT and DAAG_SBOOK using the annotation @DataAging.noAgingRestriction:true.

@AbapCatalog.sqlViewName: 'DEMOCDSDAAGSFLBK'
@DataAging.noAgingRestriction:true
DEFINE VIEW demo_cds_daag_sflight_sbook AS
  SELECT FROM daag_sflight
     INNER JOIN daag_sbook
       ON daag_sflight.carrid = daag_sbook.carrid AND
          daag_sflight.connid = daag_sbook.connid AND
          daag_sflight.fldate = daag_sbook.fldate
           { daag_sflight.carrid,
             daag_sflight.connid,
             daag_sflight.fldate,
             daag_sbook.bookid,
             daag_sbook.customid };

When this view is accessed using ABAP SQL, both old and current data is read:

SELECT *
       FROM demo_cds_daag_sflight_sbook
       WHERE fldate >= '20160101'
       INTO TABLE @DATA(result).

To avoid loading all partitions, a join dependency rule can be defined as follows, in addition to the filter dependency rules in the previous example. Here, the assumption is made that the temperature column _DATAAGING for old data in both tables has the same value as the column FLDATE.

DEFINE JOIN DEPENDENCY RULE demo_daag_sflight_sbook_rule
  ON daag_sflight, daag_sbook
    IF { daag_sflight.mandt  = daag_sbook.mandt  AND
         daag_sflight.carrid = daag_sbook.carrid AND
         daag_sflight.connid = daag_sbook.connid AND
         daag_sflight.fldate = daag_sbook.fldate }
      THEN { daag_sflight._dataaging = daag_sbook._dataaging }

The SAP HANA database then adds the following additional selection condition to the ON condition of the join expression of the CDS view implicitly:

AND daag_sflight._dataaging = daag_sbook._dataaging

The following additional selection conditions from the filter condition are also added to the WHERE condition of the shown SELECT statement:

AND daag_sflight~_dataaging >= '20160101'
AND daag_sbook~_dataaging >= '20160101'
OR  daag_sflight~_dataaging = '00000000'
OR  daag_sbook~_dataaging =  '00000000'

This does not modify the results set of the query.