question

BI DWH BALA avatar image
BI DWH BALA asked

Is there any Performance difference with different partitions?

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

oracleperformancepartition
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Andrew Mobbs avatar image
Andrew Mobbs answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you Andrew. Your answer sounds clear cut way of deciding which partition we should use is depends upon the scenario we have for a particular requirement. Thanks once again.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.