File Organizations and Indexing

1. File Organizations and Indexing "If you don't find it in the index, look very carefully through the entire catalogue." -- Sears, Roebuck, and Co., Consumer's Guide, 1897 Lecture 4 R&G Chapter 8

2. Review: Memory, Disks, & Buffer Mgt • Everything won’t fit in RAM (usually) • Hierarchy of storage, RAM, disk, tape • “Block” - unit of storage on disk • “Frame” – a block-sized chunk of memory • Allocate space on disk for fast access • Buffer pool management • Frames in RAM to hold blocks • Policy to move blocks between RAM & disk

3. Query Optimization and Execution Relational Operators Files and Access Methods Buffer Management Disk Space Management DB Context

4. Files of Records • Blocks interface for I/O, but… • Higher levels of DBMS operate on records, and files of records. • FILE: A collection of pages, each containing a collection of records. Must support: • insert/delete/modify record • fetch a particular record (specified using record id) • scan all records (possibly with some conditions on the records to be retrieved) • Note: typically page size = block size = frame size.

5. Record Formats: Fixed Length • Information about field types same for all records in a file; stored in systemcatalogs. • Finding i’th field done via arithmetic. F3 F4 F1 F2 L3 L4 L1 L2 Address = B+L1+L2 Base address (B)

6. Record Formats: Variable Length • Two alternative formats (# fields is fixed): F1 F2 F3 F4 $ $ $ $ Fields Delimited by Special Symbols F1 F2 F3 F4 Array of Field Offsets • Second offers direct access to i’th field, efficient storage • of nulls(special don’t know value); small directory overhead.

7. Page Formats: Fixed Length Records Slot 1 Slot 1 Slot 2 Slot 2 • Record id = <page id, slot #>. In first alternative, moving records for free space management changes rid; may not be acceptable. Free Space . . . . . . Slot N Slot N Slot M . . . N 1 1 1 M 0 M ... 3 2 1 number of slots number of records PACKED UNPACKED, BITMAP

8. “Slotted Page” for Variable Length Records Data Rid = (i,N) Page i Rid = (i,2) • Record id = <page id, slot #> • Can move records on page without changing rid; so, attractive for fixed-length records too. • Page is full when data space and slot array meet. Rid = (i,1) N Pointer to start of free space 16 24 20 N . . . 2 1 # slots Slot Array SLOT DIRECTORY • Q: What if a record grows too big to fit in the page???

9. System Catalogs • For each relation: • name, file location, file structure (e.g., Heap file) • attribute name and type, for each attribute • index name, for each index • integrity constraints • For each index: • structure (e.g., B+ tree) and search key fields • For each view: • view name and definition • Plus statistics, authorization, buffer pool size, etc. • Catalogs are themselves stored as relations!

10. Attr_Cat(attr_name, rel_name, type, position)

11. Alternative File Organizations Many alternatives exist, each good for some situations, and not so good in others: • Heap files:Suitable when typical access is a file scan retrieving all records. • Sorted Files:Best for retrieval in search key order, or only a `range’ of records is needed. • Clustered Files (with Indexes): A compromise between the above two extremes.

12. Unordered (Heap) Files • Simplest file structure contains records in no particular order. • As file grows and shrinks, disk pages are allocated and de-allocated. • To support record level operations, we must: • keep track of the pages in a file • keep track of free spaceon pages • keep track of the records on a page • There are many alternatives for keeping track of this. • We’ll consider 2

13. Heap File Implemented as a List • The header page id and Heap file name must be stored someplace. • Database “catalog” • Each page contains 2 `pointers’ plus data. Data Page Data Page Data Page Full Pages Header Page Data Page Data Page Data Page Pages with Free Space

14. Data Page 1 Header Page Data Page 2 Data Page N DIRECTORY Heap File Using a Page Directory • The entry for a page can include the number of free bytes on the page. • The directory is a collection of pages; linked list implementation is just one alternative. • Much smaller than linked list of all HF pages! • Q: How to find a particular record in a Heap file???

15. Cost Model for Analysis We ignore CPU costs, for simplicity: • B: The number of data blocks • R: Number of records per block • D: (Average) time to read or write disk block • Measuring number of block I/O’s ignores gains of pre-fetching and sequential access; thus, even I/O cost is only loosely approximated. • Average-case analysis; based on several simplistic assumptions. • Often called a “back of the envelope” calculation. • Good enough to show the overall trends!

16. Some Assumptions in the Analysis • Single record insert and delete. • Equality selection - exactly one match (what if more or less???). • Heap Files: • Insert always appends to end of file. • Delete just leaves free space in the page. • Sorted Files: • Files compacted after deletions. • Selections on search key.

17. B: The number of data pages • R: Number of records per page • D: (Average) time to read or write disk page Cost of Operations BD 0.5 BD BD 2D (0.5B+1)D

18. Sorted Files • Q: When do Heap files perform well? When don’t they? • Heap files are lazy on update - you end up paying on searches. • Sorted fileseagerly maintain the file on update. • The opposite choice in the trade-off • Let’s consider an extreme version • No gaps allowed, pages fully packed always • Q: How might you relax these assumptions?

19. B: The number of data pages • R: Number of records per page • D: (Average) time to read or write disk page Cost of Operations BD (log2 B) * D [(log2 B) +#match pg]*D ((log2B)+B)D (because rd,w0.5 File) Same cost as Insert BD 0.5 BD BD 2D (0.5B+1)D

20. Indexes: avoiding the extremes • Hash files are great for lots of updates and scans. • Sorted files are great for lots of “rifle-shot” look ups on one particular search key. • Q: How do they do on look ups on other fields?? • Q: Is there a “goldilocks” solution???? • Clustered files are “sort of sorted”. • Need additional structure to help find things. • A Primary Index provides such structure.

21. Index Overview • An Index is a collection of “data entries” plus a way to quickly find entries with given key values. • Two main families of indexes: Hash and Tree • Hash-based indexes only good for equality search. • Tree-based indexes best for range search; also good for equality search. (Files rarely kept sorted in practice; B+ tree index is better.) • Primary index is associated with file structure. • can have at most one per file • Can have any number of additional Secondary Indexes. • These can speed up other “Access Paths”.

22. File Structure Summary • File Layer manages access to records in pages. • Record and page formats depend on fixed vs. variable-length. • Free space management is an important issue. • Slotted page format supports variable length records and allows records to move on page. • Many alternative file organizations exist, each appropriate in some situation. • We looked at Heap and Sorted so far. • If selection queries are frequent, sorting the file or building an index is important. • Back of the envelope calculations are imprecise, but can expose fundamental systems tradeoffs. • A technique that you should become comfortable with! • Next up: Indexes.