Табличные и поточные функции

Начиная с Oracle 8i, существуют так называемые "табличные функции" возвращающие набор данных, который можно рассматривать, как реляционную таблицу в предложении FROM. Проще говоря, конвейерные функции – это просто код, с которым вы можете обращаться как с таблицей базы данных. Конвейерные функции позволяют вам использовать конструкции типа SELECT * FROM <ФУНКЦИЯ_PLSQL>.

Oracle не позволит вернуть из произвольной табличной функции любой из обычных типов данных. Указанная в качестве возвращаемого типа функции коллекция должна иметь в качестве элемента табличный тип.

Создание табличной функции в Oracle ничем не отличается от создания обычной функции. Пример создания табличной функции представлен на рис.2.

 

Рис. 2 Пример табличной функции

Теперь функция может быть вызвана из оператора SELECT при помощи ключевого слова TABLE, сообщающего Oracle, что возвращаемую коллекцию следует интерпретировать как набор записей. Вызов через select предоставляет возможность использовать возможности простых запросов, такие как операторы group by, where и т.д. Пример вызова табличной функции представлен на рис.

Конвейеризованные табличные функции появились в 9 версии Oracle. Они являются надстройкой над табличными функциями.

Конвейеризованная табличная функция - это функция, которая возвращает результирующее множество в виде коллекции, но делает это итеративно. Другими словами, Oracle не ждет, когда выполнение функции закончится, накапливая все полученные строки в коллекции PL/ SQL, прежде чем вернуть их. Вместо этого записи по мере их готовности к включению в коллекцию возвращаются из функции.

Как и при работе с табличными функциями, при работе с конвейеризованными функциями необходимо предварительно создать табличный тип.

Между неконвейеризованной и конвейеризованной версиями имеются четыре синтаксических различия:

• Ключевое слово PIPELINED добавляется в заголовок функции с целью сообщить Oracle о необходимости возвращать результат немедленно, а не накапливать предварительно все результирующее множество.

• Команда PIPE ROW обозначает место, в котором функция возвращает отдельную запись.

• Ключевое слово RETURN осталось, но не делает ничего, осуществляя лишь выход из функции. Все результаты уже были переданы по конвейеру командой PIPE ROW.

• В предложении RETURN определить тип возвращаемых функцией данных как коллекцию (вложенную таблицу или VARRAY)

• Необходимо указывать типы SQL, а не типы PLSQL. Нужно создать типы SQL с помощью оператора create or replace type.

 

Конвейерные функции позволяют:

- получить строку, когда она будет готова. В условиях больших объёмов обрабатываемых данных, занимающих время алгоритмов обработки строк и нескольких этапов обработки это довольно важно.

- распараллелить обработку строк функцией. Например, при вставке результатов вида INSERT SELECT FROM PIPELINED FUNCTION или их последующей обработке другой функцией.

- экономия используемой памяти. Вся коллекция не нужна, хватит только той части, которая будет возвращаться из функции.

- исключение NO_DATA_NEEDED позволяет корректно освободить ресурсы и завершить процесс обработки в определённых ситуациях.

 

Loader

SQL*Loader инструмент для загрузки данных из внешних файлов в таблицы БД ORACLE. SQL*Loader обрабатывает широкое разнообразие форматов входных файлов и дает вам возможность управлять загрузкой записей в таблицы ORACLE. SQL*Loader загружает данные в различных форматах, выполняет фильтрацию (выборочную загрузку записей в зависимости от значений данных), и может загружать несколько таблиц одновременно. Во время выполнения SQL*Loader формирует детальный файл отчета со статистикой загрузки и может также создавать файл отброшенных записей (записи, отброшенные из-за ошибок в данных) и файл пропущенных записей (записи, которые не соответствуют критерию выбора).

Утилита загрузки SQL*Loader обладает следующими возможностями:

1. Загрузки данных по сети.

2. Загрузки данных из нескольких файлов данных в течение одной операции загрузки.

3. Загрузки данных одновременно в несколько таблиц одной операцией загрузки.

