Обсуждение: Partitioning with the index on the master table.

I have a list-based partition table and I want to prevent scanning of all
children tables in a JOIN, which is using the partition column.   I know
that CHECK constraints are not used in this case so I hope that creating an
index on the partition column would help.  But I am not sure if I should
create this index on the master table or on each of children tables.  It
seems kind of strange to create an index on a children table where all
values of the partition column are the same.  So will an index on the master
table be sufficient in this case?

Thanks,

Simon

On 3/4/15 9:25 AM, Semyon Reyfman wrote:
> I have a list-based partition table and I want to prevent scanning of all
> children tables in a JOIN, which is using the partition column.   I know
> that CHECK constraints are not used in this case

Why do you say that? If properly written and with constraint_exclusion
<> 'off' and with good queries they will be.

> so I hope that creating an
> index on the partition column would help.  But I am not sure if I should
> create this index on the master table or on each of children tables.  It
> seems kind of strange to create an index on a children table where all
> values of the partition column are the same.  So will an index on the master
> table be sufficient in this case?

It won't help unless you actually have data in the master table. Which
normally you wouldn't.

Basically, any indexing you do on a partitioned table must be done *on
the partitions*, not on the master table.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com