ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Partitioning and subpartitioning of tables and indexes is a technique for creating a single logical entity, a table or index,
mapping multiple separate segments allowing the optimizer to access a smaller number of blocks to respond to a SQL statement.
Oracle supports partitioning only for tables, indexes on tables, materialized views, and indexes on materialized views. Oracle does not support partitioning of clustered tables or indexes on clustered tables.
Composite partitioning is a combination of other partitioning methods. Oracle currently supports range-hash and range-list composite partitioning.
Explain Plan PSTART/PSTOP Values
KEY(I)
IN subquery
KEY(SQ)
Recursive subquery
Global Index
A single index covering all partitions.
Hash Partitioning
Enables partitioning of data that does not lend itself to range or list partitioning.
-- to view the value Oracle is using within a specific session for hashing
SELECT program, sql_hash_value, prev_hash_value
FROM gv$session;
Interval Partitioning
Interval partitioning is an extension to range partitioning in which, beyond a point in time, partitions are defined by an interval. Interval partitions are automatically created by the database when data is inserted into the partition.
Invalidating Indexes
By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:
ADD (HASH)
COALESCE (HASH)
DROP
EXCHANGE
MERGE
MOVE
SPLIT
TRUNCATE
List Partitioning
Explicitly controls how rows map to partitions. You do this by specifying a list of discrete values for the partitioning key in the description for each partition.
Local Index
Separate indexes for each partition. A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns.
Unique local indexes are useful for OLTP environments. You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table.
Partition
Decompose a table or index into smaller, more manageable pieces, called partitions. Each partition of a table or index must have the same logical attributes,
such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as pctfree, pctused, and tablespaces.
Partition Key
Each row in a partitioned table is unambiguously assigned to a single partition. The partition key is a set of from 1 to 16 columns that determines the partition for each row.
Partitioning Pruning
Oracle optimizes SQL statements to mark the partitions or subpartitions that need to be accessed and eliminates (prunes) unnecessary partitions or subpartitions from access. Partition pruning is the skipping of unnecessary index and data partitions or subpartitions by a query.
Range Partitioning
Maps data to partitions based on ranges of partition key values that you establish for each partition.
Referential Partitioning
Data is mapped to partitions based on values defined in a referential constraint (foreign key)
Subpartition
Partitions created within partitions. They are just partitions themselves and there is nothing special about them.
Tablespaces
Create demo tablespaces
conn sys@pdbdev as sysdba
CREATE TABLESPACE part1
DATAFILE 'c:\temp\part01.dbf' SIZE 10M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
CREATE TABLESPACE part2
DATAFILE 'c:\temp\part02.dbf' SIZE 10M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
CREATE TABLESPACE part3
DATAFILE 'c:\temp\part03.dbf' SIZE 10M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
CREATE TABLESPACE part4
DATAFILE 'c:\temp\part04.dbf' SIZE 10M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
ALTER USER uwclass QUOTA UNLIMITED ON part1;
ALTER USER uwclass QUOTA UNLIMITED ON part2;
ALTER USER uwclass QUOTA UNLIMITED ON part3;
ALTER USER uwclass QUOTA UNLIMITED ON part4;
Drop Demo Tablespaces
conn sys@pdbdev as sysdba
DROP TABLESPACE part1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE part2 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE part3 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE part4 INCLUDING CONTENTS AND DATAFILES;
CREATE TABLE uwclass.hash_part (
prof_history_id NUMBER(10),
person_id NUMBER(10) NOT NULL,
organization_id NUMBER(10) NOT NULL,
record_date DATE NOT NULL,
prof_hist_comments VARCHAR2(2000))
PARTITION BY HASH (prof_history_id)
PARTITIONS 3
STORE IN (part1, part2, part3);
desc hash_part
SELECT table_name, tablespace_name, partitioned
FROM user_tables
ORDER BY 3;
TABLE_NAME TABLESPACE_NAME PAR
--------------- ------------------- ----------
HASH_PART YES
desc user_tab_partitions
SELECT partition_name, tablespace_name
FROM user_tab_partitions;
CREATE TABLE interval_part (
person_id NUMBER(5) NOT NULL,
first_name VARCHAR2(30),
last_name VARCHAR2(30))
PARTITION BY RANGE (person_id)
INTERVAL (100) STORE IN (part1) (
PARTITION p1 VALUES LESS THAN (101))
TABLESPACE uwdata;
desc interval_part
SELECT table_name, tablespace_name, partitioned
FROM user_tables
ORDER BY 3;
TABLE_NAME TABLESPACE_NAME PAR
--------------- ------------------- ----------
INTERVAL_PART YES
col high_value format a20
SELECT table_name, partition_name, tablespace_name, high_value
FROM user_tab_partitions
ORDER BY 1, 2;
Note that the VALUES LESS THAN clause uses the 28th of the month.
There is a perfectly valid reason for that. Figure out what it is and you will learn an important less out date interval partitioning.
If you cannot figure it out note the syntax is VALUES LESS THAN and choose the first day of the following month until you find someone that can solve the puzzle.
CREATE TABLE interval_date (
person_id NUMBER(5) NOT NULL,
last_name VARCHAR2(30),
dob DATE)
PARTITION BY RANGE (dob)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
STORE IN (part2, part4, uwdata) (
PARTITION p1 VALUES LESS THAN (TO_DATE('2020-12-28','YYYY-MM-DD')));
INSERT INTO interval_date
(person_id, last_name, dob)
VALUES
(1, 'Morgan', SYSDATE-365);
INSERT INTO interval_date
(person_id, last_name, dob)
VALUES
(2, 'Lofstrom', SYSDATE-365);
INSERT INTO interval_date
(person_id, last_name, dob)
VALUES
(3, 'Havemeyer', SYSDATE-200);
INSERT INTO interval_date
(person_id, last_name, dob)
VALUES
(4, 'Catz', SYSDATE-60);
INSERT INTO interval_date
(person_id, last_name, dob)
VALUES
(5, 'Ellison', SYSDATE+60);
COMMIT;
col partition_name format a14
col tablespace_name format a12
col high_value format a85
SELECT partition_name, tablespace_name AS TBSP_NAME, high_value
FROM user_tab_partitions
WHERE table_name = 'INTERVAL_DATE';
Interval-Interval Range Partitioned Table with new partitions created every six months
CREATE TABLE interval_interval (
program_id NUMBER,
line_number NUMBER,
order_date DATE)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTOYMINTERVAL(6,'MONTH'))
STORE IN (uwdata) (
PARTITION p2000 VALUES LESS THAN (TO_DATE('01-JAN-2025', 'DD-MON-YYYY')))
ENABLE ROW MOVEMENT;
INSERT INTO interval_interval
SELECT program_id, line_number, order_Date
FROM airplanes;
COMMIT;
SELECT partition_name, high_value
FROM user_tab_partitions
WHERE table_name = 'INTERVAL_INTERVAL';
Interval-Interval Range Partitioned Table with new partitions created every hour using OLTP compression
CREATE TABLE hourly_interval (
datetime DATE,
some_data NUMBER)
PARTITION BY RANGE (datetime)
INTERVAL (NUMTODSINTERVAL(1,'HOUR'))
STORE IN (part1, part2, part3) (
PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-2015 00:00:00', 'DD-MON-YYYY HH24:MI:SS')))
COMPRESS FOR OLTP;
INSERT INTO hourly_interval
(datetime, some_data)
VALUES
(SYSDATE, 1);
INSERT INTO hourly_interval
(datetime, some_data)
VALUES
(SYSDATE+(1/24), 1);
INSERT INTO hourly_interval
(datetime, some_data)
VALUES
(SYSDATE+(1/24), 1);
INSERT INTO hourly_interval
(datetime, some_data)
VALUES
(SYSDATE+(3/24), 1);
CREATE TABLE range_part (
prof_history_id NUMBER(10),
person_id NUMBER(10) NOT NULL,
organization_id NUMBER(10) NOT NULL,
record_date DATE NOT NULL)
PARTITION BY RANGE (record_date) (
PARTITION yrmin VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY'))
TABLESPACE part1,
PARTITION yr21 VALUES LESS THAN (TO_DATE('01-JAN-2022','DD-MON-YYYY'))
TABLESPACE part2,
PARTITION yr22 VALUES LESS THAN (TO_DATE('01-JAN-2023','DD-MON-YYYY'))
TABLESPACE part3,
PARTITION yrmax VALUES LESS THAN (MAXVALUE) TABLESPACE part4);
SELECT table_name, tablespace_name, partitioned
FROM user_tables
WHERE table_name = 'RANGE_PART';
TABLE_NAME TABLESPACE_NAME PAR
-------------------- ------------------------------ ---
RANGE_PART YES
col part_name format a9
col tbsp_name format a9
SELECT partition_name PART_NAME, tablespace_name TBSP_NAME, high_value
FROM user_tab_partitions
WHERE table_name = 'RANGE_PART'
ORDER BY 2;
ALTER SESSION SET tracefile_identifier = 'range_part';
ALTER SESSION SET EVENTS '10128 trace name context forever, level 0x0001';
SELECT * FROM range_part PARTITION(yr22);
ALTER SESSION SET SQL_TRACE=FALSE;
Trace file
C:\U01\ORABASE\diag\rdbms\orabase\orabase\trace\orabase_ora_12004_range_part.trc
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Build label: RDBMS_21.3.0.0.0DBRU_WINDOWS.X64_190528
Windows NT Version V6.2
ORACLE_HOME = C:\u01\app\oracle\product\dbhome_1
Node name : PERRITO5
CPU : 4 - type 86642 physical cores
Process Affinity : 0x0000000000000000
Memory (Avail/Total): Ph:5907M/16239M, Ph+PgF:8064M/21359M
Instance name: orabase
Redo thread mounted by this instance: 1
Oracle process number: 27
Windows thread id: 12004, image: ORACLE.EXE (SHAD)
*** TRACE CONTINUED FROM FILE C:\U01\ORABASE\diag\rdbms\orabase\orabase\trace\orabase_ora_12004.trc ***
partition pruning descriptor (aggregate information):
level = 1
flags = 0x040b000b {single, known}
dumping each kkpap ...
<start single kkpap dump>
pap=0000022D089C29E8
type = 0
method = 2
flags = 0x00000014 {single, known, set by parser, }
pct=1
QKSMA_IS_TABLE_INT_PART=0
highMatPart=3
highMatFrag=3
kcteobj=97825
<end single kkpap dump>
partition mapping descriptor:
partitioning method = range
number of partitions = 4
number of partitioning keys = 1
partitioning columns = (4)
partition pruning descriptor (aggregate information):
level = 1
flags = 0x040b000b {single, known}
dumping each kkpap ...
<start single kkpap dump>
pap=0000022D089C29E8
type = 0
method = 2
flags = 0x00000014 {single, known, set by parser, }
pct=1
QKSMA_IS_TABLE_INT_PART=0
highMatPart=3
highMatFrag=3
kcteobj=97825
<end single kkpap dump>
partition mapping descriptor:
partitioning method = range
number of partitions = 4
number of partitioning keys = 1
partitioning columns = (4)
partition pruning descriptor (aggregate information):
level = 1
flags = 0x040b000b {single, known}
dumping each kkpap ...
<start single kkpap dump>
pap=0000022D16A8E980
type = 0
method = 2
flags = 0x00000014 {single, known, set by parser, }
pct=1
QKSMA_IS_TABLE_INT_PART=0
highMatPart=3
highMatFrag=3
kcteobj=97825
<end single kkpap dump>
partition mapping descriptor:
partitioning method = range
number of partitions = 4
number of partitioning keys = 1
partitioning columns = (4)
partition pruning descriptor (aggregate information):
level = 1
flags = 0x040b000b {single, known}
dumping each kkpap ...
<start single kkpap dump>
pap=0000022D16A8E980
type = 0
method = 2
flags = 0x00000014 {single, known, set by parser, }
pct=1
QKSMA_IS_TABLE_INT_PART=0
highMatPart=3
highMatFrag=3
kcteobj=97825
<end single kkpap dump>
partition mapping descriptor:
partitioning method = range
number of partitions = 4
number of partitioning keys = 1
partitioning columns = (4)
Range Partitioned Table - By Alpha
CREATE TABLE students (
student_id NUMBER(6),
student_fn VARCHAR2(25),
student_ln VARCHAR2(25),
PRIMARY KEY (student_id))
PARTITION BY RANGE (student_ln) (
PARTITION student_ae
VALUES LESS THAN ('F%') TABLESPACE part1,
PARTITION student_fl VALUES LESS THAN ('M%') TABLESPACE part2,
PARTITION student_mr VALUES LESS THAN ('S%') TABLESPACE part3,
PARTITION student_sz VALUES LESS THAN (MAXVALUE) TABLESPACE part4);
SELECT table_name, tablespace_name, partitioned
FROM user_tables
WHERE table_name = 'STUDENTS'
TABLE_NAME TABLESPACE_NAME PAR
------------------------------ ------------------------------ ---
STUDENTS YES
col high_value format a30
SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'STUDENTS';
INSERT INTO syst_part VALUES (1, SYSDATE-10);
*
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method
INSERT INTO syst_part PARTITION (p1) VALUES (1, SYSDATE-10);
INSERT INTO syst_part PARTITION (p2) VALUES (2, SYSDATE);
INSERT INTO syst_part PARTITION (p3) VALUES (3, SYSDATE+10);
COMMIT;
CREATE TABLE json_orders (
tx_id NUMBER(5),
tx_date DATE,
jsondata JSON,
site_id AS (JSON_VALUE(jsondata, '$.siteId' RETURNING NUMBER)))
PARTITION BY RANGE (site_id) (
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20),
PARTITION pm VALUES LESS THAN (MAXVALUE));
desc json_orders
Name Null? Type
------------ -------- ------------
TX_ID NUMBER(5)
TX_DATE DATE
JSONDATA JSON
SITE_ID NUMBER
SELECT table_name, tablespace_name, partitioned
FROM user_tables
ORDER BY 3;
TABLE_NAME TABLESPACE_NAME PAR
--------------------- ------------------------------ ---
JSON_ORDERS YES
col high_value format a20
SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'JSON_ORDERS';
PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
--------------- ------------------ -------------
P1 SYSTEM 10
P2 SYSTEM 20
PM SYSTEM MAXVALUE
desc user_tab_cols
SELECT column_name, virtual_column, data_default
FROM user_tab_cols
WHERE table_name = 'JSON_ORDERS';
COLUMN_NAME VIR DATA_DEFAULT
------------------------------ --- -----------------------------------------------------------------------------
TX_ID NO
TX_DATE NO
JSONDATA NO
SITE_ID YES JSON_VALUE("JSONDATA" FORMAT OSON, '$.siteId' RETURNING NUMBER NULL ON
ERROR)
SYS_IME_OSON_2ADF3448E8D74FE1B YES OSON("JSONDATA" FORMAT OSON , 'ime' RETURNING RAW(2000) NULL ON ERROR)
FB7418A12CF01DC
CREATE TABLE json_orders (
tx_id NUMBER(5),
tx_date DATE,
jsondata VARCHAR2(4000),
site_id AS (JSON_VALUE(jsondata, '$.siteId' RETURNING NUMBER)))
PARTITION BY RANGE (site_id) (
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20),
PARTITION pm VALUES LESS THAN (MAXVALUE));
desc json_orders
SELECT table_name, tablespace_name, partitioned
FROM user_tables
ORDER BY 3;
col high_value format a20
SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'JSON_ORDERS';
desc user_tab_cols
SELECT column_name, virtual_column, data_default
FROM user_tab_cols
WHERE table_name = 'JSON_ORDERS';
CREATE TABLE vcol_part (
tx_id NUMBER(5),
begdate DATE,
enddate DATE,
staylen NUMBER(5) AS (enddate-begdate))
PARTITION BY RANGE (staylen)
INTERVAL (10) STORE IN (uwdata) (
PARTITION p1 VALUES LESS THAN (11))
TABLESPACE part3;
desc vcol_part
SELECT table_name, tablespace_name, partitioned
FROM user_tables
ORDER BY 3;
TABLE_NAME TABLESPACE_NAME PAR
-------------------- ---------------- ---
VCOL_PART YES
col high_value format a20
SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'VCOL_PART';
<xs:element name="PurchaseOrder" type="PurchaseOrderType"
xdb:defaultTable="PURCHASEORDER"
xdb:tableProps =
"VARRAY XMLDATA.LINEITEMS.LINEITEM
STORE AS TABLE lineitem_table
((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)))
PARTITION BY RANGE (XMLDATA.Reference)
(PARTITION p1 VALUES LESS THAN (1000)
VARRAY XMLDATA.LINEITEMS.LINEITEM
STORE AS TABLE lineitem_p1 (STORAGE (MINEXTENTS 13)),
PARTITION p2 VALUES LESS THAN (2000)
VARRAY XMLDATA.LINEITEMS.LINEITEM
STORE AS TABLE lineitem_p2 (STORAGE (MINEXTENTS 13)))"/>
CREATE TABLE purchaseorder OF XMLType
XMLSCHEMA "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd"
ELEMENT "PurchaseOrder"
VARRAY "XMLDATA"."LINEITEMS"."LINEITEM" STORE AS TABLE lineitem_table
((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)))
PARTITION BY RANGE (XMLDATA.Reference)
(PARTITION p1 VALUES LESS THAN (1000)
VARRAY "XMLDATA"."LINEITEMS"."LINEITEM" STORE AS TABLE lineitem_p1
(STORAGE (MINEXTENTS 13)),
PARTITION p2 VALUES LESS THAN (2000)
VARRAY "XMLDATA"."LINEITEMS"."LINEITEM" STORE AS TABLE lineitem_p2
(STORAGE (MINEXTENTS 13)));
*
ERROR at line 1:
ORA-31000: Resource 'http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd' is not an XDB schema document
CREATE TABLE orders OF XMLType
XMLTYPE STORE AS BINARY XML
VIRTUAL COLUMNS (SITE_ID AS (XMLCast(XMLQuery('/Order/@SiteId' PASSING OBJECT_VALUE RETURNING CONTENT) AS NUMBER)))
PARTITION BY RANGE (site_id) (
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20),
PARTITION pm VALUES LESS THAN (MAXVALUE));
desc orders
set describe depth all
desc orders
set describe depth 1
desc orders
SELECT column_id, column_name, virtual_column, data_default
FROM user_tab_cols
WHERE table_name = 'ORDERS'
ORDER BY 1;
COLUMN_ID COLUMN_NAME VIR DATA_DEFAULT
---------- ------------------------------ --- --------------------------------------------
1 XMLDATA NO
1 SYS_NC_ROWINFO$ YES
SITE_ID YES CAST(SYS_XQ_UPKXML2SQL(
SYS_XQEXVAL(XMLQUERY('/Order/@SiteId'
PASSING BY VALUE SYS_MAKEXML( 0, "XMLDATA")
RETURNING CONTENT ), 0, 0, 16777216,
1073741824),50,1,2) AS NUMBER)
SYS_NC_OID$ NO SYS_OP_GUID()
SELECT partition_name, high_value
FROM dba_tab_partitions
WHERE table_name = 'ORDERS';
CREATE TABLE composite_rng_rng (
cust_id NUMBER(10),
cust_name VARCHAR2(25),
cust_state VARCHAR2(2),
time_id DATE)
PARTITION BY RANGE(time_id)
SUBPARTITION BY RANGE (cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION original VALUES LESS THAN (1001) TABLESPACE part1,
SUBPARTITION acquired VALUES LESS THAN (8001) TABLESPACE part2,
SUBPARTITION recent VALUES LESS THAN (MAXVALUE) TABLESPACE part3) (
PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2015','DD/MM/YYYY')),
PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2020','DD/MM/YYYY')),
PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2025','DD/MM/YYYY')),
PARTITION future VALUES LESS THAN (MAXVALUE));
desc composite_rng_rng
SELECT partition_name, composite, high_value
FROM user_tab_partitions
WHERE table_name = 'COMPOSITE_RNG_RNG';
col partition_name format a15
col high_value format a30
SELECT partition_name, subpartition_name, tablespace_name, high_value
FROM user_tab_subpartitions
WHERE table_name = 'RANGE_LIST';
ALTER TABLE range_list DROP PARTITION sf;
ALTER TABLE range_list
ADD PARTITION s02
VALUES LESS THAN(TO_DATE('01/01/2015','DD/MM/YYYY'));
ALTER TABLE range_list
ADD PARTITION sf
VALUES LESS THAN(MAXVALUE);
SELECT partition_name, subpartition_name, tablespace_name, high_value
FROM user_tab_subpartitions
WHERE table_name = 'RANGE_LIST';
set long 1000000
SELECT dbms_metadata.get_ddl('TABLE', 'RANGE_LIST');
Change The Tablespace Name For A Future Partition
ALTER TABLE <table_name>
MODIFY DEFAULT ATTRIBUTES FOR PARTITION <partition_name>
TABLESPACE <tablespace_name>;
SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'RANGE_LIST';
ALTER TABLE range_list
MODIFY DEFAULT ATTRIBUTES FOR PARTITION s11 TABLESPACE part1;
SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'RANGE_LIST';
SELECT partition_name, subpartition_name, tablespace_name
FROM user_tab_subpartitions
WHERE table_name = 'RANGE_LIST';
Change The Tablespace Store In List For An Interval Partitioned Table
ALTER TABLE <table_name>
SET STORE IN <tablespace_name_list>;
SELECT dbms_metadata.get_ddl('TABLE', 'HOURLY_INTERVAL') FROM dual;
ALTER TABLE hourly_interval SET STORE IN (UWDATA, EXAMPLE);
SELECT dbms_metadata.get_ddl('TABLE', 'HOURLY_INTERVAL') FROM dual;
ALTER TABLE hourly_interval SET STORE IN (UWDATA, EXAMPLE, USERS);
SELECT dbms_metadata.get_ddl('TABLE', 'HOURLY_INTERVAL') FROM dual;
Modify A List Partitioned List
ALTER TABLE <table_name>
MODIFY PARTITION <partition_name>
ADD VALUES (<values_list>);
SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'LIST_PART';
ALTER TABLE list_part
MODIFY PARTITION q1_northcent
ADD VALUES ('MI', 'OH');
SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'LIST_PART';
Drop Values From A List Partitioned List
ALTER TABLE <table_name>
MODIFY PARTITION <partition_name>
DROP VALUES (<values_list>);
ALTER TABLE list_part
MODIFY PARTITION q1_southwest
DROP VALUES ('NM');
SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'LIST_PART';
Convert a partition into a stand-alone table
ALTER TABLE <table_name>
EXCHANGE PARTITION <partition_name>
WITH TABLE <new_table_name>
<including | excluding> INDEXES
<with | without> VALIDATION
EXCEPTIONS INTO <schema.table_name>;
SELECT table_name, partition_name, num_rows
FROM user_tab_partitions
WHERE table_name = 'LIST_PART';
CREATE TABLE q1_northwest AS
SELECT *
FROM list_part
WHERE 1=2;
SELECT * FROM list_part;
SELECT * FROM list_part PARTITION(q1_northwest);
ALTER TABLE list_part
EXCHANGE PARTITION q1_northwest WITH TABLE q1_northwest
INCLUDING INDEXES
WITHOUT VALIDATION
EXCEPTIONS INTO uwclass.problems;
SELECT * FROM q1_northwest;
SELECT * FROM list_part;
Convert a stand-alone table into a partition
ALTER TABLE <table_name>
EXCHANGE PARTITION <partition_name> WITH TABLE <table_name>
[INCLUDING INDEXES <WITH | WITHOUT> VALIDATION];
CREATE TABLE range_part (
rid NUMBER,
col1 VARCHAR2(10),
col2 VARCHAR2(100))
PARTITION BY RANGE(rid) (
partition p1 VALUES LESS THAN (1000),
partition p3 VALUES LESS THAN (3000),
partition pm VALUES LESS THAN (MAXVALUE));
INSERT /*+ APPEND ORDERED FULL(s1) USE_NL(s2) */
INTO new_part
SELECT 3000 + TRUNC((rownum-1)/500,6), TO_CHAR(rownum), RPAD('x',100)
FROM sys.source$ s1, sys.source$ s2
WHERE rownum <= 100000;
COMMIT;
SELECT COUNT(*) FROM range_part;
SELECT COUNT(*) FROM new_part;
col high_value format a20
SELECT table_name, partition_name, high_value
FROM user_tab_partitions;
set timing on
ALTER TABLE range_part
EXCHANGE PARTITION pm WITH TABLE new_part;
set timing off
DROP TABLE range_part PURGE;
DROP TABLE new_part PURGE;
-- recreate and populate tables
set timing on
ALTER TABLE range_part
EXCHANGE PARTITION pm WITH TABLE new_part
WITHOUT VALIDATION;
-- again drop the tables, recreate, and load them
-- add some realistic constraints
ALTER TABLE range_part
ADD CONSTRAINT pk_range_part
PRIMARY KEY(rid)
USING INDEX LOCAL;
ALTER TABLE new_part
ADD CONSTRAINT pk_new_part
PRIMARY KEY(rid)
USING INDEX;
set timing on
ALTER TABLE range_part
EXCHANGE PARTITION pm WITH TABLE new_part
INCLUDING INDEXES WITHOUT VALIDATION;
-- repeat again but this time do the following before the exchange
ALTER TABLE range_part MODIFY PRIMARY KEY NOVALIDATE;
ALTER TABLE new_part MODIFY PRIMARY KEY NOVALIDATE;
set timing on
ALTER TABLE range_part
EXCHANGE PARTITION pm WITH TABLE new_part
INCLUDING INDEXES WITHOUT VALIDATION;
Partition Exchange with an Interval Partitioned Table
-- obviously you can't exchange with what doesn't exist ... but nothing stops you from creating something to exchange
-- with as you can see here ... so just create a placeholder to perform the operation
CREATE TABLE interval_date(
per_id NUMBER(5) NOT NULL,
lname VARCHAR2(30),
dob DATE)
PARTITION BY RANGE (dob)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
STORE IN (uwdata) (
PARTITION p1 VALUES LESS THAN (TO_DATE('2021-01-01','YYYY-MM-DD')));
Table created.
INSERT INTO interval_date (per_id, lname, dob) VALUES (0,'Catz',TO_DATE('31-DEC-2020'));
1 row created.
-- table to be added by exchange
CREATE TABLE interval_new AS
SELECT * FROM interval_date
WHERE 1=2;
Table created.
INSERT INTO interval_new VALUES (1, 'Morgan', SYSDATE);
INSERT INTO interval_new VALUES (2, 'Whalen', SYSDATE+1);
INSERT INTO interval_new VALUES (3, 'Small', SYSDATE+2);
COMMIT;
col pname format a10
col high_value format a84
SELECT partition_name PNAME, high_value
FROM user_tab_partitions
WHERE table_name = 'INTERVAL_DATE';
ALTER TABLE interval_date
EXCHANGE PARTITION p2 WITH TABLE interval_new
WITHOUT VALIDATION;
EXCHANGE PARTITION p2 WITH TABLE interval_new
*
ERROR at line 2:
ORA-02149: Specified partition does not exist
-- force creation of a partition with the correct high_value for the exchange
INSERT INTO interval_date VALUES (1, 'DUMMY', SYSDATE);
SELECT partition_name PNAME, high_value
FROM user_tab_partitions
WHERE table_name = 'INTERVAL_DATE';
ALTER TABLE interval_date
EXCHANGE PARTITION SYS_P2614 WITH TABLE interval_new
WITHOUT VALIDATION;
SELECT * FROM interval_date;
PER_ID LNAME DOB
---------- ------------------------------ --------------------
0 Hurd 31-DEC-2016 00:00:00
1 Morgan 13-MAY-2017 22:24:12
2 Pace 14-MAY-2017 22:24:18
3 Dawson 15-MAY-2017 22:24:24
Rename a partition
ALTER TABLE <table_name>
RENAME PARTITION <existing_partition_name>
TO <new_partition_name>;
SELECT table_name, partition_name
FROM user_tab_partitions;
ALTER TABLE range_list RENAME PARTITION sf TO sales_future;
SELECT table_name, partition_name
FROM user_tab_partitions;
Split Partition
Note: If splitting the MAXVALUE partition perform a first split at a value higher than the maximum value in the MAXVALUE partition.
ALTER TABLE <table_name>
SPLIT PARTITION <partition_name>
AT <range_definition>
INTO (PARTITION <first_partition>, PARTITION <second_partition>)
UPDATE GLOBAL INDEXES;
SELECT table_name, partition_name, high_value
FROM user_tab_partitions
WHERE table_name = 'RANGE_PART';
INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('01-JAN-2018'), 'A');
INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('01-JAN-2019'), 'A');
INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('01-JAN-2020'), 'A');
INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('01-JAN-2021'), 'A');
INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('15-MAR-2021'), 'A');
INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('16-SEP-2021'), 'A');
INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('20-DEC-2021'), 'A');
INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('01-JAN-2022'), 'A');
INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('01-JAN-2023'), 'A');
COMMIT;
col ph_comments format a10
SELECT * FROM range_part;
SELECT * FROM range_part PARTITION(yr2a);
ALTER TABLE range_part
SPLIT PARTITION yr2
AT (TO_DATE('30-JUN-2001','DD-MON-YYYY'))
INTO (PARTITION yr2a, PARTITION yr2b)
UPDATE GLOBAL INDEXES;
SELECT * FROM range_part PARTITION(yr2a);
SELECT * FROM range_part PARTITION(yr2b);
SELECT table_name, partition_name, high_value
FROM user_tab_partitions
WHERE table_name = 'RANGE_PART';
Truncate a partition
Follow the TRUNCATE PARTITION link at page bottom
Split An LOB Partition
ALTER TABLE <table_name>
SPLIT PARTITION <partition_name> AT <split location> INTO
(PARTITION <new_partition_name> TABLESPACE <tablespace_name>"
LOB <column_name> STORE AS (TABLESPACE <tablespace_name>),
PARTITION <new_partition_name>
LOB (<column_name>) STORE AS (TABLESPACE <tablespace_name>);
CREATE TYPE adheader_typ AS OBJECT (
header_name VARCHAR2(256),
creation_date DATE,
header_text VARCHAR2(1024),
logo BLOB);
/
CREATE TABLE print_media_part (
product_id NUMBER(6),
ad_id NUMBER(6),
ad_composite BLOB,
ad_sourcetext CLOB,
ad_finaltext CLOB,
ad_fltextn NCLOB,
ad_textdocs_ntab TEXTDOC_TAB,
ad_photo BLOB,
ad_graphic BFILE,
ad_header ADHEADER_TYP)
NESTED TABLE ad_textdocs_ntab STORE AS textdoc_nt
PARTITION BY RANGE (product_id) (
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (200));
ALTER TABLE print_media_part
SPLIT PARTITION p2 AT (150) INTO (
PARTITION p2a TABLESPACE uwdata
LOB (ad_photo, ad_composite) STORE AS (TABLESPACE part1),
PARTITION p2b
LOB (ad_photo, ad_composite) STORE AS (TABLESPACE part2));
Coalesce Hash Partitions
ALTER TABLE <table_name> COALESCE PARTITION;
ALTER TABLE hash_part COALESCE PARTITION;
Add Partition And Specify BLOB/LOB Storage
ALTER TABLE <table_name>
ADD PARTITION <new_partition_name> VALUES LESS THAN (MAXVALUE)
LOB (<column_name>) STORE AS (TABLESPACE <tablespace_name);
ALTER TABLE print_media_part
ADD PARTITION p3 VALUES LESS THAN (MAXVALUE)
LOB (ad_photo, ad_composite) STORE AS (TABLESPACE part3)
LOB (ad_sourcetext, ad_finaltext) STORE AS (TABLESPACE part4);
Index Partitions
Global Index Creation
CREATE INDEX <index_name>
ON <table_name> <column_name_list>;
SELECT i.index_name, i.composite, i.partition_name, i.high_value
FROM user_ind_partitions i, user_tab_partitions t
WHERE i.partition_name = t.partition_name
AND t.table_name = 'RANGE_PART';
SELECT partition_name
FROM user_tab_partitions
WHERE table_name = 'RANGE_PART';
CREATE INDEX gi_range_part_person_id
ON range_part (person_id);
SELECT index_name, partitioned
FROM user_indexes
WHERE table_name = 'RANGE_PART';
CREATE INDEX <index_name>
ON <table_name> <column_name_list> LOCAL;
CREATE INDEX li_range_part_person_id
ON range_part (person_id)
LOCAL;
SELECT index_name, partitioned
FROM user_indexes
WHERE table_name = 'RANGE_PART';
SELECT ip.index_name, ip.composite, ip.partition_name, ip.high_value
FROM user_ind_partitions ip, user_indexes ui
WHERE ip.index_name = ui.index_name
AND ui.table_name = 'RANGE_PART';
SELECT locality
FROM utl_all_ind_comps
WHERE index_name = 'LI_RANGE_PART_PERSON_ID';
DROP INDEX li_range_part_person_id;
CREATE INDEX li_range_part_person_id
ON range_part (person_id)
LOCAL (
PARTITION yr0 TABLESPACE part1,
PARTITION yr1 TABLESPACE part2,
PARTITION yr2a TABLESPACE part3,
PARTITION yr2b TABLESPACE part4,
PARTITION yr9 TABLESPACE uwdata);
col tablespace_name format a15
SELECT ip.index_name, ip.partition_name, ip.tablespace_name, ip.high_value
FROM user_ind_partitions ip, user_indexes ui
WHERE ip.index_name = ui.index_name
AND ui.table_name = 'RANGE_PART';
SELECT * FROM range_part;
SELECT * FROM range_part PARTITION(yr2a);
EXPLAIN PLAN FOR
SELECT *
FROM range_part
WHERE record_date BETWEEN TO_DATE('01-JAN-1998') AND TO_DATE('31-JAN-1998');
SELECT * FROM TABLE(dbms_xplan.display);
EXPLAIN PLAN FOR
SELECT *
FROM range_part
WHERE record_date BETWEEN TO_DATE('01-JAN-1998') AND TO_DATE('31-DEC-2000');
SELECT * FROM TABLE(dbms_xplan.display);
EXPLAIN PLAN FOR
SELECT *
FROM range_part
WHERE record_date BETWEEN TO_DATE('01-JAN-1999') AND TO_DATE('31-DEC-2002');
SELECT * FROM TABLE(dbms_xplan.display);
Global Partition Index Creation
CREATE INDEX <index_name>
ON <table_name> <column_name_list>
GLOBAL PARTITION BY RANGE (partition_column_name_list) (
PARTITION <partition_name> VALUES <condition>);
DROP INDEX li_range_part_person_id;
UPDATE range_part
SET organization_id = ROWNUM;
col ph_comments format a15
SELECT * FROM range_part;
CREATE INDEX gi_range_part_person_id
ON range_part (organization_id)
GLOBAL PARTITION BY RANGE(organization_id) (
PARTITION p1 VALUES LESS THAN(4)
TABLESPACE part1,
PARTITION p2 VALUES LESS THAN(MAXVALUE)
TABLESPACE part2);
col high_value format a20
SELECT ip.index_name, ip.partition_name, ip.tablespace_name, ip.high_value
FROM user_ind_partitions ip, user_indexes ui
WHERE ip.index_name = ui.index_name
AND ui.table_name = 'RANGE_PART';
Query for Unusable Indexes
SELECT index_name, partition_name, status
FROM user_ind_partitions;
Alter Table and Index For Partitions
Rebuild Local All Local Indexes On A Table
ALTER TABLE <table_name>
MODIFY PARTITION <partition_name>
REBUILD UNUSABLE LOCAL INDEXES;
CREATE INDEX li_range_part_person_id
ON range_part (person_id)
LOCAL;
SELECT t.table_name, i.index_name, i.partition_name, i.status
FROM user_ind_partitions i, user_tab_partitions t
WHERE i.partition_name = t.partition_name;
ALTER TABLE range_part
MODIFY PARTITION yr0
REBUILD UNUSABLE LOCAL INDEXES;
Disable indexing of a partition
ALTER TABLE <table_name>
MODIFY PARTITION <partition_name>
INDEXING <OFF | ON>;
ALTER TABLE range_part
MODIFY PARTITION yr2020
INDEXING OFF;
Enable indexing of a partition
ALTER TABLE <table_name>
MODIFY PARTITION <partition_name>
INDEXING <OFF | ON>;
ALTER TABLE range_part
MODIFY PARTITION yr2020
INDEXING ON;
Rebuild any unusable local index partitions associated with a hash partition at the specific composite partitioned table subpartition level
ALTER TABLE <table_name>
MODIFY SUBPARTITION <subpartition_name>
REBUILD UNUSABLE LOCAL INDEXES;
SELECT i.table_name, s.index_name, s.partition_name, s.status
FROM user_ind_subpartitions s, user_indexes i
WHERE s.index_name = i.index_name;
ALTER TABLE composite_rng_hash
MODIFY SUBPARTITION sales_1999_sp4
REBUILD UNUSABLE LOCAL INDEXES;
Rebuild (and move) a local partition index
ALTER INDEX <index_name>
REBUILD PARTITION <partition_name>
TABLESPACE <new_tablespace_name>;
col partition_name format a10
col tablespace_name format a20
SELECT i.table_name, s.index_name, s.tablespace_name, s.partition_name, s.status
FROM user_ind_partitions s, user_indexes i
WHERE s.index_name = i.index_name;
ALTER INDEX li_range_part_person_id
REBUILD PARTITION yr2
TABLESPACE uwdata;
SELECT i.table_name, s.index_name, s.tablespace_name, s.partition_name, s.status
FROM user_ind_partitions s, user_indexes i
WHERE s.index_name = i.index_name;
Setting a new default tablespace
ALTER INDEX <index_owner>.<index_name>
MODIFY DEFAULT ATTRIBUTES TABLESPACE <tablespace_name>;
SELECT DISTINCT 'ALTER INDEX ' || index_owner || '.' || index_name ||
'MODIFY DEFAULT ATTRIBUTES TABLESPACE newtbs;'
FROM user_ind_partitions;
Drop Partition
Drop partition from a partitioned table
ALTER TABLE DROP PARTITION <partition_name> [UPDATE GLOBAL INDEXES];
SELECT table_name, partition_name
FROM user_tab_partitions;
ALTER TABLE range_list DROP PARTITION s2k UPDATE GLOBAL INDEXES;
-- create a list partitioned table
CREATE TABLE partdemo (
empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7, 2),
comm NUMBER(7, 2),
deptno NUMBER(2))
partition by list(deptno)(
partition p1 values (10,30) tablespace uwdata,
partition p2 values (20,40) tablespace example);
INSERT INTO partdemo SELECT * FROM scott.emp;
set linesize 121
SELECT * FROM partdemo;
SELECT * FROM partdemo PARTITION(p1);
SELECT * FROM partdemo PARTITION(p2);
-- take the example tablespace OFFLINE to examine partition elimination
conn sys@pdbdev as sysdba
ALTER TABLESPACE example OFFLINE;
conn scott/tiger@pdbdev
SELECT COUNT(*) FROM partdemo;
SELECT COUNT(*) FROM partdemo WHERE deptno = 10;
SELECT COUNT(*) FROM partdemo WHERE deptno BETWEEN 1 AND 19;
SELECT COUNT(*) FROM partdemo WHERE deptno BETWEEN 1 AND 20;
SELECT COUNT(*) FROM partdemo WHERE deptno IN(10,30);
conn sys@pdbdev as sysdba
ALTER TABLESPACE example ONLINE;
Partitioning and READ ONLY Tablespaces
CREATE TABLE ropt (
rid NUMBER(5),
datecol DATE,
msgcol VARCHAR2(20))
PARTITION BY RANGE (datecol) (
PARTITION p2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY'))
TABLESPACE part1,
PARTITION p2010 VALUES LESS THAN (TO_DATE('01-JAN-2011','DD-MON-YYYY'))
TABLESPACE part2,
PARTITION pmax VALUES LESS THAN (MAXVALUE) TABLESPACE uwdata);
INSERT INTO ropt VALUES (1, TO_DATE('01-JUL-2009', 'DD-MON-YYYY'), 'validate');
INSERT INTO ropt VALUES (2, TO_DATE('01-JUL-2010', 'DD-MON-YYYY'), 'assumptions');
INSERT INTO ropt VALUES (3, TO_DATE('01-JUL-2011', 'DD-MON-YYYY'), 'by
testing');
COMMIT;
SELECT * FROM ropt;
CREATE OR REPLACE FUNCTION part_status(rid_in IN rowid) RETURN VARCHAR2 IS
tspstat user_tablespaces.status%TYPE;
BEGIN
SELECT uts.status
INTO tspstat
FROM dba_data_files ddf, user_tablespaces uts
WHERE ddf.relative_fno = dbms_rowid.rowid_relative_fno(rid_in)
AND ddf.file_id = dbms_rowid.rowid_to_absolute_fno(rid_in, USER, 'ROPT')
AND ddf.tablespace_name = uts.tablespace_name;
RETURN tspstat;
END part_status;
/
SELECT ilv.*, dts.status
FROM (
SELECT ropt.*,
dbms_rowid.rowid_relative_fno(rowid) rfno,
dbms_rowid.rowid_to_absolute_fno(rowid,user,'ROPT') afno
FROM ropt) ilv,
dba_data_files ddf,
dba_tablespaces dts
WHERE ddf.relative_fno = rfno
AND ddf.file_id = afno
AND ddf.tablespace_name = dts.tablespace_name;
col part_status format a20
SELECT ropt.*, part_status(ropt.rowid) PART_STATUS
FROM ropt;
ALTER TABLESPACE part2 READ ONLY;
SELECT ilv.*, dts.status
FROM (
SELECT ropt.*,
dbms_rowid.rowid_relative_fno(rowid) rfno,
dbms_rowid.rowid_to_absolute_fno(rowid,user,'ROPT') afno
FROM ropt) ilv,
dba_data_files ddf,
dba_tablespaces dts
WHERE ddf.relative_fno = rfno
AND ddf.file_id = afno
AND ddf.tablespace_name = dts.tablespace_name;
SELECT ropt.*, part_status(ropt.rowid) PART_STATUS
FROM ropt;
ALTER TABLESPACE users READ WRITE;
Partitioning and READ ONLY Partitions
CREATE TABLE ro_part1 (
prof_hist_id NUMBER,
record_date DATE)
PARTITION BY RANGE(record_date)
(
PARTITION yr2020 VALUES LESS THAN (TO_DATE('01-JAN-2015','DD-MON-YYYY'))
READ ONLY,
PARTITION yr2021 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY'))
READ ONLY,
PARTITION yr2022 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY'))
READ ONLY,
PARTITION yr2023 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))
READ WRITE);
CREATE TABLE ro_part2 (
prof_hist_id NUMBER,
record_date DATE) READ ONLY
PARTITION BY RANGE(record_date)
(
PARTITION yr2014 VALUES LESS THAN (TO_DATE('01-JAN-2015','DD-MON-YYYY')),
PARTITION yr2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
PARTITION yr2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
PARTITION yr2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))
READ WRITE);
CREATE TABLE range_part (
prof_history_id NUMBER(10),
record_date DATE NOT NULL)
PARTITION BY RANGE (record_date) (
PARTITION yr0 VALUES LESS THAN (TO_DATE('01-JAN-2020','DD-MON-YYYY')),
PARTITION yr1 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY')),
PARTITION yr2 VALUES LESS THAN (TO_DATE('01-JAN-2022','DD-MON-YYYY')),
PARTITION yr9 VALUES LESS THAN (MAXVALUE));
SELECT segment_name
FROM user_segments
WHERE segment_type LIKE '%PARTITION';
CREATE INDEX ix_range_part_phid
ON range_part(prof_history_id)
LOCAL UNUSABLE;
SELECT segment_name
FROM user_segments
WHERE segment_type LIKE '%PARTITION';
SELECT index_name
FROM user_part_indexes;
ALTER INDEX ix_range_part_phid REBUILD PARTITION yr1;
SELECT segment_name
FROM user_segments
WHERE segment_type LIKE '%PARTITION';
ALTER INDEX ix_range_part_phid REBUILD PARTITION yr2;
SELECT segment_name
FROM user_segments
WHERE segment_type LIKE '%PARTITION';
ALTER INDEX ix_range_part_phid MODIFY PARTITION yr1 UNUSABLE;
SELECT segment_name
FROM user_segments
WHERE segment_type LIKE '%PARTITION';
