I have a very large table that needs to be partitioned for ease of loading and archiving data. Users require that we can archive data off by an identifier called sample_id (that is not the table's primary key).
What would be the best partitioning approach for this example so that each sample has its own partition in the table and when we need to load a new sample, it will automatically create a new partition with its own indexes and constraints. I'm looking for any helpful recommendations. We are running Oracle 11.2. I was thinking list range or list hash but am not sure how to proceed. If you have any small examples that would be great also.