Choosing Between Standard and Online Partitioning in SAP HANA: What You Need to Know

Partitioning tables in SAP HANA can significantly enhance query performance and optimize resource usage. Or is simply a way to store more than 2 billion records in a table. Administrators can choose between two methods for partitioning:?Standard?(previously called "Offline") and?Online. But how do these methods differ, and when should you use one over the other? Let’s dive into the details.

Key Differences in Execution

Both methods allow you to partition or repartition tables, but their technical implementations differ significantly:

Standard Partitioning This method directly modifies the table structure using an exclusive lock for certain tasks in the process, impacting table availability.

• Efficient for low-activity tables or operations performed during maintenance windows.
• Best suited for already partitioned tables when scaling partition counts (e.g., doubling partitions) -> Partition groups.
• The data is migrated column by column -> Column based.

Online Partitioning Introduced for systems with high availability needs, this method uses table replication to minimize downtime. It replicates data, applies changes, and then switches seamlessly to the partitioned table, allowing continued DML operations during the process.

• Ideal for high-activity tables with ongoing DML operations.
• Minimizes downtime using replication techniques.
• More effective for large, unpartitioned tables with many columns, as it processes data row by row, which minimizes the impact of high column counts.

When to Use Which Method

To determine the best partitioning method for your use case, consider these questions:

1. Access Patterns Is the table actively modified during operations? Online partitioning minimizes the impact on availability.
2. Existing Partitioning If the table is already partitioned, the Standard method may be faster due to resource-efficient grouping.
3. System Load What is the CPU, memory, and disk usage during partitioning? Can the system handle the additional load during partitioning?
4. Downtime Tolerance Can you schedule partitioning during low-activity periods or planned downtimes? If not, Online partitioning is the better choice as it offers flexibility for live systems.

Recommendations

Both methods have their advantages. Your choice should align with table activity, system capacity, and operational requirements.

For example, if you're working with a table like?EDID4?in an ABAP system where IDOC processing is critical, Online partitioning ensures no interruption in processing. However, for already partitioned tables where you’re only increasing the number of partitions, the Standard method may be more resource-efficient.

In the end, each partitioning should be tested in a pre-system. This is the only way to make precise statements about the runtime and distribution of the data. And only in this way can you build up experience specifically for your system as to which method is the best for your application.

SAP notes about Partitioning

Refer to the following SAP Notes for detailed insights on partitioning methods:

• 2044468 - FAQ: SAP HANA Partitioning
• 3469162 - Online Partitioning SAP HANA 2 SPS07

Final Thoughts

Partitioning is not a one-size-fits-all solution. Evaluate your system's activity, downtime tolerance, and table characteristics before choosing the appropriate method. Both Standard and Online partitioning are valuable tools for SAP HANA administrators to ensure optimal performance and system stability.

Remember to also consider partitioning parameters, the choice between homogeneous or heterogeneous partitioning, partition pruning, and designing for optimal query performance.

If the system is to run optimally after partitioning, good and detailed planning is essential.

What has been your experience with these partitioning methods? Share your insights in the comments below!

Outlook on our (@Jens Gleichmann) book: The topic of partitioning is one of the chapters. Stay tuned. The book will be available soon. The book will initially be available in German, with potential for other languages in the future.