Let us understand how we can leverage partitioning to fine tune the performance.
Partitioning is another key strategy to boost the performance of the queries.
It is extensively used as key performance tuning strategy as part of tables created to support reporting requirements.
Even in transactional systems, we can leverage partitioning as one of the performance tuning technique while dealing with large tables.
For application log tables, we might want to discard all the irrelevant data after specific time period. If partitioning is used, we can detach and/or drop the paritions quickly.
Over a period of time most of the orders will be in CLOSED status. We can partition table using list parititioning to ensure that all the CLOSED orders are moved to another partition. It can improve the performance for the activity related to active orders.
In case of reporting databases, we might partition the transaction tables at daily level so that we can easily filter and process data to pre-aggregate and store in the reporting data marts.
Most of the tables in ODS or Data Lake will be timestamped and partitioned at daily or monthly level so that we can remove or archive old partitions easily