Oracle Partitioned Tables and Indexes
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
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.
Partition Types
Dependencies
ALL_IND_PARTITIONS    
ALL_IND_SUBPARTITIONS CDB_PART_LOBS INDPART_PARAM$
ALL_LOB_PARTITIONS CDB_PART_TABLES INDSUBPART$
ALL_LOB_SUBPARTITIONS CDB_SUBPARTITION_TEMPLATES INSERT_TSN_LIST$
ALL_MVIEW_DETAIL_PARTITION CDB_SUBPART_COL_STATISTICS PARTLOB$
ALL_MVIEW_DETAIL_SUBPARTITION CDB_SUBPART_HISTOGRAMS PARTOBJ$
ALL_PART_COL_STATISTICS CDB_SUBPART_KEY_COLUMNS SUBPARTCOL$
ALL_PART_HISTOGRAMS CDB_TAB_PARTITIONS TABPART$
ALL_PART_INDEXES CDB_TAB_SUBPARTITIONS TABSUBPART$
ALL_PART_KEY_COLUMNS DBA_IND_PARTITIONS USER_IND_PARTITIONS
ALL_PART_LOBS DBA_IND_SUBPARTITIONS USER_IND_SUBPARTITIONS
ALL_PART_TABLES DBA_LOB_PARTITIONS USER_LOB_PARTITIONS
ALL_SUBPARTITION_TEMPLATES DBA_LOB_SUBPARTITIONS USER_LOB_SUBPARTITIONS
ALL_SUBPART_COL_STATISTICS DBA_MVIEW_DETAIL_PARTITION USER_MVIEW_DETAIL_PARTITION
ALL_SUBPART_HISTOGRAMS DBA_MVIEW_DETAIL_SUBPARTITION USER_MVIEW_DETAIL_SUBPARTITION
ALL_SUBPART_KEY_COLUMNS DBA_PART_COL_STATISTICS USER_PART_COL_STATISTICS
ALL_TAB_PARTITIONS DBA_PART_HISTOGRAMS USER_PART_HISTOGRAMS
ALL_TAB_SUBPARTITIONS DBA_PART_INDEXES USER_PART_INDEXES
CDB_IND_PARTITIONS DBA_PART_KEY_COLUMNS USER_PART_KEY_COLUMNS
CDB_IND_SUBPARTITIONS DBA_PART_LOBS USER_PART_LOBS
CDB_LOB_PARTITIONS DBA_PART_TABLES USER_PART_TABLES
CDB_LOB_SUBPARTITIONS DBA_SUBPARTITION_TEMPLATES USER_SUBPARTITION_TEMPLATES
CDB_MVIEW_DETAIL_PARTITION DBA_SUBPART_COL_STATISTICS USER_SUBPART_COL_STATISTICS
CDB_MVIEW_DETAIL_SUBPARTITION DBA_SUBPART_HISTOGRAMS USER_SUBPART_HISTOGRAMS
CDB_PART_COL_STATISTICS DBA_SUBPART_KEY_COLUMNS USER_SUBPART_KEY_COLUMNS
CDB_PART_HISTOGRAMS DBA_TAB_PARTITIONS USER_TAB_PARTITIONS
CDB_PART_INDEXES DBA_TAB_SUBPARTITIONS USER_TAB_SUBPARTITIONS
CDB_PART_KEY_COLUMNS INDPART$ UTL_ALL_IND_COMPS
 
Definitions
Composite Partitioning 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;
 
Table Partitions
Hash Partitioned Table 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;

PARTITION_NAME       TABLESPACE_NAME
-------------------- ----------------------
SYS_P501             PART1
SYS_P502             PART2
SYS_P503             PART3
Interval-Numeric Range Partitioned Table

Also possible are Interval-Hash and Interval-List
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;

TABLE_NAME      PARTITION_NAME  TABLESPACE_NAME     HIGH_VALUE
--------------- --------------- ------------------- -----------
INTERVAL_PART   P1              UWDATA              101


INSERT INTO interval_part
(person_id, first_name, last_name)
VALUES
(100, 'Dan', 'Morgan');

SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'INTERVAL_PART';

TABLE_NAME      PARTITION_NAME  TABLESPACE_NAME     HIGH_VALUE
--------------- --------------- ------------------- -----------
INTERVAL_PART   P1              UWDATA              101


INSERT INTO interval_part
(person_id, first_name, last_name)
VALUES
(101, 'Heli', 'Helskyaho');

SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'INTERVAL_PART';

INSERT INTO interval_part
(person_id, first_name, last_name)
VALUES
(567, 'Tara', 'Havemeyer');

COMMIT;

SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'INTERVAL_PART';

PARTITION_NAME  TABLESPACE_NAME     HIGH_VALUE
--------------- ------------------- -----------
P1              UWDATA              101
SYS_P506        PART1               201
SYS_P507        PART1               601
Interval-Date Range Partitioned Table

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

