130 likes | 254 Views
Partitioned Tables. Partitions / partitioning / partitioned tables For very large tables Improve querying Easier admin Backup and recovery easier Optimiser knows when partitioning used Can use in SQL also. Creating a PT. Create table JOHN ( ID number name varchar2(25) age number
E N D
Partitioned Tables • Partitions / partitioning / partitioned tables • For very large tables • Improve querying • Easier admin • Backup and recovery easier • Optimiser knows when partitioning used • Can use in SQL also
Creating a PT • Create table JOHN ( ID number name varchar2(25) age number constraint john_pk primary key (ID) ) partition by range (age) (partition PART1 values less than (21) partition PART2 values less than (40) partition PART3 values less than (maxvalue))
Warning • Specification of partition is exclusive e.g. partition by range (name) (partition part1 values less than (‘F’) implies that f is excluded • Maxvalue is a general term to pick up anything that failed so far • Works for text as well as number
Hash Partition • Only in Oracle 8i and above • Uses a numerical algorithm based on partition key to determine where to place data • Range partition = consecutive values together • Hash = consecutive values may be in different partitions • Also gives more partitions = reduces the risk of contention
What is Hash? • Imagine 8GB table – split in 8 / 1 GB • No intuitive way to split data • Or obvious way is totally imbalanced • 1 partition 7BG + 7 140MB • Huge variations in performance • Randomise breakdown of data so objects of similar size • Select one column • Select number of chunks • Oracle does the rest!
Mechanics of Hashing • Each record is allocated into a bucket based on key value – e.g. Name = John • Applying the hashing function to the value John uniquely returns the bucket number where the record is located: • e.g. using prime number • divide KEY by a prime number • If text, translation into numeric value using ASCII code • use remainder of the division = address on the disk • if record already at same address - pointer to overflow area.
Hashing Process • A hash table, or a hash map, is a data structure that associates keys with values. • The primary operation it supports efficiently is a lookup: given a key (e.g. a person's name), find the corresponding value (e.g. that person's telephone number). It works by transforming the key using a hash function into a hash, a number that is used as an index in an array to locate the desired location ("bucket") where the values should be.
Questions • Why do we use hashing? • How does it work? (exercise) • http://www.engin.umd.umich.edu/CIS/course.des/cis350/hashing/WEB/HashApplet.htm
Hash Partition - SQL Create table JOHN ( Name varchar2(25) primary key, Age number, Years abroad number ) Partition by hash (age) Partitions 2 Store in (Part1_john, Part2_john);
Sub-Partitions Create table FRED ( Name varchar2(25) primary key, Age number, Years abroad number ) Partition by range (years abroad) Subpartition by hash (name) Subpartitions 5 (partition Part1 values less than (1) partition Part2 values less than (3) partition Part3 values less than (6) partition Part4 values less than (MAXVALUE));
Indexing Partitions • Performance requirements may mean Partitioned tables should be indexed • Create index JOHN_NAME on JOHN (name) Local Partitions (Part1, Part2, Part3, Part4) • Local means create separate index for each partition of the table • Alternative is to create a global index with values from different partitions • Global indexes cannot be created for Hash partitions