Physical Database Organization

1. Physical Database Organization

2. Introduction • Physical database organization and database access methods • Enterprise Storage subsystems and business continuity

3. Physical Database Organization and Database Access Methods • From database to tablespace • Index design • Database access methods • Join implementations

4. From Database to Tablespace • Physical user database is a collection of index files and data files • A tablespace can be considered as a physical container of database objects • Consists of one or more physical files which can be distributed over multiple storage devices • Every logical table is assigned to a tablespace to be persisted physically (stored table) • A stored table occupies one or more disk blocks or pages in the tablespace • Can contain indexes as well

5. From Database to Tablespace • Use of tablespaces has an impact on physical database design: • Intra-query parallelism: different subsets of the data can be searched in parallel in the context of a single, complex query • Inter-query parallelism: many simple queries can be executed in parallel • Physical database design comes down to attributing logical concepts to physical constructs • Joint responsibility of the database designer and DBA

6. From Database to Tablespace

7. Index Design • Indexes are probably the most important tuning instrument to the database designer and DBA

8. Index Design • Main reasons for index creation • Efficient retrieval of rows according to certain queries or selection criteria • Efficient performance of join queries • Enforce uniqueness on a column value or combination of column values • Logical ordering of rows in a table • Physical ordering of rows in a table

9. Index Design • No standard SQL syntax for index creation • Common syntax CREATE [UNIQUE] INDEX INDEX_NAME ONTABLE_NAME (COLUMN_NAME [ORDER] {, COLUMN_NAME [ORDER]}) [CLUSTER]

10. Index Design CREATE UNIQUE INDEX PRODNR_INDEXON PRODUCT(PRODNR ASC) CREATE INDEX PRODUCTDATA_INDEX ON PRODUCT(PRODPRICE DESC, PRODTYPE ASC) CREATE INDEX PRODNAME_INDEX ON PRODUCT(PRODNAME ASC) CLUSTER CREATE INDEX PRODSUPPLIER_INDEX ON PRODUCT(SUPPLIERNR ASC)

11. Index Design • Can only define one primary or clustered index per table but as many secondary indexes as desired • Every index also comes with a cost: • Storage capacity • Update overhead • Physical data independence: indexes can be added or deleted without affecting the logical data model or applications • Secondary indexes can be constructed or removed without affecting the actual data files

12. Database Access Methods • Functioning of the query optimizer • Index search (with atomic search key) • Multiple index and multicolumn index search • Index only access • Full table scan

13. Functioning of the Query Optimizer • SQL is a declarative query language • Different access paths exist to get to the same data, although their time varies • Cost-based optimizers calculate the optimal access plan according to a set of built-in cost formulas as well the table(s) involved in the query, the available indexes, the statistical properties of the data in the tables, etc. • Query processor assists in the execution of queries against the database and consists of a DML compiler, a query parser, a query rewriter, a query optimizer, and a query executor

14. Functioning of the Query Optimizer • DBMS maintains the following data in its catalog: • Table-related data: • Number of rows, number of disk blocks occupied by the table, number of overflow records associated with the table • Column-related data: • Number of different column values, statistical distribution of the column values • Index-related data: • Number of different values for indexed search keys and for individual attribute types of composite search keys, number of disk blocks occupied by the index, index type: primary/clustered or secondary • Tablespace-related data: • Number and size of tables in the tablespace, device-specific I/O properties of the device on which the table resides

15. Functioning of the Query Optimizer • Filter Factor (FF) represents the fraction of the total number of rows that is expected to satisfy a query predicate associated with an attribute type (e.g., CustomerID = 11349, Gender = M, Year of Birth ≥ 1970) • For queries over a single table, the expected query cardinality (QC) equals the table cardinality (TC) multiplied by the product of the filter factors of the respective search predicates in the query:QC = TC × FF1×FF2×… FFn • Default estimate for FFi is 1/NVi, with NVi representing the number of different values of attribute type Ai

16. Functioning of the Query Optimizer • Example: Customer Table • 10,000 rows (TC = 10000) • Query: SELECT CUSTOMERID FROM CUSTOMERTABLE WHERE COUNTRY = 'U.K.' AND GENDER = 'M' • Assume 20 countries and two genders: FFCountry = 0.05 and FFGender = 0.5 • QC = 10000 ×0.05 ×0.5 = 250

