The importance of having an appropriate data segmentation (partitioning)

In this presentation will be shown real life examples from database applications in the ATLAS experiment @ LHC where we make use of many Oracle partitioning techniques available in Oracle 11g. With the broadly used range partitioning and its option of automatic interval partitioning we add our own logic in PLSQL for sustaining data sliding windows in order to enforce various data retention policies. We also make use of the reference partitioning in some use cases, however the most challenging was to segment the data of a large bookkeeping system which resulted in tens of thousands list partitions and list sub-partitions. Partition and sub-partition management, index strategy, statistics gathering and queries execution plan stability are important factors when choosing an appropriate for the use case data management model. The gained experience with all of those will be shared with the audience.