1 / 82

Working with Partitioned Tables The Unpleasant Details

Working with Partitioned Tables The Unpleasant Details. Brian Hitchcock OCP 8, 8i, 9i DBA Sun Microsystems brian.hitchcock@sun.com brhora@aol.com. NoCOUG. Brian Hitchcock November 13, 2003. Page 1. The Application – History. Website click-stream data

faunus
Télécharger la présentation

Working with Partitioned Tables The Unpleasant Details

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. Working with Partitioned Tables The Unpleasant Details Brian HitchcockOCP 8, 8i, 9i DBA Sun Microsystems brian.hitchcock@sun.com brhora@aol.com NoCOUG Brian Hitchcock November 13, 2003 Page 1

  2. The Application – History • Website click-stream data • Large amounts of data generated daily • Users want to access data immediately • Next day was acceptable • Users want 14 months of data on-line • Performance was poor • Huge tables, millions of rows • Inserting new data very slow • Retrieving data very slow • User generate SQL • No controls on the quality of the SQL

  3. Application – How it Works • Website click-stream log files sent to db machine daily • Database tables setup with weekly partitioning • 3 main tables • Range partitioning on date • New data added to db once per day • Load must finish before users can access each day • Failure of load process, users can’t work, must load 2 days data to catch up

  4. The Application – Act I • Consultants setup weekly partitioning • Vendor scripts all setup for daily partitioning • We had to setup our own maintenance scripts • Once per week • Create new week partition • oldest weekly partition • Analyze new partition • Vendor tech support won’t support weekly partitioning • 14 months of data on-line, 60 weekly partitions

  5. The Application – Act II • The Consultant Is Gone (call Brian…) • Vendor wants us to upgrade • New app version is much faster (“trust us”) • Need to move to daily partitioning • Users agree to 7 months data online • Improve performance overall (less total data) • We can use vendor’s maintenance scripts • Reduced headcount makes our scripts hard to maintain • Easier to get support from vendor tech support

  6. Project Phases • As I inherited the project • 14 months weekly partitions • Vendor Upgrade I • Move to daily partitioning • Drop 7 months of data • Vendor Upgrade II • Required renaming of index partitions • Must match table partition names

  7. What Are Partitioned Tables? • Single Big Table • Many sub-tables (partitions) • Each partition • Acts like a separate table • Index (partition) for each partition • Performance • Load to separate partition(s) • Analyze each partition separately • SQL can be satisfied with small subset of entire table • Partition pruning • Answer to a Jeopardy question • Alternate term for DBA job security…

  8. Why Use Them? • Performance • Faster SQL • Partition Pruning • Faster Analyze • Only analyze single partition • Logical • Application data is ‘partitioned’ (weekly for this case) • Easy to add new partition, drop oldest partition • Faster than deleting some rows from larger table • Faster than inserting new rows into larger table

  9. Partitioned Table Table_1 Partition_1 Table_1 Column A Column B Date Column A Column B Date Column A Column B Date Table_1 Partition_2 Column A Column B Date Non-Partitioned Table_1 Partition_3 Partitioned

  10. Inserting Data • Oracle examines • Inserted value of column used for range partitioning • High_value of existing partitions • Rows with values greater than or equal to the highest high_value • If MAXVALUE used, inserted • If MAXVALUE not used, rejected

  11. Partitioned Table Values > or = 10/02/2003 rejected Column A Column B Date Partition_1 high_value 10/02/2003 Rows with range values less than 10/02/2003 And > or = to 06/02/2003 Will be inserted into Partition_1 Column A Column B Date Partition_2 high_value 06/02/2003 Rows with range values less than 06/02/2003 And > or = 02/02/2003 Will be inserted into Partition_2 Column A Column B Date Partition_3 high_value 02/02/2003 Rows with range values less than 02/02/2003 Will be inserted into Partition_3 Partitioned

  12. Partitioned Table MAXVALUE Column A Column B Date Partition 1 high_value MAXVALUE Rows with range values greater than or = to 06/02/2003 Will be inserted into Partition 1 Column A Column B Date Partition 2 high_value 06/02/2003 Rows with range values less than 06/02/2003 Will be inserted into Partition 2 Column A Column B Date Partition 3 high_value 02/02/2003 Rows with range values less than 02/02/2003 Will be inserted into Partition 3 Partitioned – Using MAXVALUE

  13. Partitioning -- Example CREATE TABLE stock_xactions (stock_symbol CHAR(5), stock_series CHAR(1), num_shares NUMBER(10), price NUMBER(5,2), trade_date DATE) STORAGE (INITIAL 100K NEXT 50K) LOGGING PARTITION BY RANGE (trade_date) (PARTITION sx1992 VALUES LESS THAN (TO_DATE('01-JAN-1993','DD-MON-YYYY')), PARTITION sx1993 VALUES LESS THAN (TO_DATE('01-JAN-1994','DD-MON-YYYY')), PARTITION sx1994 VALUES LESS THAN (MAXVALUE)); select * from user_tab_partitions; TABLE_NAME COM PARTITION_NAME SUBPARTITI HIGH_VALUE ----------------------------- --- ------------------------------ ---------- --------------------------------------------------------------------- STOCK_XACTIONS N0 SX1992 0 TO_DATE(' 1993-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', STOCK_XACTIONS N0 SX1993 0 TO_DATE(' 1994-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', STOCK_XACTIONS N0 SX1994 0 MAXVALUE 3 rows selected.

  14. Weekly Partition Maintenance • Once per week • Compute high_value of next weekly partition • Create partition for next week • Drop oldest weekly partition • Rebuild indexes • Or drop and recreate indexes • Load data into new partition • Analyze new partition • Once loaded, analyzed • Maximum read performance • Partition no longer has inserts or analyze

  15. Weekly Maintenance Add New Partition – Load Data, Analyze Existing Partitions 14 months, 60 weekly partitions Existing Partitions Drop Oldest Partition

  16. Partitions and Tablespaces • For performance, want to control placement of partitions • New, data being loaded • Existing, data being retrieved • Use separate tablespace for each partition • Spread across 8 filesystems (8 sets of disks) • Tablespaces of sequential table partitions • Same for index partitions but offset • Corresponding table/index partitions always on different filesystems

  17. Partitions and Tablespaces • Design Goal • Partition being loaded, analyzed • One separate disks from partitions being read • Assumes most queries read data from most recent partitions • If partition_1 being loaded • Partitions 2, 3, 4 can be read without conflicting with the load, analyze • Partitions can be used for fine-grained placement of data on disk

  18. A Plug for Certification • I didn’t know anything about Partitioned Tables • Wasn’t planning to use them • Memorized what I needed to pass the OCP exam • Within a month… • Phone rings – “Can you help with partitioned tables?”

  19. Types of Partitioning • Range • Partition based on value of column(s) of table • Date • Part Number • SSN • Hash • Rows spread evenly across all partitions • List (9i) • Partitions based on user-specified lists of values

  20. Partitioning Details • Create partitions • You need to name each partition • If you don’t • Oracle names them for you • The names are not easy to use • Many other partitioning possibilities • This is not a training course on partitioned tables • Only cover what I actually used • More options with 9i, this was for 8i

  21. What About Indexes? • Indexes of Partitioned Tables • Local • Index partitions always align with table partitions • Global • Different partitioning from table partitioning • Become invalid when • Add, drop, split existing partitions • SQL to manually rebuild invalid or unusable index partitions

  22. Partition Maintenance • Depends on your application • May need to handle • Adding new • Dropping old • Splitting • Analyzing

  23. Partitioned Table Info • System Tables • dba_tables • dba_tab_partitions • dba_indexes • dba_ind_partitions • dba_part_key_columns

  24. Partitioned Table Info -- SQL • Number of partitioned tables • select count(*) from dba_tables where partitioned='YES'; • Name of each partitioned table • select table_name, partitioned from dba_tables where partitioned='YES' order by table_name; • Number of partitions in each partitioned table • select table_name, count(*) from dba_tab_partitions group by table_name; • Same info for indexes • select count(*) from dba_indexes where partitioned='YES'; • select index_name, partitioned from dba_indexes where partitioned='YES' order by index_name; • select index_name, count(*) from dba_ind_partitions group by index_name;

  25. Partitioned Table Info -- SQL • Number of columns used, range partitioning • select name, object_type, count(*) from dba_part_key_columns group by name, object_type; • Partitioning key columns • select * from dba_part_key_columns; • Indexes on each partitioned table • select table_name, index_name from dba_indexes where tablespace_name is NULL order by table_name, index_name;

  26. Partitioned Table Info -- SQL • High values for table partitions • set long 30 • select table_name, tablespace_name, partition_name, high_value from dba_tab_partitions order by table_name, tablespace_name, partition_name; • High values for index partitions • set long 30 • select index_name, tablespace_name, partition_name, high_value from dba_ind_partitions order by index_name, tablespace_name, partition_name;

  27. Partition Range • How to find ‘range’ for each partition? • Only have high_value for each partition • Have to examine ordered list of high_value • Ranges not stored in system tables • Range of values is dynamic • Range of highest partition changes as higher values inserted (assumes MAXVALUES used) • Same for lowest partition • Examine ordered list of high_value • Can’t “order by” LONG • high_value is a LONG

  28. Partition Range • Need ordered list of high_values • Use DBA_TAB_PARTITIONS • partition_position • High_value • Use partition_position? • Oracle docs only say • position of the partition within the table • What exactly does that mean?

  29. Partition Range • Partition_Position • Numbers the partitions in order of high_value • Numbers change with each add, split, drop of any partition • Is dynamic • What to do? • I decided to use high_value because it doesn’t change as partitions are manipulated • But, can’t “order by” • More on this later…

  30. Vendor Upgrade I • Existing 14 months data • Weekly partitions • Reduce to 7 months • Hard to find the correct partitions to drop • Split each weekly partition • 7 daily partitions

  31. Do the Math • 7 Months (36 weeks) • 252 daily partitions • Times 3 tables • Times 11 indexes • Total of 3528 partitions = (756 table + 2772 index) • Vendor upgrade • Requires specific, rigid partition naming scheme • I don’t get to pick the partition names

  32. Oracle Utilities? • There aren’t any that • Split tables based on your requirements • Take a set of existing partitions, tablespaces • Move to new partitions, tablespaces • No utility to map from one partitioning to another • As the number of partitions grows, so does the work involved to shift from one partitioning scheme to another • The same things that make partitioned tables so good for performance make them a lot more work to maintain • Lots of small pieces to maintain

  33. Partition Split Issues • When splitting • Need new tablespaces • Have existing tablespaces • Split SQL sends both table and index partitions to same new tablespace • New tablespace needs to be 2x final size • Separate tablespace for new index partitions • Reclaim disk space from new table partition tablespace • Overall need 3x disk space during splits • Need to reclaim this disk space after move to daily partitions

  34. Disk Space Needed Original Partitions Existing Table Partition TP1 Tablespace Tab1 Existing Index Partition IP1 Tablespace Ind1 • Need 3x original tablespace (disk space) Split Partitions Tablespace Tab2 Tablespace Ind2 Post-split Table Partition TP1a, Index Partition IP1a Post-split Table Partition TP1b, Index Partition IP1b Tablespace Tab3 Tablespace Ind3 Tablespace Tab4 Tablespace Ind4 Table Partition TP1a Index Partition IP1a Table Partition TP1b Index Partition IP1b Separate Table/Index Partitions Assuming index partition is same size as table partition Assumes indexes not dropped before split

  35. Disk Space Reclaimed EMPTY TBLSPC Existing Table Partition TP1 Tablespace Tab1 Existing Index Partition IP1 Tablespace Ind1 • After split, need to reclaim 2/3 of disk space Tablespace Tab2 Tablespace Ind2 Post-split Table Partition TP1a, Index Partition IP1a Post-split Table Partition TP1b, Index Partition IP1b Tablespace Tab3 Tablespace Ind3 Tablespace Tab4 Tablespace Ind4 Table Partition TP1a Index Partition IP1a Table Partition TP1b Index Partition IP1b

  36. Tasks for Daily Partitioning • Create new tablespaces • Single tablespace for partitions of each table • Twice as big as final size • Single tablespace for partitions of each index • Drop existing indexes (weekly partitions) • Total disk space needed reduced from 3x to 2x • Split weekly partitions into daily partitions • Re-create indexes • Daily partitions created automatically • Reclaim disk space

  37. Move to Daily Partitioning • Reduce to 7 months week partitions • SQL to drop existing weekly partitions simple to generate • Hard to see which partitions to drop since you can’t order by high_value • Split Existing Weekly Partitions • Generate SQL for one week • Cut/paste/edit for other weekly partitions • Tedious, error prone • SQL isn’t consistent enough to make automation straight-forward • 37 weekly partitions need splitting

  38. Weekly Partition Split High_value = 2003-05-10 -- existing weekly partition BRH_TAB_PART_5 High_value = 2003-05-09 partition BRH_TAB_PART_5_nexta BRH_TAB_ PART_5_7 2003-05-10 High_value = 2003-05-08 partition BRH_TAB_PART_5_nextb BRH_TAB_ PART_5_6 2003-05-09 High_value = 2003-05-07 partition BRH_TAB_PART_5_nextc BRH_TAB_ PART_5_5 2003-05-08 High_value = 2003-05-06 partition BRH_TAB_PART_5_nextd BRH_TAB_ PART_5_4 2003-05-07 High_value = 2003-05-05 BRH_TAB_PART_5_nexte BRH_TAB_ PART_5_3 2003-05-06 a BRH_TAB_ PART_5_1 BRH_TAB_ PART_5_2 High_value = 2003-05-04 2003-05-05 BRH_TAB_ PART_5_1 BRH_TAB_ PART_5_2 BRH_TAB_ PART_5_3 BRH_TAB_ PART_5_4 BRH_TAB_ PART_5_5 BRH_TAB_ PART_5_6 BRH_TAB_ PART_5_7

  39. Weekly Partition Split SQL -- existing weekly partition BRH_TAB_PART_5 TO_DATE('2003-05-10 00:00:00' alter table brhuser.BRH_TABLE split partition BRH_TAB_PART_5 at ( TO_DATE('2003-05-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) into (partition BRH_TAB_PART_5_nexta tablespace BRH_TAB_TBLSPC, partition BRH_TAB_PART_5_7 tablespace BRH_TAB_TBLSPC); alter table brhuser.BRH_TABLE split partition BRH_TAB_PART_5_nexta at ( TO_DATE('2003-05-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) into (partition BRH_TAB_PART_5_nextb tablespace BRH_TAB_TBLSPC, partition BRH_TAB_PART_5_6 tablespace BRH_TAB_TBLSPC); alter table brhuser.BRH_TABLE split partition BRH_TAB_PART_5_nextb at ( TO_DATE('2003-05-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) into (partition BRH_TAB_PART_5_nextc tablespace BRH_TAB_TBLSPC, partition BRH_TAB_PART_5_5 tablespace BRH_TAB_TBLSPC); alter table brhuser.BRH_TABLE split partition BRH_TAB_PART_5_nextc at ( TO_DATE('2003-05-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) into (partition BRH_TAB_PART_5_nextd tablespace BRH_TAB_TBLSPC, partition BRH_TAB_PART_5_4 tablespace BRH_TAB_TBLSPC); alter table brhuser.BRH_TABLE split partition BRH_TAB_PART_5_nextd at ( TO_DATE('2003-05-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) into (partition BRH_TAB_PART_5_nexte tablespace BRH_TAB_TBLSPC, partition BRH_TAB_PART_5_3 tablespace BRH_TAB_TBLSPC); alter table brhuser.BRH_TABLE split partition BRH_TAB_PART_5_nexte at ( TO_DATE('2003-05-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) into (partition BRH_TAB_PART_5_1 tablespace BRH_TAB_TBLSPC, partition BRH_TAB_PART_5_2 tablespace BRH_TAB_TBLSPC);

  40. Weekly Partition Split SQL • Split partition SQL • Requires two new partition names • Splits existing partition by specifying new high_value • Existing data moves to two new partitions based on above or below new high_value • If you don’t specify partition names, Oracle names them for you • Oracle partition names are not intuitive… • New partitions move to specified tablespaces • New index partitions also moved to same tablespaces • Need to move new index partitions to separate tablespace(s) as separate step • Or, drop indexes, recreate after all splits done • Indexes split automatically as table splits

  41. Vendor Upgrade II • Now that daily partitioning is setup • Vendor upgrade fails • Specific index partition naming requirements • Our home-grown weekly partitioning didn’t meet these • We need this done by Monday… • Labor Day Weekend • I didn’t want to do this manually again • Need to rename existing index partition names • Number in partition name must agree with number in table partition name • Lots of SQL that must be perfect

  42. But Wait, There’s More • Vendor had executed a script that partially renamed some partitions • I had to fix this • Existing names of these partitions conflicted with the names I had to generate • Vendor upgrade process requires specific format for daily partition name and number • Where is the Vendor DBA?

  43. How to Automate? • What needs to be done • Document existing names of table and index partitions • Rename index partitions using vendor requirements • Deal with vendor’s mistakes – some partitions already renamed • Develop process to deal with this and future needs

  44. SQL • Document existing table and index partition names • Create new names for index partitions • Index partitions use same numbering as corresponding table partition names • Generate all SQL statements to alter existing index partition names • Document all intermediate steps

  45. Details • High Value of each partition stored in LONG column • LONG datatype is special • Can’t be used in any SQL function • Can’t order by etc. • In SQL*Plus, set LONG 100 to see all of high_value

  46. SQL Script • Need to ‘order by’ high value to show existing weekly partitions • How to get around LONG limitations? • Export • LONG is converted to a string • Import • Import string into VARCHAR2 column • Can ‘order by’ on the VARCHAR2 column

  47. Table of Index Partition Names • Store data about existing table and index partitions • Use SQL to create new index partition names within the table • Flexible • Self-documenting • Select from table to generate SQL for • Changing index partition names • Fix vendor partition naming mistakes

  48. For Each Partitioned Index • Create table • Table name • Table tablespace name • Table partition name • Table partition high value (text version) • Index name • Index tablespace name • Index partition name (Existing) • Index partition high value (text version) • Temporary index partition name • Final index partition name (used for renaming)

  49. Index Partition Renaming • The process • Spool existing table and index info • Including high_value for each partition • Use SQL*Loader to insert info into table • Use SQL to insert temporary and final index partition names into table • Temporary index partition name deals with any name conflicts… • Use SQL to generate all SQL statements to actually change index partition names

  50. SQL Used --> generate file of table partitions for BRH_TABLE... sqlplus -s brhuser@BRH_DB1 spool BRH_TABLE_partition_data_05242003.txt set long 30 set pagesize 1500 set linesize 150 set echo off set feedback off set heading off select SUBSTR(table_name, 1, 25), ',', SUBSTR(tablespace_name,1,20), ',', SUBSTR(partition_name,1,20),',"', high_value, '"' from dba_tab_partitions where table_name='BRH_TABLE' order by partition_name; spool off exit example data: BRH_TABLE , BRH_TAB_TBLSPC , BRH_TAB_PART_1 ," TO_DATE(' 2003-05-04 00:00:00' " BRH_TABLE , BRH_TAB_TBLSPC , BRH_TAB_PART_10 ," TO_DATE(' 2003-05-13 00:00:00' " BRH_TABLE , BRH_TAB_TBLSPC , BRH_TAB_PART_100 ," TO_DATE(' 2002-12-02 00:00:00' " BRH_TABLE , BRH_TAB_TBLSPC , BRH_TAB_PART_101 ," TO_DATE(' 2002-12-03 00:00:00' " BRH_TABLE , BRH_TAB_TBLSPC , BRH_TAB_PART_102 ," TO_DATE(' 2002-12-04 00:00:00' "

More Related