So.. just what goes on inside a database table?

[#1]

This is really for the low level coders out there..
I'm curious how a database table (and more specifically single file databases) grow and how their content can be isolated and read out of the file so quickly.
Lets say I've put several 2GB files inside a table with lots of other information. I decide to edit one field (lets say the filename - which increases in length from 7 characters to 10 characters). I'm interested to know how this occurs without having to move loads of data. Now I know you can use varchar fields which will allow the database to save a structure of a fixed size containing all the fields but if the field is a variable size (eg text) then how does it adjust the value without having to move everything?
More to the point, how can there be other tables inside the same file that can also grow and have adjustments made to them without lengthy and time consuming file-re-write operations?
I read somewhere last night about ISAM using 'nodes' but even so if you have a node followed by data then how do you add extra information to the node without overwriting the following data?

[#2]

A lot of database speed is down to indexing - this allows a certain record in a table to be located very quickly.
To give you an idea, the last company I worked for had a 2Gb database - this was pretty big back in the day. To do a search on the largest table with a text string, it could take 20-30 minutes. To do the same on an indexed table would take fractions of a second.
The indexes however take up space in the database.
I remember adding a field on one of the biggest tables and knocking out the whole business for a couple of days while the database table - I wasn't popular for a while after that

[#3]

Quote from: picbits

A lot of database speed is down to indexing - this allows a certain record in a table to be located very quickly.

Yeah I'm aware of that, what I'm trying to get clear in my head though is how something like the index can be updated without moving huge chunks of data around to make space for it.

Quote from: picbits

The indexes however take up space in the database.

But how is the index stored and updated once other data has been inserted? - Is it spread over several chunks / clusters like a file on a HDD? Does the system literally grab the existing data in the file and move it to make space? (and if it does that how can it move GB's of data further along a file stream so quickly?)

Quote from: picbits

I remember adding a field on one of the biggest tables and knocking out the whole business for a couple of days while the database table - I wasn't popular for a while after that

lol I'll bet they loved you!
I reorganised one of the tables in my DB recently. Forgot the table was on my real PC and the management program was in a virtual machine with a 10Mb virtual network connection.. 40 minutes later 

[#4]

Indexes are often held in a tree structure, sometimes distributed over the data space space.
If you insert a lot of records in one place, a single leaf is typically split to allow the extra records to be referenced.
This does not require that the index is held as sequential data.
BTW Is ISAM still a going concern?

[#5]

learnt very early on some Database programs if your doing a global update, to drop the index before the update, run the update sequentially, then rebuild the index after the update.
MapInfo products are specifically bad, a simple global update command will drop the index, update a single record rebuild the whole index then move onto the next record and repeat the process. Far quicker to physically drop the index before the global update, then rebuild after.

[#6]

Some databases allow you to have an in-memory index which you build once, on opening a medium sized database.
It is, of course, extremely quick to update it.

[#7]

I really don't care to be honest.
What I want to know and what no-one will directly tell me is this:
When you have a big fat table, lets say you pick a record in the middle- say the 500th record out of 1000. You then want to insert a large blob in the form of a file.
What happens when it does this? - Does the DB move bits and bytes of data inside the file to make space, does it create two filestreams, put the file in the second and then add the rest of the table back on as a 3rd stream?

[#8]

The reason there's been no direct answer is that it all depends on the database, how the table is created, and numerous other factors.
If the database uses fixed size rows, it would have allocated a sufficient  size block to take the blob.
More likely,  the blob field would be a reference to a part of the database where the new data is appended.
Its unlikely any database would actually need to move other data to accommodate the insertion of the blob.

[#9]

The data is not held sequentially on the disk.
It may be scattered anywhere.
If you traverse the index sequentially, you can pick up the data sequentially, regardless of where it is held.
You could (if you wanted) run a long program which caused the data to be in the same physical order as the index.
You would only do this if you were prone to accessing your database sequentially. 

[#10]

Oh ok thats starting to make more sense..
What about the index's though.. presumably when you add more data to them you need more space right? - So where does that miraculously come from?

[#11]

http://www.bluerwhite.org/btree/ (for example)...