Sitaram Sahoo, March 16, 2023 - 4:33 am UTC
Thank you so much for sharing all about partitioning.
The table is already have an index and in what circumstances, we decide that it's time for partitioning.
March 16, 2023 - 12:53 pm UTC
You mean partitioning an index on a non-partitioned table?
If so it's the scenario described above - you have a "right growing" index where there's lots of contention on its right edge block. This is when you have "lots" of sessions inserting values to the table at the same time.
It's relatively unusual you'd want/need to do this without also partitioning the table.
Sitaram, March 19, 2023 - 1:17 pm UTC
I wanted to know, if index is already created on a non-partitioned table, in which scenario
i) we will drop that index and choose for only partitioning that table?
ii) we need to retain the existing index and the partitioning is not required.
Thank you so much for sharing the link, that is quite informative.
What I understood there... if I want to see the data in a particular range, partitioning is better choice than index. Am I correct?
I need one more clarification on global partitioned index over b-tree index in non-partitioned table. Searching is faster in global partitioned index because it holds the rowid inf along with partition key inf or something else?
Thanks again for your valuable time.
March 20, 2023 - 12:00 pm UTC
if I want to see the data in a particular range, partitioning is better choice than index. Am I correct?
If the data range is "small" (e.g. get orders created today), an index is often good enough. Partitioning is better suited to cases when you're querying on larger date ranges (e.g. all sales for last year). It's also very useful for archiving the oldest data - you can quickly remove it with drop/truncate partition.
Searching is faster in global partitioned index because it holds the rowid inf along with partition key inf or something else?
All B-tree indexes store rowids; non-partitioned indexes and global partitioned indexes are both B-Trees by default. Exactly which is faster will depend on your query, the data, & partitioned scheme. In most cases I would expect a regular non-partitioned index on a non-partitioned table to be the best choice.
Sitaram, March 20, 2023 - 12:56 pm UTC
Thanks a lot for sharing your valuable time and knowledge.