17. Index Search (with Atomic Search Key) • Single query predicate where the query involves a search key with only a single attribute type SELECT * FROM MY_TABLE WHERE MY_KEY >= 12 AND MY_KEY <= 24 • Index search using B+-tree

18. Index Search (with Atomic Search Key) • If the query involves only a single search key value, then hashing would be an efficient alternative • As to range queries, a primary or clustered index is even more efficient than a secondary index (sba versus rba)

19. Multiple Index and Multicolumn Index Search • If search key is composite and indexed attribute types are the same as attribute types in the search key, then a multicolumn index allows filtering out and retrieving only these data rows that satisfy the query • A search key with five attribute types, each with ten possible values, would yield a multicolumn index with 100,000 (= 105) entries. When using single-column indexes instead, there would be five indexes, each with ten entries, thus 50 entries in total • For queries involving an arbitrary subset of these attribute types, multicolumn index should have all attribute types involved in the query predicates in its leftmost columns

20. Multiple Index and Multicolumn Index Search

21. Multiple Index and Multicolumn Index Search • Multicolumn indexes are only appropriate for selected cases • e.g., queries that are executed very often or very time critical • An alternative is to use multiple single-column indexes, or indexes with fewer columns • Example: SELECT * FROM MY_TABLE WHERE A1 = VALUE1 AND A2 = VALUE2 AND … AND An = VALUEn Take intersection between the sets of pointers in the index entries that correspond to the desired values of Ai Note: Take union in case of OR!

22. Multiple Index and Multicolumn Index Search SELECT CUSTOMERID FROM CUSTOMERTABLE WHERE COUNTRY = 'U.K.' AND GENDER = 'M'

23. Multiple Index and Multicolumn Index Search • For queries with many predicates, a multicolumn index over all these predicates is the most efficient: its FF is equal to the query’s FF • Unfeasible if many attribute types are used in query predicates • As an alternative, multiple indexes can be combined • The more selective a query predicate’s FF, the more desirable it is to use the index on the corresponding attribute type • Keep in mind that ultimately the number of block accesses determines the performance, not the number of rows retrieved • Using primary index or clustered index may be more efficient than using a secondary index, especially for range queries

24. Index Only Access • Sometimes, the optimizer might be “lucky,” in the sense that the query can be executed based solely on information in the index • Example SELECT LASTNAME FROM CUSTOMERTABLE WHERE COUNTRY = 'U.K.' AND GENDER = 'M' • Index-only access if there exists a multicolumn index, or a combination of single column indexes, over the attribute types LastName, Country, and Gender • Note: the more attribute types are included in the index, the higher the negative performance impact of update queries!

25. Full Table Scan • If no index is available, then need to linearly search the entire table • For very small tables, or for queries that require nearly all of a table’s tuples anyway, a full table scan might actually be more efficient than using an index • The higher the query’s FF and/or the larger the table, the less efficient a full table scan will be

26. Join Implementations • Recap • Nested-loop join • Sort-merge join • Hash join

27. Recap • Database organization should also consider join queries • The general notation of an inner join between tables R and S is as follows: • The -operator specifies the join condition

28. Recap

29. Nested-Loop Join • One of the tables is denoted as the inner table and the other becomes the outer table • For every row in the outer table, all rows of the inner table are retrieved and compared to the current row of the outer table • If the join condition is satisfied, both rows are joined and put in an output buffer. • Inner table is traversed as many times as there are rows in the outer table • Mainly effective if the inner table is very small or if the internal data model provides facilities for efficient access to the inner table

30. Nested-Loop Join

31. Sort-Merge Join • Tuples in both tables are first sorted according to the attribute types involved in the join condition • Both tables are then traversed in this order, with the rows that satisfy the join condition being combined and put in an output buffer • Every table is traversed only once (↔Nested-Loop Join) • Appropriate if many rows in both tables satisfy the query predicates and/or if there exist no indexes over the join columns

32. Sort-Merge Join

