Primary File Organization in DBMS - Files of ordered records (sorted files)



  • "Fundamental of Database Systems", 3rd ed. by Elmasri and Navathe, page 136 says:

    "We can physically order the records of a file on disk based on the values of one of their fields [...]"

    How could this "physical ordering" possibly enforced, especially with regard to fragmentation? Most databases use system calls to store their data in the file system of the OS, only very few are capable of doing raw i/o.



  • I think it's just a sloppy turn of the phrase by the book authors. Clearly, if you don't do raw I/O (which, as you say, few modern databases do), you have limited control over how your records are placed on disk.

    DBMSes minimise the effect of potential fragmentation of database files by the operating system using these methods:

    • Space preallocation. Many DBMSes can be configured to request space allocation in chunks (terminology differs, but they are often referred to as "extents") much larger than the atomic I/O units ("pages" or "blocks"), thus reducing the scale of database file fragmentation.
    • Data file placement. Using the mechanism often called "tablespaces", or simply data file directories, one can place tables for which space allocation characteristics are deemed critical onto separate file systems, where they would be able to grow without interference from other processes.
    • I/O buffers. This is by far the most useful and important method. A well-tuned database would rarely wait for a synchronous I/O operation to read data from disk; all the data pages it needs would be available in memory, often in less fragmented form, by means of the file system cache and/or database buffer pool. Often they employ separate threads that "prefetch" or "read-ahead" the data file blocks before they are actually needed for query processing.

    See also https://en.wikipedia.org/wiki/File_system_fragmentation#Mitigation on Wikipedia.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2