Someone on a different forum asked whether gbak restores wouldn't be faster if gbak backed up indexes so it didn't have to rebuild them.  The writer wanted the

clean-up and compaction that a gbak backup and restore provides, but not the

time spent retrieving records, sorting, and rebuilding indexes.

There's a problem with that.  An old index is of absolutely no use in a restored database exactly because records are in different places.  The index contains the physical location of records.   Change the location, invalidate the index.   That's the short answer.

The longer answer:

An index contains a key value and a "record id" also called a db-key. When an index search finds an acceptable approximation of the value desired, it uses the db-key to find the corresponding record. 

 (It's actually more complicated than that, and depends on whether the index is being used to optimize a sort, but in the end, an index search finds a db-key and uses it to find a record.)

What's in a db-key?  Three things: a pointer page sequence number, and offset into the pointer page, and an index offset on the data page.  A pointer page is a part of the structure of a table that's not visible outside the database.  It's simply a page that contains a vector of page numbers.  Those page numbers belong to the data pages in the table.  The RDB$PAGES table contains the sequence number and page number of pointer pages for all tables, among other things.  After a table has been referenced, the pointer page information is kept in memory. The pointer page may be in cache or Firebird may need to read it from disk.

With the pointer page in its virtual hand, Firebird reads the page number at the offset indicated by the db-key to get the page number of the appropriate data page and finds the data page in cache or on disk.  The offset on the data page in the db-key is an indirect pointer to the record.  Each data page has an index to the records on it containing the actual offset and length of the record, so a page can be reorganized without changing the db-keys of the records on it.

A gbak restore creates a new database and populates it with the data it backed up from the old database.  Records will be stored on different pages and at different offsets.  Pages that were part of one table in the old database may be part of a different table or some other structure in the new database.  

Gbak restore could be made faster if Firebird recognized at a commit that the transaction created several indexes on the same table and built all the indexes in parallel rather than reading the table for each index, but using an old index in a new database is not a good idea.

Good luck,

Ann

 

-- 


Atenciosamente,

Hugo Eyng