Re: [firebird-support] Re: TempDirectories and ALTER INDEX ACTIVE
On 8-6-2012 18:07, Ann Harrison wrote: On Fri, Jun 8, 2012 at 11:24 AM, Alec Swan alecs...@gmail.com wrote: Thanks Vlad, but this is too complicated to give as a guidance to our customers. However, it sounds like rebuilding an index cannot require more space that the database size itself, right? On a bad day, yes it could. To have a day that bad, you'd have to have keys that are most of the size of the record, with columns that are often null, zero, or contain values much smaller than the declared size of the column. There may be a situation during the merge phase of the sort when there's more than one copy of each record. Question out of curiosity: why does an index rebuild actually require a sort? I would think that for an index rebuild this would be sufficient: * deactivate index * clear index * for each record (sequential read of all pages?) *insert column value into index * activate index These steps would not require any additional temporary space. -- Mark Rotteveel
Re: [firebird-support] Re: TempDirectories and ALTER INDEX ACTIVE
On 10-6-2012 12:32, Dmitry Yemanov wrote: 10.06.2012 14:09, Mark Rotteveel wrote: * for each record (sequential read of all pages?) * insert column value into index Inserting unsorted / random values into the b-tree is known to be much slower than sorting the values in advance and loading them into the b-tree in order. If the trade off is requiring a large part of memory or disk space extra (and the additional time required to perform the sort), then I do wonder if the advantage is really that big. The build of a balanced b-tree(*) is O(n log n), and the 'good' case of sorting is O(n log n) to O(n^2) (ideal is O(n)). Sorting before insertion might only be worth the effort if the sort can fully occur in memory. The additional overhead of writing to disk during the sort and reading from disk for insertion might negate most of the advantages against the overhead of inserting into the b-tree unsorted (ie balancing etc). It might be worth to investigate this tradeoff, or just to provide an option to rebuild indices without using temporary sort spaces so others can measure it for themselves (or to have a workaround if disk space is not large enough to accommodate the rebuild). *) I am assuming FB uses a balanced trees, because sorting first would otherwise degenerate it into a linked list -- Mark Rotteveel
Re: [firebird-support] Re: TempDirectories and ALTER INDEX ACTIVE
Thanks Vlad, but this is too complicated to give as a guidance to our customers. However, it sounds like rebuilding an index cannot require more space that the database size itself, right? On Fri, Jun 8, 2012 at 7:22 AM, hvlad hv...@users.sourceforge.net wrote: ** --- In firebird-support@yahoogroups.com, Alec Swan wrote: My main question is how can we calculate the temp sort space required to ALTER INDEX ACTIVE (on all indexes serially) given the size of the database? Is twice the size of the database a good upper bound? Enumerate all fields in index, calculate summa of full size of every field (for [var]char(N) it is N * bytes_per_char, for ex.), add extra 8 bytes (for record number) - this is sort record size. Multiply it by number of records and you'll get a lower estimate of temp space required. Substract value of TempCacheLimit and you'll get estimate of disk space required. Regards, Vlad [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Re: TempDirectories and ALTER INDEX ACTIVE
On Fri, Jun 8, 2012 at 11:24 AM, Alec Swan alecs...@gmail.com wrote: Thanks Vlad, but this is too complicated to give as a guidance to our customers. However, it sounds like rebuilding an index cannot require more space that the database size itself, right? On a bad day, yes it could. To have a day that bad, you'd have to have keys that are most of the size of the record, with columns that are often null, zero, or contain values much smaller than the declared size of the column. There may be a situation during the merge phase of the sort when there's more than one copy of each record. Good luck, Ann [Non-text portions of this message have been removed]
RE: [firebird-support] Re: TempDirectories and ALTER INDEX ACTIVE
Alex, However, it sounds like rebuilding an index cannot require more space that the database size itself, right? Correct, in order to build the index, the engine must build and sort a projection of the index values. So, if you build an index on a string [say varchar(100)] column which is only sparsely populated (avg usage = 10 char) then it is reasonable/expected that the index will be much larger than the war data storage. Sean
