1

My company is storing its data in an Oracle database. It offers services to several other companies, each of them having thousands of customers placing orders. For historical reasons, all of the data are in the same schema and, for example, all orders are in the same table, say ORDERS.

It now has performance problems and I'd like to take this opportunity to manage two goals :

  • eradicate performance problems
  • separate companies data for security concerns.

My initial thought was to use one schema for each company we serve :

  • schema A with a table named ORDERS containing all of the orders of company A customers
  • schema B with a table named ORDERS containing all of the orders of company B customers
  • etc. ...

but we have another concern : we also have some "super-companies" which also can manage data for many companies. For exemple : "supercompany" managing orders for both companies A and B.

With this approach, is there any mean to manage these "supercompany" ? I thought declaring another schema (let's say SUPERCOMPANY) having a synonym which refer to the union of ORDERS tables from A and B but :

  • Is there any performance problem having a synonym referencing a union ? Are tables' indexes used ?
  • How about INSERT ? How to target the appropriate table if "supercompany" wants to add an order for a customer which belong to company A ?

Should we better use another solution, like still having one big database and having schemas referecing appropriate partition of the big table ORDERS ? (I don't even know if this is possible)

  • schema DB containing data with huge ORDERS table, partitionned by company.
  • schema A having a synonym referencing DB.ORDERS#partitionA
  • schema B having a synonym referencing DB.ORDERS#partitionB
  • schema SUPERCOMPANY having a synonym referencing DB.ORDERS or DB.ORDERS#partitionA and ORDERS#partitionB

It doesn't sound good to me because we shouldn't directly target partitions, are we ?

I still have hope and I'm sure Oracle has solutions for such problems since it is a major player for relational databases.

What would be your approach ?

Thanks for your reading.

Aki
  • 11
  • 1
  • is 12c and multitenant an option? cause then each company would have a pdb, with one or more schemas. super companies would have > 1 schema – thatjeffsmith Oct 18 '18 at 14:55
  • In fact we just migrate to 12c so it could be an option. I need to learn more about this feature to be sure we can use it. Thanks for your response. – Aki Oct 18 '18 at 15:02
  • 11204 is about to go out of support, so 12/18 is where you want to be for updates/bug fixes anyway – thatjeffsmith Oct 18 '18 at 15:25
  • After further research it seems that I couldn't handle the supercompany case with such approach since it would not be able to operate on each company proper data. – Aki Oct 21 '18 at 15:26

1 Answers1

0

It sounds as though partitioning would be a good fit for you performance issue.

For the security issue you could look at Virtual Private Database which is designed for exactly this type of scenario. I don’t think you would even need synonyms (you’d probably need views if you went this route) as you would set up a policy such that depending on the user account you connected as, Oracle would apply the appropriate filter automatically to all affected queries.

You might look at using services too, to give more options for monitoring performance by company.

William Robertson
  • 15,273
  • 4
  • 38
  • 44