I have a sales history table that holds 68 million records, as a data architect I have partitioned the table by RANGE based on the client ID that is a sequence.
currently the table holds 120,000 physical partitions, the performence problem is reported by the developers once trying to insert a bulk of 8 million records into the table, they are trying to do so in a new partition, I provided them a partitioning syntax to do the insert directly into the table but it's taking hours without being accomplished.
only a PK on the ID is created, tablespace is OK and server space is OK. I'm trying to move some of the data into a backup table, but this is a manual job that can't be done in production.
is there any restriction on the number of partitions in a table? I did some search and nothing specified.
what would be a solution for such activity?? is there any other technology that I might adopt?
Thank you for your support,