Oracle Expert

I am working on a market risk data warehouse, where we needed to rationalize the space used by a very large table.

The characteristics of the table were as follows:

• Total number of rows 5,200,000,000 (5.2 billion rows)
• Total table size 700GB
• Data was stored in an ASSM tablespace with uniform extents of 4MB
• Data was split into 1000 partitions (by source system), and each partition was further split to around 60 subpartitions.
• Data distribution across sub-partitions and across partitions are also not even, causing a lot wastage taken by sub-partitions which do not hold a lot of data.

Having reviewed the system, each partition is normally read as a whole, and for the largest partitions, parallel query/parallel DML were used using degree of parallelism of 8 only. Therefore, there was really no good reason to maintain 60 sub-partitions within a single partition. So it was decided that we will reduce it from 60 to 8.

Initially we tried performing the work in-situ, by using partition merge. We tested this against the largest partition with 47 million rows, and it took 45 minutes to complete. Considering we have 1000 partitions to do, even if we run this in parallel, it would be impossible to do this work over a weekend.

Next we tried another strategy:

• Create a new table with the same structure
• Create all the partitions/sub-partitions using the new template
• Set the table to be nologging
• Perform insert select with APPEND and PARALLEL hints for both read and write.
• Run 12 streams

We were getting 4 billion rows throughput per hour, meaning that the work was done well within 1.5 hours.

Size of final table was 450GB, a saving of 250GB.

This means we will have all the time in the world to add any indexes if needed, plus to regenerate the statistics on this new table.

I knew Oracle was not slow, but this is impressive indeed.