Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Does table partitioning cause index partitioning too?

CUBRID Official Forum » General Discussions » Does table partitioning cause index partitioning too?

author
message
[Level:0]majidazimi

Post subject: Does table partitioning cause index partitioning too?

registered: 03/08/2013

IP: *.42.54.159

views: 1

Hi,

Does table partitioning cause index partitioning too? Or we have one big index and bunch of small data tables

I'm asking this because in PostgreSQL we should create index for each partition separately. 

Quote
[Level:5]eusto

# Post subject:Re: Does table partitioning cause index partitioning too?

profile

registered: 06/08/2011

IP: *.127.155.139

In CUBRID, users are allowed to only create indexes on the partitioned table (not partitions). An attempt to create an index on a partition would throw an "Invalid access to partition" error.

When creating an index on a partitioned table, CUBRID decides how this index will be propagated accross partitions using the following rules:

  1. All primary keys are global indexes (there is only one index which holds data for all partitions)
  2. All unique indexes are global indexes (this has been changed somewhat begining with CUBRID 9.0, i'll explain below)
  3. All foreign keys are local.
  4. All other indexes are local (one index for each partition)

The way CUBRID creates unique indexes has been changed since CUBRID 9.0. A unique index is global only if the partitioning key is not part of that index. Let me give you an example:

CREATE TABLE t(i INTEGER, j INTEGER) PARTITION BY HASH(i) PARTITIONS 5;

The following indexes are local (one for each partition) because the partitioning key (i) is part of the index:

CREATE UNIQUE INDEX u_t_i on t(i);

CREATE UNIQUE INDEX u_t_i_j on t (i, j);

The following index is global (one index holds data for all partitions) because it is unique and the column i is not part of it:

CREATE UNIQUE INDEX u_t_j ON t(j);

Local indexes should provide better performance than global indexes. However there are some optimizations which will not be available on local indexes (like ORDER BY skip, etc). In CUBRID 9.0,  these optimizations are not supported on partitioned tables anyway but might be, in the future, on global indexes.

Quote




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: