Chap19.pdf - CSC 4710 CSC 6710 Database Systems Rao Casturi*Material based on Fundaments of DATABASE SYSTEMS 7th Edition(Elmasri Navathe Additional

Chap19.pdf - CSC 4710 CSC 6710 Database Systems Rao...

This preview shows page 1 - 12 out of 53 pages.

CSC 4710 / CSC 6710 Database Systems Rao Casturi *Material based on Fundaments of DATABASE SYSTEMS 7 th . Edition (Elmasri & Navathe) ** Additional comments, slides are added by Rao Casturi
Query Optimization The process of choosing a suitable execution strategy for processing a query As you might guess, this is closely tied to relational algebra RA is the procedural formal language which specifies the ORDER of the operations HOW to retrieve the query results Rao Casturi - GSU - Database Systems 2
Query Optimization Two internal representations of a query: Query Tree Directly represents order of execution/events in the processing of a query Related to RA Query Graph Neutral select-project-join representation of a query Does not indicate a particular order to perform operations Related to TRC (Tuple Relational Calculas) (Not used for optimization any longer) Rao Casturi - GSU - Database Systems 3
Secondary Data Store Rao Casturi - GSU - Database Systems 4 (a) A single-sided disk with read/write hardware. (b) A disk pack with read/write hardware.
Tracks and Sectors Rao Casturi - GSU - Database Systems 5 Different sector organizations on disk. (a) Sectors subtending a fixed angle. (b) Sectors maintaining a uniform recording density.
Data Record Layout Rao Casturi - GSU - Database Systems 6
File Structures Files of Unordered Records (Heap Files) Files of Ordered Records (Sorted Files) Physically order the records of a file on disk based on the values of one of their field called the ordering field . This leads to an ordered or sequential file. If the ordering field is also a key field of the file—a field guaranteed to have a unique value in each record—then the field is called the ordering key for the file. Rao Casturi - GSU - Database Systems 7
Ordered File Structure Rao Casturi - GSU - Database Systems 8 Some blocks of an ordered (sequential) file of EMPLOYEE records with Name as the ordering key field. Inserting and Deleting are expensive in Ordered File structure Another primary file organization is called Hashing Technique or Hash File. Provides very fast access to records under certain search conditions. This organization is usually called a hash file. Hash Field (Single field) Hash Key The idea behind hashing is to provide a function h , called a hash function or randomizing function , which is applied to the hash field value of a record and yields the address of the disk block in which the record is stored. A search for the record within the block can be carried out in a main memory buffer. For most records, we need only a single-block access to retrieve that record.