Is there any advice on physical database table partitioning for hubs, links and sats - especially sats that grow rapidly due to frequency of attribute change spread across the satellite record? We have in some large sats a small proportion of records that contain the current picture for a given hub? Our satellite tables affected are one per hub.
We are using insert only approach for satellites, i.e, we have no load_end_dt attributes that you could easily partition into current and history partitions.
I am thinking possibly to partition both hub and sat by hash partitions and potentially use list or range subpartitioning for the satellite to deal with the high volume of records per hashkey. The lack of load_end_dt is a limiting factor here, but i am loath to implement it for the largest 'changing' satellite tables due to the move from insert only to insert/update for the satellite.
I would be grateful for any advice. Platform is Oracle 18c db.