Avatar of szadba
szadba
Flag for United States of America asked on

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!
Oracle Database

Avatar of undefined
Last Comment
szadba

8/22/2022 - Mon
SOLUTION
sventhan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sean Stuber

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)
szadba

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy