CSC 4710 / CSC 6710Database SystemsRao Casturi*Material based on Fundaments of DATABASE SYSTEMS 7th. 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 resultsRao Casturi - GSU - Database Systems2
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 Systems3
Secondary Data StoreRao Casturi - GSU - Database Systems4(a) A single-sided disk with read/write hardware.(b) A disk pack with read/write hardware.
Tracks and SectorsRao Casturi - GSU - Database Systems5Different sector organizations on disk. (a) Sectors subtending a fixed angle. (b) Sectors maintaining a uniform recording density.
Data Record LayoutRao Casturi - GSU - Database Systems6
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 Systems7
Ordered File StructureRao Casturi - GSU - Database Systems8Some blocks of an ordered (sequential) file of EMPLOYEE records with Name as theordering key field.•Inserting and Deleting are expensive in Ordered File structure•Another primary file organization is called Hashing Techniqueor 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.