1 / 12

Partitioned Tables

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

kitra
Download Presentation

Partitioned Tables

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. 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

  2. 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))

  3. 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

  4. 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

  5. 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!

  6. 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.

  7. 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.

  8. Hash Example

  9. 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

  10. 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);

  11. 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));

  12. 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

More Related