BISQL – Laymen to SQL Developer # 18 – Record Storage and Primary File Organization #3 – Operation on Files, Files of Unordered Records (Heap Files), Files of ordered records [sorted files]
Hi Folks,
This post is part of Series Database Management Systems
Currently running topic for this series is listed as below :
Series of Database Management Systems
>>Chapter 1 : DBMS [Database Management Systems]
>>Chapter 2 : Database Core Concepts and Applications
>>Chapter 3 : Record Storage and Primary File Organization <You are Here>
Continuing from my previous post on this series, If you have missed any link please visit link below
We are going to Cover the Following Points in this article
- Operation on Files
- Files of Unordered Records (Heap Files)
- Files of ordered records [sorted files]
Operation on Files
Operations on files are usually grouped into retrieval operations and update operations such as insertion or deletion of records or by modification of field values.
Ø Find (or locate): Searches for the first record satisfying search condition.
Ø Read (or Get): copies the current record from the buffer to a program variable.
Ø Find Next: Searches for the next record.
Ø Delete: Deletes the current record
Ø Modify: Modifies some field values for the current record.
Ø Insert: Inserts a new record into the file.
Files of Unordered Records (Heap Files)
In the simplest and most basic type of organization, records are placed in the file in the order in which they are inserted, and new records are inserted at the end of the file. Such an organization is called a heap or pile file.
Inserting a new record is very efficient: the last disk block of the file is copied into a buffer; the new record is added; and the block is then rewritten back to the disk. However, searching for a record using linear search is an expensive procedure.
To delete a record, a program must first find it, copy the block into a buffer, then delete the record from the buffer, and finally rewrite the block back to the disk. This leaves extra unused space in the disk block. Another technique used for record deletion is a marker stored with each record. A record is deleted by setting the deletion marker to a certain value. A different value of the marker indicates a valid (not deleted) record. Search programs consider only valid records in a block when conducting their search. Both of these deletion techniques require periodic reorganization of the file. During reorganization, records are packed by removing deleted records.
Files of ordered records [sorted files]
We can physically order the records of a file on disk based on the values of one of their fields-called the ordering field. If the ordering field is also a key field of the file, a field guaranteed to have a unique value in each record, then the field is also called the ordering key for the file.
Ordered records have some advantages over unordered files. First, reading the records in order of the ordering field values becomes extremely efficient, since no sorting is required. Second, finding the next record in an ordering field usually requires no additional block accesses, because the next record is in the same block as the current one [unless the current record is the last one in the block]. Third, using a search condition based on the value of an ordering key field results in faster access when the binary search technique is used.
Figure 3.4: Some blocks of an ordered (UNspanned) file or EMPLOYEE records with NAME as the ordering key field.
Inserting and deleting records are expensive operations for an ordered file because the records must remain physically ordered. To insert a new record; we must find its correct position in the file, based on its ordering field value, and then make space in the file to insert the record in that position. For a large file this can be very time consuming. For record deletion the problem is less severe if we use deletion markers and reorganize the file periodically.
Hope you will like Series of Database Management Systems series !
If you have not yet subscribe this Blog , Please subscribe it from “follow me” tab !
So that you will be updated @ real time and all updated knowledge in your mail daily for free without any RSS subscription OR news reading !!
Happy Learning and Sharing !!
For More information related to BI World visit our all Mentalist networks Blog
SQL Server Mentalist … SQL Learning Blog
Business Intelligence Mentalist … Business Intelligence World
Microsoft Mentalist … MVC,ASP.NET, WCF & LinQ
MSBI Mentalist … MS BI and SQL Server
NMUG Bloggers …Navi Mumbai User Group Blog
Architectural Shack … Architectural implementation and design patterns
DBA Mentalist …Advance SQL Server Blog
MVC Mentalist … MVC Learning Blog
Link Mentalist … Daily Best link @ your email
Infographics Mentalist … Image worth explaining thousand Words
Hadoop Mentalist … Blog on Big Data
BI Tools Analysis … BI Tools
Connect With me on
| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS | About Me |
SQL Server Mentalist 