Comparison of File Organizations

Comparison of File Organizations

The files and indexes are organized according  to the  composite search key (age, sal) and that all selection operations are specified on these fields.

Different File Organizations are:

1. File of randomly ordered employee records, or heap file.
2. File of employee records sorted on (age, sal).
3. Clustered B+ tree file with search key (age, sal).
4. Heap file with an unclustered B+ tree index on (age, sal).
5. Heap file with an unclustered hash index on (age, sal).

Our goal is to emphasize the importance of the choice of an appropriate file organization.We can keep the records unsorted or sort them.

The operations we consider to organize a file are these:

A. Scan:

• Fetch all records in the file.
• The pages in the file must be fetched from disk into the buffer pool.
• There  is  also  a  CPU  overhead  per  record  for  locating  the  record  on  the page (in the pool).

B. Search with Equality Selection:

• Fetch all records that satisfy an equality selection.
• Example,  "Find  the  employee  record  for  the  employee  with age  23  and sal 50."

C. Search with Range Selection:

• Fetch all records that satisfy a range selection.
• Example, "Find all employee records with age greater than 35."

D. Insert a Record:

• Insert a given record into the file.
• We must identify  the  page  in the file  into which the  new  record must be inserted,  fetch  that  page  from disk, modify  it  to  include  the  new  record, and then write back the  modified page.
• Depending  on  the  file  organization,  we  may  have  to  fetch,  modify,  andwrite back other pages as well.

E. Delete a Record:

Delete a record that is specified using its rid.

We  must  identify  the  page  that  contains  the  record,  fetch  it  from  disk,

modify it, and write it back.

Depending  on  the  file  organization,  we  may  have  to  fetch,  modify,  and

write back other pages as well.