4. Указания символьного набора данных.

5. Выборочной загрузки данных (можно загружать записи в зависимости от значений отдельных полей).

6. Изменять данные перед их загрузкой, используя функции SQL.

7. Генерации уникальных последовательных значений ключа в указанных столбцах.

8. Загрузки данных с диска, кассеты или именованного шлюза.

9. Генерации замысловатых отчетов об ошибках, которые могут значительно помочь в локализации ошибок.

10. Загрузки сколь угодно сложных объектно-реляционных данных.

11. Для загрузки данных типа LOB и коллекций из вспомогательных файлов данных.

Чтобы загрузить данные из внешних файлов в БД ORACLE, нужно подготовить для SQL*Loader входную информацию 2 типов: сами данные и управляющую информацию, описывающую, как выполнять загрузку. Данные, загружаемые в БД ORACLE, должны находиться в файлах на диске. Эти файлы данных SQL*Loader должны распознаваться при загрузке. Информация для этого находится в управляющем файле. Данные и управляющая информация может быть подготовлены в отдельных файлах или вместе в одном и том же файле.

Контрольный файл

Назначение этого файла – описать данные, которые должно быть загружены. Например, он описывает:

§ имена файлов данных

§ формат файлов данных

§ поля данных в этих файлах

§ как загружать данные в таблицы (какие таблицы и колонки должно быть загружены).

Некоторая информация является обязательной (где найти данные и как они соответствуют таблицам БД), однако имеется также много опций для описания и манипулирования данными. Например, инструкции могут включать указание, как форматировать или фильтровать данные, или как генерировать уникальный № ID.

Выходная информация

1. Файл отчета (log file)

2. Файл отброшенных записей (bad file)

3. Файл пропущенных данных

Порядок загрузки данных

1. Создать таблицу в которую будут загружаться данные

2. Создать файл данных в своем текущем рабочем каталоге. Данные в файле должны быть разделены. Роль разделителей могут играть любые знаки препинания. Содержимое файла может быть следующим:

"Абалхассанбейнги Араш";"Магистрант";"Факультет технологии органических веществ\Магистранты";Абалхассанбейнги Араш.bmp

3. Создать контрольный файл SQL*Loader формата ctl в своем текущем рабочем каталоге. Его можно создать в любом текстовом редакторе. Содержимое файла:

LOAD DATA

INFILE personal.txt --имя файла с данными

INTO TABLE personal --имя таблицы

REPLACE -- метод загрузки

--задается что разделителем является “;”

FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'

(fio, jobtitle, subdn, img FILLER CHAR(100),

photo LOBFILE (img) TERMINATED BY EOF

)

Следует отметить, что колонки содержащие имя файла отмечены как FILLER, они не загружаются, но используются при определении LOBFILE, для определения места содержания LOB информации.

Методы загрузки:

Ø INSERT Метод используемый по умолчанию. При этом предполагается, что таблица перед загрузкой пустая. Если в таблице есть строки данных, то выполнение загрузки будет прекращено.

Ø APPEND Этот метод позволяет добавлять строки в таблицу таким образом, чтобы они не оказывали воздействия на уже существующие строки данных.

Ø REPLACE При использовании этого метода вначале удаляются все имеющиеся в таблице строки, а затем загружаются новые.

 

4. Из текущего рабочего каталога выполняется следующая команду SQL*Loader в приглашении командной строки

 

sqlldr имя_пользователя/пароль CONTROL=контрольный_файл.ctl

 

IMP/EXP, PUMP

Утилита экспорта exp.exe

Представляет собой исполняемый модуль, который находится в каталоге определенным переменной ORACLE_HOME.

Утилита читает базу данных, включая словарь данных, и записывает результаты в двоичный файл, который именуется как файл дампа экспорта (export dump file). В данном смысле можно экспортировать всю базу данных, конкретных пользователей (схемы) или конкретные таблицы вашей БД. В процессе экспорта, вы можете определиться есть ли необходимость экспортировать связанную с таблицами информацию словаря данных, такую как привилегии, индексы и ограничения. Созданный утилитой экспорта файл, будет содержать команды необходимые для полного восстановления всех выбранных объектов. Можно осуществлять полный (complete) экспорт всех таблиц БД, или только тех, которые были изменены, со времени последнего экспорта. Во втором случае экспорт будет инкрементальным или кумулятивным.

