Oracle partitioning list or range or ?
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.
Thanks!
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.
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
note that there are 3 partitions when you are done, 2 with 1 row each and the third with 2 rows (both for sample_id =3)
ASKER
Thanks. I followed the second example and it worked. Another question - is it possible for each partition to have it's own set of constraints (foreign keys) and indexes created when the partition is automatically created via interval? If we need to reload the data, it helps us to disable the keys/indexes on the partitions without having to do it for all the data. I wasn't sure if I have to manually create the keys and indexes after Oracle automatically creates the partition.