Downloaded 684 times
Uploaded byMyOnlineITCourses
Oracle Table Partitioning - Introduction
The document provides an overview of Oracle partitioning, explaining how it enables large tables or indexes to be divided into smaller and more manageable pieces for improved performance and flexibility. It discusses the benefits of partitioning, including faster query access, simplified maintenance, and easier data management strategies such as range, hash, and list partitioning. Additionally, it outlines when to partition tables, the methods for implementing different partitioning strategies, and the implications for data management.
Oracle Table Partitioning - Introduction
- 1. Oracle Partitioning --A Primer 1 1 MyOnlineITCourses.com MyOnlineITCourses.com
- 2. Partitioning Partitioning is theability of the database to take very large tables or indexes and physically break them into smaller manageable pieces. 2 2 MyOnlineITCourses.com MyOnlineITCourses.com
- 3. Partitioned VS Non Partitioned Table 3 3 MyOnlineITCourses.com MyOnlineITCourses.com
- 4. Partitions - Benefits ORDERS ORDERS ORDERS USA EUROPE JAN FEB JAN FEB Large Table Partition Composite Partition Difficult to Manage Divide and Conquer Better Performance Easier to Manage More flexibility to match business needs Improve Performance Transparent to applications 4 4 MyOnlineITCourses.com MyOnlineITCourses.com
- 5. Partitioning - Benefits • Queries will access only those relevant partitions Faster • An entire partition can be exported • Exported partition can be deleted from Cheaper database • Partition Maintenance is simpler when compared to a large un-partitioned table Flexible • Partition backup/restoration is easier 5 5 MyOnlineITCourses.com MyOnlineITCourses.com
- 6. When To Partition- Table Here are some suggestions for when to partition a table: Tables greater than 2 GB should always be considered as candidates for partitioning. Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only. When the contents of a table need to be distributed across different types of storage devices. 6 6 MyOnlineITCourses.com MyOnlineITCourses.com
- 7. Partition Strategies 7 7 MyOnlineITCourses.com MyOnlineITCourses.com
- 8. Partition Type ..(Examples) 8 8 MyOnlineITCourses.com MyOnlineITCourses.com
- 9. Range Partitioned Tables • Data distribution is based on range of values • Data distribution is continuous Definition • Best performance when data distribution is even on the partition key. • Knowledge of data is critical before choosing this • Date as partitioning key Jan 2011, Feb 2011, Mar 2011 so on.. Example • Department code as partitioning key Values <= 30, Values > 30 9 9 MyOnlineITCourses.com MyOnlineITCourses.com
- 10. DDL Example … CREATETABLE DEPT ( DEPTNO NUMBER (2), DEPT_NAME VARCHAR2 (30)) PARTITION BY RANGE (DEPTNO) ( PARTITION D1 VALUES LESS THAN (10) TABLESPACE DEPT1, PARTITION D2 VALUES LESS THAN (20) TABLESPACE DEPT2, PARTITION D1 VALUES LESS THAN (MAXVALUE) TABLESPACE DEPT3 ) 10 10 MyOnlineITCourses.com MyOnlineITCourses.com
- 11. Range Partition (Multicolumn) • Defined on two or more columns of the table Multi Column Range Partition • Enhances Query performance if searched on partition key • Improves the Manageability of Advantages partitions 11 11 MyOnlineITCourses.com MyOnlineITCourses.com
- 12. When to useRange Partition Very less time Very large for tables being administrative scanned on a operations like range predicate backup on large like Order-Date tables Need to maintain rolling window of data 12 12 MyOnlineITCourses.com MyOnlineITCourses.com
- 13. Hash Partitioning Specify the Oracle Specify Always Define 2n Partitions number of database storage for Partition Key partitions inserts the entire Storage rows table and based on the hash value tablespace of partition for key partitions 13 13 MyOnlineITCourses.com MyOnlineITCourses.com
- 14. Hash Partition –Example Hash-partitioned table that splits the table into four parts based on the hash of the partition key, acct_no. CREATE TABLE CUST_SALES_HASH ( ACCT_NO NUMBER (5), CUST_NAME CHAR (30)) PARTITION BY HASH (ACCT_NO) PARTITIONS 4 STORE IN (USERS1, USERS2, USERS3, USERS4); 14 14 MyOnlineITCourses.com MyOnlineITCourses.com
- 15. When to useHash Partition Improve Manageability/availability of Large tables Avoid data skew in partitions Maximize I/0 throughput 15 15 MyOnlineITCourses.com MyOnlineITCourses.com
- 16. List Partitioning •Segmenting data with a list of values •Flexible means of partitioning where data is better understood Definition • Similar to Range Partitioning, but without any max value • CREATE TABLE DEPT_PART (DEPTNO NUMBER (2),DNAME VARCHAR2 (14),LOC VARCHAR2 (13)) PARTITION BY LIST (DNAME) (PARTITION D1_EAST VALUES (‘NEW YORK’), Example PARTITION D2_WEST VALUES (‘SAN FRANCISCO’, ‘LOS ANGELES’), PARTITION D3_SOUTH VALUES (‘ATLANTA’,’DALLAS’,’HOUSTON’), PARTITION D4_NORTH VALUES (‘CHICAGO’,’DETROIT’)); 16 16 MyOnlineITCourses.com MyOnlineITCourses.com
- 17. Range ,List ,Hash Partitions 17 17 MyOnlineITCourses.com MyOnlineITCourses.com
- 18. Composite Partitioning A distinct value Composite pair for the two Data is Partitioning is dimensions Partitioned complementary uniquely along two to multi determines the dimensions column range target partition partition 18 18 MyOnlineITCourses.com MyOnlineITCourses.com
- 19. Composite Partitioning Strategies New 11g Strategy Use Case List – Range Geography –Time Range - Range Ship Date – Order Date List - Hash Geography – OrderID List - List Geography – Product 19 19 MyOnlineITCourses.com MyOnlineITCourses.com
- 20. Range – ListPartitioning 20 20 MyOnlineITCourses.com MyOnlineITCourses.com
- 21. Composite Partitioning Table SALES Range - Range RANGE(order_date)-RANGE(ship_date) ship_date • All records with Jan order_date in 2006 ... ... March 2006 AND Feb ship_date in May 2006 ... ... 2006 ... ... May May 2006 ... ... ... ... Jan 2006 Feb 2006 Mar 2006 Mar 2006 Jan 2007 order_date 21 21 MyOnlineITCourses.com MyOnlineITCourses.com
- 22. Would like tolearn more about oracle ??? 22 22 MyOnlineITCourses.com MyOnlineITCourses.com
- 23. www.MyOnlineITCourses.com We give quality online trainings by IT Professionals for Oracle SQL , PL/SQL Oracle DBA Oracle Performance Tuning Data Modelling with Erwin Tool To get more details contact us info@myonlineitcourses.com +91 991 2323 000 www.Facebook.com/MyOnlineITCourses 23 23 MyOnlineITCourses.com MyOnlineITCourses.com
- 24. 24 24 MyOnlineITCourses.com MyOnlineITCourses.com