Counter Letter Of Insu

partition tables - Performance increase

There is a new power-Note from SAP, the 1333328 Note: He comes - all the rivalries of the CEOs of Oracle and SAP, despite [1] - an exclusive benefit to all SAP customers that have selected Oracle as underlying DBMS offers Oracle since version 8, the feature of the partitioning of
: Man , a large database table (the reference is recommended as a useful minimum size of 10 gigabytes) are divided into many small blocks, each of which behave like a mini-table: For example you can have their own indices. In which block is a sentence that can be decided on the basis of his data. So you can use a field that contains a date, to monthly or weekly blocks defined. Selections, this Field as a selection criterion mentioned, can be performed much faster, since they generally have access only to a few partitions. More elegant, you can delete old partitions: The command
DROP PARTITION ... the data of a whole week will be deleted instantly. But the usual archiving
DELETE statements (and only this regard, the trust 1,333,328, so that existing preservation programs can continue to function unchanged) is faster by orders of magnitude. be

 To carry out the partitioning, the table must reorganized once again (just "partitioned"). That would the giant tables with hundreds of millions of entries that we have in the eye, a seemingly feasible task, if it was not possible to carry out this work online, so while the table is used productively. This is indeed possible, and the statement defines a new command for the backup and restore tool 
 BRSPACE 
 by which this redefinition can be done online. 
 
 For us, unfortunately, still too early for the note - be the required Support Package level, we have in the relevant systems probably only come in the Andean year - but we are looking forward to it! The reference leads the tables 
 MSEG 
, 
 BDCP 
, 
 CDHDR 
 & co. 
 LIPS 
, 
 VBRP 
 so on. This shows that the authors know their Pappenheimer. These tables are included in our retail systems to the greatest. 
 
 Since we did not want to wait until the information is available on our systems, we have the tables of the Retail Information Systems 
 
 (RIS) independently made the start, especially our largest info-structure, the table 
 
 S520. It has 420 million entries. The primary key consists essentially of the week, the item number and the branch using the table two years are kept in the past should. The mere filing of the (unpartitioned), the periodic table, and compression of the data by product cost the system a week around 103,000 seconds, or about 28 hours. Here one should remember that the S520 only 
 
 a table of the RIS (albeit the largest) - there's still more of their ilk ... 
 
 needed After partitioning the "archive" [2] with the new program 
 Z_RIS_PARTITION_REORG 

only three seconds!
In three seconds to delete the 4.5 million sets of the oldest calendar week, and for the current week is a new partition created. The performance factor - fair, with only the recent fighting program compared this calculation so the archive without writing programs and the departments compressor - will amount to 1:10 '000 .

addition, there are beneficial secondary effects: For the execution of a

 DELETE statements had 
 namely blocks are read into the database buffer to evaluate the where condition. This does not apply to the DROP PARTITION statement 
 
: The buffer is available for other statements. Also, the merchandise category compression program could nevertheless still be accelerated by a factor of 200, since it operates only on the current calendar week and so only one partition must search. 
 
 course it was a stroke of luck to start with the 
 S520 
: it has in our system at the database level, no inter-partition secondary indexes: All existing secondary indexes contain the substance used for the partitioning week (
 SPWOC 
) so that the removal of partition no Index update is necessary. There are other information structures for which we use inter-partition indices. Then it will not take two seconds, but two minutes to delete a partition. But this is still orders of magnitude faster than the current archiving and deletion times. How did 
 
 We organized the partitioning programs? The centerpiece is the SAP class 
 CL_SQL_STATEMENT 
. To delete partitions or create, we need the method 
 execute_ddl (
 statement 
) 
 use this class. We have for this purpose, a local abstract superclass 
 lcl_stmnt 
 introduced that contains an object of type 
 CL_SQL_STATEMENT 
 as delegate. From the upper class for each statement type your own classes are derived, which have the task of substituting in "their" Statement of the current parameters of the call and then the specific statement as a string by calling a method to execute the upper class: 
 
 class  lcl_stmt definition abstract. 
   public section. 
     methods: 
       constructor importing iv_tablename type tabname. 

protected section.
methods:
_execute importing iv_statement type string optional raising cx_sql_exception. data:
gv_statement type string,

       gv_tabname type tabname, 
       go_sql_stmt type ref to cl_sql_statement. 
 endclass. 
  
 class lcl_split_partition_stmt definition inheriting from lcl_stmt. 

public section.
methods:
constructor importing iv_tablename type tabname,
execute importing iv_next type zora_partition_name iv_lower_than type zora_partition_name raising cx_sql_exception. endclass.
lcl_drop_partition_stmt class definition inheriting from lcl_stmt.
public section.

methods: constructor importing
iv_tablename type tabname,
• execute importing iv_part type zora_partition_name raising cx_sql_exception. endclass.
Here, for example, the implementation of the class to delete a partition:

• lcl_drop_partition_stmt class implementation. constructor method. super-> constructor (iv_tablename = iv_tablename).
gv_statement
= 'alter table & TABLE_NAME' &
• 'drop partition "PARTITION &"' & 'update index'. ENDMETHOD. "Constructor method execute data:.. Lv_statement type = string
lv_statement gv_statement
replace:...
• '& PARTITION' iv_part in lv_statement with _execute (lv_statement) ENDMETHOD" execute endclass . "Lcl_drop_partition_stmt IMPLEMENTATION replaced at runtime, the execute () method the partition name as parameter and replaces the drop partition statement. Then the is _execute () method in the upper class is executed, which sends the statement using the class
CL_SQL_STATEMENT
to the database.
• This small statement classes are based in a global class ZCL_SQL_PARTITIONER
, all offered in connection with partitions available database operations:

This class wants to know anything about the procedure for naming the partition. The knowledge should be implemented in the client class, which manages the partitions of a specific table or a family of similar tables. We have written such a client class called
ZCL_RIS_PARTITIONER
for the family of information structures. They have all the day, week or month (generic term: the period) as a key time. The main method reorg () computes this client class period, depending on the type, the names of all periods that fall outside the residence time and then calls methods of the service class
ZCL_SQL_PARTITIONER
to delete these partitions. Second, calculate the next partition name in the future, then again with the help of ZCL_SQL_PARTITIONER s to create a new partition.

Conclusion: Although we can notice published in

1333328 partitioning tool for use yet, but our own moves in this direction are very promising. All SAP users with an Oracle database should consider to partition their biggest database tables. You do not realize just great performance improvements, and if it goes well, probably a relief to be felt through the whole system. not remain

[1] At least in a footnote to mean the now 14-years previous Kenwood Cup unmentioned, a regatta, Hasso Plattner's yacht in the wrecked. Oracle CEO Larry Ellison failed to do so to make his yacht requested assistance from Plattner, preferring to film the unfortunate pleasure yacht. In his anger Plattner let it down his pants to give it at least to spoil this fun: "If you have to have this on your video, when you go home you should feel s - tty about what you did." (See the article in the Sailing World ).
[2] We have found the opportunity that there is a physical archive of RIS structures need not have such legal requirement, so we delete the data may also like. generate