Large SAP tables which are good candidates for partitioning


Not all tables are good candidates for partitioning. The tool we have developed runs through multiple checks for large tables to identify the right candidate. Generally if the answer is yes to the following questions, table partitioning may be a suitable strategy for improving performance.
1. Is the database large enough?
2. Is the table large enough?
3. Do your reports and queries have distinguishable access patterns?
4. Are you experiencing slowness and performance degradation?
5. Is the skew factor for the data distribution under reasonable limits?

Below are some of the large tables which have been candidates for table partitioning amongst our SAP customers.

Large material master tables
MARA                   Material Master Table
MARC                   Plant Material Data
MARO                   Company Material Data
MAKT                    Material Text Table.
MARCH                                Material Master C Segment: History
MARD                   Storage Location Data for Material
MARDH                                Material Master Storage Location Segment: History
MARM                  Units of Measure for Material
MBEW                  Material Valuation
MBEWH               Material Valuation: History
MDKP                   Header Data for MRP Document
MDVM                 Entry in MRP File
MEAN                   International Article Numbers (EANs) for Material
MLAN                   Tax Classification for Material
MOFF                    Outstanding Material Master Records
MSTA                    Material Master Status
MVKE                    Sales Data for Material


Large Sales tables
VBSK                           Collective Processing for a Sales Documen
VBSN                           Change status relating to scheduling agre
VBSP                           SD Document Item for Models of Material S
VBSS                           Collective Processing: Sales Documents
VBUK                           Sales Document: Header Status and Adminis
VBUP                           Sales Document: Item Status
VBRK                           Billing Document: Header Data
VBRL                           Sales Document: Invoice List
VBRP                           Billing Document: Item Data
VBAG                           Sales Document: Release Data by Schedule lines
VBAK                           Sales Document: Header Data
VBAP                           Sales Document: Item Data
VBBE                           Sales Requirements: Individual Records
VBBPA                          Sales Document: Partner
VBBS                           Sales Requirement Totals Record
VBEH                           Schedule line history
VBEP                           Sales Document: Schedule Line Data
VBFA                           Sales Document Flow
VBFS                           Error Log for Collective Processing
VBHDR                          Update header
VBKA                           Sales Activities
VBKD                           Sales Document: Business Data
VBKK                           SD Doc.Export Letter of Credit
VBKOF                          SD index: Open sales activities by partne
VBKPA                          SD index: Sales activities by partner fun
VBKPF                          Document Header for Document Parking
VBLB                           Sales document: Release order data
VBLK                           SD Document: Delivery Note Header
VBMOD                          Update function modules
VBMUE                          Sales Document: Characteristic Overview
VBMUET                         Sales Document: Characteristic Overview D
VBMUEZ                         Sales Document: Characteristic Overview A
VBOX                           SD Document: Billing Document: Rebate Ind
VBPA                           Sales Document: Partner
VBPA2                          Sales document: Partner (used several tim
VBPA3                          Tax Numbers for One-Time Customers
VBPK                           Sales Document: Product Proposal Header
VBPM                           Supplement for Sales Document Items: Tech
VBPV                           Sales Document: Product Proposal
VBREF                          SD Object Link to Item References


Large EHS Health and Safety
ESTDR                                   EHS: Additional Information: Value Assignment Assessment
ESTDU                                   EHS: Additional Information: Value Assignment Usage
ESTALE                                  EHS: ALE - reference to originals
EST_CHANGE_DOCS       EHS: Change Documents in the EH&S System
ESTAUSP                              EHS: Shadow table for AUSP
ESTVH                                   EHS: Specification - Value Assignment Type Assignment
ESTVA                                   EHS: Value Assignment


Large IS-OIL Tables
OIGV                     TD Vehicle Header
OIGVL                   TD Licenses Needed for Vehicle
OIGVT                   TD Vehicle Header Text
OIGVTU               TD Vehicle - Transport Unit Assignment
VI01                       Create Shipment costing documents
VI02                       Display Shipment costing documents
O401                      Create Business location
O402                      Change Business location
O403                      Change Business location
O4R1/O4R2/O4R3 - Rack meter create/change/display


Large COPA tables
CE1xxxx                               Actual line items  (where xxxx = operating concern). 
CE2xxxx                               Plan line items
CE3xxxx                               Segment summarized form.
CE4xxxx                               Unique profitability segment number for each combination of characteristic values
COEP & COBK    CO Object: (Line Items (by Period) & Document Header)
COEJ                      CO Object: Line Items (by Fiscal Year)
COSP & COSS     CO Object: (Cost Totals for Internal & External Postings)


Large CRM tables
CRMD_ACTIVITY_H         Activity
CRMD_AUSP_HIST          Characteristic Values
CRMD_BILLING Business Transaction - Billing Set
CRMD_DHR_ACTIV         Activities
CRMD_DHR_HSRVOR    Service Orders
CRMD_LINK       Transaction - Set - Link
CRMD_ORDERADM_H   Business Transaction
CRMD_ORDERADM_I    Business Transaction Item
CRMD_ORDER_INDE      Index table for one-order document
CRMD_ORGMAN             Organizational Unit Set
CRMD_PARTNER              Partners
CRMD_PRICING               Business Transaction Pricing Parameter Set
CRMD_PRICING_I           Transaction Item Price
CRMD_PRODUCT_I         Transaction Item - Product
CRMD_SALES     Business Transaction - Sales Set
CRMD_SCHEDLIN             Business Transaction Item - Schedule Line
CRMD_SERVICE_I            Transaction Item - Service
CRMD_SRV_OSSET         Subject Reference Object
CRMURLCONT   SDOK: Table for Document Contents (Import/Export)
CRMURLLOIO    CRM: URL:CD: Instances of Logical Information Objects
CRMURLLOIOT  CRM: URL:CD:: Descriptions of Logical Information Objects
CRMURLPHF      CRM: URL: CD:: Files of Physical Information Objects
CRMURLPHIO    CRM: URL: CD:: Instances of Physical Information Objects
CRMURLPHPR   CRM: URL: CD:: Attributes of Physical Information Objects
CRM_JCDS          Change Documents for System/User Statuses (Table JEST)
CRM_JEST           Individual Object Status
CRM_JSTO          Status Object Information


Large FICO/FI/CO tables
BSIS                                       Accounting: Secondary Index for G/L Accounts
FAGLFLEXA                         Intercompany posting procedures
RFBLG                                   Cluster structure for change documents
FAGL_SPLINFO                 Change document header
FAGL_SPLINFO_VAL       Additional Change Document - Table for STRINGs
BKPF                                      Accounting Document Header
ZFI_INVDETAIL                  External tax document: header info
BSAD                                     Accounting: Secondary Index for Customers (Cleared Items)
CDCLS                                   Cluster structure for change documents
BSAS                                      Accounting: Secondary Index for G/L Accounts (Cleared Items)
BSE_CLR                               Additional Data for Document Segment: Clearing Information
CDHDR                                  Change document header
BSAK                                     Accounting: Secondary Index for Vendors (Cleared Items)
STXL                                       G/L accounting: Subsequent derivations
STXH                                      FI Document Reference
BVOR                                    Intercompany posting procedures
BSIP                                       Index for Vendor Validation of Double Documents


Large HCM/HR Tables
HRP1001              Cost Center Texts
T512T                    Deduction of Time Quotas 
T510                       Activities per Payroll Result 
T512W                  Deduction of Time Quotas 
PA0001                 DB Table for Infotype YYYY
PA0000                 DB Table for Infotype YYYY
HRP1000              Cost Center Texts
PA0002                 DB Table for Infotype YYYY
PA0008                 DB Table for Infotype YYYY
PA0007                 HR Master Record- Infotype 0007 (Planned Working Time)
PA0006                 HR Master Record- Infotype 0006 (Addresses)
T528T                    Deduction of Time Quotas 
T552A                    Activities per Payroll Result 
PA0003                 DB Table for Infotype YYYY
PA0009                 DB Table for Infotype YYYY
HRP1008              Infotype 1008 DB Table
HRP1013              Infotype 1013 DB Table
HRP1028              Infotype 1028 DB Table
T511                       Deduction of Time Quotas 
T512Z                    Activities per Payroll Result 
T554T                    Activities per Payroll Result 
T554C                    Deduction of Time Quotas 
T554S                    Activities per Payroll Result 
HRP1002              Infotype 1002 DB Table


Large Production Planning tables
STAS      BOMS  ITEM SELECTION
STPO     BOM FOLLOW UP CONTROL
STZU      SALES ORDER TO BOM LINK
STKO     SALES ORDER TO BOM LINK
MAST    SALES ORDER TO BOM LINK
CRHD    WORK CENTER HEADER
CRHH    WORK CENTER HIERARCHY
CRHS     HIERARCHY STRUCTURE


Large SAP Office
SOC3     SAPoffice: DB for objects (import/export)
SOFM    SAPoffice: Folder contents
SOOD    SAPoffice: Object definition
SOOS     SAPoffice: send process
SMIMCONT1      SDOK: Table for Document Contents (Import/Export)


Large RFC tables
ARFCSSTATE       Description of ARFC Call Status (Send)
ARFCSDATA        ARFC Call Data (Callers)
TRFCQSTATE      Description of qRFC Call Conditions (Inbound Queue)
TRFCQDATA       qRFC Call Data (Inbound Queue)
TRFCQOUT          tRFC Queue Description (Outbound Queue)
TRFCQIN              tRFC Queue Description (Inbound Queue)


Large BDOC Message tables
SMW3_BDOC    BDoc Message Store: Header
SMW3_BDOC1  BDoc Message Store: Message Body (classic  part)
SMW3_BDOC2  BDoc Message Store: Message Body (extension part)
SMW3_BDOC4  BDoc: Validation Errors
SMW3_BDOC5  BDoc: Receivers with state
SMW3_BDOC6  BDoc: Receiver specific error segments
SMW3_BDOC7  BDoc: Receiver Root IDs
SMW3_BDOCQ qRFC Queues in use by BDoc Message
TSMW3_STAT    Attributes of Bdoc Processing States


Large Basis tables
BALDAT                                Application Log: Log data
BALHDR                Application log: log header
BALM                    Application log: log message
BAL_INDX           Application Log: INDX tables
BDCP2                   Aggregated Change Pointers (BDCP
EDIDC                    Control record (IDoc)
EDIDS                    Status Record (IDoc)
IDOCREL               Links between IDoc and application object
QRFCTRACE        Trace Description
SRRELROLES       Object Relationship Service: Roles


Appendix

1. If you use a SAP HANA database, you can use the SQL command SQL: "HANA_Tables_LargestTables" and ONLY_TECHNICAL_TABLES = 'X'  (SAP Note 1969700) to determine the largest of these tables.
2. If you use an Oracle database, you can use the SQL command SQL: "Space_LargestTables" and ONLY_BASIS_TABLES = 'X' (SAP Note 1438410) to determine the largest of these tables.
3. SAP Note 2243625 provides SAP Enterprise Support offerings in the context of data volume management
4. SAP Note 2388483 How-To: Data Management for Technical Tables
5. SAP Note 16083 that describes the scheduling of standard and reorganization jobs
6. SAP Note 706478 Preventing Basis tables from increasing considerably
7. SAP Note 966854 Reorganization - new report
8. SAP Note 618868 FAQ: Oracle performance




Comments

  1. Lea WatsonMay 30, 2021 at 1:49 AM

    Office partitions provide a completely different level of privacy to an open plan office. They establish separate sections within the office creating individual spaces that employees can personalise. https://lugenfamilyoffice.com

    ReplyDelete

Post a Comment

Popular posts from this blog

Partitioning in SAP HANA database

SAP HANA provides two ways of distributing data 1. Database Partitioning - different tables being distributed across several hosts (SAP Note 2044468 - FAQ:SAP HANA Partitioning) 2. Table Partitioning - splitting a column store table and distributing across several hosts (SAP Note 2081591 - FAQ: SAP HANA Table Distribution)  SAP HANA supports: 1. Hash Partitioning 2. Range Partitioning 3. Round-robin Partitioning In SAP HANA, we can create 1. Single-Level Partitioning 2. Multi-Level Partitioning The optimizing algorithm takes factors into account such as: - Balance tables equally in a way that they are spread over all the slave nodes and make optimal use of the available physical memory on each node - Ensure that tables/table partitions which are belonging to the same table groups (see tables _SYS_RT.TABLE_PLACEMENT, SYS.TABLE_GROUPS) are located on the same nodes, as they are logically frequently queried from application side via joins and if t...
Read more

SAP HANA exploits partitioning in SAP BW for optimization

Image
Partitioning significantly improves query performance, based on the types of queries and on your hardware configuration as the system will find faster the requested information, because it will filter out all the irrelevant partitions and thus reduce the data volume to be read. This is a standard feature in most databases but SAP did not truly exploit this feature being database agnostic until HANA. In SAP/R3, SAP provided partition keys for BW but did not partition data during installation. With HANA the table partitioning/distribution is automated for SAP NetWeaver Business Warehouse. Database tables created for InfoCubes or classic DataStore objects are partitioned on the database. While the technical partitioning of data at database level helps optimizing the runtime of BW processes to a great extent, they alone are inadequate in building and maintaining data warehouses that scale easily with growing data volumes. Hence, to address some of the aspects of dealing with ...
Read more