Инкрементальный (incremental) экспорт приведет к записи всех таблиц изменившихся со времени последнего экспорта, а кумулятивный (cumulative) - всех таблиц изменившихся со времени последнего полного экспорта. Также утилита предоставляет вам возможность сжимать свободные экстенты сильно фрагментированных сегментов данных. Например, когда какая-либо схема более менее сформировалась можно уничтожить ее табличное пространство предварительно все слив в экспорт, а далее восстановив табличное пространство импортировать данные.

Для работы с данным инструментом пользователь Oracle должен обладать привилегиями dba.

Формат исполнения команд выглядит таким образом:

exp … parameter_name = value …,

exp – имя утилиты, parameter_name – список параметров с указанными значениями value.

Утилита импорта imp.exe

Утилита импорта тоже представляет собой исполняемый модуль, как и exp.exe. Однако применяется она для импорта данных в БД. Это означает, что imp.exe считывает двоичный дамп файл, созданный при экспорте утилитой exp, и запускает все находящиеся в нем команды на исполнение. Формат исполнения ничем не отличается от exp. Отличие заключается лишь в списке параметров.

Утилита экспорта expdp.exe

Утилита экспорта Oracle Pump позволяет экспортировать данные и метаданные в набор файлов операционной системы. Набор файлов дампа состоит из одного или нескольких файлов, содержащих табличные данные, метаданные объектов базы данных и управляющую информацию. Эти файлы записываются в специальном бинарном формате, поэтому они могут быть импортированы только impdp.exe.

Необходимо заметить, что данные утилиты являются более совершенными по сравнению с традиционными утилитами exp и imp. В поставку Oracle они входят начиная с версии Oracle Database 10gR1. К основным нововведениям можно отнести:

• Существенные архитектурные и функциональные усовершенствования.

• Поддержка внешних таблиц и предоставление PL/SQL API.

• Один поток expdp примерно в два раза быстрее exp.

• В отличие от утилит IMP и EXP, все файлы Data Pump создаются на сервере Oracle, а не на клиентской машине.

• Возможность параллельного выполнения извлечения или загрузки данных.

Так как файлы дампа записываются сервером базы данных, а не самой помпой данных (клиентом), то необходимо создать объекты с типом directory, для тех каталогов, в которые эти файлы будут записаны. Объектdirectory - это объект базы данных, который является синонимом соответствующего каталога в файловой системе сервера.

Можно также осуществлять экспорт по сети. При этом, данные из экземпляра базы данных записываются в набор файлов дампа на сервере.

Утилита импорта impdp.exe

Помпа данных импорта позваляет импортировать набор файлов дампа в целевую базу данных Oracle. Набор файлов дампа может быть импортирован в ту же самую базу данных, откуда был произведен, или в другую базу данных Oracle на другой системе. При импорте по сети, данные загружаются в целевую базу данных из базы-источника прямо по сети, минуя стадию файлов дампа. С помощью этого механизма можно запускать экспорт и импорт параллельно, минимизируя время, необходимое для всей этой операции. По сравнению с утилитой imp impdp в 15-45 раз быстрее. Нужно сказать, что данное отличие явно заметно при импорте очень большого объема данных.

 

JOB, SHEDULE

Это может быть и блок PL/SQL, и хранимая процедура, и внешняя процедура на C или JAVA.Частота выполнения задается или как однократное задание или регулярное.

Важное свойство пакета – задание попадает в очередь только после фиксации транзакции. То есть, если задание включено в транзакцию, а транзакция подверглась откату, то задание выполняться не будет.

Управление фоновыми заданиями реализуется отдельными серверными процессами (SNP), которые должны быть запущены, прежде чем будут активизированы задания. Эти процессы активизируются с установленной периодичностью, просматривают очередь и выполняют те задания, у которых настало время активизации. Одновременно может работать до 10 процессов SNP, которые являются неотъемлемой частью нашего экземпляра.

