On Tue, 2005-10-04 at 18:16 +0200, Zeugswetter Andreas DAZ SD wrote: > > Another possibility is optimizing for the special case of > > indexing on a partitioning key. In this case, index values > > would be very localized to one table, so just storing the > > table info on each index page (or something similar) would work well. > > If you have the partitioning key in the index and the partitions don't > overlap, it is better to create separate [unique] indexes on the > subtables. > Building separate indexes per partition is usually preferred because of: > 1. performance of dropping a partition > 2. smaller index for CE
... > Imho the best solution would be a merge node, > that merges results of > several index accesses to avoid a sort and still use separate indexes. > Such > a merge node could probably also detect the case where accessing > partitions in a certain > order still produces ordered results. Yes, that was my conclusion also. There are a number of intermediate steps along the way, so it will take some time to achieve it. > Usually you will only want the "one big unique index" when the > partitioning is not > reflectable in the index keys, and then (also in other db's) such an > index is usually a pain ... Agreed^2. The idea of a global index is a non-starter for all of the reasons that Tom gave and the main one: Its's unusably huge. There's no point in partitioning a 1TB table if you then have to build a 500GB index on it. The tree would be so deep that each insert would require maybe 3 I/Os on index branch blocks before you got to the leaf. Insert performance would suck real bad, which is a blocker since if you have a large table you almost certainly have a lot of data to load. If you don't have a big table you shouldn't be partitioning it anyway. Best Regards, Simon Riggs ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly