Index Fragmentation

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.