APACOUC partitioning bigger and better

Partitiong came along way back in Oracle 8.0, and it is often thought that (perhaps with the exception of interval partitioning in 11g), not much has happened in terms of the functionality. But hidden away from the marketing hype, is a plethora of improvements that have come to partitioning from 12c all the way up to 19c. This session will look at those enhancement to ensure that you get the maximum return on your partitioning option investment.

Transcript

1. 1 Connor McDonald
2. Partitioning - bigger and better Connor McDonald Database Advocate Copyright © 2020 Oracle and/or its affiliates.
3. 3 3
4. 4 Me youtube.com/c/ConnorMcDonaldOracle connor-mcdonald.com @connor_mc_d
5. https://asktom.oracle.com/officehours
6. 7 dedicated customer sessions available
7. 8 before we begin
9. 10 communities need .... a community! stay safe ! stay healthy !
10. 11 why "bigger and better" ?
11. 12 version 8.0
12. 13 2017 2018 2019 2020 SALES
13. 14 today...
14. 15 establish a (quick) baseline
15. 16 partitioned tables
16. 17 range partitioning
17. 18 SQL> create table SALES 2 ( tstamp timestamp(6) not null, 3 empno number(10) not null, 4 ename varchar2(10) not null, 5 deptno varchar2(10) not null 6 ) 7 PARTITION BY RANGE (TSTAMP) 8 ( 9 PARTITION p01 VALUES LESS THAN 10 ( TIMESTAMP' 2018-01-01 00:00:00'), 11 PARTITION p02 VALUES LESS THAN 12 ( TIMESTAMP' 2018-02-01 00:00:00'), 13 PARTITION p03 VALUES LESS THAN 14 ( TIMESTAMP' 2018-03-01 00:00:00'), ... ... 25 PARTITION p13 VALUES LESS THAN 26 ( TIMESTAMP' 2019-01-01 00:00:00') 27 ); Table created.
18. 19 split by upper bound
19. 20 PARTITION p01 VALUES LESS THAN (TIMESTAMP' 2018-01-01 00:00:00'), PARTITION p02 VALUES LESS THAN (TIMESTAMP' 2018-02-01 00:00:00'), P02 >= 2018-01-01 P02 < 2018-02-01
20. 21 hash partitioning
21. 22 big stuff is a pain
24. 26 SQL> create table T 2 ( x number(10) ) 3 partition by hash ( x ) 4 partitions 8 5 / Table created.
25. 27 even spread of values
26. 28 SQL> create table T 2 ( x number(10) ) 3 partition by hash ( x ) 4 partitions 8 5 / Table created. SQL> insert into T 2 select level 3 from dual connect by level <= 100000 4 / 100000 rows created. 1,2,3,4 .... 100000
27. 29 SQL> select DBMS_ROWID.ROWID_OBJECT(rowid) ptn, 2 count(*) 3 from T 4 group by DBMS_ROWID.ROWID_OBJECT(rowid); PTN COUNT(*) ---------- ---------- 72166 12381 72167 12628 72161 12603 72162 12574 72163 12581 72168 12382 72164 12508 72165 12342
28. 30 list partitioning
29. 31 handling a small number of values
30. 32 SQL> create table TOURISM 2 ( trans_id varchar2(10) not null, 3 city varchar2(10) not null, 4 ... 5 ) 6 PARTITION BY LIST (city) 7 ( 8 PARTITION P_PERTH VALUES ('PER'), 9 PARTITION P_SYDNEY VALUES ('SYD'), 10 PARTITION P_MELBOURNE VALUES ('MEL'), 11 PARTITION P_BRISBANE VALUES ('BRI'), 12 13 ) 14 / Table created.
31. 33 SQL> create table TOURISM 2 ( trans_id varchar2(10) not null, 3 city varchar2(10) not null, 4 ... 5 ) 6 PARTITION BY LIST (city) 7 ( 8 PARTITION P_PERTH VALUES ('PER'), 9 PARTITION P_SYDNEY VALUES ('SYD'), 10 PARTITION P_MELBOURNE VALUES ('MEL'), 11 PARTITION P_BRISBANE VALUES ('BRI'), 12 PARTITION OTHER_SIGNIF VALUES ('ADE','HOB',...) 13 ) 14 / Table created.
32. 34 SQL> create table TOURISM 2 ( trans_id varchar2(10) not null, 3 city varchar2(10) not null, 4 ... 5 ) 6 PARTITION BY LIST (city) 7 ( 8 PARTITION P_PERTH VALUES ('PER'), 9 PARTITION P_SYDNEY VALUES ('SYD'), 10 PARTITION P_MELBOURNE VALUES ('MEL'), 11 PARTITION P_BRISBANE VALUES ('BRI'), 12 PARTITION OTHER_SIGNIF VALUES (DEFAULT) 13 ) 14 / Table created.
33. 35 composite partitioning
34. 36 sometimes one level is not enough
37. 39 SQL> create table COMP 2 ( tstamp timestamp(6) not null, 3 empno number(10) not null, 4 ename varchar2(10) not null, 5 deptno varchar2(10) not null 6 ) 7 PARTITION BY RANGE (TSTAMP) 8 SUBPARTITION BY LIST (deptno) 9 ( 10 PARTITION p01 VALUES LESS THAN 11 (TIMESTAMP' 2018-01-01 00:00:00') 12 (SUBPARTITION p01_d1 VALUES (1), 13 SUBPARTITION p01_d2 VALUES (2), 14 SUBPARTITION p01_d3 VALUES (3), 15 SUBPARTITION p01_d4 VALUES (4)), 16 PARTITION p02 VALUES LESS THAN 17 (TIMESTAMP' 2018-02-01 00:00:00') 18 (SUBPARTITION p02_d1 VALUES (1), 19 SUBPARTITION p02_d2 VALUES (2), 20 SUBPARTITION p02_d3 VALUES (3), 21 SUBPARTITION p02_d4 VALUES (4)), .....
38. 40 40 interval partitioning
39. 41 SQL> create table SALES 2 ( cal_year date, 3 txn_id int, ... ... 24 ) 25 partition by range ( cal_year ) 26 ( ... ... 34 partition p2019 values less than ( date '2020-01-01' ) 35 ); Table created.
40. 42 SQL> insert into SALES 2 values ( trunc(sysdate), .... ); insert into SALES * ERROR at line 1: ORA-14400: inserted partition key does not map to any partition
41. 43 SQL> create table SALES 2 ( tstamp timestamp(6) not null, 3 empno number(10) not null, 4 ename varchar2(10) not null, 5 deptno varchar2(10) not null 6 ) 7 PARTITION BY RANGE (TSTAMP) 8 INTERVAL( NUMTOYMINTERVAL(1,'MONTH')) 9 ( 10 PARTITION P00 VALUES LESS THAN 11 (TIMESTAMP' 2018-01-01 00:00:00') 11 ); Table created.
42. 44 44 partitions created as required
43. 45 SQL> select PARTITION_NAME, HIGH_VALUE 2 from user_tab_partitions 3 where table_name = 'SALES'; PARTITION_NAME HIGH_VALUE ------------------------- -------------------------------- P00 TIMESTAMP' 2018-01-01 00:00:00' SQL> insert into SALES 2 values ( to_date('12-DEC-2019'),....); SQL> select PARTITION_NAME, HIGH_VALUE 2 from user_tab_partitions 3 where table_name = 'SALES'; PARTITION_NAME HIGH_VALUE ------------------------- -------------------------------- P00 TIMESTAMP' 2018-01-01 00:00:00' SYS_P362 TIMESTAMP' 2020-01-01 00:00:00'
44. 46 46 reference partitioning
45. 2017 2018 2019 2020 SALES SALES_ITEMS SQL> desc SALES_ITEMS Name Null? Type -------------------- -------- ----------- SALE_ID NOT NULL NUMBER(12) ITEM_ID NOT NULL NUMBER(12) ... SQL> desc SALES Name Null? Type -------------------- -------- -------------- SALE_DATE NOT NULL DATE ...
46. 48 48 SQL> create table SALES 2 ( dte date not null, 3 pk number(10) not null, 4 pad char(10) 5 ) 6 PARTITION BY RANGE (dte) 7 ( 8 PARTITION p1 VALUES LESS THAN ( to_date('01-JAN-2018')), 9 PARTITION p2 VALUES LESS THAN ( to_date('01-FEB-2018')), ... 17 ); Table created. SQL> alter table SALES add primary key ( pk ); Table altered.
47. 49 49 SQL> create table SALES_ITEMS 2 ( p number(10) not null, 3 c number(10) not null, 4 constraint CHILD_FK foreign key ( p ) 5 references SALES (pk) 6 on delete cascade 7 ) 8 PARTITION BY REFERENCE (CHILD_FK) 9 / Table created.
48. 50 151 partitioned indexes
49. 51 global index
50. 52 2017 2018 2019 2020 SALES SQL> create index SALES_IX on SALES ( CUSTOMER );
51. 53 2017 2018 2019 2020
52. 54 local index
53. 55 2017 2018 2019 2020 SQL> create index SALES_IX ON SALES ( CUSTOMER ) 2 LOCAL;
54. 56 21 partition queries
55. 57 SQL> create table SALES 2 ( tstamp timestamp(6) not null, 3 empno number(10) not null, 4 ename varchar2(10) not null, 5 deptno varchar2(10) not null 6 ) 7 PARTITION BY RANGE (TSTAMP) 8 ( 9 PARTITION p01 VALUES LESS THAN 10 (TIMESTAMP' 2018-01-01 00:00:00'), 11 PARTITION p02 VALUES LESS THAN 12 (TIMESTAMP' 2018-02-01 00:00:00'), 13 PARTITION p03 VALUES LESS THAN 14 (TIMESTAMP' 2018-03-01 00:00:00'), ... ... 25 PARTITION p13 VALUES LESS THAN 26 (TIMESTAMP' 2019-01-01 00:00:00') 27 ); Table created. monthly partitions
56. 58 SQL> insert /*+ APPEND */ into SALES 2 select trunc(sysdate,'YYYY')+rownum/( 1000000 / 360 ), 3 rownum, 4 rownum, 5 mod(rownum,1000) 6 from dual 7 connect by level <= 1000000 8 / 1000000 rows created. 1m rows over 12 months
57. SQL> select * from SALES 2 where TSTAMP = to_date('01-JUN-2018'); --------------------------------------------------------------- | Id | Operation | Name | Rows | Pstart| Pstop | --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 1 | PARTITION RANGE SINGLE| | 1 | 7 | 7 | |* 2 | TABLE ACCESS FULL | SALES| 1 | 7 | 7 | ---------------------------------------------------------------
58. 60 21 partition maint
59. 61 61 SQL> alter table PAR_TABLE split partition P1 2 at ( ... ) 3 into ( partition p1a, 4 partition p1b ); SQL> alter table PAR_TABLE drop partition P2; SQL> alter table PAR_TABLE truncate partition P3; SQL> alter table PAR_TABLE merge partition P4, P5 2 into partition P45; SQL> alter table PAR_TABLE exchange partition P6 2 with table NEW_DATA;
60. 62 62 asktom.oracle.com/partitioning-for-developers.htm
61. 63 dedicated customer sessions available
62. 64 partitioned tables ... digging deeper ... recent features
63. 65 hash partitioning
64. 66 the power of 2 rule
65. 67 SQL> create table T 2 ( x number(10) ) 3 partition by hash ( x ) 4 partitions 4 | 8 | 16 | 32 ... 5 / Table created.
66. 68 why ?
67. 69 SQL> create table T 2 ( x number(10) ) 3 partition by hash ( x ) 4 partitions 8 5 / Table created. SQL> insert into T 2 select level 3 from dual connect by level <= 100000 4 / 100000 rows created.
68. 70 12.5
69. 71 SQL> create table T 2 ( x number(10) ) 3 partition by hash ( x ) 4 partitions 5 5 / Table created. SQL> insert into T 2 select level 3 from dual connect by level <= 100000 4 / 100000 rows created.
70. 72 12.5 20
71. 73 12.5 20 25
72. 74 SQL> select DBMS_ROWID.ROWID_OBJECT(rowid) ptn, 2 count(*) 3 from T 4 group by DBMS_ROWID.ROWID_OBJECT(rowid); PTN COUNT(*) ---------- ---------- 72174 12342 72172 25209 72171 24955 72173 24890 72170 12603
73. 75 deliberately for maintenance...
74. 76 12.5 20 25
75. 77 composite partitioning
76. 78 long DDL .....
77. 79 subpartition templates
78. 80 SQL> create table COMP 2 ( tstamp timestamp(6) not null, 3 empno number(10) not null, 4 ename varchar2(10) not null, 5 deptno varchar2(10) not null 6 ) 7 PARTITION BY RANGE (TSTAMP) 8 SUBPARTITION BY LIST (deptno) 9 SUBPARTITION TEMPLATE 10 (SUBPARTITION d1 VALUES (1), 11 SUBPARTITION d2 VALUES (2), 12 SUBPARTITION d3 VALUES (3), 13 SUBPARTITION d4 VALUES (4)) 14 ( 15 PARTITION p01 VALUES LESS THAN 16 (TIMESTAMP' 2018-01-01 00:00:00'), 17 PARTITION p02 VALUES LESS THAN 18 (TIMESTAMP' 2018-02-01 00:00:00'), 19 .... implicit subpar's
79. 81 logical versus physical
80. 82 eg, less newer data 2017 2018 2019 2020
81. 83 SQL> create table COMP 2 ( tstamp timestamp(6) not null, 3 empno number(10) not null, 4 ename varchar2(10) not null, 5 deptno varchar2(10) not null 6 ) 7 PARTITION BY RANGE (TSTAMP) 8 SUBPARTITION BY LIST (deptno) 9 ( 10 PARTITION p2017 VALUES LESS THAN 11 (TIMESTAMP' 2018-01-01 00:00:00') 12 (SUBPARTITION p01_d1 VALUES (1), 13 SUBPARTITION p01_d2 VALUES (2), 14 SUBPARTITION p01_d3 VALUES (3), 15 SUBPARTITION p01_d4 VALUES (4)), 16 PARTITION p2018 VALUES LESS THAN 17 (TIMESTAMP' 2019-01-01 00:00:00') 18 (SUBPARTITION p02_d1 VALUES (1), 19 SUBPARTITION p02_d2 VALUES (2), 20 SUBPARTITION p02_d3 VALUES (3), 21 SUBPARTITION p02_d4 VALUES (4)), .....
82. 84 30 PARTITION p2019 VALUES LESS THAN 31 (TIMESTAMP' 2020-01-01 00:00:00') 32 (SUBPARTITION p01_d1 VALUES (1,2), 33 SUBPARTITION p01_d2 VALUES (3,4), 34 PARTITION p2020 VALUES LESS THAN 35 (TIMESTAMP' 2021-01-01 00:00:00') .....
83. 85 interval partitioning
84. 86 partition name control lost
85. 87 SQL> alter table SALES partition ??? move;
86. 88 can rename …
87. 89 can use FOR syntax
88. 90 SQL> alter table SALES partition 2 FOR ( TIMESTAMP' 2020-02-01 05:30:00') 3 move; Table altered.
89. 91 existing range tables ?
90. 92 convert range to interval
91. 93 SQL> alter table MY_RANGE_TABLE 2 SET INTERVAL ( NUMTOYMINTERVAL(1,'MONTH') ); Table altered.
92. 94 be careful with 12.1
93. 95 virtual columns
94. SQL> select invoice_num, quantity, order_date, discount, ... 2 from orders; INVOICE_NUM QUANTITY ORDER_DATE DISCOUNT ------------ ---------- -------------- ---------- I7-2020-12A5 800 17-DEC-19 I7-2020-23E5 1600 20-FEB-19 300 I7-2020-43G5 1250 22-FEB-19 500 I7-2020-65R5 2975 02-APR-19 I7-2020-12R5 1250 28-SEP-19 1400 I7-2020-43H5 2850 01-MAY-19 I7-2021-65W5 2450 09-JUN-19 I7-2021-34D5 3000 09-DEC-19 I7-2021-87Y5 5000 17-NOV-20 I7-2021-82H5 1500 08-SEP-20 I7-2021-32D5 1100 12-JAN-20 I7-2021-52EW 950 03-DEC-20 I7-2021-72FD 3000 03-DEC-20 I7-2021-18H5 1300 23-JAN-20
95. SQL> create table ORDERS 2 ( invoice_num varchar2(20), 3 quantity number, 4 order_date date, 5 discount number ... ... 11 fin_year number(4) generated always as 12 ( to_number(substr(invoice_num,3,4)) ) 13 )
96. SQL> create table ORDERS 2 ( invoice_num varchar2(20), 3 quantity number, 4 order_date date, 5 discount number ... ... 11 fin_year number(4) generated always as 12 ( to_number(substr(invoice_num,3,4)) ) 13 ) 14 partition by range ( fin_year ) 15 ( 16 partition p2016 values less than ( date '2017-01-01' ) ...
97. 99 no transitivity ... yet
98. 100 automatic lists
99. 101 SQL> create table PEOPLE 2 ( 3 id int, 4 forename varchar2(30), 5 surname varchar2(30), 6 gender varchar2(1) 7 ) 8 partition by list (gender) 9 ( 10 partition male values ('M'), 11 partition female values ('F') 12 ); Table created. must still be >=1 automatic
100. 102 SQL> insert into PEOPLE 2 values ( 1,'Connor','McDonald','U'); 1 row created. SQL> select partition_name 2 from user_tab_partitions 3 where table_name = 'PEOPLE'; PARTITION_NAME ------------------------------------------------- FEMALE MALE SYS_P661
101. 103 multi-column lists
102. SQL> create table IOT_EVENTS ( 2 sensor_type varchar2(50), 3 channel varchar2(50), ... 11 ) 12 partition by list (sensor_type, channel) 13 ( 14 partition p1 values ('GYRO','CH1'), 15 partition p2 values ('GYRO','CH2'), 16 partition p3 values ('CAMERA','CH4'), .. 31 partition p22 values ( 32 ('THERMO','CH8'), 33 ('THERMO','CH14') 34 ) 35 )
103. single default SQL> create table IOT_EVENTS ( ... 30 partition p_def1 values ('XXX',DEFAULT) 31 partition p_def2 values (DEFAULT,DEFAULT) SQL> create table IOT_EVENTS ( ... 31 partition p_def values (DEFAULT)
104. SQL> create table IOT_EVENTS ( ... 30 partition p_def1 values ('CAMERA',DEFAULT) 31 partition p_def2 values (DEFAULT,'ROOM7') SQL> insert into IOT_EVENTS 2 values ( ... , 'CAMERA','ROOM7', .... ); ?
105. 107 maybe ...
106. 108 ... we didn't want to store it in Oracle
108. 110 polyglot persistence
110. 112 "I have to learn Hadoop ....and Spark ....and Hive ....and ? ....and ? ....and ?"
111. 113 partitioned external tables
112. 114 SQL> create table orders ( order_id number, 2 order_date date, 3 ...) 4 organization external 5 ( type ORACLE_LOADER 6 default directory DATA_DIR 7 access parameters (...) 8 ) 9 reject limit unlimited 10 partition by range(order_date) 11 ( partition q1_2015 values less than ('2014-10-01') 12 location ('q1_2015.csv'), 13 partition q2_2015 values less than ('2015-01-01') 14 location ('q2_2015.csv'), 15 partition q3_2015 values less than ('2015-04-01') 16 location ('q3_2015.csv'), 17 partition q4_2015 values less than ('2015-07-01') 18 );
113. 115 SQL> create table orders ( order_id number, 2 order_date date, 3 ...) 4 organization external 5 ( type ORACLE_LOADER 6 default directory DATA_DIR 7 access parameters (...) 8 ) 9 reject limit unlimited 10 partition by range(order_date) 11 ( partition q1_2015 values less than ('2014-10-01') 12 location ('q1_2015.csv'), 13 partition q2_2015 values less than ('2015-01-01') 14 location ('q2_2015.csv'), 15 partition q3_2015 values less than ('2015-04-01') 16 location ('q3_2015.csv'), 17 partition q4_2015 values less than ('2015-07-01') 18 );
114. 116 SQL> create table orders ( order_id number, 2 order_date date, 3 ...) 4 organization external 5 ( type ORACLE_LOADER 6 default directory DATA_DIR 7 access parameters (...) 8 ) 9 reject limit unlimited 10 partition by range(order_date) 11 ( partition q1_2015 values less than ('2014-10-01') 12 default directory OLD_DATA_DIR location ('q1_2015.csv'), 13 partition q2_2015 values less than ('2015-01-01') 14 location ('q2_2015.csv'), 15 partition q3_2015 values less than ('2015-04-01') 16 location ('q3_2015.csv'), 17 partition q4_2015 values less than ('2015-07-01') 18 );
115. 117 data can be "anywhere"
116. 118 SQL> create table orders ( order_id number, 2 order_date DATE, 3 ... ) 4 organization external 5 ( type oracle_loader default directory data_dir 6 access parameters ( 7 credential 'OSS_ACCESS' ) 8 ) reject limit unlimited 9 partition by range(order_date) 10 ( 11 partition q1_2015 values less than ('2014-10-01') 12 location ('https://swiftobjectstorage.us-oracle-cloud-1 ...'), 13 partition q2_2015 values less than ('2015-01-01') 14 location ('...'), 15 partition q3_2015 values less than ('2015-04-01') 16 location ('...'), 17 partition q4_2015 values less than ('2015-07-01') 18 );
117. 119 your responsibilities increased
118. 120 SQL> select invoice_num, order_date 2 from orders partition ( q2_2015 ); INVOICE_NUM ORDER_DATE ----------- ----------- NAG62O 12-APR-2015 4Z3CU8 23-MAY-2015 6BV8JI 07-APR-2015 GQBVUT 12-APR-2015 LMSUIK 22-MAT-2015 3S2X5L 16-APR-2015 3I9CZJ 18-OCT-2017 DBBPXU 21-JUN-2015 Y8CK6V 05-APR-2015 S9W81K 09-MAY-2015 97S6A0 13-APR-2015 T9U4LI 24-MAY-2015 GV2Z67 09-APR-2015 5CQ1J9 16-APR-2015
119. 121 SQL> select invoice_num, order_date 2 from orders partition ( q2_2015 ); INVOICE_NUM ORDER_DATE ----------- ----------- NAG62O 12-APR-2015 4Z3CU8 23-MAY-2015 6BV8JI 07-APR-2015 GQBVUT 12-APR-2015 LMSUIK 22-MAT-2015 3S2X5L 16-APR-2015 3I9CZJ 18-OCT-2017 DBBPXU 21-JUN-2015 Y8CK6V 05-APR-2015 S9W81K 09-MAY-2015 97S6A0 13-APR-2015 T9U4LI 24-MAY-2015 GV2Z67 09-APR-2015 5CQ1J9 16-APR-2015
120. 122 SQL> select invoice_num, order_date 2 from orders 3 where order_date between '01-JAN-2015' and '30-APR-2015' INVOICE_NUM ORDER_DATE ----------- ----------- NAG62O 12-APR-2015 4Z3CU8 23-MAY-2015 6BV8JI 07-APR-2015 GQBVUT 12-APR-2015 LMSUIK 22-MAT-2015 3S2X5L 16-APR-2015 <filter still applied> DBBPXU 21-JUN-2015 Y8CK6V 05-APR-2015 S9W81K 09-MAY-2015 97S6A0 13-APR-2015 T9U4LI 24-MAY-2015 GV2Z67 09-APR-2015 5CQ1J9 16-APR-2015
121. 123 data validation
122. 124 SQL> select invoice_num, 2 order_date, 3 ora_partition_validation(rowid) good 4 from orders partition ( q2_2015 ); INVOICE_NUM ORDER_DATE GOOD ----------- ----------- ---- NAG62O 12-APR-2015 1 4Z3CU8 23-MAY-2015 1 6BV8JI 07-APR-2015 1 GQBVUT 12-APR-2015 1 LMSUIK 22-MAT-2015 1 3S2X5L 16-APR-2015 1 3I9CZJ 18-OCT-2017 0 DBBPXU 21-JUN-2015 1 Y8CK6V 05-APR-2015 1 S9W81K 09-MAY-2015 1 97S6A0 13-APR-2015 1 T9U4LI 24-MAY-2015 1 GV2Z67 09-APR-2015 1 5CQ1J9 16-APR-2015 1
123. 125 "gee thanks...."
124. SALES SALES_EXTERNAL
125. 127 19c
126. 128 hybrid partitioned table
127. 129 Q4_2018 Q3_2018 Q2_2018 Q1_2018 Q4_2017 Q3_2017 Q2_2017 Q1_2017 Q4_2016 ORDERS OBJECT STORAGE TABLE PARTITIONED BY QUARTER SQL HOT DATA COLD DATA UPDATES
128. 130 SQL> create table hybrid_orders 2 ( 3 year_key varchar2(10) not null 4 ... 5 ) 6 external partition attributes ( 7 type oracle_loader 8 default directory orders_data 9 ) 10 partition by list (year_key) 11 (partition orders_2015 values ('2015') external 12 location ('orders2015.txt'), 13 partition orders_2016 values ('2016') external 14 location ('orders2016.txt'), 15 partition orders_2017 values ('2017'), 16 partition orders_2018 values ('2018'), 17 ); Table created.
129. 131 SQL> insert into hybrid_orders 2 values ('2017',...); 1 row created. SQL> insert into hybrid_orders 2 values ('2015',...); ERROR at line 1: ORA-14466: Data in a read-only partition or subpartition cannot be modified.
130. 132 partitioned indexes ... digging deeper ... recent features
131. 133 2017 2018 2019 2020 SQL> create index SALES_IX ON SALES ( CUSTOMER );
132. 134 2017 2018 2019 2020 SQL> create index SALES_IX ON SALES ( CUSTOMER ) 2 LOCAL;
133. 135 global partitioned indexes
134. 2017 2018 2019 2020 SQL> create index SALES_IX 2 on SALES ( location, product ) 3 global partition by range ( location ) 4 ( partition p0 values less than (100), 6 partition p1 values less than (200), ... 12 partition pmax values less than (maxvalue) 13 );
135. 137 rare ....
136. 138 ...one special case
137. 139 hash partitioned index
138. 140 high volume transactions
139. 141 ASSM
141. 143 indexes a problem ascending key
143. 145 hash partitioned indexes
144. 146 SQL> create index SALES_PK on SALES( TXN_ID ) 2 global partition by hash ( TXN_ID ) 3 partitions 8 4 / Index created.
146. 148 extreme cases
147. 149 SQL> create table SALES 2 ( trans_id number default trans_seq.nextval, 3 ... 4 INST_ID number default sys_context('USERENV','INSTANCE') 5 ) 6 PARTITION BY LIST (INST_ID) 7 SUBPARTITION BY HASH (TRANS_ID) SUBPARTITIONS ( 8 ) 7 ( 8 PARTITION P_1 VALUES (1), 9 PARTITION P_2 VALUES (2), 10 PARTITION P_3 VALUES (3), 11 PARTITION P_4 VALUES (4) 12 ); Table created. SQL> create unique index SALES_PK ( trans_id, inst_id) LOCAL;
148. 150 partial indexing
149. 151 SQL> create table SALES 2 ( trans_id varchar2(10) not null, 3 ... 4 ) 5 INDEXING OFF 6 PARTITION BY LIST (fin_year) 7 ( 8 PARTITION P_2015 VALUES (2015) INDEXING OFF, 9 PARTITION P_2016 VALUES (2016) INDEXING ON, 10 PARTITION P_2017 VALUES (2017) INDEXING ON, 11 PARTITION P_2018 VALUES (2018) INDEXING ON, 12 ) 13 / Table created.
150. 152 2017 2018 2019 2020 SQL> create index SALES_IX on SALES ( ... ) LOCAL 2 INDEXING PARTIAL;
151. 153 2017 2018 2019 2020 SQL> alter table SALES modify partition SALES_2017 2 INDEXING OFF;
152. 154 why ?
153. 155 or the opposite
154. 156 2017 2018 2019 2020 SQL> alter table SALES modify partition SALES_2020 2 INDEXING OFF; 2020
155. 157 free in-memory 19c
156. 158 global as well
157. 159 2017 2018 2019 2020 SQL> create index SALES_IX on SALES ( ... ) LOCAL 2 INDEXING PARTIAL;
158. 160 optimizer is aware
159. SQL> select * from SALES 2 where cust_id = 1234 ------------------------------------------------------ | Id | Operation | Name | Rows | ------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 | VIEW | VW_TE_2 | 1 | | 2 | UNION ALL | | 1 | | 3 | TABLE ACCESS BY ROWID | SALES | 1 | | 4 | INDEX RANGE SCAN | SALES_IX | 1 | ------------------------------------------------------
160. SQL> select * from SALES 2 where cust_id = 1234 ---------------------------------------------------------------------- | Id | Operation | Name | Rows | Pstart| Pstop | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 1 | VIEW | VW_TE_2 | 1 | | | | 2 | UNION ALL | | 1 | | | | 3 | TABLE ACCESS BY ROWID | SALES | 1 | ROWID | ROWID | | 4 | INDEX RANGE SCAN | SALES_IX | 1 | | | | 5 | PARTITION RANGE SINGLE | | 1 | 1 | 1 | | 6 | TABLE ACCESS FULL | SALES | 1 | 1 | 1 | ----------------------------------------------------------------------
161. 163 index maintenance ... see later
162. 164 partitioned queries ... digging deeper ... recent features
163. 165 partition pruning
164. SQL> select * from SALES 2 where TSTAMP = to_date('01-JUN-2018'); --------------------------------------------------------------- | Id | Operation | Name | Rows | Pstart| Pstop | --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 1 | PARTITION RANGE SINGLE| | 1 | 7 | 7 | |* 2 | TABLE ACCESS FULL | SALES| 1 | 7 | 7 | ---------------------------------------------------------------
165. SQL> select * from SALES 2 where TRUNC(TSTAMP) = to_date('01-JUN-2018'); ------------------------------------------------------------ | Id | Operation | Name | Rows | Pstart| Pstop | ------------------------------------------------------------ | 0 | SELECT STATEMENT | | 10000 | | | | 1 | PARTITION RANGE ALL| | 10000 | 1 | 13 | |* 2 | TABLE ACCESS FULL | SALES| 10000 | 1 | 13 | ------------------------------------------------------------
166. 168 works with binds too
167. SQL> select * from SALES 2 where TSTAMP = :b1; --------------------------------------------------------------- | Id | Operation | Name | Rows | Pstart| Pstop | --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 1 | PARTITION RANGE SINGLE| | 1 | KEY | KEY | |* 2 | TABLE ACCESS FULL | SALES| 1 | KEY | KEY | ---------------------------------------------------------------
168. 170 lots of power
169. SQL> select * from SALES 2 where TSTAMP between to_date('12-JAN-2018') 3 and to_date('07-FEB-2018') 4 or TSTAMP between to_date('03-JUN-2018') 5 and to_date('06-AUG-2018'); ----------------------------------------------------------- | Id | Operation | Name | Rows | Pstart| Pstop | ----------------------------------------------------------- | 0 | SELECT STATEMENT | | 240K| | | | 1 | PARTITION RANGE OR| | 240K|KEY(OR)|KEY(OR)| |* 2 | TABLE ACCESS FULL| SALES| 240K|KEY(OR)|KEY(OR)| -----------------------------------------------------------
170. SQL> select * from SALES 2 where TSTAMP in (to_date('01-JUN-2018'), 3 to_date('01-DEC-2018')); --------------------------------------------------------------- | Id | Operation | Name | Rows | Pstart| Pstop | --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | | | | 1 | PARTITION RANGE INLIST| | 2 |KEY(I) |KEY(I) | |* 2 | TABLE ACCESS FULL | SALES| 2 |KEY(I) |KEY(I) | ---------------------------------------------------------------
171. 173 interval partition curiosity
172. range versus interval partitions 174 Length Length Length
173. 175 INTERVAL = 1 million potential partitions
174. 176 SQL> create table SALES 2 ( tstamp timestamp(6) not null, 3 empno number(10) not null, 4 ename varchar2(10) not null, 5 deptno varchar2(10) not null 6 ) 7 PARTITION BY RANGE (TSTAMP) 8 INTERVAL( NUMTOYMINTERVAL(1,'MONTH')) 9 ( 10 PARTITION P00 VALUES LESS THAN 11 (TIMESTAMP' 2018-01-01 00:00:00') 12 ); Table created.
175. SQL> select * from SALES; --empty ------------------------------------------------------------ | Id | Operation | Name | Rows | Pstart| Pstop | ------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | | 1 | PARTITION RANGE ALL| | 1 | 1 |1048575| | 2 | TABLE ACCESS FULL | SALES| 1 | 1 |1048575| ------------------------------------------------------------
176. 178 smart I/O avoidance
177. SQL> select * from SALES 2 where TSTAMP = to_date('01-JUN-2020'); -------------------------------------------------------------- | Id | Operation | Name | Rows | Pstart| Pstop | -------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 1 | PARTITION RANGE EMPTY| | 1 |INVALID|INVALID| |* 2 | TABLE ACCESS FULL | SALES| 1 |INVALID|INVALID| --------------------------------------------------------------
178. 180 pruning by subquery
179. SQL> select e.deptno, max(d.empno) 2 from SALES d, EMP e 3 where d.tstamp = e.hiredate 4 and e.sal < 10000 5 group by e.deptno; ----------------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | ----------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | HASH GROUP BY | | | | |* 2 | HASH JOIN | | | | | 3 | PART JOIN FILTER CREATE | :BF0000 | | | |* 4 | TABLE ACCESS FULL | EMP | | | | 5 | PARTITION RANGE JOIN-FILTER| |:BF0000|:BF0000| | 6 | TABLE ACCESS FULL | SALES |:BF0000|:BF0000| -----------------------------------------------------------------
180. SQL> select e.deptno, max(d.empno) 2 from SALES d, scott.emp e 3 where d.tstamp = e.hiredate 4 and e.sal < 10000 5 group by e.deptno; ----------------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | ----------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | HASH GROUP BY | | | | |* 2 | HASH JOIN | | | | | 3 | PART JOIN FILTER CREATE | :BF0000 | | | |* 4 | TABLE ACCESS FULL | EMP | | | | 5 | PARTITION RANGE JOIN-FILTER| |:BF0000|:BF0000| | 6 | TABLE ACCESS FULL | SALES |:BF0000|:BF0000| -----------------------------------------------------------------
181. 183 bloom filters
182. 184 The Bloom filter...is a space-efficient probabilistic data structure that is used to test whether an element is a member of a set. - Wikipedia
183. 185 185
184. 186 metaphor
185. 187 187 "phone in advance"
188. 190 "Are you serious !? Try in 6 months"
189. 191 "There's a few seats left..."
191. 193 false positives possible
192. 194 false negatives impossible
193. 195 EMP JUN 20 JAN 19 AUG 19 MAY 15 JUN 19 AUG 20 FEB 15 etc
194. 196 EMP JUN 20 JAN 19 AUG 19 MAY 15 JUN 19 AUG 20 FEB 15 etc
195. 197 EMP JUN 20 JAN 19 AUG 19 MAY 15 JUN 19 AUG 20 FEB 15 etc
196. 198 EMP JUN 20 JAN 19 AUG 19 MAY 15 JUN 19 AUG 20 FEB 15 etc Dictionary for SALES P1 2022 P2 2015 P3 2016 P4 2017 P5 2018 P6 2019 P7 2020
197. 199 EMP JUN 20 JAN 19 AUG 19 MAY 15 JUN 19 AUG 20 FEB 15 etc Dictionary for SALES P1 2022 P2 2015 P3 2016 P4 2017 P5 2018 P6 2019 P7 2020
198. 200 EMP JUN 20 JAN 19 AUG 19 MAY 15 JUN 19 AUG 20 FEB 15 etc Dictionary for SALES P1 2022 P2 2015 P3 2016 P4 2017 P5 2018 P6 2019 P7 2020
199. 201 EMP JUN 20 JAN 19 AUG 19 MAY 15 JUN 19 AUG 20 FEB 15 etc Dictionary for SALES P1 2022 P2 2015 P3 2016 P4 2017 P5 2018 P6 2019 P7 2020
200. 202 EMP JUN 20 JAN 19 AUG 19 MAY 15 JUN 19 AUG 20 FEB 15 etc Dictionary for SALES P1 2022 P2 2015 P3 2016 P4 2017 P5 2018 P6 2019 P7 2020 SALES
201. 203 other query benefits
202. 204 partition wise join
203. 205 "big deal" join the partitions join the table
204. 206 another metaphor :-)
207. 209 match the pairs
210. SQL> select ... 2 from LEFT_SOCKS n, RIGHT_SOCKS s 3 where n.STYLE_SIZE = s.STYLE_SIZE -------------------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | |* 1 | HASH JOIN | | | | | 3 | PARTITION RANGE ALL | | 1 | 4 | | 4 | TABLE ACCESS FULL | LEFT_SOCKS | 1 | 4 | | 5 | PARTITION RANGE ALL | | 1 | 4 | | 6 | TABLE ACCESS FULL | RIGHT_SOCKS | 1 | 4 | --------------------------------------------------------------------
211. SQL> select ... 2 from LEFT_SOCKS n, RIGHT_SOCKS s 3 where n.STYLE_SIZE = s.STYLE_SIZE ----------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | ----------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | PARTITION RANGE ALL| | 1 | 4 | |* 2 | HASH JOIN | | | | | 3 | TABLE ACCESS FULL| LEFT_SOCKS | 1 | 4 | | 4 | TABLE ACCESS FULL| RIGHT_SOCKS | 1 | 4 | -----------------------------------------------------------
212. 214 partition maint ... digging deeper ... recent features
213. read only partitions
214. what we used to do ...you still can
215. 217 2017 2018 2019 2020 SQL> alter table SALES move partition SALES_2017 2 tablespace LONG_TERM_STORAGE; SQL> alter tablespace LONG_TERM_STORAGE read only; RMAN
216. 218 SQL> create table t 2 partition by range ( x ) 3 ( partition p1 values less than ( 30000 ), 4 partition p2 values less than ( 3000000 ) 5 ) as 6 select rownum x, rownum y 7 from dual connect by level <= 100000; Table created. SQL> alter table t modify partition p1 read only; Table altered.
217. 219 "big deal"
218. SQL> create table t 2 partition by range ( x ) 3 ( partition p1 values less than ( 30000 ), 4 partition p2 values less than ( 3000000 ) 5 ) as 6 select rownum x, rownum y 7 from dual connect by level <= 100000; Table created. SQL> alter table t modify partition p1 read only; Table altered. SQL> alter table t move partition p1 compress; Table altered.
219. exchange partition
220. 222 handling new data
221. 2018 2019 2020 2021 2021 SQL> alter table SALES exchange 2 partition SALES_2021 with 3 table NEW_SALES;
222. sometimes you can ...
223. ... sometimes you can't
224. "huh ?"
225. SQL> desc PAR Name Null? Type ----------------------------- -------- -------------------- X NUMBER(10) Y NUMBER(10) SQL> select partition_name 2 from user_tab_partitions 3 where table_name = 'PAR'; PARTITION_NAME ------------------------------------------------------------ P1 P2 2 rows selected.
226. SQL> create table EXCH_PAR 2 as select * from PAR 3 where 1=0; Table created. SQL> desc EXCH_PAR Name Null? Type ----------------------------- -------- -------------------- X NUMBER(10) Y NUMBER(10)
227. SQL> alter table PAR exchange partition P1 2 with table EXCH_PAR; alter table PAR exchange partition P1 * ERROR at line 1: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE
229. SQL> alter table PAR exchange partition P1 2 with table EXCH_PAR 3 without validation; alter table PAR exchange partition P1 * ERROR at line 1: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE
230. SQL> select column_id, column_name 2 from user_tab_columns 3 where table_name = 'PAR'; COLUMN_ID COLUMN_NAME ---------- ------------------------------ 1 X 2 Y SQL> select column_id, column_name 2 from user_tab_columns 3 where table_name = 'EXCH_PAR'; COLUMN_ID COLUMN_NAME ---------- ------------------------------ 1 X 2 Y
231. USER_TAB_COLUMN S
232. SQL> select column_id, column_name, data_type 2 from user_tab_cols 3 where table_name = 'PAR'; COLUMN_ID COLUMN_NAME DATA_TYPE ---------- ------------------------------ --------- 1 X NUMBER SYS_C00002_16083109:09:55$ DATE 2 Y NUMBER SQL> desc PAR Name Null? Type ----------------------------- -------- ----------- X NUMBER(10) Z DATE Y NUMBER(10) SQL> alter table PAR set unused column Z;
233. SQL> alter table PAR drop column Z; bit.ly/dropcolumn
234. "no problem"
235. SQL> alter table EXCH_PAR add Z date; Table altered. SQL> alter table EXCH_PAR set unused column Z; Table altered. SQL> alter table PAR exchange partition P1 2 with table EXCH_PAR; alter table PAR exchange partition P1 * ERROR at line 1: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE
236. precision matters
237. SQL> create table EXCH_PAR ( 2 x number(10), 3 dummy date , 4 y number(10)); Table created. SQL> alter table EXCH_PAR set unused column dummy; Table altered. SQL> alter table PAR exchange partition P1 2 with table EXCH_PAR; Table altered.
238. it could be worse
239. 241 fast add column default invisible columns stats extensions
240. 242 "this one is for exchange"
241. 243 SQL> create table EXCH_PAR 2 for exchange with table PAR; Table created. SQL> alter table PAR exchange partition P1 2 with table EXCH_PAR; Table altered.
242. 244 drop partition
243. 245 dealing with ILM
244. 246 2017 2018 2019 2020 SQL> alter table SALES drop partition SALES_2017
245. 250 keep the index valid
246. 2017 2018 2019 2020 SQL> alter table SALES drop partition SALES_2017 2 update indexes; Elapsed: 01:17:22.00
247. 252  large delete redo undo
248. 253 asynchronous cleanup
249. 2017 2018 2019 2020 SQL> alter table SALES drop partition SALES_2017 2 update indexes; Elapsed: 00:00:01.17
250. SQL> select index_name, status, orphaned_entries 2 from user_indexes 3 where index_name = 'SALES_CUST_IX'; INDEX_NAME STATUS ORP ------------------------------ -------- --- SALES_CUST_IX VALID YES
251. SQL> select owner||'.'||job_name job_name, 2 case 3 when job_action is not null then job_action 4 when program_name is not null then 5 ( select 'PGM:'||program_action 6 from dba_scheduler_programs 7 where owner = s.owner 8 and program_name = s.program_name ) 9 end what 10 from dba_scheduler_jobs s; JOB_NAME WHAT -------------------------------- ------------------------------------ SYS.PMO_DEFERRED_GIDX_MAINT_JOB PGM:dbms_part.cleanup_gidx_job
252. 257 truncate partition
253. Parent Child 1 Child 2 Grandchild 1 Grandchild 2 Grandchild 3 Great Grandchild 1 Parent Child 1 Child 2 Grandchild 1 Grandchild 2 Grandchild 3 Great Grandchild 1 SQL> alter table PARENT truncate partition P2020 cascade;
254. same with exchange
255. Parent Child 1 Child 2 Grandchild 1 Grandchild 2 Grandchild 3 Great Grandchild 1 New Child New Grandchild New Grandchild SQL> alter table REF_PAR exchange 2 partition CHILD1 2 with table NEW_CHILD 4 cascade;
256. split / merge partition
257. no more limits
258. 263 SQL> alter table sales merge partitions 2 P20180701 3 ,P20180702 4 ,P20180703 ... 32 ,P20180731 33 into partition p201807;
259. 264 "big deal"
260. partition operations are now online
261. SQL> create table sales 2 ( ts timestamp, 3 id int, 4 amt number, 5 product int, 6 customer int, ... ... 17 ) 18 partition by range ( ts ) 19 interval ( numtodsinterval(1,'HOUR') ) 20 ( 21 partition p1 values less than ( timestamp '2018-07-01 00:00:00' ) 22 ); Table created.
262. 24 hours later
263. SQL> select partition_name, high_value 2 from user_tab_partitions 3 where table_name = 'SALES' 4 order by partition_position; PARTITION_NAME HIGH_VALUE ------------------------------ ----------------------------------- P1 TIMESTAMP' 2018-07-01 00:00:00' P20180701_00 TIMESTAMP' 2018-07-01 01:00:00' P20180701_01 TIMESTAMP' 2018-07-01 02:00:00' P20180701_02 TIMESTAMP' 2018-07-01 03:00:00' P20180701_03 TIMESTAMP' 2018-07-01 04:00:00' ... ... P20180702_13 TIMESTAMP' 2018-07-02 14:00:00' P20180702_14 TIMESTAMP' 2018-07-02 15:00:00' P20180702_15 TIMESTAMP' 2018-07-02 16:00:00' 41 rows selected.
264. 269 one year later ...
265. 270 ... lot of partitions
266. 271 SQL> declare 2 d date := date '2018-07-01'; 3 ddl varchar2(4000); 4 begin 5 select listagg(partition_name||chr(10),',') 6 within group ( order by partition_position ) 7 into ddl 8 from user_tab_partitions 9 where table_name = 'SALES' 10 and partition_name like 'P'||to_char(d,'yyyymmdd')||'%'; 11 12 ddl := 'alter table sales merge partitions '||ddl|| 13 ' into partition p'||to_char(d,'yyyymmdd')||' online'; 14 15 dbms_output.put_line(ddl); 16 execute immediate ddl; 17 end; 18 / alter table sales merge partitions P20180701_00 ,P20180701_01 ,P20180701_02 ... ,P20180701_23 into partition p20180701 online
267. partition operations are now online ... ... and less impactful
268. 273 SQL> drop index SALES_IX 2 deferred invalidation; Index dropped. ...more coming
269. 274 speaking of online ...
270. 275 "I'm super excited but ..."
271. 276 partition existing table one command
272. SQL> create table T as 2 select d.* 3 from dba_Objects d, 4 ( select 1 from dual 5 connect by level <= 20 ) 6 where d.object_id is not null; Table created. SQL> create index IX on t ( object_id ); Index created. SQL> create index IX2 on t ( created, object_name ); Index created.
273. SQL> alter table T modify 2 partition by range (object_id) interval (10000) 3 ( 4 partition p1 values less than (20000) 5 ) online 6 including rows where created > date '2018-01-01' 7 update indexes 8 ( ix local tablespace new_idx_ts, 9 ix2 global partition by range (created) 10 ( 11 partition ix2_p1 values less than (date '2016-08-01'), 12 partition ix2_p2 values less than (maxvalue) 13 ) 14 ); Table altered. compress
274. 279 wrap up
275. 280 lots of potential ...
276. 281 benefits in many places
277. 282 did you notice ?
278. 283 SQL> create table PRESENTATION 2 ( ... 3 ... 4 ... 5 ) 6 PARTITION BY LIST (topic) 7 ( 8 PARTITION PART1 VALUES ('BASELINE'), 9 PARTITION PART1 VALUES ('TABLES'), 10 PARTITION PART2 VALUES ('INDEXES'), 11 PARTITION PART3 VALUES ('QUERIES') 12 PARTITION PART3 VALUES ('MAINTENANCE') 13 ); Table created.
279. 284 We're done! youtube.com/c/ConnorMcDonaldOracle connor-mcdonald.com @connor_mc_d