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