1 / 14

Revision and Exam Briefing

Revision and Exam Briefing. M. Akhtar Ali School of CEIS. Agenda. Query optimization Object-relational and object-oriented databases. Query optimization. SQL Query: SELECT C.fName, C.lName, P.street, P.city, P.rooms, P.rent FROM PropertyForRent P, Client C, Viewing V

isaac-curry
Download Presentation

Revision and Exam Briefing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Revision and Exam Briefing M. Akhtar Ali School of CEIS

  2. Agenda • Query optimization • Object-relational and object-oriented databases

  3. Query optimization • SQL Query: SELECT C.fName, C.lName, P.street, P.city, P.rooms, P.rent FROM PropertyForRent P, Client C, Viewing V WHERE P.propertyNo = V.propertyNo AND V.clientNo = C.clientNo AND V.viewDate = ‘10-JAN-2004’ AND P.city = ‘Newcastle’ AND P.type = ‘F’ AND C.maxRent > 595

  4. Query optimization … • The Relational Algebra Query Tree

  5. Query optimization: Information Assumed * The page size for the database is 2048 bytes. * The size of the PropertyForRenttable is 5000 pages There are 90,000 records;Each record is of 112 bytes; 18 records occupy one page on disk; and log 2 5000 = 12 * The size the Clienttable is 4500 pages There are 103,500 records; Each record is of 88 bytes; 23 records occupy one page on disk; and log 2 4500 = 12 * The size the Viewingtable is 5000 pages There are 145,000 records; Each record is of 70 bytes; 29 records occupy one page on disk; and log 2 5000 = 12

  6. Query optimization: Information Assumed • There are 5800 records in the Viewingtable with viewDate = ‘10-JAN-2004’. • There are 31,500 records in the PropertyForRent table with type = ‘F’. • There are 1800 records in the PropertyForRent table with city = ‘Newcastle’, however, only 900 of these properties are flats (i.e. with type = ‘F’). • There are 20,700 records in the Client table with maxRent > 595. • The result of the SQL query contains 2700 records and the size of each record is 146 bytes. • The number of buffer pages available during query processing is 5 i.e. B = 5. • Hash indexes (clustered) are available on city and type attributes of PropertyForRent table. • B+ tree index (clustered) is available on maxRent attribute of Client table. • The Viewing table is sorted in ascending order of viewDate. • The join algorithms used by the DBMS for evaluating joins are: page-orientednested and block-nested loops. Also note that you should consider the cost of writing to temporary tables for intermediate results. • While calculating the evaluation cost of the query, ignore the cost of writing out the final result.

  7. Query optimization: Questions and Answers • Apply logical query optimisation (i.e. transformation/re-writing rules and algebraic equivalence) to the relational algebra query tree and draw the logically optimised query tree thus obtained. Initial Query Tree Logically Optimized Query Tree

  8. Query optimization: Questions and Answers . . . • Re-draw the query tree from part (a) and devise a physical plan for executing the relational operations involved in the tree.

  9. Query optimization: Questions and Answers . . . • Compute the cost for evaluating the physical plan in terms of I/O operations. • The cost of Selection over PropertyForRent (using Hash Index on city attribute) • Let H = 1, is the constant cost associated with Hash index to find out the RIDs (record identifiers) of the qualifying tuples. • The number of qualifying tuples = 1800 • The number of data pages read = 1800 / 18 = 100 • The cost of this selection = H + 100 = 101 I/Os • After reading each data page the condition type = ‘F’ is tested on each tuple. All tuples for which this condition is true are written to T1. • The number of tuples in the result of 2nd round of selection = 900 • The cost of writing to T1 = 900 / 18 = 50 I/Os • Subtotal cost = 101 + 50 = 151 I/Os • The size of temp T1 = 50 pages

  10. Query optimization: Questions and Answers . . . • The cost of Selection over Client (using B+ tree index on maxRent) • Let BT = 2, the height of B+ tree index.The number of qualifying tuples = 20700 • The number of pages read = 20700 / 23 = 900 • Cost of selection = BT + 900 = 902 I/Os • The cost of writing out the result to temp T2 = 900 I/Os. • Subtotal cost = 902 + 900 = 1802 I/Os • The size of temp T2 = 900 pages

  11. Query optimization: Questions and Answers . . . • The cost of Selection over Client (using B+ tree index on maxRent) • Let BT = 2, the height of B+ tree index.The number of qualifying tuples = 20700 • The number of pages read = 20700 / 23 = 900 • Cost of selection = BT + 900 = 902 I/Os • The cost of writing out the result to temp T2 = 900 I/Os. • Subtotal cost = 902 + 900 = 1802 I/Os • The size of temp T2 = 900 pages • The cost of Selection over Viewing (using Binary search because the table is sorted on viewDate) • Let VM = 5000 (the number of pages, the size of Viewing table) • Let BS (be the average cost of using Binary Search) = log2 5000 = log 5000 / log 2 = 12.29 » 12 • The number of qualifying tuples = 5800 • The number of pages read = 5800 / 29 = 200 • Cost of selection = BS + 200 = 212 I/Os • The cost of writing out the result to temp T3 = 200 I/Os. • Subtotal cost = 212 + 200 = 412 I/Os • The size of temp T3 = 200 pages

  12. Query optimization: Questions and Answers . . . • The cost of Join between T2 and T3 • Given B = 5 (buffer pages) • The Cost of Join = size of T3 + size of T2 * (size of T3 / B – 2) = 200 + 900 * (200 / 3) = 60,200 I/Os. • The size of the join result is computed as follows: • The join will result in 5800 tuples, size of each tuple will be 70 + 88 = 158 bytes • Tuples per page = 2048 / 158 = 12, • Size in pages = 484 • The cost of writing to T4 = 484 I/Os • Subtotal cost = 60200 + 484 = 60,684 I/Os. • The cost of Join between T1 and T4 • The Cost of Join = size of T1 + size of T4 * (size of T1 / B – 2) = 50 + 484 * (50 / 3) = 50 + 484 * 17 = 8278 I/Os. • The overall cost = 151 + 1802 + 412 + 60684 + 8278 = 71,327 I/Os

  13. OO Databases • Questions like: • Using ODL, provide an object-oriented representation of some classes/tables in the given UML class diagram / relational schema. • Questions usually need a clear understanding of how to map / implement classes, attributes, associations and inheritance from UML onto the ODMG ODL or how to implement relations using OO database design and ODL. • For this you should study past exam papers. • Lecture material and seminars for weeks 10-12. • Compare and contrast object-oriented representations with relational equivalent. • This should be specific to the question, and classes etc you have written in ODL and their equivalent relational counterpart given in the scenario. • Answering a query in SQL and OQL and comparison and contrasting.

  14. OR Databases • Questions like: • Using Oracle 9i, provide an object-relational representation of some classes in the given UML class diagram. • Study past exam papers. • Material covered on object-relational databases. • Compare and contrast object-relational representations with relational equivalent. • This should be specific to the question, and classes etc you have written in OR SQL and their equivalent relational counterpart given in the scenario. • Answering a query OR SQL over the OR representations.

More Related