Storage and Indexing DBS Database Systems Reading: R&G Chapters - PowerPoint PPT Presentation

Storage and Indexing DBS Database Systems Reading: R&G Chapters 8, 9 & 10.1 Implementing and Optimising Query Languages We typically store data in external (secondary) storage. Why? Becuase: Peter Buneman • Secondary storage is cheaper. £ 100 buys you 1gb of RAM or 100gb of disk (2006 figures) 9 November 2010 • Secondary storage is more stable. It survives power cuts and – with care – system crashes. DBS 4.1 Differences between disk and main memory Communication between disk and main memory A buffer pool keeps images of disk pages in main memory cache . • Smallest retrievable chunk of data: memory = 1 byte, disk = 1 page = 1kbyte (more or less) Also needed a table that maps between positions on the disk and positions in the cache • Access time (time to dereference a pointer): memory < 10 − 8 sec, disk > 10 − 2 sec. (in both directions) However sequential data , i.e. data on sequential pages, can be retrieved rapidly from disk. D i sk B uffe r poo l DBS 4.2 DBS 4.3

When a page is requested Storing tuples Tuples are traditionally stored contiguoulsy on disk. Three possible formats (at least) for • If page is already in pool present, return address. storing tuples: • If there is room in the pool, read page in and return address. • If no room, choose a frame for replacement. INT CHAR(4) VARCHAR(6) CHAR(6) – if current frame is dirty – it has been written to – write frame to disk. Fixed size • read page in and return address. Delimited % % % % Requesting process may pin page. Indicating that it “owns” it. Page replacement policy: LRU, MRU, random, etc. Pathological examples defeat MRU Offset array and LRU. DBS 4.4 DBS 4.5 Comments on storing tuples Placing Records on a Page Fixed format appears more efficient. We can “compile in” the offsets. But remember that We typically want to keep “pointers” or object identifiers for tuples. We need them if we DB processing is dominated by i/o are going to build indexes, and we’d like them to be persistent. Delimited can make use of variable length fields (VARCHAR) and simple compression (e.g. 7 deleting trailing blanks) Fixed and delimited formats require extra space to represent null values. We get them for free (almost) in the offset array representation. 4 3 1 2 3 4 1 Array of pointers Array of tuples DBS 4.6 DBS 4.7

Comments on page layouts File organization – unordered data Array of tuples suitable for fixed length records. Keep two lists: pages with room and pages with no room. • Object identifier is (page-identifier, index) pair. Full • Cannot make use of space economy of variable-length record. Free space Pointer array is suitable for variable length records • Object identifier is (page-identifier, pointer-index) pair. Variations: • Can capitalize on variable length records. • Records may be moved on a page to make way for new (or expanded) records. • Keep an array of pointers. • Order by amount of free space (for variable length tuples) These are called heap files. DBS 4.8 DBS 4.9 Other organizations I/O Costs We are primarily interested in the I/O (number of page reads and writes) needed to perform • Sorted files. Records are kept in order of some attribute (e.g. Id ). Records are assumed various operations. Assume B pages and that read or write time is D to be fixed-length and “packed” onto pages. That is, the file can be treated as an array of records. Scan Eq. Search Range Search Insert Delete • Hashed files. Records are kept in an array of pages indexed by some hash function Heap BD 0 . 5 BD BD 2D Search + D applied to the attribute. Naive example: D log 2 B + m ∗ D log 2 B Search + BD Search + BD Sorted BD Hashed 1 . 25 BD D 1 . 25 BD 2 D Search + D Hash function = id mod 100 100 pages . . . . . . ∗ m = number of matches Assumes 80% occupancy of hashed file Page 34 Tuple with id = 1234 DBS 4.10 DBS 4.11

Indexing – Introduction Example. Hash indexes and files Index is a collection of data entries with efficient methods to locate, insert and delete data entries. Hashed files and sorted files are simple examples of indexing methods, but they don’t do Smith 44 3000 3000 all of these efficiently. Jones 40 6003 3000 Tracy 44 5004 5004 We index on some key . 5004 sal age Ashby 25 3000 sal mod 4 age mod 4 Note the index key is not (necessarily) the “key” in the database design sense of the term. Basu 33 4003 4003 Bristow 29 2007 2007 We can only organize a data file by one key, but we may want indexes on more than one 6003 Cass 50 5004 6003 key. Daniels 22 6003 file of record id / sal pairs hashed on age File hashed on age DBS 4.12 DBS 4.13 Indexes are needed for optimization What an index can provide Given a key k , an index returns k ∗ where k ∗ is one of three things: How are these queries helped by the presence of indexes? 1. A data record (the tuple itself) with the search key value k SELECT * SELECT * 2. A pointer to a record with search key k together with k . FROM Employee FROM Employee 3. A list of pointers to records with search key k together with k . WHERE age = 33 WHERE age > 33 SELECT * SELECT * FROM Employee FROM Employee WHERE sal = 3000 WHERE sal > 3000 DBS 4.14 DBS 4.15

