1 / 51

[ Michelle Kolbe]

[ Michelle Kolbe]. 12c Partitioning for Data Warehouses OOW 2014. [ 12c Partitioning for Data Warehouses]. Some Background. [12c Partitioning for Data Warehouses]. My Story. Lead BI Engineer at Past: Data Architect and BI Developer at Intermountain Healthcare

autumn-jackson
Download Presentation

[ Michelle Kolbe]

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. [ Michelle Kolbe] 12c Partitioning for Data Warehouses OOW 2014

  2. [ 12c Partitioning for Data Warehouses] Some Background

  3. [12c Partitioning for Data Warehouses] My Story • Lead BI Engineer at • Past: Data Architect and BI Developer at Intermountain Healthcare • President of Utah Oracle Users Group • IOUG 2014-15 Board Member • Collaborate 13 & 14 Conference Committee • Utah State MIS Advisory Council Member • University of Utah Adjunct Professor • Past Contributing Editor of IOUG Select Journal • Past Advisory Board Member of Healthcare Data Warehousing Association

  4. [12c Partitioning for Data Warehouses] The Backcountry Story • Online retailer of outdoor products • Started in 1996 selling avalanche beacons out of a garage in Park City, Utah • 8 Websites: • Backcountry.com • Dogfunk • Competitive Cyclist • Steep and Cheap • Whiskey Militia • Chainlove • MotoSport • Bergfreunde.de

  5. Visit IOUG at the User Group Pavilion • Stop by at the User Group Pavilion in the lobby of Moscone South and catch up with the user community!  • Connect with IOUG members and volunteers • Pick up a discount to join the IOUG community of 20,000+ technologists strong • Enter for the chance to win books from IOUG Press or a free registration to COLLABORATE 15! • Visit us Sunday through Wednesday!

  6. IOUG SIG Meetings at OpenWorld All meetings located in Moscone South - Room 208 Sunday, September 28Cloud Computing SIG: 1:30 p.m. - 2:30 p.m. Monday, September 29Exadata SIG: 2:00 p.m. - 3:00 p.m.BIWA SIG: 5:00 p.m. – 6:00 p.m. Tuesday, September 30Internet of Things SIG: 11:00 a.m. - 12:00 p.m.Storage SIG: 4:00 p.m. - 5:00 p.m.SPARC/Solaris SIG: 5:00 p.m. - 6:00 p.m. Wednesday, October 1Oracle Enterprise Manager SIG: 8:00 a.m. - 9:00 a.m.Big Data SIG: 10:30 a.m. - 11:30 a.m. Oracle 12c SIG: 2:00 p.m. – 3:00 p.m.Oracle Spatial and Graph SIG: 4:00 p.m. (*OTN lounge)

  7. COLLABORATE 15 – IOUG Forum April 12-16, 2015 Mandalay Bay Resort and Casino Las Vegas, NV The IOUG Forum Advantage • Save more than $1,000 on education offerings like pre-conference workshops • Access the brand-new, specialized IOUG Strategic Leadership Program • Priority access to the hands-on labs with Oracle ACE support • Advance access to supplemental session material and presentations • Special IOUG activities with no "ante in" needed - evening networking opportunities and more www.collaborate.ioug.org Follow us on Twitter at @IOUG or via the conference hashtag #C15LV! COLLABORATE 15 Call for SpeakersEnds October 10

  8. [ 12c Partitioning for Data Warehouses] Why and What is Partitioning?

  9. [12c Partitioning for Data Warehouses] Partitioning Background • First introduced in Oracle 8i • Enables large table to be split into smaller pieces to improve • Performance • Availability • Manageability • Queries use partition pruning to only read pertinent blocks

  10. [12c Partitioning for Data Warehouses] Partitioning Strategies • Range • List • Hash • Interval • Reference • Virtual Column • System • Composite – Combination of Range, List or Hash

  11. [ 12c Partitioning for Data Warehouses] How We Use Partitioning

  12. [12c Partitioning for Data Warehouses] Fact Tables All fact tables are interval partitioned by date, some monthly, weekly, and daily partitions Last 2 years stored on SSD storage Yearly run a process to move partitions older than 2 years to slow storage and merge into bigger partitions Most indexes are local except PK, no partial indexes yet

  13. [12c Partitioning for Data Warehouses] Visits Fact Table 2012 – Current in weekly partitions on SSD disk Prior to 2012 in quarterly partitions on slow disk Global index on PK and Natural Key Local bitmap indexes on all FKs

  14. [12c Partitioning for Data Warehouses] Special Cases A few fact tables have more than one date field that will be used frequently in queries For examples, for sales we care about the ship date and also the order date Partition on date used the most Global index on other date

  15. [ 12c Partitioning for Data Warehouses] Interval-Reference Partitioning

  16. [12c Partitioning for Data Warehouses] Interval-Reference Partitioning • Composite partition first by interval then reference • Parent table creates new partitions when data arrives • Child table is automatically maintained • Partition names inherited

  17. [12c Partitioning for Data Warehouses] Interval-Reference Example in 11g create table orders ( order_number number, order_date_id number, constraint orders_pk primary key(order_number) ) partition by range(order_date_id) INTERVAL(7) ( partition p1 values less than (20140101) ); table ORDERS created.

  18. [12c Partitioning for Data Warehouses] Interval-Reference Example in 11g create table orderlines ( orderline_id number, order_number number not null, constraint orderlines_pk primary key(orderline_id), constraint orderlines_fk foreign key (order_number) references orders ) partition by reference(orderlines_fk); ORA-14659: Partitioning method of the parent table is not supported

  19. [12c Partitioning for Data Warehouses] Interval-Reference Example in 12c --Same script as 11g table ORDERS created. table ORDERLINES created.

  20. [12c Partitioning for Data Warehouses] Interval-Reference Example in 12c --What partitions do we have? select table_name, partition_name, high_value, interval from user_tab_partitions where lower(table_name) in ('orders', 'orderlines');

  21. [12c Partitioning for Data Warehouses] Interval-Reference Example in 12c --Insert some data into and check partitions insert into orders values (1, 20131231); insert into orders values (2, 20140102); insert into orders values (3, 20140113); commit; 1 rows inserted. 1 rows inserted. 1 rows inserted. committed. select table_name, partition_name, high_value, interval from user_tab_partitions where lower(table_name) in ('orders', 'orderlines');

  22. [12c Partitioning for Data Warehouses] Interval-Reference Example in 12c --Insert into orderlines and check partitions insert into orderlines values (1, 2); commit; 1 rows inserted. committed.

  23. [12c Partitioning for Data Warehouses] Interval-Reference Example in 12c --Split into subpartitions alter table orders split partition for (20140104) at (20140104) into (partition p20140101, partition p20140104); table ORDERS altered. select table_name, partition_name, high_value, interval from user_tab_partitions where lower(table_name) in ('orders', 'orderlines');

  24. [ 12c Partitioning for Data Warehouses] Indexes

  25. [12c Partitioning for Data Warehouses] Types of Indexes on Partitioned Tables • Global Non-Partitioned Index • Global Partitioned Index • Local Index

  26. [12c Partitioning for Data Warehouses] Partial Index • Index that only spans certain partitions, not all • Works on local and global indexes • Can be overwritten at any time

  27. [12c Partitioning for Data Warehouses] Partial Index * Chart from an Oracle presentation

  28. [12c Partitioning for Data Warehouses] Partial Index Example create table orders ( order_number number , col2 number , col3 number , col4 number ) indexing off partition by range(order_number) ( partition p1 values less than (100) indexing on, partition p2 values less than (200) indexing on, partition p3 values less than (300) indexing on, partition p4 values less than (400) indexing on, partition p5 values less than (500) indexing on, partition p_max values less than (MAXVALUE) indexing off ); table ORDERS created.

  29. [12c Partitioning for Data Warehouses] Partial Index Example Partition definitions: select table_name, partition_name, high_value, indexing from user_tab_partitions where table_name = 'ORDERS';

  30. [12c Partitioning for Data Warehouses] Partial Index Example • Now create two LOCAL indexes; the first one is a partial index. • create index orders_idx1 on orders(order_number) local indexing partial; • create index orders_idx2 on orders(col2) local; • index ORDERS_IDX1 created. • index ORDERS_IDX2 created. • And let’s check out how these are defined in the Index Partitions table.select index_name, partition_name, statusfrom user_ind_partitionswhere index_name in ('ORDERS_IDX1', 'ORDERS_IDX2');

  31. [12c Partitioning for Data Warehouses] Partial Index Example • Now let’s create two GLOBAL indexes, the first one being a partial index.create index orders_idx3 on orders(col3) indexing partial; • create index orders_idx4 on orders(col4); • index ORDERS_IDX3 created. • index ORDERS_IDX4 created. • And now let’s query the indexes table for these indexes. • select index_name, status, indexing • from user_indexes • where index_name in ('ORDERS_IDX3', 'ORDERS_IDX4');

  32. [12c Partitioning for Data Warehouses] Partial Index Example Check segments: select segment_name, segment_type, count(*) from user_segments where segment_name in ('ORDERS_IDX1', 'ORDERS_IDX2', 'ORDERS_IDX3', 'ORDERS_IDX4') group by segment_name, segment_type order by 1;

  33. [12c Partitioning for Data Warehouses] Partial Index Example Explain Plan for a query against orders_idx3 explain plan for select count(*) from orders where col3 = 3; select * from table(dbms_xplan.display);

  34. [ 12c Partitioning for Data Warehouses] Partition Maintenance

  35. [12c Partitioning for Data Warehouses] Adding or Dropping Multiple Partitions Now available in 12c ALTER TABLE orders_range_part ADD PARTITION 2014 VALUES LESS THAN to_date(‘01-01-2015’, ‘MM-DD-YYYY’), PARTITION 2015 VALUES LESS THAN to_date(‘01-01-2016’, ‘MM-DD-YYYY’), PARTITION 2016 VALUES LESS THAN to_date(‘01-01-2017’, ‘MM-DD-YYYY’);

  36. [12c Partitioning for Data Warehouses] Splitting or Merging Multiple Partitions Now available in 12c Merge: ALTER TABLE orders_range_partMERGE PARTITIONS year_2010, year_2011, year_2012, year_2013INTO PARTITION historical_data_partition; Split: ALTER TABLE orders_range_partSPLIT PARTITION year_2013 INTO(year_2013_q1 VALUES LESS THAN to_date(‘04-01-2013’, ‘MM-DD-YYYY’), year_2013_q2 VALUES LESS THAN to_date(‘07-01-2013’, ‘MM-DD-YYYY’), year_2013_q3 VALUES LESS THAN to_date(‘10-01-2013’, ‘MM-DD-YYYY’), year_2013_q4);

  37. [12c Partitioning for Data Warehouses] Splitting or Merging Multiple Partitions Another way to write these as a range or list of values. ALTER TABLE orders_range_partMERGE PARTITIONS year_2010 to year_2013INTO PARTITION historical_data_partition; ALTER TABLE orders_range_partMERGE PARTITIONS for (to_date(‘01-01-2010’, ‘MM-DD-YYYY’)), for (to_date(‘01-01-2011’, ‘MM-DD-YYYY’)), for (to_date(‘01-01-2012’, ‘MM-DD-YYYY’)), for (to_date(‘01-01-2013’, ‘MM-DD-YYYY’)),INTO PARTITION historical_data_partition;

  38. [12c Partitioning for Data Warehouses] Cascading Truncate or Exchange Partition • For Reference partitioning • CASCADE applies to the whole tree in one single, atomic transaction • ON DELETE for FK’s required • ALTER TABLE orders TRUNCATE PARTITION 2011_q1 CASCADE;

  39. [12c Partitioning for Data Warehouses] Cascading Truncate 1 3 Parent Parent Child 1 Child 2 Child 1 1 Child 2 2

  40. [12c Partitioning for Data Warehouses] Exchange Partition New Parent New Parent Parent Parent New Child Child 1 Child 2 New Child Child 2 Child 1

  41. [12c Partitioning for Data Warehouses] Asynchronous Global Index Maintenance • For TRUNCATE or DROP commands • Results in no waiting for global index maintenance • What does it do? • Database will keep track of what records have been orphaned • Index stays usable • When queries are run, these orphaned records are filtered out of the index • Synchronization of orphaned records can happen multiple ways: • SYS.PMO_DEFERRED_GIDX_MAINT_JOB, runs by default at 2 am • Can manually run above job • Run ALTER INDEX REBUILD [PARTITION] • Run ALTER INDEX [PARTITION] COALESCE CLEANUP

  42. [12c Partitioning for Data Warehouses] Asynchronous Global Index Maintenance Example Create a range partitioned table with 500 records in 5 partitions. create table orders ( order_number number ) partition by range(order_number) ( partition p1 values less than (100), partition p2 values less than (200), partition p3 values less than (300), partition p4 values less than (400), partition p5 values less than (500), partition p_max values less than (MAXVALUE) ); table ORDERS created.

  43. [12c Partitioning for Data Warehouses] Asynchronous Global Index Maintenance Example insert /*+ APPEND*/ into orders select level from dual connect by level < 501; commit; 500 rows inserted. committed. select count(*) from orders; COUNT(*) ---------- 500

  44. [12c Partitioning for Data Warehouses] Asynchronous Global Index Maintenance Example Now create an index on this table. When the index is first created, it will not have any orphaned records. create index orders_idx on orders(order_number); index ORDERS_IDX created. select index_name, orphaned_entries from user_indexes where index_name = 'ORDERS_IDX'; INDEX_NAME ORPHANED_ENTRIES -------------------------------------------- ORDERS_IDX NO

  45. [12c Partitioning for Data Warehouses] Asynchronous Global Index Maintenance Example Now we are going to truncate the partition. This statement runs super fast and the index is still valid. alter table orders truncate partition p1 update indexes; table ORDERS altered. select index_name, status, orphaned_entries from user_indexes where index_name = 'ORDERS_IDX'; INDEX_NAME STATUS ORPHANED_ENTRIES ---------------------------------------------- ORDERS_IDX VALID YES

  46. [12c Partitioning for Data Warehouses] Asynchronous Global Index Maintenance Example Let’s manually clean up orphaned records. exec dbms_part.cleanup_gidx(); anonymous block completed select index_name, status, orphaned_entries from user_indexes where index_name = 'ORDERS_IDX'; INDEX_NAME STATUS ORPHANED_ENTRIES ---------------------------------------------- ORDERS_IDX VALID NO

  47. [12c Partitioning for Data Warehouses] Online Partition Move • Partition move operations can now be done without locking the object • Allows for 24/7 availability • Can be used to move partitions with older data to slower, cheaper storage • ALTER TABLE ordersMOVE PARTITION year2010TABLESPACE old_storageUPDATE INDEXES ONLINE; • ALTER TABLE ordersMOVE PARTITION year2010COMPRESSUPDATE INDEXES ONLINE;

  48. [12c Partitioning for Data Warehouses] Online Partition Move • Compression while DML is being performed with have an impact on compression efficiency • Best practice to reduce concurrent DML during partition move because it requires additional disk space and resources for journaling

  49. [12c Partitioning for Data Warehouses] A few tips • If you want to change an existing partitioned table to interval partitioning, you can execute this command:ALTER TABLE <table name> SET INTERVAL (numtoyminterval(1,'MONTH')); • With interval partitioning, to change the tablespace that new partitions are stored in use: • ALTER TABLE <table name> SET STORE IN (<tablespace name>);

  50. [12c Partitioning for Data Warehouses] Maintenance at Backcountry At the end of the year, we will merge our 2012 partitions (tables & indexes) daily to weekly or monthly weekly to quarterly These partitions also get moved off of SSD storage and onto slower netapp disks Any non-interval partitioned tables need 2015 partitions added Non of this is manual. We have a script to perform these tasks automatically on Dec 31st.

More Related