I have, in Oracle database, fact table based upon which reports are generated. Table contains sales data - large volume.
Users generating reports are usually using grouping by (sale_day, customer, ...) or (sale_day, product, ...).
To optimize table structure for such reads I am planning to partition it by sale_day. But then I have a problem. If I use sub-partitions by customer then aggregations by product will read all sub-partitions for given day. Sub-partitioning by products generates analogical problems.
Bitmap indexes will rather not be helpful, because of multitude of possible values: thousands of customers and tens of thousands of products.
Any ideas/best practices how to optimize this table structure for such reads?

  • Bitmap indexes are actually intended to be used with "thousands of customers and tens of thousands of products" Why not just partition by sale_day? – Wernfried Domscheit Dec 6 '19 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.