I've come across a table which has index fragementation as follows:

create table "blah".tabwhatever
(
pk_id serial not null ,
......
fragment by expression
(mod(pk_id , 10 ) = 0 ) in data_dbs01 ,
(mod(pk_id , 10 ) = 1 ) in data_dbs02 ,
(mod(pk_id , 10 ) = 2 ) in data_dbs03 ,
(mod(pk_id , 10 ) = 3 ) in data_dbs04 ,
(mod(pk_id , 10 ) = 4 ) in data_dbs05 ,
(mod(pk_id , 10 ) = 5 ) in data_dbs06 ,
(mod(pk_id , 10 ) = 6 ) in data_dbs07 ,
(mod(pk_id , 10 ) = 7 ) in data_dbs08 ,
(mod(pk_id , 10 ) = 8 ) in data_dbs09 ,
(mod(pk_id , 10 ) = 9 ) in data_dbs10
extent size N next size M lock mode row;

create unique index "root".i_tabwhatever_pk on "blah".tabwhatever
(pk_id)
using btree
fragment by expression
(mod(pk_id , 10 ) = 0 ) in idx_dbs01 ,
(mod(pk_id , 10 ) = 1 ) in idx_dbs02 ,
(mod(pk_id , 10 ) = 2 ) in idx_dbs03 ,
(mod(pk_id , 10 ) = 3 ) in idx_dbs04 ,
(mod(pk_id , 10 ) = 4 ) in idx_dbs05 ,
(mod(pk_id , 10 ) = 5 ) in idx_dbs06 ,
(mod(pk_id , 10 ) = 6 ) in idx_dbs07 ,
(mod(pk_id , 10 ) = 7 ) in idx_dbs08 ,
(mod(pk_id , 10 ) = 8 ) in idx_dbs09 ,
(mod(pk_id , 10 ) = 9 ) in idx_dbs10 ;

alter table "blah".tabwhatever add constraint primary key (pk_id)
constraint "blah".cwhatever_pk ;

Am I correct in thinking that this is going to lead to poor
performance as the index is effectly being fragmented based on round
robin?

Thanks,

Paul

paul_beattie@hotmail.com wrote:
> I've come across a table which has index fragementation as follows:
>
> create table "blah".tabwhatever
> (
> pk_id serial not null ,
> ......
> fragment by expression
> (mod(pk_id , 10 ) = 0 ) in data_dbs01 ,
> (mod(pk_id , 10 ) = 1 ) in data_dbs02 ,
> (mod(pk_id , 10 ) = 2 ) in data_dbs03 ,
> (mod(pk_id , 10 ) = 3 ) in data_dbs04 ,
> (mod(pk_id , 10 ) = 4 ) in data_dbs05 ,
> (mod(pk_id , 10 ) = 5 ) in data_dbs06 ,
> (mod(pk_id , 10 ) = 6 ) in data_dbs07 ,
> (mod(pk_id , 10 ) = 7 ) in data_dbs08 ,
> (mod(pk_id , 10 ) = 8 ) in data_dbs09 ,
> (mod(pk_id , 10 ) = 9 ) in data_dbs10
> extent size N next size M lock mode row;
>
> create unique index "root".i_tabwhatever_pk on "blah".tabwhatever
> (pk_id)
> using btree
> fragment by expression
> (mod(pk_id , 10 ) = 0 ) in idx_dbs01 ,
> (mod(pk_id , 10 ) = 1 ) in idx_dbs02 ,
> (mod(pk_id , 10 ) = 2 ) in idx_dbs03 ,
> (mod(pk_id , 10 ) = 3 ) in idx_dbs04 ,
> (mod(pk_id , 10 ) = 4 ) in idx_dbs05 ,
> (mod(pk_id , 10 ) = 5 ) in idx_dbs06 ,
> (mod(pk_id , 10 ) = 6 ) in idx_dbs07 ,
> (mod(pk_id , 10 ) = 7 ) in idx_dbs08 ,
> (mod(pk_id , 10 ) = 8 ) in idx_dbs09 ,
> (mod(pk_id , 10 ) = 9 ) in idx_dbs10 ;
>
> alter table "blah".tabwhatever add constraint primary key (pk_id)
> constraint "blah".cwhatever_pk ;
>
> Am I correct in thinking that this is going to lead to poor
> performance as the index is effectly being fragmented based on round
> robin?

I see what you're getting at but it's not round robin even though
fragmenting in this way may give even data distributions in each fragment.

The question to ask is what happens if I need to find a given serial
number, say 11. With genuine round robin, you'd have no idea where it
was and would have to look in all fragments until it was found. As
you're fragmenting by expression IDS will divide 11 by 10 and get a
remainder of 1 and therefore only need to look in idx_dbs02.

Ben.