X

Technologie - Trends - Tipps&Tricks
in deutscher Sprache

Hybrid Partitioned Tables und Lifecycle Management

Mit EXTERNAL TABLES kann man seit jeher lesend auf Daten aus externen Quellen zuzugreifen, als ob sie in einer Tabelle in der Datenbank gespeichert wären. In der Datenbank werden dabei allerdings nur die erforderlichen Metadaten der External Tables gespeichert. Die Dateien können in unterschiedlichen Formaten im Dateisystem, im HDFS oder aber auch im Oracle Cloud Object Storage vorliegen. Bei Letzterem ist die Verwendung des Package DBMS_CLOUD erforderlich.

Mit Oracle Database 19c gibt es nun auch die Möglichkeit einer hybriden Speicherung: Mithilfe der External Table Technologie können dabei Daten in Partitionen einer Tabelle innerhalb und auch außerhalb der Datenbank gespeichert werden. Wie die generelle Funktionsweise dazu ist, haben wir in einem unserer älteren Tipps zu Hybrid Partitioned Tables erklärt. Dort wurde das Anlegen von Hybrid Partitioned Tables mit CREATE TABLE, das Umwandeln einer Partitioned Table in eine Hybrid Partitioned Table und das Ganze auch für Autonomous Database und den Object Storage gezeigt. Bei Letzerem ist zusätzlich die Verwendung des PL/SQL Package DBMS_CLOUD erforderlich. Wer das Ganze noch einmal nachlesen will, findet den Link zum Tipp unter "Hybrid Partitioned Tables - Lifecycle Management leicht gemacht".

Aber was bedeutet das ganz konkret? Wie kann man Daten in externe Partitionen verlagern? Oder auch umgekehrt: Wie kann man Daten in interne Partitionen einlagern? Dies ist eine typische Anforderung im Lifecycle Management Umfeld . 

Die Lösung dazu ist ganz einfach: Man lagert die Daten mithilfe einer Hilfstabelle aus und benutzt das Kommando EXCHANGE PARTITION zum Austausch und somit zur Verlagerung der Daten. Eine andere Idee ist die Nutzung des SPILT Kommandos. Das Vorgehen dazu hat mein Kollege Sinan Petrus Toma im Zusammenhang mit Autonomous Database und Object Storage gezeigt (siehe auch Blogeintrag "Save Storage Cost with Hybrid Partitioned Tables in Oracle Autonomous Database"). Dieses Vorgehen ist natürlich auch einfach auf On-premises Installationen zu übertragen.

Demonstrieren wir das Vorgehen mit dem EXCHANGE Kommando an einem ganz einfachen Beispiel. Nehmen wir dazu die EMPLOYEES Tabelle aus dem HR Schema als Basis und wandeln diese in eine partitionierte Tabelle um. Die Anforderung lautet: Die Partition mit den geringsten Gehältern soll in eine externe Partition ausgelagert werden. Die Schritte dazu sehen dann folgendermassen aus:

Hinweis: Falls das HR Schema und die Tabelle EMPLOYEES nicht vorhanden sind, kann man sich diese zum Beispiel über Github laden.     

Zur Vorbereitung legen wir die partitionierte Tabelle EMPLOYEES_HYBRID an und befüllen diese mit Daten aus der EMPLOYEES Tabelle. Zu Beginn besteht die Tabellel EMPLOYEES_HYBRID aus den 4 internen Partitionen (RANGE): salary_4000,  salary_10000, salary_30000 und natürlich einer Partition für MAXVALUE.  

DROP TABLE hr.employees_hybrid;

CREATE TABLE hr.employees_hybrid
   (  EMPLOYEE_ID varchar2(10),
      SALARY      number,
      JOB_TITLE   varchar2 (35))
    PARTITION BY RANGE (salary)
    ( PARTITION salary_4000 VALUES less than (4000),   -- intern
      PARTITION salary_10000 VALUES less than (10000), -- intern
      PARTITION salary_30000 VALUES less than (30000), -- intern
      PARTITION salary_max values less than (MAXVALUE))
/

