Лекция 12: Служебные виды объектов. Работа с редакциями объектов

Таблицы с внешним хранением данных

В версии 9 в Oracle введены таблицы "с внешним хранением данных", представляющие собой по сути табличную видимость в БД данных, хранимых фактически в файлах ОС. Связь между таблицей и файлами осуществляется через "драйвер" одного из двух видов:

• ORACLE_LOADER: способен отображать текстовые данные из файла ОС в таблицу;
• ORACLE_DATAPUMP[10-): допускает выгрузку данных БД в двоичный файл ОС и последующее многократное прочитывание их в виде таблицы.

[10-) начиная с версии 10

Далее приводится пример одностороннего отображения. Подразумевается, что действия выполняются на сервере (это используется ниже только во время правки содержимого файла с помощью команд HOST в SQL*Plus и echo командной оболочки ОС).

Предположим наличие каталога EXTFILES_DIR в БД, доступного для чтения (READ). Создадим файл employee.txt с исходными данными:

Bush,13/03/2001,5000

Powell,14/03/2001,,400.50

Обратите внимание, что во второй строке применено английское форматирование для записи числа. Оно предполагает языковую ориентировку СУБД игнорируемых ошибок формата, могущих возникать в процессе работы на местности, где это принято, например, AMERICAN/AMERICA.

Заведем в схеме SCOTT таблицу с внешним хранением:

CREATE TABLE emp_load
  ( ename    VARCHAR2 ( 10 )
  , hiredate DATE
  , sal      NUMBER ( 7, 2 )
  , comm     NUMBER ( 7, 2 )
  )
ORGANIZATION EXTERNAL
 ( TYPE ORACLE_LOADER
   DEFAULT DIRECTORY extfiles_dir
   ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE 
      NOBADFILE
      NOLOGFILE
      FIELDS TERMINATED BY ','
      MISSING FIELD VALUES ARE NULL
       ( ename
       , hiredate CHAR DATE_FORMAT DATE MASK "dd/mm/yyyy"
       , sal
       , comm 
       )
    )
   LOCATION ( 'employee.txt' )
 )
;

Проверка в SQL*Plus:

SQL> SELECT * FROM emp_load;
ENAME      HIREDATE         SAL       COMM
---------- --------- ---------- ----------
Bush       13-MAR-01       5000
Powell     14-MAR-01                 400.5
SQL> HOST echo Hussein,,1000.44,20000 >> employee.txt
SQL> /
ENAME      HIREDATE         SAL       COMM
---------- --------- ---------- ----------
Bush       13-MAR-01       5000
Powell     14-MAR-01                 400.5
Hussein                 1000.44      20000
SQL> SELECT SUM ( sal ) FROM emp_load;
  SUM(SAL)
----------
   6000.44

Во втором запросе результат вычисляется по ходу прочтения файла employee.txt, то есть не требуется полной загрузки данных этого файла в БД для вычисления результата.

Конструкция LOCATION в определении таблицы допускает задание списка файлов или же переопределение файлов-источников, например:

SQL> HOST echo Laden,13/03/2005,10000 >> centralasia.txt
SQL> ALTER TABLE emp_load LOCATION ('employee.txt', 'centralasia.txt');
Table altered.
SQL> SELECT * FROM emp_load;
ENAME      HIREDATE         SAL       COMM
---------- --------- ---------- ----------
Bush       13-MAR-01       5000           
Powell     14-MAR-01                 500.5
Hussein                 1000.44      20000
Laden      13-MAR-05      10000

Это позволяет переориентировать таблицу EMP_LOAD на другие файлы по мере подготовки их внешними программами приложения. Переопределять разрешено и DEFAULT DIRECTORY.

Еще пример свойств. Указания NOBADFILE и NOLOGFILE можно заменить на противоположные (и подразумеваемые молчаливо), в результате чего в каталоге начнут появляться протокольные файлы доступа к данным из БД. Это, однако, потребует дополнительно привилегии WRITE для SCOTT на использование каталога EXTFILES_DIR (для предыдущих действий хватало привилегии READ). Указание REJECT LIMIT сообщит предельное количество игнорируемых нарушений формата в записях из файлов-источников, обнаруживаемых в процессе выполнения SELECT:

ALTER TABLE emp_load ACCESS PARAMETERS ( 
   RECORDS DELIMITED BY NEWLINE
   BADFILE 
   LOGGING 
);
ALTER TABLE emp_load REJECT LIMIT 20;

Пока нарушений формата менее 21, ошибку доступа СУБД порождать не будет, а только будет пополнять записями о нарушениях протокольный файл.

Таблицу с внешним хранением можно использовать для обновления данных наряду с обычными:

MERGE INTO bonus b USING emp_load e 
ON ( b.ename = e.ename )
WHEN MATCHED THEN UPDATE SET sal = sal * 10
;