Oracle Commerce Cat A/B Foreign Keys
Recently, I was in a meeting with a Senior Director of Architecture from Oracle Commerce (formerly ATG) and he made an interesting observation concerning the non-versioned databases of Oracle Commerce: Cat A/B.
Cat A/B are the non-versioned databases that hold the latest copy of the versioned assets. The Cat A/B databases only ever change when new or updated assets are published from the Core database. Changes are first pushed to the passive database where, once the updates are complete, it becomes active and the other database is updated.
Assets in the core database have already had their foreign key constraints validated. That being said foreign keys constraints are not needed on the databases and can be removed to improve response time of the database when pushing changes.
Don’t take this out of context as evil pre-optimization we’ve been warned about, but part of bigger plan that should be in place to maximize performance.
Since the Cat A/B tables will only contain the latest versioned assets the performance gains seen with using foreign keys and the indexes on those keys don’t provide the value they would on a table at scale.