oracle partition - ghdrako/doc_snipets GitHub Wiki
select * from USER_PART_TABLES;
select * from user_tab_partitions;
select * from user_tab_subpartitions;
select * FROM DBA_PART_INDEXES;
select * FROM USER_PART_INDEXES;
select * FROM USER_IND_PARTITIONS;
- Range Partitioning - the data is distributed based on a range of values.
- List Partitioning The data distribution is defined by a discrete list of values. One or multiple columns can be used as partition key.
- Auto-List Partitioning - extends the capabilities of the list method by automatically defining new partitions for any new partition key values.
- Hash Partitioning - an internal hash algorithm is applied to the partitioning key to determine the partition.
- Composite Partitioning - combinations of two data distribution methods are used. First, the table is partitioned by data distribution method one and then each partition is further subdivided into subpartitions using the second data distribution method.
- Multi-Column Range Partitioning - an option for when the partitioning key is composed of several columns and subsequent columns define a higher level of granularity than the preceding ones.
- Interval Partitioning - extends the capabilities of the range method by automatically defining equi-partitioned ranges for any future partitions using an interval definition as part of the table metadata.
- Reference Partitioning Partitions - a table by leveraging an existing parent-child relationship. The primary key relationship is used to inherit the partitioning strategy of the parent table to its child table.
- Virtual Column Based Partitioning - allows the partitioning key to be an expression, using one or more existing columns of a table, and storing the expression as metadata only.
- Interval Reference Partitioning - an extension to reference partitioning that allows the use of interval partitioned tables as parent tables for reference partitioning.
CREATE TABLE sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
TABLESPACE tsa
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
TABLESPACE tsb
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
TABLESPACE tsc
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
TABLESPACE tsd
);
ENABLE ROW MOVEMENT
CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER,
store_name VARCHAR(30), state_code VARCHAR(2),
sale_date DATE)
STORAGE(INITIAL 10K NEXT 20K) TABLESPACE tbs5
PARTITION BY LIST (state_code)
(
PARTITION region_east
VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')
STORAGE (INITIAL 8M)
TABLESPACE tbs8,
PARTITION region_west
VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO')
NOLOGGING,
PARTITION region_south
VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'),
PARTITION region_central
VALUES ('OH','ND','SD','MO','IL','MI','IA'),
PARTITION region_null
VALUES (NULL),
PARTITION region_unknown
VALUES (DEFAULT)
);
CREATE TABLE sales_auto_list
(
salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE
)
PARTITION BY LIST (sales_state) AUTOMATIC
(PARTITION P_CAL VALUES ('CALIFORNIA')
);
You can check the AUTOLIST column of the *_PART_TABLES view to determine whether a table is automatic list-partitioned.
DEFAULT partition must be the last partition. It handle either null value.
CREATE TABLE sales
( prod_id NUMBER(6) NOT NULL
, cust_id NUMBER NOT NULL
, time_id DATE NOT NULL
, channel_id CHAR(1) NOT NULL
, promo_id NUMBER(6) NOT NULL
, quantity_sold NUMBER(3) NOT NULL
, amount_sold NUMBER(10,2) NOT NULL
, total_amount AS (quantity_sold * amount_sold)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY RANGE(total_amount)
SUBPARTITION TEMPLATE
( SUBPARTITION p_small VALUES LESS THAN (1000)
, SUBPARTITION p_medium VALUES LESS THAN (5000)
, SUBPARTITION p_large VALUES LESS THAN (10000)
, SUBPARTITION p_extreme VALUES LESS THAN (MAXVALUE)
)
(PARTITION sales_before_2007 VALUES LESS THAN
(TO_DATE('01-JAN-2007','dd-MON-yyyy'))
)
ENABLE ROW MOVEMENT
PARALLEL NOLOGGING;
CREATE TABLE TBL(
BILL_CYCLE_FULL_CODE_N VARCHAR2(20),
YEAR_MON DATE INVISIBLE GENERATED ALWAYS AS (TO_DATE(SUBSTR(BILL_CYCLE_FULL_CODE_N,10,6),'YYYYMM'))
)
PARTITION BY RANGE(YEAR_MON)
INTERVAL (INTERVAL '1' MONTH)
(PARTITION BEFORE_2019 VALUES LESS THAN(DATE '2019-01-01'))
YEAR_MON is virtual column used to partitioning
CREATE TABLE costs_demo (
prod_id NUMBER(6), time_id DATE,
unit_cost NUMBER(10,2), unit_price NUMBER(10,2))
PARTITION BY RANGE (time_id)
(PARTITION costs_old
VALUES LESS THAN (TO_DATE('01-JAN-2003', 'DD-MON-YYYY')) COMPRESS,
PARTITION costs_q1_2003
VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
PARTITION costs_q2_2003
VALUES LESS THAN (TO_DATE('01-JUN-2003', 'DD-MON-YYYY')),
PARTITION costs_recent VALUES LESS THAN (MAXVALUE));
CREATE INDEX amount_sold_ix ON sales(amount_sold)
GLOBAL PARTITION BY RANGE(sale_month)
( PARTITION p_100 VALUES LESS THAN (100)
, PARTITION p_1000 VALUES LESS THAN (1000)
, PARTITION p_10000 VALUES LESS THAN (10000)
, PARTITION p_100000 VALUES LESS THAN (100000)
, PARTITION p_1000000 VALUES LESS THAN (1000000)
, PARTITION p_greater_than_1000000 VALUES LESS THAN (maxvalue)
);
select partition_name, status from user_ind_partitions
select index_name,partition_name, status from user_ind_partitions where partition_name like 'BASE%' and status = 'UNUSABLE'
To make all local indexes on a partition unusable:
ALTER TABLE <table_name> MODIFY PARTITION <part_name> UNUSABLE LOCAL INDEXES;
To rebuild:
ALTER TABLE <table_name> MODIFY PARTITION <part_name> REBUILD UNUSABLE LOCAL INDEXES;
ALTER INDEX .. REBUILD ... TABLESPACE syntax is to be used when you want to move an index to another tablespace.
ALTER INDEX indexname REBUILD PARTITION <partitionname> nologging;
select ' alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||';' from dba_ind_partitions where status='UNUSABLE';
alter index SLOG.IDX_TRM_DAY_BASE rebuild partition BASE201603;
show parameter unusable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean TRUE
- https://docs.oracle.com/database/121/VLDBG/GUID-256BA7EE-BF49-42DE-9B38-CD2480A73129.htm#VLDBG14102
- https://dba.stackexchange.com/questions/217266/partial-local-index-on-partitioned-oracle-table
select table_name, partition_name, indexing from user_tab_partitions;
-
indexing - on/off enable/disable partition indexing on column
-
Global A global index makes one single index correspond to multiple table partitions.
CREATE INDEX ORDERS_ORDER_TOTAL_GIDX ON ORDERS (ORDER_TOTAL)
GLOBAL INDEXING PARTIAL;
The ORDERS_ORDER_TOTAL_GIDX index is created to index only those partitions that have INDEXING ON, and excludes the remaining partitions.
- Local
CREATE INDEX loc_dept_ix ON departments_hash(department_id) LOCAL;
- Partial
Note !! Even with the partition indexing settings in place, by default indexes are created as
INDEXING FULL, so the partition setting are ignored.
- Change default attribute of table to disable automatic creation of indexes on partitions:
alter table mytab modify default attributes indexing off;
It sets the table's default indexing to off. No existing index will be affected. It relates to partial indexes only and every new local index with "PARTIAL INDEXING" will not be created on the local partitions. 2. Create the index with INDEXING PARTIAL:
create index mylocal_partidx on mytab(accountid) local indexing partial;
Since index is partial and default attribute on table is NO, no local partitions are created. 3. Create required index partitions by simply swith indexing on for selected partitions. Each command creates the partial marked indexes on the partition:
alter table mytab modify partition P100 indexing ON;
alter table mytab modify partition P101 indexing ON;
alter table ...
Now, you have only the relevant n partitions indexed. All indexes not marked partial are fully created.
If partition is composite you mast set every subpartition individually:
ALTER TABLE MODIFY SUBPARTITION INDEXING [ON|OFF]
To drop unwanted partitions:
alter table mytab modify partition P001 indexing OFF;
Nie mozna utworzyc unikalnego indexu tylko na wyrane partycje. Zamiast tego mozna wykonac:
CREATE TABLE EBKTA.ACP_STAGE_RL_INCOMING
(RIN_ORG CHAR(1 BYTE),
RIN_NR_REF VARCHAR2(23 BYTE),
RIN_NR_REF_ROZW NUMBER(10,0),
RIN_NR_PACZKI NUMBER(10,0),
RIN_REK_SOURCE VARCHAR2(2000 BYTE),
RIN_NR_KARTY VARCHAR2(19 BYTE),
RIN_NRB VARCHAR2(26 BYTE),
RIN_NAZWA_KLIENTA VARCHAR2(45 BYTE),
RIN_MIEJSCE VARCHAR2(4 BYTE),
RIN_DATA_TRAN DATE,
RIN_TRAN_AMT NUMBER(15,2),
RIN_TRAM_WAL CHAR(3 BYTE),
RIN_SETT_AMT NUMBER(15,2),
RIN_SETT_WAL CHAR(3 BYTE),
RIN_MCC VARCHAR2(4 BYTE),
RIN_APPROVAL_CODE VARCHAR2(6 BYTE),
RIN_CASH_AMT NUMBER(15,2),
RIN_CASH_WAL CHAR(3 BYTE),
RIN_DATA_ORG DATE,
RIN_ACQ_BID VARCHAR2(11 BYTE),
RIN_TYP_KARTY VARCHAR2(4 BYTE),
RIN_MOBI_NR_TEL VARCHAR2(15 BYTE),
RIN_MOBI_TYP_INSTR CHAR(3 BYTE),
RIN_MOBI_TYP_AUT CHAR(1 BYTE),
RIN_MOBI_KOD_IDENT VARCHAR2(25 BYTE),
RIN_TRAN_TERM_ID VARCHAR2(16 BYTE),
RIN_MOBI_TYP_KONTA VARCHAR2(5 BYTE),
RIN_MOBI_PRW_AMT NUMBER(15,2),
RIN_MOBI_PRW_WAL CHAR(3 BYTE),
RIN_CZAS_TR VARCHAR2(10 BYTE),
RIN_POS_DATA_CODE VARCHAR2(12 BYTE),
RIN_TERM_TYPE CHAR(3 BYTE),
RIN_ID_TARYFY NUMBER(10,0),
RIN_ODDZIAL NUMBER(8,0),
RIN_MOBI_ID_KLIENTA VARCHAR2(12 BYTE),
RIN_TRACE_ID VARCHAR2(15 BYTE),
RIN_BUS_APP_ID CHAR(2 BYTE),
RIN_FAST_FUNDS_IND CHAR(1 BYTE),
IMP_DATE DATE,
IMP_ID NUMBER,
RIN_CZAS TIMESTAMP (6),
RIN_OPERATOR VARCHAR2(15 BYTE),
RIN_DUPLI_FL VARCHAR2(1 BYTE),
BIN CHAR(6 BYTE) INVISIBLE GENERATED ALWAYS AS (substr(RIN_NR_KARTY,1,6)),
MM CHAR(2 BYTE) INVISIBLE GENERATED ALWAYS AS (to_char(RIN_DATA_ORG,'mm'))
)
PARTITION BY LIST (BIN)
SUBPARTITION BY LIST (MM)
(
PARTITION mrs VALUES ('535470')
(
SUBPARTITION mrs_sub VALUES (DEFAULT)
),
PARTITION other VALUES (DEFAULT)
(
SUBPARTITION other_1 VALUES ('01','04','07','10'),
SUBPARTITION other_2 VALUES ('02','05','08','11'),
SUBPARTITION other_3 VALUES ('03','06','09','12'),
SUBPARTITION other_default VALUES (DEFAULT)
)
)
TABLESPACE EBKTA ;
CREATE UNIQUE INDEX EBKTA.IDX_ACP_STAGE_RL_INCOMING2_UNIQ ON EBKTA.ACP_STAGE_RL_INCOMING (RIN_ORG, RIN_NR_REF, RIN_NR_REF_ROZW, IMP_ID, bin,mm) local
TABLESPACE EBKTA ;
-- partycje other beda mialy wylaczony index a mrs nie
alter table EBKTA.ACP_STAGE_RL_INCOMING move subpartition other_1;
alter table EBKTA.ACP_STAGE_RL_INCOMING move subpartition other_2;
alter table EBKTA.ACP_STAGE_RL_INCOMING move subpartition other_3;
alter table EBKTA.ACP_STAGE_RL_INCOMING move subpartition other_default;
CREATE TABLE TEST
(
PART CHAR(1 BYTE)
, SPART VARCHAR2(2 BYTE)
, DATA_DANYCH DATE
, ACT_TYP_IDN_CDE CHAR(4 BYTE) NOT NULL
, ACT_TYP_DES_TXT CHAR(30 BYTE) NOT NULL
, LST_UPD_DTE DATE NOT NULL
, LST_UPD_TME CHAR(8 BYTE) NOT NULL
, LST_UPD_UID CHAR(8 BYTE) NOT NULL
, LST_UPD_NBR NUMBER(5, 0) NOT NULL
)
PARTITION BY RANGE (PART)
SUBPARTITION BY LIST (SPART)
(
PARTITION TGEN003_C VALUES LESS THAN ('D') TABLESPACE POHIO -- dzienne trzymane 7 dni
(
SUBPARTITION TGEN003_C1 VALUES ('1') TABLESPACE POHIO
, SUBPARTITION TGEN003_C2 VALUES ('2') TABLESPACE POHIO
, SUBPARTITION TGEN003_C3 VALUES ('3') TABLESPACE POHIO
, SUBPARTITION TGEN003_C4 VALUES ('4') TABLESPACE POHIO
, SUBPARTITION TGEN003_C5 VALUES ('5') TABLESPACE POHIO
, SUBPARTITION TGEN003_C6 VALUES ('6') TABLESPACE POHIO
, SUBPARTITION TGEN003_C7 VALUES ('7') TABLESPACE POHIO
)
, PARTITION TGEN003_I VALUES LESS THAN ('J') TABLESPACE POHIO_INIT -- inicjalne
(
SUBPARTITION SYS_SUBP988 VALUES (DEFAULT) TABLESPACE POHIO_INIT
)
);
SELECT * FROM EMPL SUBPARTITION(P10JOBA);
alter table t drop partition <partition name>;
alter table t truncate subpartition <subpartition>;
alter index t_idx modify partition part2 unusable;
alter index t_idx modify subpartition part2 unusable;
alter table tlf_eps
add partition TLF_EPS201807
values less than (TO_DATE(' 2018-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
- Multi-column list Partitioning
create table mc (col1 number, col2 number)
partition by list (col1, col2)
(partition p1 values ((1,2),(3,4)),
partition p2 values ((4,4)),
partition p3 values (default));
With multi-column partitioning you can also use the partition extended syntax with the FOR () clause.
select * from mc partition for (1,3);
select * from mc partition for (default);
- Auto-list Partitioning
Similar to interval partitioning, auto-list automatically creates a new partition as soon as a new partitioning key value is seen. As data is loaded into the table, the database automatically creates a new partition if the loaded partitioning key value does not correspond to any of the existing partitions.
Let's create a simple auto-list partitioned table:
-- create a simple auto-list partitioned table<br>
create table alp (col1 number, col2 number)
partition by list (col1) automatic
(partition p1 values (1,2,3,4,100),
partition p2 values (5));
Just by using the new keyword AUTOMATIC you created an auto-list partitioned table. Note that you always have to specify at minimum one partition. Oracle does not allow the creation of a table with at least one partition.
- Conversion of a nonpartitioned table to a partitioned table
Oracle Database 12c Release 2 adds this functionality: A non-partitioned heap table can be converted to a partitioned table with a MODIFY clause added to the ALTER TABLE SQL statement. In addition, the keyword ONLINE can be specified, enabling concurrent DML operations while the conversion is ongoing.
- Read only partitions and subpartitions
create table ropt (col1, col2, col3) nocompress
partition by range (col1) interval (10)
(partition p1 values less than (1) read only,
partition p2 values less than (11))
as select rownum, rownum*10, rpad('a',rownum,'b')
from dual connect by level <= 100;
alter table ropt modify partition for (5) read only;