Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, SITARAM.

Asked: March 13, 2023 - 12:42 pm UTC

Last updated: March 20, 2023 - 12:00 pm UTC

Version: 18c

Viewed 10K+ times! This question is

You Asked

Hello Tom,
If we have table partition, then why do we need Index partitioning? Is this something ultra faster execution of the query? And in what context we can choose local and global partitioning in OLTP database with examples. Thanks in advance.

and Chris said...

Usually you'll partition tables and indexes in the same way.

In rare cases, you may want partitioned indexes on non-partitioned tables. The most likely reason for doing this is to avoid the "hot blocks" problem for indexes on always increasing values (like sequence-assigned primary keys). In this case you'll hash partition the index to spread the contention across the index.

Connor explains more in this video:



For more details on partitioning see this guide https://asktom.oracle.com/partitioning-for-developers.htm

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sitaram Sahoo, March 16, 2023 - 4:33 am UTC

Thank you so much for sharing all about partitioning.
one question...
The table is already have an index and in what circumstances, we decide that it's time for partitioning.
Chris Saxon
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.
Chris Saxon
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?

Maybe!

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.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database