1 / 28

Partitioning Basics

Partitioning Basics. Objectives. After completing this lesson, you should be able to do the following: Outline the Oracle Database partitioning architecture Describe the supported partition List options for creating a partitioned table Create partitioned tables.

ramona
Download Presentation

Partitioning Basics

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

  2. Objectives • After completing this lesson, you should be able to do the following: • Outline the Oracle Database partitioning architecture • Describe the supported partition • List options for creating a partitioned table • Create partitioned tables

  3. Partitioned Tables and Indexes • Large tables and indexes can be partitioned into smaller, more manageable pieces. Table T1 Index I1 Table T1 Index I1

  4. Manageability: Rolling Window Operations OCT00 JUL01

  5. Benefits of Partitioning: Performance Considerations • The optimizer eliminates (prunes) partitions that do not need to be scanned. • Partitions can be scanned, updated, inserted, or deleted in parallel. • Join operations can be optimized to join “by the partition.” • Partitions can be load-balanced across physical devices.

  6. Performance Consideration:Partition Pruning Sales 01-Jan Partition pruning: Only the relevant partitions are accessed. 01-Feb SQL> SELECT SUM(amount_sold) 2 FROM sales 3 WHERE time_id BETWEEN 4 TO_DATE('01-MAR-2000', 5 'DD-MON-YYYY') AND 6 TO_DATE('31-MAY-2000', 7 'DD-MON-YYYY'); 01-Mar 01-Apr 01-May 01-Jun

  7. A partitioned table can have partitioned or nonpartitioned indexes. Table Versus Index Partitioning A nonpartitioned table can have partitioned or nonpartitioned indexes. Table T1 Table T2 Index I2 Index I4 Index I1 Index I3

  8. Partitioning Methods • Range partitioning maps data to partitions on the basis of ranges of partition key values for each partition. • Hash partitioning maps data to partitions by using a hashing algorithm applied to a partitioning key. • List partitioning maps rows to partitions by using a list of discrete values for the partitioning column. • Composite partitioning: • Range-Hash subpartitions the range partitions using a hashing algorithm. • Range-List subpartitions the range partitions using an explicit list.

  9. CREATE TABLE Statement with Partitioning • A partitioned table declaration contains three elements: • The logical structure of the table • The partition structure defining the type and columns • The structure of each table partition SQL> CREATE TABLE example 2 ( idx NUMBER, txt VARCHAR2(20) ) 3 PARTITION BY RANGE ( idx ) 4 ( PARTITION VALUES LESS THAN ( 0 ) 5 TABLESPACE data01 6 , PARTITION VALUES LESS THAN ( MAXVALUE ));

  10. Logical and Physical Attributes • Logical attributes: • Normal table structure (columns, constraints) • Partition type • Keys and values • Row movement • Physical attributes: • Tablespace • Extent sizes, block attributes

  11. Partitioning Type • The partition type is declared in the PARTITION BY clause: SQL> CREATE TABLE ( … column … ) 2 PARTITION BY RANGE ( column_list ) 3 ( PARTITION specifications ) ; 2 PARTITION BY HASH ( column_list ) 2 PARTITION BY LIST ( column ) 2 PARTITION BY RANGE ( column_list ) SUBPARTITION BY HASH ( column_list2 ) 2 PARTITION BY RANGE ( column_list ) SUBPARTITION BY LIST ( column )

  12. Specifying Partition Attributes • Each partition is specified in a partition value clause. • There can be up to 65,535 partitions per table. ... PARTITION simple_p1 VALUES ( 'HIGH', 'MED' ) TABLESPACE data01 PCTFREE 5 , PARTITION simple_p2 VALUES ( 'LOW' ) TABLESPACE data02 STORAGE ( INITIAL 1M ) ...

  13. Partition Key Value • The partition key value must be a literal. • Constant expressions are not allowed, with the exception of TO_DATE conversion. • The partition key can consist of up to 16 columns.

  14. Range Partitioning • Specify the columns to be partitioned, and the break values for each partition. • Each partition must be defined. • The MAXVALUE value allows the greatest possible value and fits all data types. • The MAXVALUE value includes NULL values.

  15. CREATE TABLE salestable (s_productid NUMBER, s_saledate DATE, s_custid NUMBER, s_totalprice NUMBER) PARTITION BY RANGE(s_saledate) (PARTITION sal99q4 VALUES LESS THAN (TO_DATE('01-JAN-2000', 'DD-MON-YYYY')), PARTITION sal00q1 VALUES LESS THAN (TO_DATE('01-APR-2000', 'DD-MON-YYYY')), PARTITION sal00q2 VALUES LESS THAN (TO_DATE('01-JUL-2000', 'DD-MON-YYYY')), PARTITION sal00q3 VALUES LESS THAN (TO_DATE('01-OCT-2000', 'DD-MON-YYYY')), PARTITION sal00q4 VALUES LESS THAN (TO_DATE('01-JAN-2001', 'DD-MON-YYYY'))); Range Partitioning: Example

  16. List Partitioning CREATE TABLE sales_list (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_state VARCHAR2(20), sales_amount NUMBER(10), sales_date DATE) PARTITION BY LIST(sales_state) (PARTITION sales_west VALUES('California', 'Hawaii'), PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'), PARTITION sales_central VALUES('Texas', 'Illinois'), PARTITION sales_other VALUES(DEFAULT));

  17. Hash Partitioning,Named Partitions • Specify the columns to be partitioned, and the number of partitions: • Partition may be defined, or just quantified. • NULL is placed in the first partition. • The number of partitions should be a power of two. CREATE TABLE simple (idx NUMBER, txt VARCHAR2(20) PRIMARY KEY) ORGANIZATION INDEX PARTITION BY HASH ( txt ) ( PARTITION s_h1 tablespace data01 , PARTITION s_h2 tablespace data03 ) ;

  18. Hash-Range Partitioning • Hash-Range partitioning example: SQL> CREATE TABLE simple 2 ( idx NUMBER, txt VARCHAR2(20) ) 3 PARTITION BY RANGE ( idx ) 4 SUBPARTITION BY HASH ( txt ) 5 SUBPARTITIONS 4 STORE IN (data01, data02) 6 ( PARTITION ns_lo VALUES LESS THAN ( 0 ) 7 , PARTITION ns_hi VALUES LESS THAN ( 1E99 ) 8 , PARTITION ns_mx 9 VALUES LESS THAN (MAXVALUE) 10 SUBPARTITIONS 2 STORE IN ( data03 ) ) ;

  19. Range-List Partitioning • Functionality supported for range-hash partitioning is extended to range-list partitioning. • Well suited for: • Historical data at the partition level • Controlled data distribution at subpartition level Range (year) < 2000 < 2002 < 2001 < 2003 p1_s1 p2_s1 p3_s1 p4_s1 'CA', 'CO' p1_s2 p2_s2 p3_s2 p4_s2 List (state) 'FL', 'TX' p1_s3 p2_s3 p3_s3 p4_s3 DEFAULT

  20. Composite Partitioning Using a Template • When all partitions have the same subpartition definition, consider using subpartition templates. • Templates are useful to avoid defining individual subpartitions. • Can be used for: • Range-list partitions • Range-hash partitions • Related dictionary view: • DBA_SUBPARTITION_TEMPLATES

  21. Composite Partitioning andTemplate: Example CREATE TABLE quarterly_regional_sales ( deptno NUMBER, item VARCHAR2(20), d DATE, amunt NUMBER, state VARCHAR2(2) , details CLOB) PARTITION BY RANGE (d) SUBPARTITION BY LIST (state) SUBPARTITION TEMPLATE ( SUBPARTITION northwest VALUES ('OR','WA') TABLESPACE tbs1 LOB (details) STORE AS details1 (TABLESPACE tbs3), SUBPARTITION others VALUES (DEFAULT) TABLESPACE tbs2 LOB (details) STORE AS details7 (TABLESPACE tbs4) ) ( PARTITION q1_2002 VALUES LESS THAN ('01-APR-2002'), PARTITION q2_2002 VALUES LESS THAN ('01-JUL-2002'), PARTITION q3_2002 VALUES LESS THAN ('01-OCT-2002'), PARTITION q4_2002 VALUES LESS THAN ('01-JAN-2003') );

  22. Partition Extended Table Names • Specify the partition in a table to limit an operation: SQL> SELECT idx 2 FROM simple PARTITION ( s_neg ) ; SQL> DELETE FROM simple SUBPARTITION ( s_h2 ) ; SQL> CREATE TABLE sim2 2 AS SELECT * FROM simple PARTITION ( p3 ) ;

  23. Create Partitions with Enterprise Manager

  24. Equipartitioning • If two tables have the same partition keys and partition key values, then they are equipartitioned. • This is useful for tables with a common key, like master-detail relationships. • A partitionwise join operation requires equipartitioning. • Indexes can be equipartitioned with the table.

  25. Full Partitionwise Joins • A full partitionwise join divides a large join into smaller joins. • The tables being joined must be equipartitioned on their join keys. • When a full partitionwise join is executed in parallel, the granule of parallelism is a partition. SELECT c.cust_last_name, COUNT(*) FROM sales s, customers c WHERE s.cust_id = c.cust_id AND s.time_id BETWEEN TO_DATE('01-JUL-1999', 'DD-MON-YYYY') AND (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')) GROUP BY c.cust_last_name HAVING COUNT(*) > 100;

  26. Partial Partitionwise Joins • Partial partitionwise joins require you to partition only one table on the join key. • The partitioned table is called the reference table. • The other table is dynamically repartitioned based on the partitioning of the reference table. • Partial partitionwise joins can be done only in parallel.

  27. Summary • In this lesson, you should have learned how to: • Outline the Oracle Database partitioning architecture • Describe the supported partition • List options for creating a partitioned table • Create partitioned tables

  28. Practice 3: Overview • This practice covers the following topics: • Analyzing execution plans involving partition pruning • Employing subpartition templates in range-list partitioned table creation

More Related