You have two major options:
- Partition the table, or
- Move it to wider pages
Moving to wider pages is simple:
- Create a new dbspace with wider page size if you don't have one, then:
- ALTER FRAGMENT ON mytable INIT IN new_dbspace;
Partitioning is just as simple, except that you first have to decide on a partitioning schema. Options with comment:
- ROUND ROBIN - fastest for inserting from multiple sessions concurrently, but no fragment elimination on query.
- BY EXPRESSION - you have to figure out a set of expressions, one for each partition (with an optional remainder partition). Extensive expression lists can be slow to process for inserts and over time you may have to reorganize the order of partition expressions in the list so that the most important ones are earliest in the list. Can be high maintenance.
- LIST - you have to figure out lists of discrete values for each partition and optionally a NULL partition and/or a REMAINDER partition
- INTERVAL - if your partitioning column is numeric, date, or datetime you can use this one. Partitions are defined as ranges of values. New partitions are automatically created when a value arrives that does not have an existing partition. Optionally you can specify a specific maximum number of partitions to keep and whether to DISCARD the oldest partition when the N+1st partition is created or to DETACH it as a standalone table.
All of these options are "safe" as in not losing data. You should probably want to drop all indexes and make the table RAW before running the ALTER FRAGMENT statement (not practical if HDR, RS, or DSS secondaries are configured as you would have to reestablish the secondaries via a restore afterwards) to avoid long transaction rollbacks. As for fast, I would say that moving the table to wider pages is probably fastest to complete.