We can partition a table in multiple ways such as List, Range and Hash (Please specify more if there are any more).
If we do table a partition it would improve performance of query while fetching data.
I would like to know, is there any difference in performance if we define a specific type of parition? I mean, which one is better for performance among List, Range and Hash.
Thank you, Regards,
BI DWH BALA
I'm afraid there isn't a straightforward answer to this question.
It depends on what the data is, and how you are querying the data. Partitions improve performance in two ways, by allowing better parallel operation, and by reducing the amount of data that needs to be read.
For performance, you should design your partition scheme such that common queries that need to perform a scan can do so on the least reasonable data set.
For example, imagine a large table called ORDERS that has columns (orderid,customerid,productid,orderdate,orderstate).
Assume the orderstate is low cardinality and one of the four values "NEW", "PROCESSING", "PROCESSED", "REJECTED". The table could be list partitioned to make queries on a given state efficient (e.g. quickly show all new orders).
Alternatively, your queries might frequently reference by date, so you might want to range partition the ORDERS table by orderdate, which would give efficient range scans within date ranges, such as showing all orders for a given month.
On the other hand, you might choose to hash partition on customerid, which could give very efficient access to all orders for a given customer.
There isn't one right answer, it all depends on your data and your application.
