External Table with PARTITIONS

 Una tabella partizionata è una tabella che è divisa in più sezioni per facilitare la ricerda dei dati.

La partizione è usata quando la tabella ha milioni di records e consiste nel dividerla in più sezioni così che quando si effettua una query e si impone la where condition sulle colonne che definiscono la partizione, viene effettuata la ricerca solo in quella sezione della tabella ed ingnorata la restante.

Colleghiamoci allo schema HR del pluggable database ORCLPDB.

Creiamo una tabella partizionata per LIST (ci sono altre opzioni come il range, etc.).

CREATE TABLE vendite_per_regione
(trans_id number,
trans_amount number,
country varchar2(3))
PARTITION BY LIST (country)
(PARTITION c1 VALUES ('LAZ'),
PARTITION c2 VALUES ('PUG')
);

Questa query individua il nome delle paritzioni e il valore:
SELECT *
FROM user_tab_partitions
WHERE TABLE_NAME= 'VENDITE_PER_REGIONE'
ORDER BY table_name, partition_name;

insert into vendite_per_regione values (1,500,'LAZ');

insert into vendite_per_regione values (2,600,'LAZ');

insert into vendite_per_regione values (3,400,'LAZ');

insert into vendite_per_regione values (4,500,'PUG');

insert into vendite_per_regione values (5,600,'PUG');

insert into vendite_per_regione values (6,400,'PUG');

COMMIT;

Alcune query da fare per singola partizione

SELECT *  FROM vendite_per_regione PARTITION  (c1);

La tabella ha due partizioni distinte per il valore della colonna country.

Se provo ad inserire una riga che ha un valore diverso della colonna country ricevo un messaggio di errore:

insert into vendite_per_regione values (7,3500,'EMI');

Errore con inizio alla riga : 27 nel comando -

insert into vendite_per_regione values (7,3500,'EMI')

Report error -

ORA-14400: inserted partition key does not map to any partition

Per aggiungere una partizione effettuare la statement seguente:

ALTER TABLE vendite_per_regione ADD PARTITION C3 VALUES('EMI');

insert into vendite_per_regione values (7,3500,'EMI');

COMMIT;

Creare una directory fiscia e logica dove salvare il file esterno contente i dati.

Colleghiamoci al pluggable database 

SQL > conn /as sysdba
SQL> alter session set container=orclpdb;
SQL> grant create any directory to hr;

Accediamo come hr e creaimo la directory su file systeme e sul db.

SQL> conn hr/hr@orclpdb

SQL> CREATE DIRECTORY EXT_DIR as '/u01/app/oracle/file_ext';

SQL> select * from ALL_DIRECTORIES where directory_name='EXT_DIR';

Creare tre file, uno per partizione dove i dati sono separati dalla virgola.

# vi LAZ.dat

1,500,LAZ

2,600,LAZ

3,400,LAZ

# vi PUG.dat

4,500,PUG

5,600,PUG

6,400,PUG

# vi EMI.dat

7,3500,EMI

Creare la tabella esterna che deve leggere i dati prensenti nel file.

CREATE TABLE  vendite_per_regione_ext

     (trans_id number, 

       trans_amount number,

       country varchar2(3)

      )

    ORGANIZATION EXTERNAL

      (TYPE ORACLE_LOADER

      DEFAULT DIRECTORY EXT_DIR

      ACCESS PARAMETERS

        (RECORDS DELIMITED BY NEWLINE

         FIELDS TERMINATED BY ','

        )

     )

     reject limit unlimited

   PARTITION BY LIST (country)

   (

   PARTITION c1 VALUES ('LAZ') LOCATION ('LAZ.dat'),

   PARTITION c2 VALUES ('PUG') LOCATION ('PUG.dat'),

   PARTITION c3 VALUES ('EMI') LOCATION ('EMI.dat')

   )

Verifica

SELECT * FROM vendite_per_regione_ext;

SELECT table_name, partition_name, location, directory_name

FROM all_XTERNAL_LOC_PARTITIONS

where lower(table_name)='vendite_per_regione_ext';