Another approach to get the number of records stored in a table in HANA DB

SAP Blog

Kayıtlı Üye
Katılım
22 Ara 2017
Mesajlar
1,274
Tepki puanı
6
Puanları
6
Still used the old way SELECT COUNT(*) to get total number of records stored in a table?
If you are using HANA database, there is another approach to achieve the same.
In HANA there is a metadata table m_tables which stores the related information:
clipboard1-39.png

You can find its definition in SAP help.
clipboard2-25.png

You can use the following ABAP code to access this HANA table from ABAP server:

class CL_CRM_HOME_TABLE_SIZE_TOOL definition
public
final
create public .

public section.

TYPES: BEGIN OF ty_size,
table_name TYPE char256,
record_count TYPE int4,
table_size TYPE int4,
END OF ty_size.

TYPES: tt_size TYPE TABLE OF ty_size with key table_name.

class-methods GET_SIZE
importing
!IT_INPUT type STRING_TABLE
returning
value(RT_RESULT) type tt_size .
protected section.
private section.
ENDCLASS.

CLASS CL_CRM_HOME_TABLE_SIZE_TOOL IMPLEMENTATION.
METHOD get_size.
DATA(lv_in) = REDUCE string( INIT x TYPE string FOR <data> IN it_input NEXT x = SWITCH #( x
WHEN space THEN |'{ <data> }'| ELSE x && ',' && |'{ <data> }'| ) ).

TRY.
DATA(lo_sql_con) = cl_sql_connection=>get_connection( ).
DATA(lo_stmt) = lo_sql_con->create_statement( ).
DATA: lv_stmt TYPE string.

lv_stmt = |select table_name, record_count, table_size from m_tables where table_name in ({ lv_in })|.

DATA(lo_res) = lo_stmt->execute_query( lv_stmt ).

GET REFERENCE OF rt_result INTO DATA(lr_data).
lo_res->set_param_table( lr_data ).
lo_res->next_package( ).
lo_res->close( ).
CATCH cx_sql_exception INTO DATA(cx_root).
WRITE:/ 'Error:', cx_root->get_text( ).
RETURN.
ENDTRY.
ENDMETHOD.
ENDCLASS.

Consumer report:

REPORT zsize.

DATA: lt_input TYPE String_table.

lt_input = VALUE #( ( CONV string( 'TADIR' ) )
( CONV string( 'TFDIR' ) )
).

DATA(lt_size) = cl_crm_home_table_size_tool=>get_size( lt_input ).

cl_demo_output=>display_data( lt_size ).

Result:

clipboard1-40.png


Okumaya devam et...
 
Üst