So.. just what goes on inside a database table?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Plusnet Community
- :
- Forum
- :
- Other forums
- :
- Tech Help - Software/Hardware etc
- :
- Re: So.. just what goes on inside a database table...
So.. just what goes on inside a database table?
17-09-2013 10:24 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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?
Re: So.. just what goes on inside a database table?
17-09-2013 10:33 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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
Re: So.. just what goes on inside a database table?
17-09-2013 11:12 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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
Re: So.. just what goes on inside a database table?
19-09-2013 3:49 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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?
"In The Beginning Was The Word, And The Word Was Aardvark."
Re: So.. just what goes on inside a database table?
22-09-2013 6:40 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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.
Re: So.. just what goes on inside a database table?
22-09-2013 7:10 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
It is, of course, extremely quick to update it.
"In The Beginning Was The Word, And The Word Was Aardvark."
Re: So.. just what goes on inside a database table?
22-09-2013 7:31 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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?
Re: So.. just what goes on inside a database table?
22-09-2013 8:08 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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.
Re: So.. just what goes on inside a database table?
22-09-2013 9:56 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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.
"In The Beginning Was The Word, And The Word Was Aardvark."
Re: So.. just what goes on inside a database table?
23-09-2013 11:38 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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?
Re: So.. just what goes on inside a database table?
23-09-2013 12:42 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
"In The Beginning Was The Word, And The Word Was Aardvark."
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Plusnet Community
- :
- Forum
- :
- Other forums
- :
- Tech Help - Software/Hardware etc
- :
- Re: So.. just what goes on inside a database table...