Hi everybody,
I have a question about the best practices when it comes to partitioning? Basically, here's my problem. We have redesigned our db model and we need to migrate around 30 millions records (from 10 different tables to another big one flattening almost everything). The structure of the final table is like this:
CREATE TABLE dest_table
(
my_id NUMBER(16), -- pk
my_ref_id NUMBER(16), -- fk
my_type VARCHAR2(20),
my_name VARCHAR2(255),
my_lastname VARCHAR2(255),
-- a couple of other fields
my_final_attribute VARCHAR2(255)
)
We also expect the table to grow by around 10 million records a year. So it will quickly grow. So, I decided to go for partionning. Since, this table will always be accessed by the reference id (my_ref_id), I decided to partition it by that field. Something like :
CREATE TABLE dest_table
(
...
)
PARTITION BY HASH(my_ref_id)
PARTITIONS 64
Initially, each partitions will have like 500.000 records. But I was wondering if the amount of partitions is not too much? This table will be heavilly accessed. By heavy, I mean like 100 transactions by minutes ( Insert, update, delete and select).
So, I was wondering:
1- Is 64 partitions too much for this?
2- I plan to create the table with a PCT FREE of 20. To reduce migrated & chained rows.
3- Do you think I should create a different tablespace to store this table?
4- About indexes... I will have a global index on the primay key and another index on the attribute used to partition the table. Is it relevant to also partition it the same way as the table? Like this then:
CREATE INDEX my_index_on_dest_table
ON dest_table(my_ref_id)
GLOBAL
PARTITION BY HASH(my_ref_id)
PARTITIONS 64
Unfortunately, I don't have any date in this table. So a range partition is not an option. That's why I went for hash. But I don't know how Oracle will access/update the data with so many partitions. Will it be performant? I'm still on Oracle 10g
Any advice is welcome,
Thanks,