Design table for performance (order - ordervisibility)
I have tables like order, orderitem, etc. and requirements for order visibility:
- One order can be seen by many users
- One user can see many orders
create table order (orderid number, ...);
create table ordervisibility (orderid number, username varchar2(20));
Order size is estimated to be >=500Mio. Application is a data warehouse with reporting.
The relationship order - user will be skewed: some users will be allowed to see many orders while some will only see a small percentage.
How should we design the system for performance?
We think about creating table ordervisibility as index-organized table. Or are there better approaches like partitioning? Or is the approach with tables order na ordervisibility already suboptimal?