1 / 31

Title Slide

Title Slide. Learn How To Partition In Oracle 9i Release 2 By Eric Yen System Consultant Quest Software. Agenda. Partitioning Defined Evolution of Partitioning in Oracle When to partition tables Range Partitioning Hash Partitioning List Partitioning Composite Range-Hash Range-List.

damara
Download Presentation

Title Slide

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. Title Slide Learn How To Partition In Oracle 9i Release 2 By Eric Yen System Consultant Quest Software

  2. Agenda • Partitioning Defined • Evolution of Partitioning in Oracle • When to partition tables • Range Partitioning • Hash Partitioning • List Partitioning • Composite • Range-Hash • Range-List

  3. Agenda • Globally Partitioned Indexes • Locally Partitioned Indexes • Bringing It All Together • Other Sources Of Information • Summary • Questions and Answers • Short Survey . . . How did I do?

  4. Partitioning Defined • The concept of Divide and Conquer. • Breaking down a large problem into smaller manageable pieces. • Making a mountain into a mole hill. • Your definition here. • “Dividing Tables and Indexes into manageable pieces.”

  5. Evolution of Partitioning in Oracle • Oracle 8 • Range • Oracle 8i • Range • Hash • Composite • Range-Hash • Oracle 9i • Release 1 • Range • Hash • List • Composite • Range-Hash • Oracle 9i • Release 2 • Range • Hash • List* • Composite • Range-Hash • Range-List

  6. When to partition tables • For “large” table. Tables >= 2 Gigs* • Performance gain outweighs the management of partitioning. • Archiving of data is on a schedule and repetitive.

  7. Range Partitioning • Often used when there is a logical range. • Examples: • Dates • Start Date • Transaction Date • Close Date • Date of Payment • IDs • Product ID • Location ID • UPC

  8. Partition Method Partition Key Partition Definition Range Partitioning Code CREATE TABLE PARTITION_BY_RANGE ( . . . BIRTH_MM INT NOT NULL, BIRTH_DD INT NOT NULL, BIRTH_YYYY INT NOT NULL) PARTITION BY RANGE(BIRTH_YYYY, BIRTH_MM, BIRTH_DD) (PARTITION PARTITION_01 VALUES LESS THAN (1970, 01 ,01) TABLESPACE TS01, . . . PARTITION PARTITION_N VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE) TABLESPACE TS05 ) ENABLE ROW MOVEMENT;

  9. Hash Partitioning • Hashing allows for distributed data by a hash key. • DBAs do not have to know the data. • Distribution is handled by Oracle. • Each partition can have its own tablespace.

  10. Partition Method Partition Key Partition Definition Hash Partitioning Code Hash Partitioning Code CREATE TABLE PARTITION_BY_HASH (FIRST_NAME VARCHAR2(10), MIDDLE_INIT VARCHAR2(1), LAST_NAME VARCHAR2(10), AGE INT NOT NULL) PARTITION BY HASH (AGE) (PARTITION P1_AGE TABLESPACE TS01, PARTITION P2_AGE TABLESPACE TS02, PARTITION P3_AGE TABLESPACE TS03, PARTITION P4_AGE TABLESPACE TS04) ENABLE ROW MOVEMENT;

  11. List Partitioning • Added in Oracle 9.1 • In 9.2 the “DEFAULT” partition method was added. • Allows DBAs to explicitly define what is in a partition. • Example • States into a Region • Departments into a Division

  12. Partition Method Partition Key Partition Definition List Partitioning Code List Partitioning Code CREATE TABLE PARTITION_BY_LIST (DEPTID NUMBER, DEPTNAME VARCHAR2(15), STATE VARCHAR2(2)) PARTITION BY LIST (STATE) (PARTITION DEPTS_IN_NORTH VALUES ('AK') TABLESPACE TS01, . . . PARTITION DEPTS_WITH_NO_REGION VALUES (DEFAULT) TABLESPACE TS05) ENABLE ROW MOVEMENT;

  13. Composite Range-Hash • Partition by Range • Stored by a Hash algorithm • DBAs can focus on both the ease of Range Partitioning and get the benefits of Hash Partitioning • Logically divide the data and let Oracle determine where to store.

  14. Composite Range-Hash Code Composite Range-Hash Code CREATE TABLE PARTITION_BY_RANGE_HASH ( FIRST_NAME VARCHAR2(10), MIDDLE_INIT VARCHAR2(1), LAST_NAME VARCHAR2(10), BIRTH_MM INT NOT NULL, BIRTH_DD INT NOT NULL, BIRTH_YYYY INT NOT NULL) TABLESPACE USERS

  15. Partition Key Partition Key Partition Partition Definition Composite Range-Hash Code continued Composite Range-Hash Code continued PARTITION BY RANGE (BIRTH_YYYY, BIRTH_MM, BIRTH_DD) <SUBPARTITION TEMPLATE> (PARTITION DOBS_IN_1971 VALUES LESS THAN (1972, 01 ,01), . . . PARTITION DOBS_IN_1975 VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE)) ENABLE ROW MOVEMENT;

  16. Subpartition Method Subpartition Key Subpartition Name Composite Range-Hash Code continued Composite Range-Hash Code continued SUBPARTITION BY HASH(FIRST_NAME, MIDDLE_INIT, LAST_NAME) SUBPARTITION TEMPLATE( SUBPARTITION SP1 TABLESPACE TS01, SUBPARTITION SP2 TABLESPACE TS02, SUBPARTITION SP3 TABLESPACE TS03, SUBPARTITION SP4 TABLESPACE TS04, SUBPARTITION SP5 TABLESPACE TS05)

  17. Composite Range-List • Similar to Range-Hash partitioning. • Subpartition is by List method. • Allows for greater control by the DBAs. • Proper use of Range-List must be carefully thought out.

  18. Composite Range-List Code Composite Range-List Code CREATE TABLE PARTITION_BY_RANGE_LIST ( FIRST_NAME VARCHAR2(10), MIDDLE_INIT VARCHAR2(1), LAST_NAME VARCHAR2(10), BIRTH_MM INT NOT NULL, BIRTH_DD INT NOT NULL, BIRTH_YYYY INT NOT NULL, STATE VARCHAR2(2) NOT NULL) TABLESPACE USERS

  19. Partition Key Partition Key Partition Partition Definition Composite Range-List Code continued Composite Range-List Code continued PARTITION BY RANGE (BIRTH_YYYY, BIRTH_MM, BIRTH_DD) <SUBPARTITION TEMPLATE> (PARTITION DOBS_IN_1971 VALUES LESS THAN (1972, 01 ,01), . . . PARTITION DOBS_IN_1975 VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE)) ENABLE ROW MOVEMENT;

  20. Subpartition Key Subpartition Method Subpartition Name Composite Range-List Code continued Composite Range-List Code continued SUBPARTITION BY LIST (STATE) SUBPARTITION TEMPLATE (SUBPARTITION IN_NORTH VALUES ('AK') TABLESPACE TS01, SUBPARTITION IN_EAST VALUES ('NY', 'NJ', 'VA', 'CT') TABLESPACE TS02, . . . SUBPARTITION NO_STATE VALUES (DEFAULT) TABLESPACE TS05)

  21. Globally Partitioned Indexes • Two types of Globally Partition Indexes. • Non-Partitioned • Partitioned • Globally Non-Partitioned Indexes are “regular” indexes used in OLTP. • Globally Partitioned Indexes are similar in syntax to Range partitioned tables.

  22. Index Method Index Partition Index Partition Globally Partitioned Index Code Globally Partitioned Index Code CREATE INDEX PARTITION_BY_RANGE_GPI ON PARTITION_BY_RANGE (BIRTH_YYYY) GLOBAL PARTITION BY RANGE (BIRTH_YYYY) (PARTITION DOBS_IN_1971_OR_B4 VALUES LESS THAN (1972) TABLESPACE ITS01, PARTITION DOBS_IN_1972_GPI VALUES LESS THAN (1973) TABLESPACE ITS02, . . . PARTITION DOBS_IN_1975_OR_L8R VALUES LESS THAN (MAXVALUE) TABLESPACE ITS05);

  23. Locally Partitioned Indexes • Oracle manages the rebuild of LPI • Extra time should be allocated for • Range-Hash • Range-List • LPI can “point” to partition or subpartition level. • No SUBPARTITION TEMPLATE

  24. Index Method Index Partition Locally Partitioned Index Code Locally Partitioned Index Code – Partition Level (LPI at Partition Level) CREATE INDEX PARTITION_BY_RANGE_HASH_LIP ON PARTITION_BY_RANGE_HASH (LAST_NAME) LOCAL ( PARTITION <PARTITION_NAME01> TABLESPACE ITS01, . . . PARTITION <PARTITION_NAMEN> TABLESPACE ITSN);

  25. Index Method Index Partition Index Subpartition Locally Partitioned Index Code Continued Locally Partitioned Index Code – Subpartition Level (LPI at Subpartition Level) CREATE INDEX <INDEX_NAME> ON <TABLE_NAME> (<COLUMNS>) LOCAL (PARTITION <PARTITION_NAME> TABLESPACE ITS0N ( SUBPARTITION <SUBPARTITION_NAME> TABLESPACE ITS01 , . . . SUBPARTITION <SUBPARTITION_NAME> TABLESPACE ITS0N ), . . .

  26. Index Partition Index Subpartition Locally Partitioned Index Code Continued Locally Partitioned Index Code – Subpartition Level (LPI at Subpartition Level) (PARTITION <PARTITION_NAME> TABLESPACE ITS0N ( SUBPARTITION <SUBPARTITION_NAME> TABLESPACE ITS01 , . . . SUBPARTITION <SUBPARTITION_NAME> TABLESPACE ITS0N ) );

  27. Bringing It All Together • 5 table partition methods • Range, Hash, List, Range-Hash, Range-List • 3 index partition methods • Global Non-partition, Global partition, Locally partition • Guidelines • Is the table the “right” size? • How volatile is the data? • What are your maintenance considerations?

  28. Other Sources Of Information • Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01 Chapter 11 Partitioned Tables and Indexes • Oracle9i Database Administrator's Guide Release 2 (9.2) Part Number A96521-01 Chapter 17 Managing Partitioned Tables and Indexes*

  29. Summary • Basics of partitioning • Definition, Evolution & When to partition • Table partitioning methods • Range, Hash, List, Range-Hash, Range-List • Index partitioning methods • Global Nonpartition, Global Partition, Locally Partition • Wrapped It Up • Bringing it all together, Other Sources Of Information

  30. Question and Answer Question and Answer

  31. THANK YOU FOR LISTENING

More Related