Поэтому сначала необходимо установить количество фоновых процессов для выполнения наших заданий.

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=NN,

где NN – желаемое количество процессов (допустимо от 0 до 10).

NN зависит от интенсивности использования пакета. Если у вас много снапшотов, различных заданий, то может потребоваться увеличение NN.

Еще один параметр инициализации JOB_QUEUE_INTERVAL интервал активизации фоновых процессов в секундах. Допустимо от 1 до 3600.

Основные методы:

· DBMS_JOB. SUBMIT – создание задания

· DBMS_JOB.ISUBMIT – создание задания с указанием номера

· DBMS_JOB.INSTANCE – выбор экземпляра для выполнения

· DBMS_JOB.REMOVE – удаление задания

· DBMS_JOB.RUN – немедленное выполнение

· DBMS_JOB.CHANGE – изменение параметров

· DBMS_JOB.WHAT – изменение задания

· DBMS_JOB.NEXT_DATE – изменение следующей даты выполнения

Задания выполняются последовательно в соответствии с заданным временем выполнения. Если у вас один процесс обработки очереди (смотрите параметр JOB_QUEUE_PROCESSES в init.ora), а в очереди у вас несколько заданий на одно и тоже время, то очевидно задания будут выполняться последовательно, с некоторым смещение относительно заданного времени. И к тому же очередь просматривается периодически, например 30 сек. Исходя из всего этого очевидно, что если задавать относительный момент времени (например, sysdate+1), то получим медленное смещение времени выполнения регулярного задания. Поэтому, если важно точно выполнять задание в конкретный момент времени, то используйте функцию, которая всегда возвращает фиксированный момент времени (например, trunc(sysdate)+1+2/24).

 

Dbms_sсhedule

К версии 11 такое устройство имевшегося планировщика заданий было сочтено слишком примитивным, и в ней появился новый планировщик, DBMS_SHEDULE,значительно более проработанный. Он использует следующие основные понятия:

q Schedule (расписание)

q Program (программа)

q Job (плановое задание = расписание + программа)

В отличие от старого планировщика, в новом «программой» может быть не только блок PL/SQL, но и хранимая процедура на PL/SQL или на Java, внешняя процедура на С или даже команда ОС. Последнее означает, что Oracle отменяет необходимость использовать специфичные для разных платформ планировщики заданий ОС. Вдобавок, сам запуск заданий получил возможность учета текущей вычислительной обстановки в СУБД, а также желаемой приоритетности среди прочих заданий.

Расписание (Schedule)

Расписание - объект базы данных, применяемый для хранения определенного расписания выполнения заданий.

Программа (Program)

Программа - объект базы данных, хранящий определенный набор действий, который впоследствии будет сопоставлен расписанию.

 

DBLINK

Database Link (dblink): объект базы данных (СУБД), предназначенный для доступа к объектам базы данных, управляемой другим сервером

dblink: привилегии

Dblink: user1-user2

Dblink: shared user1-user2

Dblink: global

 

Секцинирвание

Секционирование – метод, позволяющий хранить сегмент данных (таблица, индекс) в виде нескольких секций, причём секции-сегменты при общности логической структуры могут иметь собственные физические атрибуты.

К секционированию предъявляют следующие требования:

Прозрачность для пользователя (пользователь приложения не должен знать, работает ли он с секционированными данными или нет)

Прозрачность для разработчика (запросы в приложении не должны специальным образом модифицироваться для работы с секционированными данными)

Удобство администрирования (секционирование данных должно не осложнять жизнь администратора базы данных, а упрощать ее)

Диапазонное секционирование

Диапазонное (range) секционирование – это секционирование, при котором для каждой секции определяется диапазон значений ключа секционирования.

Ключ секционирования в диапазонном секционировании может принимать значение даты и времени, числа или текста. Для задания диапазона используется ключевое слово less than. Используются секции небольшого, примерно равного размера.