INSERT INTO hr.employees_hybrid
SELECT e.employee_id, e.salary, j.job_title 
FROM hr.employees e JOIN hr.jobs j ON e.job_id=j.job_id;

COMMIT;

Jetzt wollen wir die Daten aus der Partition salary_4000 auslagern. Das Auslagern von Daten funktioniert wie immer mit der EXTERNAL TABLE Technologie, dem Treiber ORACLE_DATAPUMP und dem entsprechenden AS SELECT Statement.
Dazu legen wir jetzt die Tabelle EXT_HELP als Hilfstabelle an.

/*
DROP TABLE ext_help;
! rm salaryless3000.dmp 
*/
CREATE TABLE ext_help
ORGANIZATION EXTERNAL
 (
    TYPE oracle_datapump
     DEFAULT DIRECTORY home
     LOCATION ('salaryless3000.dmp')
    )
    REJECT LIMIT UNLIMITED
AS SELECT employee_id, salary, job_title FROM hr.employees_hybrid partition(SALARY_4000);

SQL> select max(salary) from ext_help;

MAX(SALARY)
-----------
       3900

Nun wandeln wir die partitionierte Tabelle EMPLOYEES_HYBRID in eine hybrid partitioned Tabelle um. Dazu müssen wir die Syntax EXTERNAL PARTITION ATTRIBUTES hinzufügen um den Ort (LOCATION) und die Art des EXTERNAL TABLE Treibers (TYPE) anzugeben. In unserem Beispiel verwenden wir das Directory HOME und den Treiber ORACLE_DATAPUMP.

SQL> select directory_name from all_directories;

DIRECTORY_NAME
--------------------------------------------------
MY_EXP
HOME

ALTER TABLE hr.employees_hybrid 
ADD EXTERNAL PARTITION ATTRIBUTES
   ( TYPE oracle_datapump 
     DEFAULT DIRECTORY home 
     REJECT LIMIT UNLIMITED ); 

Bei der Überprüfung stellen wir fest, dass wir zwar über eine Hybrid Partitioned Table verfügen, allerdings noch nicht über externe Partitionen.

SQL> select table_name, type_name from user_XTERNAL_PART_TABLES
  2  /

TABLE_NAME           TYPE_NAME
-------------------- --------------------
EMPLOYEES_HYBRID     ORACLE_DATAPUMP


SQL> select partition_name from USER_XTERNAL_TAB_PARTITIONS;
no rows selected

SQL> select partition_name, high_value from user_tab_partitions where table_name like 'EMP%';

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------
SALARY_10000                   10000
SALARY_30000                   30000
SALARY_4000                    4000
SALARY_MAX                     MAXVALUE

Und jetzt kommt der einfache Trick: Um die Daten der Partition auszulagern, kann man einfach das Kommando EXCHANGE PARTITION anwenden um die Datensegmente auszutauschen. Zur Erinnerung: Man kann damit eine Partition oder Subpartition in eine nicht partitionierte Tabelle und eine nicht partitionierte Tabelle in eine Partition oder Subpartition einer partitionierten Tabelle umwandeln.

ALTER table hr.employees_hybrid
EXCHANGE PARTITION(salary_4000) WITH TABLE ext_help;

Und schon sind wir fertig und können die Hilfstabelle löschen oder anderweitig verwenden. Überprüfen wir die Partitionen um ganz sicher zu gehen.

SQL> select partition_name from USER_XTERNAL_TAB_PARTITIONS where table_name like 'EMP%';
PARTITION_NAME
------------------------------
SALARY_4000

SQL> select partition_name, high_value from user_tab_partitions where table_name like 'EMP%';

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------
SALARY_10000                   10000
SALARY_30000                   30000
SALARY_4000                    4000
SALARY_MAX                     MAXVALUE

SQL> select max(salary) from employees_hybrid partition (salary_4000);

MAX(SALARY)
-----------
3900

Übrigens der andere Weg funktioniert natürlich auch: Man könnte auf diese Art und Weise auch Daten einlagern als interne Partition. Damit wäre das Lifecycle Management perfekt!  

Am Besten einfach einmal ausprobieren :)

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.