SAP HANA DATA AGING DRULS
Get Example source ABAP code based on a different SAP table
Dependency Rules for Optimization of Access to Outdated Data
In database tables with active
ITOC
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 outdated data is moved to other partitions. By default, the
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 result set of the original query. This is technically possible, but it 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 outdated data can be defined.
Filter Dependency Rules for Data Aging
The
This means that filter dependency rules can be defined as follows:
Latest notes:
The actual design of filter dependency rules for optimizing access to outdated data depends on the layout of the database tables and the rules applied in the data aging run.
ABAP_HINT_END
Example ABAP Coding
In the following CDS view entity, data aging is disabled for the database table
DDLS DEMO_CDS_DAAG_SFLIGHT
When this view entity is accessed using AB-SQL , both outdated and current data is read:
ABEXA 01467
To avoid loading all partitions, a filter dependency rule can be defined as follows, assuming that the temperature column
DDLS DEMO_CDS_DAAG_SFLIGHT_SBOOK
The SAP HANA database optimizer then adds the following additional selection condition to the
OR daag_sflight~_dataaging = '00000000'
This does not affect the result set of the query.
ABAP_EXAMPLE_END
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 AB_SQL , Native SQL, or in views.
Example ABAP Coding
In the following CDS view, data aging is disabled for the database tables
@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 AB-SQL , both outdated and current data is read:
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
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 optimizer then adds the following additional selection condition to the
The following additional selection conditions from the filter condition are also added to the
AND daag_sbook~_dataaging >= '20160101'
OR daag_sflight~_dataaging = '00000000'
OR daag_sbook~_dataaging = '00000000'
This does not modify the result set of the query.
ABAP_EXAMPLE_END