33. Hash Join • Hashing algorithm is applied to join attribute type(s) for table R • The corresponding rows are assigned to buckets in a hash file • The same hashing algorithm is applied to the join attribute type(s) of the second table S • If a hash value for S refers to a non-empty bucket in the hash file, the corresponding rows of R and S are compared according to the join condition • If join condition is satisfied, the rows of R and S are joined and put in the output buffer • Performance of this approach depends on the size of the hash file and whether it can be kept in internal memory

34. Enterprise Storage Subsystems and Business Continuity • Disk arrays and RAID • Enterprise storage subsystems • Business continuity

35. Disk Arrays and RAID • Storage capacity of hard disk drives has increased but performance has stalled • More efficient to combine multiple smaller physical disk drives into one larger logical drive • Distributing data over multiple physical drives allows for parallel retrieval • A certain measure of data redundancy is introduced, mitigating the risk of failure

36. Disk Arrays and RAID • RAID is a technology in which standard HDDs are coupled to a dedicated hard disk controller (the RAID controller) to make them appear as a single logical drive

37. Disk Arrays and RAID • Techniques applied in RAID • Data striping • Subsections of a data file (strips) are distributed over multiple disks to be read and written in parallel • With n disks, bit or block i is written to disk (i mod n) + 1 • With bit-level data striping, a byte is split into eight individual bits, to be distributed over the available disks • With block-level data striping, each block is stored in its entirety on a single disk, but the respective blocks of the same file are distributed over the disks

38. Disk Arrays and RAID • Techniques applied in RAID (contd.) • Redundancy • Redundant data are stored to increase reliability • e.g., error detection and correcting codes such as Hamming codes or parity bits • Disk mirroring • For each disk there is an exact copy (mirror) containing the same data • Mirroring consumes much more storage space than error correcting codes

39. Disk Arrays and RAID • Multiple RAID configurations (RAID levels)

40. Disk Arrays and RAID

41. Disk Arrays and RAID

42. Disk Arrays and RAID • RAID level 0 is used if performance is more important than fault tolerance • RAID 1 is mostly used for very critical data (e.g., logfile of the DBMS) • RAID level 5 is quite popular overall, as it strikes a balance between read and write performance, storage efficiency, and fault tolerance

43. Enterprise Storage Subsystems • Overview and classification • DAS (directly attached storage) • SAN (storage area network) • NAS (network attached storage) • NAS gateway • iSCSI/storage over IP

44. Overview and Classification • Starting insights • Management cost of storage tends to exceed by far the purchasing cost • Need for more flexible, distributed storage architectures, which still offer centralized management capabilities • Modern enterprise storage subsystems often involve networked storage • Network topology provides for transparent any-to-any connectivity

45. Overview and Classification • Main characteristics • Accommodate high-performance data storage • Cater for scalability • Support data distribution and location transparency • Support interoperability and data sharing • Reliability and near-continuous availability • Protection against hard- and software malfunctions and data loss as well as hackers • Improve manageability and reduce management cost

46. Overview and Classification • Classification according to connectivity, medium, and I/O protocol • Connectivity • Refers to the way in which storage devices are connected to processors and/or servers • Direct versus network

47. Overview and Classification • Medium refers to the physical cabling and corresponding low-level protocol • SCSI (Small Computer Systems Interface) • Mainly used for high-performance and high-capacity workstations and servers • Involves two elements: command set to communicate with storage devices and specifications for a low-level protocol and cabling • Ethernet • Long-standing standard medium for LANs and WANs • Fiber Channel (FC) • Connect high-end storage systems to servers • Can be fiber optical cable or copper wire

48. Overview and Classification • I/O protocol denotes the command set to communicate with the storage device • Block-level I/O protocols • I/O commands are defined at the level of requests for individual blocks • Can be based on SCSI command set • File-level I/O protocols • Commands are defined at the level of requests for entire files • Protocol is device independent • e.g., Network File System (NFS, UNIX), Common Internet File System (CIFS, Windows), HTTP, FTP

49. DAS (Directly Attached Storage) • Storage devices are directly connected to individual servers • Block-level I/O protocol is used • Medium can be standard SCSI cable, fiber channel, or ethernet cable • No network is used for traffic between servers and storage devices • Simplest and least expensive solution • No capabilities for centralized storage management and sharing unused disk capacity • Vulnerable to hardware failures

50. DAS (Directly Attached Storage)