RE: Unique constraint across all partitions?

["Michel SALAIS" <msalais@xxxxxxx>]

Hi all,

 

Global indexes are useful in Oracle for performance when indexed columns are different from partitioning key. An “index scan” (well it is named “range scan” in Oracle) using a local index has to navigate through all index partitions in that case. For a global index, it is only one tree to scan and this is more efficient than doing the same for a lot of them.

 

Regards

 

Michel SALAIS

Consultant Oracle, PostgreSQL

MSYM

23 rue du Départ

Boîte 37

75014 Paris

 

+33 6 68 94 19 60

msalais@xxxxxxx

www.msym.fr

 

De : Kanninen Anssi EXT <Anssi.Kanninen@xxxxxxxxx>
Envoyé : mardi 26 janvier 2021 11:09
À : pgsql-admin@xxxxxxxxxxxxxxxxxxxx
Objet : RE: Unique constraint across all partitions?

 

But why use global indexes?
Oracle also has local partitioned indexes which are just like we would like to have in PostgreSQL.

 

From: Victor Yegorov <vyegorov@xxxxxxxxx>
Sent: tiistai 26. tammikuuta 2021 11.54
To: Thomas Kellerer <shammat@xxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxxxxxxxx
Subject: Re: Unique constraint across all partitions?

 

вт, 26 янв. 2021 г. в 09:48, Thomas Kellerer <shammat@xxxxxxx>:

> Global indexes are likely more pain than gain.

While not extremely popular, many Oracle users do find them useful.

I've heard and been advised many times exactly the opposite for ORACLE,
especially when performance is important.

Global indexes makes a 15-minute maintenance job into a 9-hour downtime experience.

 

--

Victor Yegorov