8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Hybrid Partitioned Table Enhancements in Oracle Database 23c
Hybrid partitioned tables were introduced in Oracle 19c, allowing us to create partitioned tables with a mix of internal and external partitions. In 19c it was not possible to combine this with interval or auto-list partitioning, but that has changed in Oracle database 23c. This article demonstrates hybrid partitioned tables with interval and auto-list partitioning in Oracle Database 23c.
- Setup
- Hybrid Partitioned Table With Interval Partitioning
- Hybrid Partitioned Table With Auto-List Partitioning
Related articles.
- Hybrid Partitioned Tables in Oracle Database 19c
- Partitioning : All Articles
- External Tables : All Articles
Setup
Connect to a privileged user and create a new test user called TESTUSER1.
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba drop user if exists testuser1 cascade; create user testuser1 identified by testuser1 quota unlimited on users; grant db_developer_role to testuser1;
Create a directory object, making sure TESTUSER1 has READ, WRITE and EXECUTE permissions on them.
create or replace directory tmp_dir1 as '/tmp/'; grant read, write, execute on directory tmp_dir1 to testuser1;
All other actions will be performed from TESTUSER1.
conn testuser1/testuser1@//localhost:1521/freepdb1
Hybrid Partitioned Table With Interval Partitioning
Generate some flat files with dummy data for use with external table partitions.
set markup csv on quote on
set trimspool on linesize 1000 feedback off pagesize 0
spool /tmp/2022-1.txt
select '2022-01-01',
object_id,
owner,
object_name
from all_objects
where object_id <= 3999
and rownum <= 1000;
spool off
spool /tmp/2022-2.txt
select '2022-01-01',
object_id,
owner,
object_name
from all_objects
where object_id between 4000 and 5999
and rownum <= 1000;
spool off
set markup csv off
set trimspool on linesize 1000 feedback on pagesize 14
The following example creates a hybrid partitioned table. It uses interval partitioning, with a single external partition for 2022 data, and a single internal partitions for 2023 data respectively.
drop table if exists test_ol_hybrid_part_tab purge;
create table test_ol_hybrid_part_tab (
created_date date not null,
object_id number not null,
owner varchar2(128) not null,
object_name varchar2(128) not null
)
external partition attributes (
type oracle_loader
default directory tmp_dir1
access parameters (
fields csv with embedded terminated by ',' optionally enclosed by '"'
missing field values are null
(created_date date "YYYY-MM-DD", object_id, owner, object_name)
)
reject limit unlimited
)
partition by range (created_date)
interval (numtoyminterval(1,'YEAR')) (
partition part_2022 values less than (to_date('2023-01-01','YYYY-MM-DD')) external
location ('2022-1.txt', '2022-2.txt'),
partition part_2023 values less than (to_date('2024-01-01','YYYY-MM-DD'))
);
The EXTERNAL PARTITION ATTRIBUTES clause defines the table-level external table parameters. For external partitions, the EXTERNAL clause is used to define partition-level attributes, such as file locations and non-default directory objects.
The HYBRID column in the CDB|DBA|ALL|USER}_TABLES views show this is a hybrid table.
column table_name format a30 column hybrid format a6 select table_name, hybrid from user_tables; TABLE_NAME HYBRID ------------------------------ ------ TEST_OL_HYBRID_PART_TAB YES 1 row selected. SQL>
We only have data in the external partitions at the moment.
column created_date format a12 select to_char(created_date,'YYYY') as created_date, count(*) as amount from test_ol_hybrid_part_tab group by created_date order by created_date; CREATED_DATE AMOUNT ------------ ---------- 2022 2000 1 row selected. SQL>
We can perform DML on the internal partition as normal.
insert into test_ol_hybrid_part_tab
select to_date('2023-01-01','YYYY-MM-DD'),
object_id,
owner,
object_name
from all_objects
where rownum <= 2000;
commit;
column created_date format a12
select to_char(created_date,'YYYY') as created_date, count(*) as amount
from test_ol_hybrid_part_tab
group by created_date
order by created_date;
CREATED_DATE AMOUNT
------------ ----------
2022 2000
2023 2000
2 rows selected.
SQL>
All this behaviour is typical for a hybrid list partitioned table. Because this is an interval hybrid partitioned table, we can also insert records for new years, which create new partitions. First we check the current partitions for the table.
column partition_name format a30 select partition_name from user_tab_partitions where table_name = 'TEST_OL_HYBRID_PART_TAB' order by 1; PARTITION_NAME ------------------------------ PART_2022 PART_2023 2 rows selected. SQL>
Now we insert some data for two new years.
insert into test_ol_hybrid_part_tab
select to_date('2024-01-01','YYYY-MM-DD'),
object_id,
owner,
object_name
from all_objects
where rownum <= 2000;
commit;
insert into test_ol_hybrid_part_tab
select to_date('2025-01-01','YYYY-MM-DD'),
object_id,
owner,
object_name
from all_objects
where rownum <= 2000;
commit;
We can see two new partitions have been created for the new years.
column partition_name format a30 select partition_name from user_tab_partitions where table_name = 'TEST_OL_HYBRID_PART_TAB' order by 1; PARTITION_NAME ------------------------------ PART_2022 PART_2023 SYS_P467 SYS_P468 4 rows selected. SQL>
And of course we can see the data is present when we query the table.
column created_date format a12 select to_char(created_date,'YYYY') as created_date, count(*) as amount from test_ol_hybrid_part_tab group by created_date order by created_date; CREATED_DATE AMOUNT ------------ ---------- 2022 2000 2023 2000 2024 2000 2025 2000 4 rows selected. SQL>
Hybrid Partitioned Table With Auto-List Partitioning
Generate some flat files with dummy data for use with external table partitions.
set markup csv on quote on
set trimspool on linesize 1000 feedback off pagesize 0
spool /tmp/gbr1.txt
select 'GBR',
object_id,
owner,
object_name
from all_objects
where object_id <= 3999
and rownum <= 1000;
spool off
spool /tmp/gbr2.txt
select 'GBR',
object_id,
owner,
object_name
from all_objects
where object_id between 4000 and 5999
and rownum <= 1000;
spool off
spool /tmp/ire1.txt
select 'IRE',
object_id,
owner,
object_name
from all_objects
where object_id <= 3999
and rownum <= 1000;
spool off
spool /tmp/ire2.txt
select 'IRE',
object_id,
owner,
object_name
from all_objects
where object_id between 4000 and 5999
and rownum <= 1000;
spool off
set markup csv off
set trimspool on linesize 1000 feedback on pagesize 14
The following example creates a hybrid partitioned table. It uses auto-list partitioning, with a single internal partition for USA data, and two external partitions for GBR and IRE data respectively.
drop table if exists test_ol_hybrid_part_tab purge;
create table test_ol_hybrid_part_tab (
country_code varchar2(3) not null,
object_id number not null,
owner varchar2(128) not null,
object_name varchar2(128) not null
)
external partition attributes (
type oracle_loader
default directory tmp_dir1
access parameters (
fields csv with embedded terminated by ',' optionally enclosed by '"'
missing field values are null
(country_code, object_id, owner, object_name)
)
reject limit unlimited
)
partition by list (country_code) automatic (
partition usa values ('USA'),
partition gbr values ('GBR') external
location ('gbr1.txt', 'gbr2.txt'),
partition ire values ('IRE') external
location ('ire1.txt', 'ire2.txt')
);
The EXTERNAL PARTITION ATTRIBUTES clause defines the table-level external table parameters. For external partitions, the EXTERNAL clause is used to define partition-level attributes, such as file locations and non-default directory objects.
The HYBRID column in the CDB|DBA|ALL|USER}_TABLES views show this is a hybrid table.
column table_name format a30 column hybrid format a6 select table_name, hybrid from user_tables; TABLE_NAME HYBRID ------------------------------ ------ TEST_OL_HYBRID_PART_TAB YES 1 row selected. SQL>
We only have data in the external partitions at the moment.
column country_code format a12 select country_code, count(*) as amount from test_ol_hybrid_part_tab group by country_code order by country_code; COUNTRY_CODE AMOUNT ------------ ---------- GBR 2000 IRE 2000 2 rows selected. SQL>
We can perform DML on the internal partition as normal.
insert into test_ol_hybrid_part_tab
select 'USA',
object_id,
owner,
object_name
from all_objects
where rownum <= 2000;
commit;
column country_code format a12
select country_code, count(*) as amount
from test_ol_hybrid_part_tab
group by country_code
order by country_code;
COUNTRY_CODE AMOUNT
------------ ----------
GBR 2000
IRE 2000
USA 2000
3 rows selected.
SQL>
All this behaviour is typical for a hybrid list partitioned table. Because this is an auto-list hybrid partitioned table, we can also insert records for new countries, which create new partitions. First we check the current partitions for the table.
column partition_name format a30 select partition_name from user_tab_partitions where table_name = 'TEST_OL_HYBRID_PART_TAB' order by 1; PARTITION_NAME ------------------------------ GBR IRE USA 3 rows selected. SQL>
Now we insert some data for two new countries.
insert into test_ol_hybrid_part_tab
select 'FRA',
object_id,
owner,
object_name
from all_objects
where rownum <= 2000;
commit;
insert into test_ol_hybrid_part_tab
select 'DEU',
object_id,
owner,
object_name
from all_objects
where rownum <= 2000;
commit;
We can see two new partitions have been created for the new countries.
column partition_name format a30 select partition_name from user_tab_partitions where table_name = 'TEST_OL_HYBRID_PART_TAB' order by 1; PARTITION_NAME ------------------------------ GBR IRE SYS_P462 SYS_P463 USA 5 rows selected. SQL>
And of course we can see the data is present when we query the table.
column country_code format a12 select country_code, count(*) as amount from test_ol_hybrid_part_tab group by country_code order by country_code; COUNTRY_CODE AMOUNT ------------ ---------- DEU 2000 FRA 2000 GBR 2000 IRE 2000 USA 2000 5 rows selected. SQL>
For more information see:
- Creating Hybrid Partitioned Tables
- Hybrid Partitioned Tables in Oracle Database 19c
- Partitioning : All Articles
- External Tables : All Articles
Hope this helps. Regards Tim...