1 / 43

Optimal Usage of Oracle’s Partitioning Option

Optimal Usage of Oracle’s Partitioning Option. Frank Bommarito Paper # 35697 SageLogix, Inc. . Overview. Partitioning – The Beginning. Partitioning Concepts. Partition Maintenance. Types of Partitioning. Practical Partitioning Usages. Statistics. Partitioning Options. Conclusions.

damon
Download Presentation

Optimal Usage of Oracle’s Partitioning Option

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. Optimal Usage of Oracle’s Partitioning Option Frank Bommarito Paper # 35697 SageLogix, Inc. www.sagelogix.com

  2. Overview • Partitioning – The Beginning • Partitioning Concepts • Partition Maintenance • Types of Partitioning • Practical Partitioning Usages • Statistics • Partitioning Options • Conclusions www.sagelogix.com

  3. Partitioning – The Beginning • The concept: one large table divided into many small tables • Database sizes growing at phenomenal pace • “interesting” challenges appeared for DBAs • rebuilding indexes • Duplication of tables • Query tuning • Partitioning allows for growth, with maintaining the DBA's management of the database • The result = more uptime www.sagelogix.com

  4. Partitioning – The Beginning • Oracle-based partitioning available in 8.0 & above • Smaller tables (partitions) can be accessed, maintained separately • “Overview” mechanism • masks the division • operations performed with no modifications to application • Oracle’s “optimizer” • partition aware • partition pruning www.sagelogix.com

  5. Partitioning Concepts • Two distinct object types for a partitioned table – GLOBAL & LOCAL • GLOBAL objects refer to table as a whole • LOCAL objects are the individual partitions • Standard & partitioned tables have same features (indexes, constraints, triggers), but implementation is different. • Table’s rows stored in multiple objects, not just one. www.sagelogix.com

  6. Partitioning Concepts • Example: Create table range_partition ( Part_key number, Value1 varchar2(30), Value2 number) Partition by range (part_key) ( partition p1 values less than (80000), partition p2 values less than (160000), partition pmax values less than (maxvalue) ); • Single table created, with three physical segments. • Indexes need to account for each segment. www.sagelogix.com

  7. Partitioning Concepts • Statement creates NON-PREFIXED LOCAL index. • NON-PREFIXED = partition key not leading column • LOCAL = 3 separate indexes created • Partition key derived from: “Partition by range (part_key)” • Other examples: Create index idx_example1 on range_partition (value2) LOCAL; • Column part_key = partition key. Create index idx_example2 on range_partition (value1) GLOBAL; • Creates one single GLOBAL index - includes rows from all partitions. Create index idx_example3 on range_partition (part_key) LOCAL; • Creates a PREFIXED LOCAL index. www.sagelogix.com

  8. Performance Considerations • Example: • Table range_partition loaded with 256,000 rows. • All partitions a near equal distribution of rows. • All three columns unique values. • Three indexes above created on the table. • 20,000 queries generated & executed for each column indexed. www.sagelogix.com

  9. Performance Considerations • Results • Same table, without partitioning: Slowest ( index scans) Similar Fastest (partition pruning) www.sagelogix.com

  10. Partition Maintenance • Why utilize a non-prefixed local index? • Partitions splits table into smaller sizes - allows maintenance on excessively large tables. • A partition can have maintenance performed without affecting other partitions. • However, this does impact GLOBAL table items. • Some of the maintenance operations include: • Rebuild a specific partitions data segments • Exchange a non-partitioned table with a partition • Merge two partitions together • Divide two partitions apart • Add new partitions to the table • Drop old partitions from the table www.sagelogix.com

  11. Partition Maintenance • Global Indexes • A single ROW affected by a partition maintenance operation will cause ENTIRE global index to be invalid. • 9i of Oracle contains “update global index” clause - can be applied to partition maintenance operations. • Rebuilds only components of GLOBAL index impacted. • Rebuilding can cause performance degradations - but are minimal compared to impact of INVALIDATING important indexes.  www.sagelogix.com

  12. Partition Maintenance • Examples: Alter table range_partition move partition p1 tablespace new_tablespace; • Will rebuild partition & locate newly rebuilt partition in “new_tablespace”. If partition “p1” = one or more rows, global indexes become “UNUSABLE”. Application will receive errors if application needs to access index. Alter index idx_example2 rebuild; • Will rebuild unusable index. Needs to re-index entire table’s contents, will not be working only on deltas. Alter table range_partition move partition p1 tablespace new_tablespace update global indexes; • Performs same operation, but adds task of “fixing” global index (release 9i and above). www.sagelogix.com

  13. Partition Maintenance • Constraints • Likely largest prohibitive unit for partition maintenance. • Most partition maintenance operations do not work when constraints are enabled. • Typically, constraint needs to be dropped and re-applied after partition maintenance operations. • Oracle added new syntax that is handy when disabling constraints. Example: Alter constraint pk_contstraint disable keep index; • “Keep index” clause will not drop the index. Maintenance operations proceed and index pieces can be rebuilt. Once complete, constraint can be re-enabled in relatively short time. www.sagelogix.com

  14. Partition Maintenance • Example: CREATE TABLE part_test (ID NUMBER NOT NULL, NUMB NUMBER) PARTITION BY RANGE (ID) (PARTITION P1 VALUES LESS THAN (10), PARTITION P2 VALUES LESS THAN (20)); CREATE unique INDEX part_test_pkx ON part_test (ID) LOCAL; ALTER TABLE part_test ADD CONSTRAINT part_test_pk PRIMARY KEY (ID) USING INDEX; create table fk_table (id number, descr varchar2(30)); www.sagelogix.com

  15. Partition Maintenance • Example, cont. ALTER TABLE fk_table ADD CONSTRAINT fk_table_fk FOREIGN KEY (ID) REFERENCES PART_TEST(ID); create table part_exch (ID NUMBER NOT NULL, NUMB NUMBER); insert into part_test values (1,1); alter table part_test exchange partition p1 with table part_exch; ERROR at line 1: ORA-02266: unique/primary keys in table referenced by enabled foreign keys www.sagelogix.com

  16. Partition Maintenance • Stored PL/SQL • Stored PL/SQL often exists. • When objects are modified, PL/SQL program units need re-compilation to ensure that modifications are valid. Partition maintenance operations are excluded. A new partition does not impact stored PL/SQL. However, addition of new partition will invalidate any dependent PL/SQL program. Release 9i automatically recompiles the invalidated programs. • Whenever data dictionary adds or removes row resulting from partition maintenance operation, PL/SQL become invalid. www.sagelogix.com

  17. Partition Maintenance • Stored PL/SQL, cont. • The following command does not cause invalidation as the data dictionary is simply updated: Alter table range_partition exchange partition p1 with table no_partition; • The following command does cause invalidation as the data dictionary is removing a row: Alter table range_partition drop partition p1; www.sagelogix.com

  18. Types of Partitioning • RANGE • HASH • LIST • SUB-PARTITION (HASH or LIST) www.sagelogix.com

  19. Types of Partitioning • RANGE PARTITIONS • Most common. • Table & index partitions based on list of columns, allowing database to store occurrence in given partition. Typically used within data warehousing systems. Most common range boundary = dates. • Each partition is defined with upper boundary. Storage location found by comparing partitioning key with upper boundary. Upper boundary is non-inclusive: the key of each occurrence must be less than this limit for record to be stored in this partition. www.sagelogix.com

  20. Types of Partitioning • HASH PARTITIONS • Ideal when there is no real method to divide table based on range. • Utilize hashing algorithm to programmatically take column value and store within given partition. • Each partition is defined with an internal hashing algorithm. • Recommended when it is difficult to define criteria for distribution of data. www.sagelogix.com

  21. Types of Partitioning • LIST PARTITIONS • Hard-coded LIST of values that will exist within any partition. • Common usage would be states. A state partition table would commonly have 50 partitions, one for each state. •  SUB-PARTITIONS • Utilized most often when partition strategy does not provide small enough partition units to achieve maintenance goals. Sub-partitions further divide table based another column. www.sagelogix.com

  22. Types of Partitioning • Example: RANGE - (a max partition will capture any values beyond the stated ranges – including NULLS) Create table range_partition ( date_col date) partition by RANGE (date_col) ( partition p_jan_2001 values less than (to_date(‘01022001’,’ddmmyyyy’)), partition p_feb_2001 values less than (to_date(‘01032001’,’ddmmyyyy’)), partition pmax values less than (maxvalue) ); www.sagelogix.com

  23. Types of Partitioning • Example: HASH – (most optimal when 8, 16, or 32 partitions are used). Create table hash_partition (account_id varchar2(30)) partition by HASH (account_id) partitions 16 www.sagelogix.com

  24. Types of Partitioning • Example: LIST Create table list_partition (state_id varchar2(2)) partition by LIST (state_id) ( partition P_MI values (‘MI’), partition P_CO values (‘CO’) ); www.sagelogix.com

  25. Practical Partitioning Usages • Partition Usage I – Data Warehousing • based on date ranges (daily or monthly) • Partition Usage II – OLTP • based upon frequently accessed key • Partition Usage III – ODS • based upon date range and key • Partition Usage IV – Temporary Storage • Partitions rotate and reused over time Example: A partition is based by day of month. 31 partitions created & date function used to place rows in partition based by day of month. Partitions read by application that TRUNCATES partitions after reading data. www.sagelogix.com

  26. Statistics • Cost-based optimizer of Oracle is partitioning-aware. Rule-based optimizer does not “do” partitions. • Cost-based optimizer works off of statistics. Statistics on standard tables easier to generate & comprehend than statistics on partition tables. • Statistics are the number one problem with partitioning implementations. • With partitions, there are LOCAL and GLOBAL statistics. • GLOBAL statistics are utilized when GLOBAL operations are performed. • LOCAL statistics are utilized when partition key is available and partition elimination is possible. www.sagelogix.com

  27. Statistics • Examples: Select * from range_partition where value1 = :b1; • Value1 is indexed GLOBALLY (i.e. only global statistics are reviewed). Optimizer will determine if full tables scan or index lookup is most appropriate. Select * from range_partition Where value1 = :b1 And value2 = :b2 And part_key = :b3 • Local statistics & global statistics are evaluated. Local statistics come into play because PART_KEY is within the where clause. www.sagelogix.com

  28. Statistics • Statistics are gathered LOCALLY or GLOBALLY. • Once gathered, they are tied together (i.e. partition maintenance operations that impact GLOBAL operations also impact GLOBAL statistics). • If a partition is added to a table, GLOBAL statistics may “disappear”. www.sagelogix.com

  29. Statistics • NO table statistics • If there are NO table statistics at all, the optimizer acts “relatively” rule-based. • Relatively Rule • Rule 1: If GLOBAL index exists and can be used, it will be. • Rule 2: If there are no GLOBAL indexes, LOCAL indexes will be used (if they exist). • Therefore, NO statistics is an option if all indexes created on the table are good choices and any GLOBAL indexes are superior to LOCAL indexes. • Conclusion: If the partition is to be queried from a single column and that column is the partition key with an index, then the absence of gathering statistics is optimal. www.sagelogix.com

  30. Statistics • Gathering Statistics - LOCALLY • If the following commands are used INITIALLY to gather statistics, and no other command used, then GLOBAL statistics are derived. Execute dbms_stats.gather_table_stats(owner,'RANGE_PARTITION','P2',CASCADE=>TRUE); OR execute dbms_stats.gather_table_stats(owner,'RANGE_PARTITION','P2',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 200'); • The only difference between these statistics commands is the generation of histograms. www.sagelogix.com

  31. Statistics • Gathering Statistics – LOCALLY, How To: • The GLOBAL statistics are populated after running a LOCAL script. • Before generating statistics on any of the partitions: Select num_rows from dba_tables where table_name=‘RANGE_PARTITION’; NUM_ROWS=NULL Select partition_name,num_rows from dba_tab_partitions where table_name = ‘RANGE_PARTITION’; • All rows have a NUM_ROWS=NULL www.sagelogix.com

  32. Statistics • Gathering Statistics – LOCALLY, cont. • After a SINGLE execution of a statistic generation statement: execute dbms_stats.gather_table_stats ('SYSTEM','RANGE_PARTITION','P1',CASCADE=>TRUE); Select num_rows from dba_tables where table_name = ‘RANGE_PARTITION’; • Global Result = 250,000 Select partition_name,num_rows from dba_tab_partitions where table_name = ‘RANGE_PARTITION’; • Results =P1 79999, P2 NULL, PMAX NULL www.sagelogix.com

  33. Statistics • Gathering Statistics – LOCALLY, cont. • The GLOBAL statistics are “guessed” & populated. Once LOCAL statistics are generated, GLOBAL statistics are aggregate and not “reality”. Gathering statistics this way uses the relatively rule method of optimization: if GLOBAL index exists, it’s used. LOCAL indexes are evaluated if the where clause allows for partition pruning. • These commands do not account for GLOBAL table units (the GLOBAL indexes were never analyzed). • Once GLOBAL indexes are analyzed, all needed “units” have statistics and optimizer takes over. execute dbms_stats.gather_index_stats ('SYSTEM','RANGE_PARTITION_DESC'); www.sagelogix.com

  34. Statistics • Gathering Statistics – GLOBALLY • The following command will gather GLOBAL and LOCAL statistics. execute dbms_stats.gather_table_stats(owner, ‘RANGE_PARTITION', GRANULARITY=>'ALL',CASCADE=>TRUE); • This command is equivalent to all the commands earlier. This is the recommended approach for initial gathering of statistics on partitions, as it ensures that ALL statistics are gathered. www.sagelogix.com

  35. Statistics • Partition Maintenance Effects • Vary by release. • Release 8.x, GLOBAL statistics temporarily disappear (the num_rows value becomes NULL). • Release 9.x, the GLOBAL statistics do not change. • For both, statistics are no longer valid and need updating. • An advantage of a partitioned table is the ability to perform maintenance work on smaller segments - ONLY the modified partitions are updated. • All GLOBAL statistics for the table will be corrected (including GLOBAL index statistics). www.sagelogix.com

  36. Statistics • Partition Maintenance Effects, cont. • Once any partition modifications occur, ensure to run the statistics immediately on the effected partitions. Failure to do so can lead to the optimizer’s inability to parse the SQL statement. HANGING CAN OCCUR. • If this occurs, best corrective actions are to remove all statistics and generate them again. www.sagelogix.com

  37. Partitioning Options • Enable Row Movement • Release 8I and above: A new option with Oracle partition. • Allows updates to partition key to occur when update would “relocate” a row from one partition to another. • As the partition id is stored within the ROWID, this WILL CHANGE the ROWID for the row. This could impact application programs that utilize ROWID. • Exchange without validation • When a partition is exchanged with another table: Alter table part_table exchange partition p1 with table fk_table without validation; www.sagelogix.com

  38. Partitioning Options • Exchange without Validation, cont. • It is possible that rows from this partition cannot be transparently queried. • Validation ensures rows in “fk_table” qualify for given partition. When this option is bypassed (for performance reasons), one must ensure that new partition rows do not violate constrained partition boundaries. • Given that partition pruning occurs prior to selection, violation of boundaries could render false results from a query. www.sagelogix.com

  39. Partitioning Options • Exchange without validation - Example: CREATE TABLE part_test (ID NUMBER NOT NULL) PARTITION BY RANGE (ID) (PARTITION P1 VALUES LESS THAN (10), PARTITION P2 VALUES LESS THAN (20)); create table fk_table (id number not null); insert into part_test values (5); insert into fk_table values (5); commit; alter table part_test exchange partition p2 with table fk_table without validation; -- Returns 2 rows - both with the value of 5 select * from SYSTEM.PART_TEST; -- Returns 1 row - with the value of 5 select * from SYSTEM.PART_TEST where id=5; www.sagelogix.com

  40. Partitioning Options • Oracle Initialization Parameters • Oracle’s partitioning option coverts one table into many physical segments. • More physical segments require more resources from Oracle SGA. • In particular, Oracle initialization parameter DML_LOCKS must be set to accommodate partitioning. (i.e. if a table has 1000 partitions, then DML_LOCKS must be set to at least 1000 or the table cannot be created.) www.sagelogix.com

  41. Conclusion • With the advent of partitioning, improved database administration with maintenance operations occurring at a partition level rather than at the table or index level, allow Database Administrators to provide improved SLAs. • Partitioning then becomes a crucial aspect of any database containing large amounts of data. As maintenance windows decrease in length due to cost of downtime, understanding methods to shorten database downtime is critical for success. • After researching the various methods, each DBA should test partitioning scheme best suited for their environment. • Performance and maintenance are the primary concerns to account for when implementing partitioning option. The partitioning of large tables allows for faster data access, as well as decreased maintenance windows. www.sagelogix.com

  42. Conclusion, cont. • Partitioning should not be taken lightly. However, it should be considered for any database with excessive data or when excessive growth is anticipated. • Please check out our website at www.sagelogix.com/partitioning. This location has a download zip file containing source code, which will automate the maintenance of date based range partitions. www.sagelogix.com

  43. Q & A Frank Bommarito SageLogix, Inc www.sagelogix.com www.sagelogix.com

More Related