> There are some businesses rules that I have apply why loading data.
> Theto schema of the xml files is highly normalized to reduce the xml
> file size. Hence I load all the data into temporary tables via

external

> tables. These temporary tables are generic or thin tables, that all

the

> data that are present in the xml file i.e. for every element I

store the

> records. Then I move the records from these temp tables into the

main

> area applying the business rules in the procedures. I cannot go to

the

> low level design of the application here whose performance right

now is

> not much of an issue.

External tables are a different kettle of fish. You can not update or
delete the records in an external table (select or insert only).
There is no record versioning, which means you can fill and drop an
external table hundreds of times without impacting the size of the
database (if you are not doing anything else).

>
> My primary question was
>
> As said that Firebird will utilize the deleted (unused) space for
> storing other data. But please correct me, is this table specific

or is

> it database specific. I mean does Firebird utilize the deleted disk
> space of one table, for inserts or updates into another table.

Neither.

A database is not necessarily contained in a single file. The
database contains 'pages'

Generally (ie unless you set it to something else), 1 Page is 4096
bytes. A single page can be used to store either table data, index
data, blob data, generators etc.

The following link explains what can be stored in what page:
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_expert2

Firebird is a multi generational database. It is able to provide each
transaction with its own isolated view of the data. Transaction 2 can
delete, insert and update records, and Transaction 1 will still see
the version of the records before Transaction 2's changes. You
***really*** need to get this into your head if you work with
Firebird.

This is important because it means that, unlike a locking database,
Transaction 2 can't just go and change the contents of a record. It
must create a new 'record version' which contains the differences.
When Transaction 1 finally commits, and no other transaction can
possibly be interested in the original record version, garbage
collection occurs. That means that the two record versions are merged
back into a single record version, and the space it used to occupy is
marked as free to be re-used.

The mechanism that defines what can and can not be cleaned up is
described here:
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_expert4

Basically, Firebird only asks for more pages (ie increase the file
size), if it hasn't any spare it can use.

>To
> elaborate this consider the following process
>
> 1) Delete from table A -> this deletes say 40 MB of data. Hence I

have

> 40MB of unused space.
> 2) Some Inserts into Table B -> this inserts 20 MB of data but into
> Table B. Wil this utilize 20MB of the unused 40 MB?
> Please let me know this.

As you can see, it is not that simplistic, but conceptually yes it
could use a page that was formerly used to store 'table a' data.

Adam