PARTITION_NAME  TBSP_NAME   HIGH_VALUE
--------------- ----------- -----------------------------------------------------------
P1              SYSTEM      TO_DATE(' 2020-12-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P508        PART4       TO_DATE(' 2021-01-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 
SYS_P509        PART4       TO_DATE(' 2021-07-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P510        UWDATA      TO_DATE(' 2021-11-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P511        PART2       TO_DATE(' 2022-03-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


conn sys@pdbdev as sysdba

SELECT * FROM sys.insert_tsn_list$;

    BO#  POSITION#  TS#
------- ---------- ----
  76528          1    9
  76528          2   11
  76528          3    7
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';

PARTITION_NAME  HIGH_VALUE
--------------- -----------------------------------------------------------------
P2000           TO_DATE(' 2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P3695       TO_DATE(' 2053-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
...
SYS_P3924       TO_DATE(' 2142-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P3925       TO_DATE(' 2142-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


266 rows selected.
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);

COMMIT;

exec dbms_stats.gather_table_stats(USER,'HOURLY_INTERVAL', CASCADE=>TRUE);

col table_name format a11
col partition_name format a9
col compress_for format a8

SELECT table_name, partition_name, tablespace_name, compression, compress_for
FROM user_tab_partitions
WHERE table_name = 'HOURLY_INTERVAL';

TABLE_NAME       PARTITION  TABLESPACE_NAME  COMPRESS COMPRESS_FOR
---------------- ---------- ---------------- -------- ------------
HOURLY_INTERVAL  P1         SYSTEM           ENABLED ADVANCED
HOURLY_INTERVAL  SYS_P3926  PART3            ENABLED ADVANCED
HOURLY_INTERVAL  SYS_P3927  PART1            ENABLED ADVANCED
HOURLY_INTERVAL  SYS_P3928  PART3            ENABLED ADVANCED
List Partitioned Table CREATE TABLE list_part(
deptno          NUMBER(10),
deptname        VARCHAR2(20),
quarterly_sales NUMBER(10,2),
state           VARCHAR2(2))
PARTITION BY LIST (state) (
PARTITION q1_northwest VALUES ('OR', 'WA') TABLESPACE part1,
PARTITION q1_southwest VALUES ('AZ', 'CA', 'NM') TABLESPACE part2,
PARTITION q1_northeast VALUES ('NY', 'VT', 'NJ') TABLESPACE part3,
PARTITION q1_southeast VALUES ('FL', 'GA') TABLESPACE part4,
PARTITION q1_northcent VALUES ('MN', 'WI') TABLESPACE part1,
PARTITION q1_southcent VALUES ('OK', 'TX') TABLESPACE part2);

SELECT table_name, tablespace_name, partitioned
FROM user_tables;

TABLE_NAME           TABLESPACE_NAME                PAR
-------------------- ------------------------------ ---
LIST_PART                                           YES


SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'LIST_PART';

PARTITION_NAME TABLESPACE_NAME    HIGH_VALUE
--------------- ----------------- ---------------
Q1_NORTHCENT    PART1             'MN', 'WI'
Q1_NORTHEAST    PART3             'NY', 'VT', 'NJ'
Q1_NORTHWEST    PART1             'OR', 'WA'
Q1_SOUTHCENT    PART2             'OK', 'TX'
Q1_SOUTHEAST    PART4             'FL', 'GA'
Q1_SOUTHWEST    PART2             'AZ', 'CA', 'NM'


INSERT INTO list_part VALUES (10, 'A', 1000, 'OR');
INSERT INTO list_part VALUES (20, 'B', 1000, 'AZ');
INSERT INTO list_part VALUES (10, 'A', 1000, 'WA');
INSERT INTO list_part VALUES (20, 'B', 1000, 'WA');
INSERT INTO list_part VALUES (10, 'A', 1000, 'AZ');
INSERT INTO list_part VALUES (20, 'B', 1000, 'CA');
COMMIT;

SELECT * FROM list_part;

 DEPTNO DEPTNAME  QUARTERLY_SALES ST
------- --------- --------------- --
     10 A                    1000 OR
     10 A                    1000 WA
     20 B                    1000 WA
     20 B                    1000 AZ
     10 A                    1000 AZ
     20 B                    1000 CA


SELECT * FROM list_part PARTITION(q1_northwest);

 DEPTNO DEPTNAME  QUARTERLY_SALES ST
------- --------- --------------- --

     10 A                    1000 OR
     10 A                    1000 WA
     20 B                    1000 WA
Range Partitioned Table - By Date 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;

PART_NAME TBSP_NAME HIGH_VALUE
--------- --------- --------------------------------------------------------------------------------
YRMIN PART1 TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

YR13 PART2 TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

YR14 PART3 TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

YRMAX PART4 MAXVALUE


INSERT INTO range_part VALUES (1, 1, 1, SYSDATE-720);
INSERT INTO range_part VALUES (2, 2, 2, SYSDATE);
INSERT INTO range_part VALUES (3, 3, 3, SYSDATE+180);
INSERT INTO range_part VALUES (4, 4, 4, SYSDATE+720);
COMMIT;

SELECT * FROM range_part;

PROF_HISTORY_ID PERSON_ID ORGANIZATION_ID RECORD_DATE
--------------- ---------- --------------- --------------------
              1          1               1 29-JAN-2020 19:28:26
              2          2               2 18-JAN-2022 19:28:26
              3          3               3 17-JUL-2022 19:28:26
              4          4               4 08-JAN-2024 19:28:26


SELECT * FROM range_part PARTITION(yrmin);

PROF_HISTORY_ID PERSON_ID ORGANIZATION_ID RECORD_DATE
--------------- ---------- --------------- --------------------
              1          1               1 29-JAN-2020 19:28:26


SELECT * FROM range_part PARTITION(yr21);

no rows selected

SELECT * FROM range_part PARTITION(yr22);

PROF_HISTORY_ID PERSON_ID ORGANIZATION_ID RECORD_DATE
--------------- ---------- --------------- --------------------
              2          2               2 18-JAN-2022 19:28:26
              3          3               3 17-JUL-2022 19:28:26


SELECT * FROM range_part PARTITION(yrmax);

PROF_HISTORY_ID PERSON_ID ORGANIZATION_ID RECORD_DATE
--------------- ---------- --------------- --------------------
              4          4               4 08-JAN-2024 19:28:26


conn sys@pdbdev as sysdba

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)

*** 2022-01-18T20:00:57.099166-06:00 (PDBDEV(4))
*** SESSION ID:(387.64076) 2022-01-18T20:00:57.099166-06:00
*** CLIENT ID:() 2022-01-18T20:00:57.099166-06:00
*** SERVICE NAME:(pdbdev) 2022-01-18T20:00:57.099166-06:00
*** MODULE NAME:(sqlplus.exe) 2022-01-18T20:00:57.099166-06:00
*** ACTION NAME:() 2022-01-18T20:00:57.099166-06:00
*** CLIENT DRIVER:(SQL*PLUS) 2022-01-18T20:00:57.099166-06:00
*** CONTAINER ID:(4) 2022-01-18T20:00:57.099166-06:00

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

PARTITION_NAME  TABLESPACE_NAME  HIGH_VALUE
--------------- ---------------- --------------
STUDENT_AE      PART1            'F%'
STUDENT_FL      PART2            'M%'
STUDENT_MR      PART3            'S%'
STUDENT_SZ      PART4            MAXVALUE
Referential Partitioned Table SELECT MIN(num_rows), MAX(num_rows)
FROM all_tables
WHERE num_rows IS NOT NULL;

MIN(NUM_ROWS) MAX(NUM_ROWS)
------------- -------------
            0        427460


CREATE TABLE ref_parent (
table_name   VARCHAR2(30),
order_date   DATE,
num_rows     NUMBER)
PARTITION BY RANGE(num_rows) (
PARTITION num_rows1 VALUES LESS THAN (100) TABLESPACE part1,
PARTITION num_rows2 VALUES LESS THAN (1000) TABLESPACE part2,
PARTITION num_rows3 VALUES LESS THAN (10000) TABLESPACE part3,
PARTITION num_rows4 VALUES LESS THAN (MAXVALUE) TABLESPACE part4);

ALTER TABLE ref_parent
ADD CONSTRAINT pk_ref_parent
PRIMARY KEY (table_name)
USING INDEX;

desc ref_parent

SELECT table_name, tablespace_name, partitioned
FROM user_tables
WHERE table_name = 'REF_PARENT';

TABLE_NAME  TABLESPACE_NAME                PAR
----------- ------------------------------ ---
REF_PARENT                                 YES


SELECT partition_name, tablespace_name
FROM user_tab_partitions
WHERE table_name = 'REF_PARENT';

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
NUM_ROWS1                      PART1
NUM_ROWS2                      PART2
NUM_ROWS3                      PART3
NUM_ROWS4                      PART4


CREATE TABLE ref_child (
table_name VARCHAR2(30) NOT NULL,
index_name VARCHAR2(30) NOT NULL,
CONSTRAINT fk_ref_child_parent
FOREIGN KEY(table_name) REFERENCES ref_parent(table_name))
PARTITION BY REFERENCE(fk_ref_child_parent);

col ref_ptn_constraint_name format a30

SELECT table_name, partitioning_type, ref_ptn_constraint_name
FROM user_part_tables
WHERE table_name LIKE 'REF%';

TABLE_NAME                     PARTITION REF_PTN_CONSTRAINT_NAME
------------------------------ --------- ------------------------------
REF_CHILD                      REFERENCE FK_REF_CHILD_PARENT
REF_PARENT                     RANGE
Partition by System CREATE TABLE syst_part (
tx_id   NUMBER(5),
begdate DATE)
PARTITION BY SYSTEM (
PARTITION p1 TABLESPACE part1,
PARTITION p2 TABLESPACE part2,
PARTITION p3 TABLESPACE part3);

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;

SELECT * FROM syst_part PARTITION(p2);

     TX_ID BEGDATE
---------- --------------------
         2 18-JAN-2022 20:42:53
Partition by Virtual Column: JSON in 21c 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


INSERT INTO json_orders
(tx_id, tx_date, jsondata)
VALUES
(1, SYSDATE, '{"Seattle": 1, "siteId": 9}');

INSERT INTO json_orders
(tx_id, tx_date, jsondata)
VALUES
(2, SYSDATE, '{"New York": 2, "siteId": 11}');

COMMIT;

col jsondata format a30

SELECT * FROM json_orders;

 TX_ID TX_DATE              JSONDATA                        SITE_ID
------ -------------------- ------------------------------ --------
     1 19-JAN-2022 20:13:48 {"Seattle":1,"siteId":9}              9
     2 19-JAN-2022 20:13:48 {"New York":2,"siteId":11}           11


SELECT * FROM json_orders PARTITION(p1);

 TX_ID TX_DATE              JSONDATA                        SITE_ID
------ -------------------- ------------------------------ --------
     1 19-JAN-2022 20:13:48 {"Seattle":1,"siteId":9}              9

SELECT * FROM json_orders PARTITION(p2);

 TX_ID TX_DATE              JSONDATA                        SITE_ID
------ -------------------- ------------------------------ --------
     2 19-JAN-2022 20:13:48 {"New York":2,"siteId":11}           11
Partition by Virtual Column: JSON prior to 21c 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';

INSERT INTO json_orders
(tx_id, tx_date, jsondata)
VALUES
(1, SYSDATE, '{"Seattle": 1, "siteId": 9}');

INSERT INTO json_orders
(tx_id, tx_date, jsondata)
VALUES
(2, SYSDATE, '{"New York": 2, "siteId": 11}');

COMMIT;

col jsondata format a30

SELECT * FROM json_orders;

SELECT * FROM json_orders PARTITION(p1);

SELECT * FROM json_orders PARTITION(p2);
Partition by Virtual Column: XML 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';

PARTITION_NAME  TABLESPACE_NAME   HIGH_VALUE
--------------- ----------------- --------------------
P1                                 PART3 11


desc user_tab_cols

SELECT column_name, virtual_column, data_default
FROM user_tab_cols
WHERE table_name = 'VCOL_PART';

COLUMN_NAME                    VIR DATA_DEFAULT
------------------------------ --- ----------------------
TX_ID                          NO
BEGDATE                        NO
ENDDATE                        NO
STAYLEN                        YES "ENDDATE"-"BEGDATE"


INSERT INTO vcol_part
(tx_id, begdate, enddate)
VALUES
(1, SYSDATE-5, SYSDATE);

INSERT INTO vcol_part
(tx_id, begdate, enddate)
VALUES
(2, SYSDATE-10, SYSDATE);

INSERT INTO vcol_part
(tx_id, begdate, enddate)
VALUES
(3, SYSDATE-15, SYSDATE);

INSERT INTO vcol_part
(tx_id, begdate, enddate)
VALUES
(4, SYSDATE-25, SYSDATE);

COMMIT;

SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'VCOL_PART';

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------
P1 PART3 11
SYS_P3933 UWDATA 21
SYS_P3934 UWDATA 31


EXPLAIN PLAN FOR
SELECT *
FROM vcol_part;

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------------------------------------------
| Id | Operation           | Name      | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop  |
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |           |    4 |   176 |    25   (0)| 00:00:01 |       |        |
|  1 |  PARTITION RANGE ALL|           |    4 |   176 |    25   (0)| 00:00:01 |     1 | 1048575|
|  2 |   TABLE ACCESS FULL | VCOL_PART |    4 |   176 |    25   (0)| 00:00:01 |     1 | 1048575|
------------------------------------------------------------------------------------------------


EXPLAIN PLAN FOR
SELECT *
FROM vcol_part
WHERE staylen < 11;

SELECT * FROM TABLE(dbms_xplan.display);

-------------------------------------------------------------------------------------------------
| Id | Operation              | Name      | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop|
-------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |           |    2 |    88 |    9    (0)| 00:00:01 |       |      |
|  1 |  PARTITION RANGE SINGLE|           |    2 |    88 |    9    (0)| 00:00:01 |     1 |    1 |
|* 2 |   TABLE ACCESS FULL    | VCOL_PART |    2 |    88 |    9    (0)| 00:00:01 |     1 |    1 |
-------------------------------------------------------------------------------------------------


EXPLAIN PLAN FOR
SELECT *
FROM vcol_part
WHERE staylen BETWEEN 11 AND 20;

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------------------------------
| Id | Operation              | Name      | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |           |    1 |    44 |    9    (0)| 00:00:01 |       |       |
|  1 |  PARTITION RANGE SINGLE|           |    1 |    44 |    9    (0)| 00:00:01 |     2 |     2 |
|* 2 |   TABLE ACCESS FULL    | VCOL_PART |    1 |    44 |    9    (0)| 00:00:01 |     2 |     2 |
--------------------------------------------------------------------------------------------------


EXPLAIN PLAN FOR
SELECT *
FROM vcol_part
WHERE staylen >= 11;

SELECT * FROM TABLE(dbms_xplan.display);

---------------------------------------------------------------------------------------------------
| Id | Operation                | Name      | Rows| Bytes| Cost (%CPU)| Time     | Pstart|  Pstop |
---------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT         |           |    2|    88|    17   (0)| 00:00:01 |       |        |
|  1 |  PARTITION RANGE ITERATOR|           |    2|    88|    17   (0)| 00:00:01 |     2 | 1048575|
|* 2 |  TABLE ACCESS FULL       | VCOL_PART |    2|    88|    17   (0)| 00:00:01 |     2 | 1048575|
--------------------------------------------------------------------------------------------------
Nested Table Partition <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
Virtual Column Partitioning with XML Binary storage 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';

PARTITION_NAME  HIGH_VALUE
--------------- --------------
P1              10
P2              20
PM              MAXVALUE


DECLARE
 x XMLTYPE;
BEGIN
  x := XMLTYPE('<?xml version="1.0" encoding="utf-8"?>
         <Order orderId="1" orderRevision="1" orderTimeStamp="01-JAN-2022">
           <OrderHeader>
             <AlternateIds>
               <AlternateId altIdType="SiteId">2</AlternateId>
               <AlternateId altIdType="MerchantOrderNumber">Merch</AlternateId>
               <AlternateId altIdType="MarketplaceOrderNumber">Place</AlternateId>
               <AlternateId altIdType="CustomerReferenceId">Ref</AlternateId>
               <AlternateId altIdType="CartId">Cart</AlternateId>
               <AlternateId altIdType="SessionId">1</AlternateId>
             </AlternateIds>
           </OrderHeader>
         </Order>');
  INSERT INTO orders VALUES (x);

  x := XMLTYPE('<?xml version="1.0" encoding="utf-8"?>
         <Order orderId="1" orderRevision="1" orderTimeStamp="01-JAN-2022">
           <OrderHeader>
             <AlternateIds>
               <AlternateId altIdType="SiteId">245</AlternateId>
               <AlternateId altIdType="MerchantOrderNumber">Merch</AlternateId>
               <AlternateId altIdType="MarketplaceOrderNumber">Place</AlternateId>
               <AlternateId altIdType="CustomerReferenceId">Ref</AlternateId>
               <AlternateId altIdType="CartId">Cart</AlternateId>
               <AlternateId altIdType="SessionId">2</AlternateId>
             </AlternateIds>
           </OrderHeader>
         </Order>');
  INSERT INTO orders VALUES (x);
END;
/

SELECT * FROM orders PARTITION(P1);
SELECT * FROM orders PARTITION(P2);
SELECT * FROM orders PARTITION(PM);

SYS_NC_ROWINFO$
----------------------------------------------------------------------------
<?xml version="1.0" encoding="WINDOWS-1252"?>
  <Order orderId="1" orderRevision="1" orderTimeStamp="01-JAN-2022">
    <OrderHeader>
      <AlternateIds>
        <AlternateId altIdType="SiteOrderNumber">123</AlternateId>
        <AlternateId altIdType="MerchantOrderNumber">Merch</AlternateId>
        <AlternateId altIdType="MarketplaceOrderNumber">Place</AlternateId>
        <AlternateId altIdType="CustomerReferenceId">Ref</AlternateId>
        <AlternateId altIdType="CartId">Cart</AlternateId>
        <AlternateId altIdType="SessionId">123</AlternateId>
      </AlternateIds>
    </OrderHeader>
  </Order>
Partitioning on Exadata Hybrid Columnar Compression ALTER TABLE <table_name>
MODIFY PARTITION <partition_name>
COMPRESS FOR <compression_level>
STORAGE (cell_flash_cache <KEEP | NONE>);
ALTER TABLE t
MODIFY PARTITION q12022
COMPRESS FOR QUERY HIGH
STORAGE (cell_flash_cache KEEP);
 
Composite Partitions
Composite Partitioned Table - By Range And Hash conn uwclass/uwclass@pdbdev

CREATE TABLE composite_rng_hash (
cust_id     NUMBER(10),
cust_name   VARCHAR2(25),
amount_sold NUMBER(10,2),
time_id     DATE)
PARTITION BY RANGE(time_id)
SUBPARTITION BY HASH(cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE part1,
SUBPARTITION sp2 TABLESPACE part2,
SUBPARTITION sp3 TABLESPACE part3,
SUBPARTITION sp4 TABLESPACE part4) (
PARTITION sales_pre19
VALUES LESS THAN (TO_DATE('01/01/2019','DD/MM/YYYY')),
PARTITION sales_2019
VALUES LESS THAN(TO_DATE('01/01/2020','DD/MM/YYYY')),
PARTITION sales_2020
VALUES LESS THAN(TO_DATE('01/01/2021','DD/MM/YYYY')),
PARTITION sales_2021
VALUES LESS THAN(TO_DATE('01/01/2022','DD/MM/YYYY')),
PARTITION sales_2022
VALUES LESS THAN(TO_DATE('01/01/2023','DD/MM/YYYY')),
PARTITION future_sales
VALUES LESS THAN(MAXVALUE));

set linesize 121
col table_name format a20

SELECT table_name, partitioned, secondary
FROM user_tables
WHERE table_name = 'COMPOSITE_RNG_HASH';

TABLE_NAME                     PAR S
------------------------------ --- -
COMPOSITE_RNG_HASH             YES N


desc user_tab_partitions

col partition_name format a15
col spc format 99999
col high_value format a50

SELECT partition_name, composite, subpartition_count SPC, high_value
FROM user_tab_partitions
WHERE table_name = 'COMPOSITE_RNG_HASH';

PARTITION_NAME COM SPC HIGH_VALUE
-------------- --- --- --------------------------------------------------
FUTURE_SALES   YES   4 MAXVALUE
SALES_2019     YES 4 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_2020     YES 4 TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_2021     YES 4 TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_2022     YES 4 TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_PRE19    YES 4 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


desc user_tab_subpartitions

col subpartition_name format a20

SELECT partition_name, subpartition_name, subpartition_position
FROM user_tab_subpartitions
WHERE table_name = 'COMPOSITE_RNG_HASH';

PARTITION_NAME  SUBPARTITION_NAME  SUBPARTITION_POSITION
--------------- ------------------ ---------------------
FUTURE_SALES    FUTURE_SALES_SP1                       1
FUTURE_SALES    FUTURE_SALES_SP2                       2
FUTURE_SALES    FUTURE_SALES_SP3                       3
FUTURE_SALES    FUTURE_SALES_SP4                       4
SALES_2019      SALES_2019_SP1                         1
SALES_2019      SALES_2019_SP2                         2
SALES_2019      SALES_2019_SP3                         3
SALES_2019      SALES_2019_SP4                         4
SALES_2020      SALES_2020_SP1                         1
SALES_2020      SALES_2020_SP2                         2
SALES_2020      SALES_2020_SP3                         3
SALES_2020      SALES_2020_SP4                         4
SALES_2021      SALES_2021_SP1                         1
SALES_2021      SALES_2021_SP2                         2
SALES_2021      SALES_2021_SP3                         3
SALES_2021      SALES_2021_SP4                         4
SALES_2022      SALES_2022_SP1                         1
SALES_2022      SALES_2022_SP2                         2
SALES_2022      SALES_2022_SP3                         3
SALES_2022      SALES_2022_SP4                         4
SALES_PRE19     SALES_PRE19_SP1                        1
SALES_PRE19     SALES_PRE19_SP2                        2
SALES_PRE19     SALES_PRE19_SP3                        3
SALES_PRE19     SALES_PRE19_SP4                        4


desc user_subpartition_templates

col high_bound format a20

SELECT subpartition_name, tablespace_name, high_bound
FROM user_subpartition_templates
WHERE table_name = 'COMPOSITE_RNG_HASH';

SUBPARTITION_NAME TABLESPACE_NAME  HIGH_BOUND
----------------- ---------------- --------------------
SP1               PART1
SP2               PART2
SP3               PART3
SP4               PART4


CREATE DATABASE LINK pdbdev
CONNECT TO sh IDENTIFIED BY sh
USING 'PDBDEV';

INSERT INTO composite_rng_hash
SELECT c.cust_id, c.cust_first_name || ' ' || c.cust_last_name, s.amount_sold, s.time_id+5000
FROM sh.sales@orcl s, sh.customers@orcl c
WHERE s.cust_id = c.cust_id
AND rownum < 250001;

exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_PRE19');
exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_2019');
exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_2020');
exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_2021');
exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_2022');
exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_FUTURE');

SELECT table_name, partition_name, num_rows
FROM user_tab_partitions
WHERE table_name = 'COMPOSITE_RNG_HASH';

SELECT table_name, partition_name, subpartition_name, num_rows
FROM user_tab_subpartitions
WHERE table_name = 'COMPOSITE_RNG_HASH';

exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', GRANULARITY=>'ALL');

SELECT table_name, partition_name, subpartition_name, num_rows
FROM user_tab_subpartitions
WHERE table_name = 'COMPOSITE_RNG_HASH';

set long 1000000

SELECT dbms_metadata.get_ddl('TABLE', 'COMPOSITE_RNG_HASH')
FROM dual;
Composite Partitioned Table - By Range And List CREATE TABLE composite_rng_list (
cust_id    NUMBER(10),
cust_name  VARCHAR2(25),
cust_state VARCHAR2(2),
time_id    DATE)
PARTITION BY RANGE(time_id)
SUBPARTITION BY LIST (cust_state)
SUBPARTITION TEMPLATE(
SUBPARTITION west VALUES ('OR', 'WA') TABLESPACE part1,
SUBPARTITION east VALUES ('NY', 'CT') TABLESPACE part2,
SUBPARTITION cent VALUES ('OK', 'TX') TABLESPACE part3) (
PARTITION
per1 VALUES LESS THAN (TO_DATE('01/01/2010','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/2030','DD/MM/YYYY')),
PARTITION future VALUES LESS THAN(MAXVALUE));

desc composite_rng_list

SELECT partition_name, composite, high_value
FROM user_tab_partitions
WHERE table_name = 'COMPOSITE_RNG_LIST';

PARTITION_NAME  COM HIGH_VALUE
--------------- --- ----------------------------------------------------------------------------------
FUTURE          YES MAXVALUE
PER1            YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PER2            YES TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PER3            YES TO_DATE(' 2030-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


SELECT partition_name, subpartition_name, num_rows
FROM user_tab_subpartitions
WHERE table_name = 'COMPOSITE_RNG_LIST'
ORDER BY 1,3,2;

PARTITION_NAME  SUBPARTITION_NAME    NUM_ROWS
--------------- -------------------- ----------
FUTURE          FUTURE_CENT
FUTURE          FUTURE_EAST
FUTURE          FUTURE_WEST
PER1            PER1_CENT
PER1            PER1_EAST
PER1            PER1_WEST
PER2            PER2_CENT
PER2            PER2_EAST
PER2            PER2_WEST
PER3            PER3_CENT
PER3            PER3_EAST
PER3            PER3_WEST
Composite Partitioned Table - By Range And Range 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';

PARTITION_NAME  COM HIGH_VALUE
--------------- --- --------------------------------------------------
FUTURE          YES MAXVALUE
PER1            YES TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PER2            YES TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PER3            YES TO_DATE(' 2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


SELECT partition_name, subpartition_name, num_rows
FROM user_tab_subpartitions
WHERE table_name = 'COMPOSITE_RNG_RNG'
ORDER BY 1,2;

PARTITION_NAME  SUBPARTITION_NAME  NUM_ROWS
--------------- ------------------ ----------
FUTURE          FUTURE_ACQUIRED
FUTURE          FUTURE_ORIGINAL
FUTURE          FUTURE_RECENT
PER1            PER1_ACQUIRED
PER1            PER1_ORIGINAL
PER1            PER1_RECENT
PER2            PER2_ACQUIRED
PER2            PER2_ORIGINAL
PER2            PER2_RECENT
PER3            PER3_ACQUIRED
PER3            PER3_ORIGINAL
PER3            PER3_RECENT
Composite Partitioned Table - By List And Hash CREATE TABLE composite_list_hash (
cust_id     NUMBER(10),
cust_name   VARCHAR2(25),
cust_state  VARCHAR2(2),
time_id     DATE)
PARTITION BY LIST(cust_state)
SUBPARTITION BY HASH (cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE part1,
SUBPARTITION sp2 TABLESPACE part2,
SUBPARTITION sp3 TABLESPACE part3,
SUBPARTITION sp4 TABLESPACE part4) (
PARTITION west VALUES ('OR', 'WA'),
PARTITION east VALUES ('NY', 'CT'),
PARTITION cent VALUES ('IL', 'MN'));
Composite Partitioned Table - By List And List CREATE TABLE composite_list_list (
cust_id     NUMBER(10),
cust_name   VARCHAR2(25),
cust_state  VARCHAR2(2),
time_id     DATE)
PARTITION BY LIST(cust_state)
SUBPARTITION BY LIST (cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION beg VALUES (1,3,5) TABLESPACE part1,
SUBPARTITION mid VALUES (2,4,6) TABLESPACE part2,
SUBPARTITION end VALUES (7,8,9,0) TABLESPACE part3) (
PARTITION west VALUES ('OR', 'WA'),
PARTITION east VALUES ('NY', 'CT'),
PARTITION cent VALUES ('IL', 'MN'));
Composite Partitioned Table - By List And Range CREATE TABLE composite_list_rng (
cust_id     NUMBER(10),
cust_name   VARCHAR2(25),
cust_state  VARCHAR2(2),
time_id     DATE)
PARTITION BY LIST(cust_state)
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 west VALUES ('OR', 'WA'),
PARTITION east VALUES ('NY', 'CT'),
PARTITION cent VALUES ('IL', 'MN'));
Composite Interval Partition CREATE TABLE t(
sequence_id      NUMBER,
reservation_date DATE,
location_code    VARCHAR2(5))
PARTITION BY RANGE (reservation_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
SUBPARTITION BY LIST(location_code)
SUBPARTITION TEMPLATE (
SUBPARTITION spart01 VALUES ('USA'),
SUBPARTITION spart02 VALUES ('IND'),
SUBPARTITION spart03 VALUES ('GER')) (
PARTITION root VALUES LESS THAN (TO_DATE('01-JAN-2015','DD-MON-YYYY')));
 
Compressed Partitions
Partition Level Compression CREATE TABLE <table_name> (
<column_definitions>) COMPRESS
<partitioning_clauses> [NOCOMPRESS];
CREATE TABLE sales (
saleskey    NUMBER,
quarter     NUMBER,
product     NUMBER,
salesperson NUMBER,
amount      NUMBER(12,2),
region      VARCHAR2(10)) COMPRESS
PARTITION BY LIST (region) (
PARTITION northwest VALUES ('NORTHWEST'),
PARTITION southwest VALUES ('SOUTHWEST'),
PARTITION northeast VALUES ('NORTHEAST') NOCOMPRESS,
PARTITION southeast VALUES ('SOUTHEAST'),
PARTITION western VALUES ('WESTERN'));

SELECT tablespace_name, partitioned, compression
FROM user_tables
WHERE table_name = 'SALES';

TABLESPACE_NAME                PAR COMPRESS
------------------------------ --- --------
                               YES


SELECT partition_name, tablespace_name, high_value, compression
FROM user_tab_partitions
WHERE table_name = 'SALES'
ORDER BY partition_name;

PARTITION_NAME  TABLESPACE_NAME  HIGH_VALUE  COMPRESS
--------------- ---------------- ----------- --------
NORTHEAST       PART1            'NORTHEAST' DISABLED
NORTHWEST       PART1            'NORTHWEST' ENABLED
SOUTHEAST       PART1            'SOUTHEAST' ENABLED
SOUTHWEST       PART1            'SOUTHWEST' ENABLED
WESTERN         PART1            'WESTERN'   ENABLED
 
Alter Table For Partitions
Moving Non-Composite Partitions ALTER TABLE <table_name>
MOVE PARTITION <partition_name>
TABLESPACE <tablespace_name>;
SELECT table_name, partition_name, tablespace_name
FROM user_tab_partitions;

ALTER TABLE hash_part
MOVE PARTITION sys_p26
TABLESPACE uwdata;

ALTER TABLE list_part
MOVE PARTITION q1_southcent
TABLESPACE uwdata NOLOGGING;

ALTER TABLE range_part
MOVE PARTITION yr0
TABLESPACE uwdata;

ALTER TABLE composite_rng_hash
MOVE PARTITION sales_pre98
TABLESPACE uwdata;

SELECT table_name, partition_name, tablespace_name
FROM user_tab_partitions
WHERE table_name LIKE '%PART%';
Moving Partition and Rebuild Global Index ALTER TABLE <table_name>
MOVE SUBPARTITION <subpartition_name>
TABLESPACE <tablespace_name>
UPDATE INDEXES [(<index_name>(<partition_name>));
ALTER TABLE composite_rng_hash
MOVE PARTITION sales_pre11
TABLESPACE uwdata
UPDATE INDEXES;
Moving Subpartitions ALTER TABLE <table_name>
MOVE SUBPARTITION <subpartition_name>
TABLESPACE <tablespace_name>;
SELECT partition_name, subpartition_name, tablespace_name
FROM user_tab_subpartitions
WHERE TABLE_NAME = 'COMPOSITE_RNG_HASH';

ALTER TABLE composite_rng_hash
MOVE SUBPARTITION sales_pre11_sp1
TABLESPACE uwdata
PARALLEL (DEGREE 2);

SELECT partition_name, subpartition_name, tablespace_name
FROM user_tab_subpartitions
WHERE TABLE_NAME = 'COMPOSITE_RNG_HASH';
Setting a new default tablespace ALTER TABLE <table_owner>.<table_name>
MODIFY DEFAULT ATTRIBUTES TABLESPACE <tablespace_name>;
SELECT DISTINCT 'ALTER TABLE ' || table_owner || '.' || table_name ||
' MODIFY DEFAULT ATTRIBUTES TABLESPACE newtbs;'
FROM user_tab_partitions;
Merging List Subpartitions ALTER TABLE <table_name>
MERGE SUBPARTITIONS <subpartition_name>
INTO SUBPARTITION <subpartition_name
TABLESPACE <tablespace_name>;
ALTER TABLE composite_rng_hash
MERGE SUBPARTITIONS sales_pre11_sp1, sales_pre11_sp2
INTO SUBPARTITION sales_pre11_sp12
TABLESPACE part1;

CREATE TABLE range_list (
cust_id     NUMBER(10),
channel_id  NUMBER(3),
amount_sold NUMBER(10,2),
time_id     DATE)
PARTITION BY RANGE(time_id)
SUBPARTITION BY LIST(channel_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 VALUES (2, 3) TABLESPACE part1,
SUBPARTITION sp2 VALUES (4, 5) TABLESPACE part2,
SUBPARTITION sp3 VALUES (6, 7) TABLESPACE part3,
SUBPARTITION sp4 VALUES (8, 9) TABLESPACE part4)
(PARTITION sp10
VALUES LESS THAN(TO_DATE('01/01/2019','DD/MM/YYYY')),
PARTITION s11
VALUES LESS THAN(TO_DATE('01/01/2020','DD/MM/YYYY')),
PARTITION s12
VALUES LESS THAN(TO_DATE('01/01/2021','DD/MM/YYYY')),
PARTITION s13
VALUES LESS THAN(TO_DATE('01/01/2022','DD/MM/YYYY')),
PARTITION s14
VALUES LESS THAN(TO_DATE('01/01/2023','DD/MM/YYYY')),
PARTITION pm
VALUES LESS THAN(MAXVALUE));

col high_value format a20

SELECT partition_name, subpartition_name, tablespace_name, high_value
FROM user_tab_subpartitions
WHERE table_name = 'RANGE_LIST';

ALTER TABLE range_list
MERGE SUBPARTITIONS sp10_sp1, sp10_sp2 INTO SUBPARTITION spmin
PARALLEL (DEGREE 2)
TABLESPACE part1;

SELECT partition_name, subpartition_name, tablespace_name, high_value
FROM user_tab_subpartitions
WHERE table_name = 'RANGE_LIST';
Modify A Subpartition Template ALTER TABLE <table_name>
SET SUBPARTITION TEMPLATE (
SUBPARTITION <subpartition_name>, TABLESPACE <tablespace_name>,
SUBPARTITION <subpartition_name>, TABLESPACE <tablespace_name>);
SELECT partition_name, subpartition_name, tablespace_name
FROM user_tab_subpartitions
WHERE table_name = 'RANGE_LIST';

ALTER TABLE range_list
SET SUBPARTITION TEMPLATE (
SUBPARTITION sp1 VALUES (2, 3) TABLESPACE part1,
SUBPARTITION sp2 VALUES (4, 5) TABLESPACE part2,
SUBPARTITION sp3 VALUES (6, 7) TABLESPACE part3,
SUBPARTITION sp4 VALUES (8, 9) TABLESPACE part4,
SUBPARTITION sp5 VALUES (0, 1) TABLESPACE uwdata);

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

CREATE TABLE new_part (
rid  NUMBER,
col1 VARCHAR2(10),
col2 VARCHAR2(100));

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

PNAME      HIGH_VALUE
---------- ------------------------------------------------------------------------------------
P1         TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


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

PNAME      HIGH_VALUE
---------- ------------------------------------------------------------------------------
P1         TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P2614  TO_DATE(' 2022-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


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

DROP INDEX gi_range_part_person_id;
Local Index Creation and Partition Pruning Demo 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;
 
Demos
Partition Elimination conn scott/tiger@pdbdev

-- 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);
Maximize Storage by Utilizing Zero Size Unusable Indexes 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';

Related Topics
Built-in Functions
Built-in Packages
Clusters Range Partitioned
DATAOBJ_TO_PARTITION
DBMS_PCLXUTIL
DBMS_STATS
Hybrid Columnar Compression
Indexes
Secure Files
TBL$OR$IDX$PART$NUM
Tables
Truncate Partition
Update Statements
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2021 Daniel A. Morgan All Rights Reserved
  DBSecWorx