Before You Begin
This 15-minute tutorial shows you how to create and load partitions in in-memory hybrid partitioned table.
Background
Oracle Database 18c enables the population of data from external tables into the In-Memory column store (IM column store). This allows the population of data that is not stored in Oracle Database but in source data files. Nevertheless, the population must be completed manually by executing the DBMS_INMEMORY.POPULATE
procedure.
In Oracle Database 19c, querying an in-memory enabled external table automatically initiates the population of the external data into the IM column store.
What Do You Need?
- Oracle Database 19c installed
- A CDB and a PDB
- Tablespaces created in the PDB for internal partitions of the hybrid partitioned table
- Source data files for external partitions of the hybrid partitioned table:
cent19.dat
andcent20.dat
. Download the .dat files to the labs directory created on your server in their respective subdirectories,/home/oracle/labs/CENT19
and/home/oracle/labs/CENT20
. - The
create_inmem_hybrid_table.sql
SQL script. Download the SQL script to the labs directory created on your server/home/oracle/labs
. - The
insert_select.sql
SQL script. Download the SQL script to the labs directory created on your server/home/oracle/labs
.
Configure the IM Column Store Size
- Set the IM column store size to 800M.
sqlplus / AS SYSDBA ALTER SYSTEM SET inmemory_SIZE = 800M SCOPE=SPFILE;
- Restart the instance and open the database.
SHUTDOWN IMMEDIATE STARTUP ALTER PLUGGABLE DATABASE pdb1 OPEN;
Create the Tablespaces for the Internal Partitions
In this section, you create two tablespaces to store data of the internal partitions. One of the two tablespaces will be the default tablespace for internal partitions.
- Log in to the PDB as
SYSTEM
.CONNECT system@PDB1 Enter password: password
- Create the tablespaces
TS1
andTS2
to store internal partitions of the hybrid partitioned table.CREATE TABLESPACE ts1 DATAFILE '/u02/app/oracle/oradata/ORCL/pdb1/ts1.dbf' SIZE 100M; CREATE TABLESPACE ts2 DATAFILE '/u02/app/oracle/oradata/ORCL/pdb1/ts2.dbf' SIZE 100M;
Create the Logical Directories for the External Partitions
In this section, you create the logical directories to store the source data files for external partitions.
- Create the logical directory
CENT18
to store the source data filecent18.dat
for theCENT18
external partition.CREATE DIRECTORY cent18 AS '/home/oracle/labs/CENT18';
- Create the logical directory
CENT19
to store the source data filecent19.dat
for theCENT19
external partition.CREATE DIRECTORY cent19 AS '/home/oracle/labs/CENT19';
- Create the logical directory
CENT20
to store the source data filecent20.dat
for theCENT20
external partition.CREATE DIRECTORY cent20 AS '/home/oracle/labs/CENT20';
Create the In-Memory Hybrid Partitioned Table
- Create the user that owns the in-memory hybrid partitioned table.
CREATE USER hypt IDENTIFIED BY password;
- Grant the read and write privileges on the directories that store the source data files to the table owner.
GRANT read, write ON DIRECTORY cent18 TO hypt;
GRANT read, write ON DIRECTORY cent19 TO hypt;
GRANT read, write ON DIRECTORY cent20 TO hypt;
- Grant the
CREATE SESSION
,CREATE TABLE
, andUNLIMITED TABLESPACE
privileges to the table owner.GRANT create session, create table, unlimited tablespace TO hypt;
- Execute the
create_inmem_hybrid_table.sql
SQL script to create theHYPT_INMEM_TAB
hybrid partitioned table with the following attributes:- The table is partitioned by range on the
TIME_ID
column. - The default tablespace for internal partitions is
TS1
. - The default tablespace for external partitions is
CENT20
. - The fields in the records of the external files are separated by comma ','.
- The table is partitioned into five parts:
- Three external partitions:
CENT18
is empty for the moment;CENT19
has thecent19.dat
file stored in a directory other than the default,CENT19
;CENT20
has thecent20.dat
file stored in the default directory. - Two internal partitions:
Y2000
is stored in tablespaceTS2
andPMAX
is stored in the default tablespaceTS1
.
- Three external partitions:
@/home/oracle/labs/create_inmem_hybrid_table.sql
CREATE TABLE hypt.hypt_inmem_tab (history_event NUMBER , time_id DATE) TABLESPACE ts1 EXTERNAL PARTITION ATTRIBUTES (TYPE ORACLE_LOADER DEFAULT DIRECTORY cent20 ACCESS PARAMETERS (FIELDS TERMINATED BY ',' (history_event, time_id DATE 'dd-MON-yyyy')) REJECT LIMIT UNLIMITED ) PARTITION BY RANGE (time_id) (PARTITION cent18 VALUES LESS THAN (TO_DATE('01-Jan-1800','dd-MON-yyyy')) EXTERNAL, PARTITION cent19 VALUES LESS THAN (TO_DATE('01-Jan-1900','dd-MON-yyyy')) EXTERNAL DEFAULT DIRECTORY cent19 LOCATION ('cent19.dat'), PARTITION cent20 VALUES LESS THAN (TO_DATE('01-Jan-2000','dd-MON-yyyy')) EXTERNAL LOCATION('cent20.dat'), PARTITION y2000 VALUES LESS THAN (TO_DATE('01-Jan-2001','dd-MON-yyyy')) TABLESPACE ts2, PARTITION pmax VALUES LESS THAN (MAXVALUE)) INMEMORY MEMCOMPRESS FOR QUERY HIGH;
- The table is partitioned by range on the
- Find the partitions that are defined as in-memory segments.
SELECT partition_name, inmemory, inmemory_compression FROM dba_tab_partitions WHERE table_name = 'HYPT_INMEM_TAB';
PARTITION_NAME INMEMORY INMEMORY_COMPRESS -------------- -------- ----------------- CENT18 DISABLED CENT19 DISABLED CENT20 DISABLED PMAX ENABLED FOR QUERY HIGH Y2000 ENABLED FOR QUERY HIGH
Only internal partitions are defined as in-memory segments.
Insert Data Into the Partitions
- Execute the
insert_select.sql
SQL script to insert rows into the different partitions of the table and query the table.@/home/oracle/labs/insert_select.sql
SET ECHO ON INSERT INTO hypt.hypt_inmem_tab VALUES (21,to_date('31.12.2000', 'dd.mm.yyyy')); INSERT INTO hypt.hypt_inmem_tab VALUES (22,to_date('31.10.2000', 'dd.mm.yyyy')); INSERT INTO hypt.hypt_inmem_tab VALUES (23,to_date('01.02.2000', 'dd.mm.yyyy')); INSERT INTO hypt.hypt_inmem_tab VALUES (24,to_date('27.03.2000', 'dd.mm.yyyy')); INSERT INTO hypt.hypt_inmem_tab VALUES (25,to_date('31.03.2000', 'dd.mm.yyyy')); INSERT INTO hypt.hypt_inmem_tab VALUES (26,to_date('15.04.2000', 'dd.mm.yyyy')); INSERT INTO hypt.hypt_inmem_tab VALUES (27,to_date('02.09.2000', 'dd.mm.yyyy')); INSERT INTO hypt.hypt_inmem_tab VALUES (28,to_date('12.08.2000', 'dd.mm.yyyy')); INSERT INTO hypt.hypt_inmem_tab VALUES (29,to_date('12.08.2018', 'dd.mm.yyyy')); INSERT INTO hypt.hypt_inmem_tab VALUES (30,to_date('15.09.2017', 'dd.mm.yyyy')); COMMIT; SELECT * FROM hypt.hypt_inmem_tab;
The execution of the query on the table rows automatically populates the data into the IM column store. - Verify which partitions are populated into the IM column store.
SELECT segment_name, partition_name, tablespace_name, populate_status FROM v$im_segments; SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME POPULATE_STAT -------------- -------------- --------------- ------------- HYPT_INMEM_TAB PMAX TS1 COMPLETED HYPT_INMEM_TAB Y2000 TS2 COMPLETED
Only the partitions defined as in-memory segments are populated into the IM column store, and thus the internal partitions.
Determine How Data In Internal And External Partitions Is Accessed
- Display the execution plan for a query on all rows in the table.
EXPLAIN PLAN FOR SELECT * FROM hypt.hypt_inmem_tab;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows| Bytes| Cost (%CPU)| Time | Pstart| Pstop| ---------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 368K| 7917K| 778 (11)| 00:00:01 | | | | 1 | PARTITION RANGE ALL | | 368K| 7917K| 778 (11)| 00:00:01 | 1 | 5| | 2 | TABLE ACCESS HYBRID PART INMEMORY FULL| HYPT_INMEM_TAB | 368K| 7917K| 778 (11)| 00:00:01 | 1 | 5| | 3 | TABLE ACCESS INMEMORY FULL | HYPT_INMEM_TAB | | | | | 1 | 5| -----------------------------------------------------------------------------------------------------------------------
- Display the execution plan for a query on the rows of one of the internal partition in the table.
EXPLAIN PLAN FOR SELECT * FROM hypt.hypt_inmem_tab PARTITION (PMAX);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time | Pstart| Pstop| ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 82171| 1765K| 25 (56)| 00:00:01| | | | 1 | PARTITION RANGE SINGLE | | 82171| 1765K| 25 (56)| 00:00:01| 5 | 5| | 2 | TABLE ACCESS INMEMORY FULL| HYPT_INMEM_TAB | 82171| 1765K| 25 (56)| 00:00:01| 5 | 5| ----------------------------------------------------------------------------------------------------------
- Display the execution plan for a query on the rows of one of the external partition in the table.
EXPLAIN PLAN FOR SELECT * FROM hypt.hypt_inmem_tab PARTITION (CENT19);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time | Pstart| Pstop| ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8169| 175K| 31 (7)| 00:00:01| | | | 1 | PARTITION RANGE SINGLE | | 8169| 175K| 31 (7)| 00:00:01| 2 | 2| | 2 | EXTERNAL TABLE ACCESS FULL| HYPT_INMEM_TAB | 8169| 175K| 31 (7)| 00:00:01| 2 | 2| ----------------------------------------------------------------------------------------------------------
According to the type of partition accessed and the number of partitions accessed at the same time, the operation shows eitherEXTERNAL TABLE ACCESS FULL
(external partitions, notINMEMORY
),TABLE ACCESS INMEMORY FULL
(internal partitions,INMEMORY
) orHYBRID PART INMEMORY FULL
(both internal and external partitions).
Clean Up the Environment
- Drop the in-memory hybrid partitioned
HYPT.HYPT_INMEM_TAB
table.DROP TABLE hypt.hypt_inmem_tab PURGE;
- Quit the session.
EXIT