Comparison of Partitioning Techniques Evaluate how well partitioning techniques support the following types of data access: 1.Scanning the entire relation. 2.Locating a tuple associatively-point queries. E.g,KA=25. 3.Locating all tuples such that the value of a given attribute lies within a specified range-range queries. E.g.,10≤rA<25. Database System Concepts-5th Edition,Aug 22,2005. 21.7 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 21.7 ©Silberschatz, Korth and Sudarshan th Edition, Aug 22, 2005. Comparison of Partitioning Techniques Evaluate how well partitioning techniques support the following types of data access: 1.Scanning the entire relation. 2.Locating a tuple associatively – point queries. E.g., r.A = 25. 3.Locating all tuples such that the value of a given attribute lies within a specified range – range queries. E.g., 10 r.A < 25
Comparison of Partitioning Techniques (Cont.) Round robin: Advantages Best suited for sequential scan of entire relation on each query. All disks have almost an equal number of tuples;retrieval work is thus well balanced between disks. Range queries are difficult to process No clustering--tuples are scattered across all disks Database System Concepts-5th Edition,Aug 22,2005. 21.8 @Silberschatz,Korth and Sudarshan
Database System Concepts - 5 21.8 ©Silberschatz, Korth and Sudarshan th Edition, Aug 22, 2005. Comparison of Partitioning Techniques (Cont.) Round robin: Advantages Best suited for sequential scan of entire relation on each query. All disks have almost an equal number of tuples; retrieval work is thus well balanced between disks. Range queries are difficult to process No clustering -- tuples are scattered across all disks
Comparison of Partitioning Techniques(Cont.) Hash partitioning: Good for sequential access Assuming hash function is good,and partitioning attributes form a key,tuples will be equally distributed between disks Retrieval work is then well balanced between disks. Good for point queries on partitioning attribute Can lookup single disk,leaving others available for answering other queries. Index on partitioning attribute can be local to disk,making lookup and update more efficient No clustering,so difficult to answer range queries Database System Concepts-5th Edition,Aug 22,2005. 21.9 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 21.9 ©Silberschatz, Korth and Sudarshan th Edition, Aug 22, 2005. Comparison of Partitioning Techniques(Cont.) Hash partitioning: Good for sequential access Assuming hash function is good, and partitioning attributes form a key, tuples will be equally distributed between disks Retrieval work is then well balanced between disks. Good for point queries on partitioning attribute Can lookup single disk, leaving others available for answering other queries. Index on partitioning attribute can be local to disk, making lookup and update more efficient No clustering, so difficult to answer range queries
Comparison of Partitioning Techniques(Cont.) Range partitioning: Provides data clustering by partitioning attribute value. Good for sequential access Good for point queries on partitioning attribute:only one disk needs to be accessed. For range queries on partitioning attribute,one to a few disks may need to be accessed Remaining disks are available for other queries. Good if result tuples are from one to a few blocks. If many blocks are to be fetched,they are still fetched from one to a few disks,and potential parallelism in disk access is wasted Example of execution skew. Database System Concepts-5th Edition,Aug 22,2005. 21.10 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 21.10 ©Silberschatz, Korth and Sudarshan th Edition, Aug 22, 2005. Comparison of Partitioning Techniques (Cont.) Range partitioning: Provides data clustering by partitioning attribute value. Good for sequential access Good for point queries on partitioning attribute: only one disk needs to be accessed. For range queries on partitioning attribute, one to a few disks may need to be accessed Remaining disks are available for other queries. Good if result tuples are from one to a few blocks. If many blocks are to be fetched, they are still fetched from one to a few disks, and potential parallelism in disk access is wasted Example of execution skew
Partitioning a Relation across Disks If a relation contains only a few tuples which will fit into a single disk block,then assign the relation to a single disk. Large relations are preferably partitioned across all the available disks. If a relation consists of m disk blocks and there are n disks available in the system,then the relation should be allocated min(m,n)disks. Database System Concepts-5th Edition,Aug 22,2005. 21.11 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 21.11 ©Silberschatz, Korth and Sudarshan th Edition, Aug 22, 2005. Partitioning a Relation across Disks If a relation contains only a few tuples which will fit into a single disk block, then assign the relation to a single disk. Large relations are preferably partitioned across all the available disks. If a relation consists of m disk blocks and there are n disks available in the system, then the relation should be allocated min(m,n) disks