Collaborate 13 - Partitioning Benefits P6 OLTP
By Brian Diehl on Apr 10, 2013
Attending conferences like Collaborate 13 gets me thinking deeply about the process and technologies we use in the P6 suite. If in can think of a single Oracle technology with the most bang-for-the-buck, it would be hands down Database Partitioning.
Partitioning is part of Oracle RDBMS Enterprise Edition. You might think that partitioning is only something applicable to a data warehouse. Not true. Of course it is practically a requirement for any significantly large warehouse (we support partitioning in the P6 Star Schema). But in a basic way partitioning sub-divides large tables into smaller, manageable chucks. This can be useful even in OLTP databases where there are very large tables with queries only looking at subsets of the rows.
A great example of this in P6 is the PROJWBS table. While this is a single table in the schema, it is really two logical tables. One logical table is very small, containing only the EPS nodes. The majority of the data comes from the individual WBS nodes. When querying just for WBS, this single table structure is no problem. But queries for EPS now contend with a very large table. Simply partitioning this table by EPS and WBS (using the PROJ_NODE_FLAG) can have a major impact on many queries. Another table were OLTP partitioning has significant impact is UDFVALUE because it contains many sub-tables for each subject areas.
There is much more to partitioning and we will look at these in more detail in the future.