Hello,
We have a legacy application for which we are always looking to improve performance without making too many changes to the application.
I have some question/clarification points for table cluster v/s table partition.
We have few tables: Both these tables are very bulky especially DOCL.
DOC: We store different kinds of documents in them. example Purchase Orders, Payments, Accounts Payable, Accounts Receivable etc.
DOCL; This table has the line item for each document.
Now most of the times these two tables are queried together, so in my mind they make perfect candidate to create a table cluster and store them together.
Since DOC stores all different kinds of documents, it makes sense to partition it as well. If we are searching for a PO it makes sense not to go through all the AP/AR records.
Now you can not cluster and partition same table. They are mutually exclusive (you get error ORA-14026: Partition and Cluster clauses are mutually exclusive).
In this case how to decide should we cluster the table or partition the table?
Appreciate your thoughts on the matter.
Thanks,