slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
[ Michelle Kolbe] PowerPoint Presentation
Download Presentation
[ Michelle Kolbe]

Loading in 2 Seconds...

play fullscreen
1 / 51

[ Michelle Kolbe] - PowerPoint PPT Presentation


  • 166 Views
  • Uploaded on

[ 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about '[ Michelle Kolbe]' - autumn-jackson


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

[ Michelle Kolbe]

12c Partitioning for Data Warehouses

OOW 2014

slide3

[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
slide4

[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
slide5

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!
slide6

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)

slide7

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

slide8

[ 12c Partitioning for Data Warehouses]

Why and What is Partitioning?

slide9

[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
slide10

[12c Partitioning for Data Warehouses]

Partitioning Strategies

  • Range
  • List
  • Hash
  • Interval
  • Reference
  • Virtual Column
  • System
  • Composite – Combination of Range, List or Hash
slide12

[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

slide13

[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

slide14

[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

slide15

[ 12c Partitioning for Data Warehouses]

Interval-Reference Partitioning

slide16

[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
slide17

[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.

slide18

[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

slide19

[12c Partitioning for Data Warehouses]

Interval-Reference Example in 12c

--Same script as 11g

table ORDERS created.

table ORDERLINES created.

slide20

[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');

slide21

[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');

slide22

[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.

slide23

[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');

slide25

[12c Partitioning for Data Warehouses]

Types of Indexes on Partitioned Tables

  • Global Non-Partitioned Index
  • Global Partitioned Index
  • Local Index
slide26

[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
slide27

[12c Partitioning for Data Warehouses]

Partial Index

* Chart from an Oracle presentation

slide28

[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.

slide29

[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';

slide30

[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');
slide31

[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');
slide32

[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;

slide33

[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);

slide35

[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’);

slide36

[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);

slide37

[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;

slide38

[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;
slide39

[12c Partitioning for Data Warehouses]

Cascading Truncate

1

3

Parent

Parent

Child 1

Child 2

Child 1

1

Child 2

2

slide40

[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

slide41

[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
slide42

[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.

slide43

[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

slide44

[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

slide45

[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

slide46

[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

slide47

[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;
slide48

[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
slide49

[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>);
slide50

[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.

slide51

????

Thank you.

Twitter: @mekolbe

Email: mkolbe@backcountry.com