Clustered vs. Unclustered Indexes Tree indexing If we use tree indexing (to be described) we can exploit the ordering on a key and make Why not use the standard search tree indexing techniques that have been developed for range queries efficient. main memory data (variations on binary search trees): AVL trees, 3-3 trees, red-black trees, etc? An index is clustered if the data entries that are close in this ordering are stored physically 10 6 tuples (common) log 2 (10 6 ) = 20 – order 1 close together (i.e. on the same page). Reason: binary search is still slow. second because “dereferencing” a pointer on disk takes between 0 . 01 and 0 . 1 seconds. UNC L U S T E R E D C L U S T E R E D Solution: 1. Use n -ary trees rather than binary. 2. Keep only keys and pointers at internal nodes. Leaves can be data values (either D a t a e n t r i e s D a t a e n t r i e s records or record-id/key-value pairs) ( I nd e x F il e ) ( D a t a f il e ) D a t a R ec o r d s D a t a R ec o r d s DBS 4.16 DBS 4.17 Range Search ISAM Example of point (2). We can speed up ordinary binary search on a sorted array by keeping ISAM = Indexed Sequential Access Method : a search tree whose nodes contain m keys indexes and page pointers in a separate file. and m + 1 pointers. m is chosen to “fill out” a page. A “pointer” is a page-id. The “index file” will typically fit into cache. The pointer p i between keys k i − 1 and k i points to a subtree whose keys are all in the range k i − 1 < k < k i . I nd e x F il e k N k 1 k 2 P0 K 1 P 1 K2 P mP K m 2 D a t a F il e P a g e N P a g e 1 P a g e 3 N o n − l e a f P a g e 2 P a g e s Consider queries such as or SELECT * FROM Employee L e a f WHERE 20 < Sal AND Sal < 30 P a g e s O v e r f l o w p a g e “Find all employees whose name begins with ‘Mac’.” (also a range search) P r i m a r y p a g e s DBS 4.18 DBS 4.19

How ISAM works A simple ISAM example This is not realistic. The example is only a 3-ary tree. In practice one might have 100-way • Create file(s): Data entries are sorted. Leaf data pages are allocated sequentially. Index branching. is constructed. Space for overflow pages is allocated. • Find an entry (search). Obvious generalisation of method for binary search tree. Note that we can perform an ordered traversal of the data entries by a traversal of the – If pages are large, we can also do binary search on a page, but this may not be worth index. the effort. I/o costs dominate! • Insert an item. Find leaf data page (search) and put it there. Create overflow page if R oo t needed. 40 • Delete and item. Find leaf data page (search) and remove it. Maybe discard overflow page. 20 33 51 63 Note. In ISAM, the index remains fixed after creation. It is easy to construct pathological examples which make ISAM behave badly. 10 * 15 * 20 * 27 * 33 * 37 * 40 * 46 * 51 * 55 * 97 * 63 * DBS 4.20 DBS 4.21 ISAM after inserts ISAM after deletes Inserting 23 ∗ , 48 ∗ , 41 ∗ , 42 ∗ Deleting 42 ∗ , 51 ∗ R oo t Note that 51 appears as a key but no longer as a leaf value. 40 I nd e x R oo t P a g e s 40 20 33 51 63 20 33 51 63 P r i m a r y L e a f 10 * 15 * 20 * 27 * 33 * 37 * 40 * 46 * 51 * 55 * 63 * 97 * 10 * 15 * 20 * 27 * 33 * 37 * 40 * 46 * 55 * 63 * P a g e s 48 * 41 * 23 * 41 * O v e r f l o w 23 * 48 * P a g e s Main problem with ISAM: index is fixed . Can become worse than useless after a long series 42 * of inserts and deletes. DBS 4.22 DBS 4.23