Local versus Global indexes on partitioned tables

Some people suggest that local indexes should always be used in preference to global indexes. This is not the case, and each indexing technique should be employed to best match the application requirements you have. A local index can be the absolutely wrong choice for particular kinds of queries
Follow me!
blog: connor-mcdonal...
twitter: / connor_mc_d
Full playlist: • An introduction to Par...
The Podcast!
podcasts.apple...
open.spotify.c...
Subscribe for new tech videos every week
Other social media channels here: linktr.ee/connor
A free Oracle database forever ?! Oh yes indeed!
www.oracle.com...
Music: Night Owl (Broke For Free), Kevin Mcleod (incomptech), Dyalla, Bensounds
#oracle #partitioning

[@humayunhimu6045]

Very well explanatory tutorial

[@DatabaseDude]

Glad it was helpful!

[@tqk0609]

It's well explanation

[@elmorautube]

Question: is it a good practice to create indexes on partition columns? I've always tried to avoid this in the past, whenever it was possible. Thanks.

[@DatabaseDude]

Like any index, it is cost versus benefit. If you need unique (or nearly unique) lookups on a partition column (eg you might partition by a month but need to query for all sales in just one hour of that month) then an index is probably going to be the way go to.

[@fahdbahoo1]

can the Primary key index also be Local or Global ? or only Global

[@DatabaseDude]

If the key contains the partition column(s) then it can be local, otherwise it needs to be global. Some times people will compromise, eg, the primary key is logically (say) a column called TRANSACTION_ID, but they will add in (say) TRANSACTION_DATE as well so that the index can be local (and get the benefits associated with that)