Максим Чалышев - Oracle SQL. 100 шагов от новичка до профессионала

9213333331,Андрей, Некрасов,2,27

9213333332,Миша, Рогозин,2,21

9214444444,Алексей, Галкин,1,38

Вы можете создать эти файлы сами с помощью любого текстового редактора. Напоминаю, что кодировка файлов UTF8.

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

CREATE OR REPLACE DIRECTORY EXT_TAB_DATA AS ’c:/temp’;

Далее формируем временные таблицы.

Создаем таблицу для файла CITY. csv:

Создаем таблицу для файла MAN. csv:

Обращаемся к таблицам, которые мы создали, с помощью запроса:

Используем агрегатную функцию и группировку данных.

Соединяем внешнюю таблицу и обычную таблицу в запросе.

Используем аналитический SQL.

• Для внешних таблиц могут создаваться представления и синонимы.

• Кодировка файлов во внешних таблицах должна совпадать с кодировкой базы данных.

• На папки, в которых собираются файлы для подготовки внешних таблиц, администратор системы должен выдать специальное разрешение на чтение пользователю, из-под которого устанавливалась ORACLE СУБД.

• Вешние таблицы неэффективны при частом обращении к этим таблицам в высоконагруженных проектах; для таких проектов следует искать другое решение.

Где еще используются на практике внешние таблицы?

Довольно часто внешние таблицы используются при обмене данными между системами.

Где можно посмотреть материалы по ACCESS PARAMETERS?

В описании SQLLoader на сайте ORACLE.

В разобранном примере используется драйвер SQLLoader. Можно ли использовать другие драйвера?

Да, например можно для загрузки данных воспользоваться драйвером DATAPUMP.

Можно ли загрузить данные из внешней таблицы в обычную таблицу?

Да, и на этой возможности основано довольно много ETL (процедур загрузки).

Пример — следующий запрос создаст таблицу CITYcCODE:

Запрос добавляет в эту таблицу данные, не забудьте завершить запрос операцией COMMIT.

Любой SQL-запрос выполняется по заданному плану. В плане выполнения запроса описывается последовательность операций для доступа к данным, особенности использования, последовательность операций сортировок и объединения строк таблиц. Профессиональный SQL-разработчик должен уметь читать план запроса, чтобы оценить эффективность выполнения запроса.

План запроса строит оптимизатор ORACLE, задача разработчика — анализируя план запроса, найти проблемные места в производительности и постараться решить проблемы.

Для того чтобы посмотреть план запроса, необходимо выполнить команду:

EXPLAIN PLAN FOR SELECT FIRSTNAME, LASTNAME FROM MAN WHERE CITYCODE> 1; — или любой другой запрос

Здесь SELECT FIRSTNAME, LASTNAME FROM MAN WHERE CITYCODE> 1; может быть изменен на любой другой запрос.

После выполнения данной команды можно посмотреть план запроса с помощью команды:

SELECT * FROM TABLE (DBMS_XPLAN. DISPLAY (NULL, NULL,«BASIC»));

Во многих редакторах есть специальные средства для просмотра плана запроса, подобный инструмент есть и в ORACLE Appex.

Рисунок 245. План запроса: сервис APEX

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

Как читать план запроса?

План запроса читается с самого дальнего уровня: читается операция самого максимального уровня вложения.

На представленном плане запроса:

• Операция — операция;

• OPTION — доступ к данным;

• Объект — таблица или представление, из которого выбираются данные;

• ROWS — количество строк, которое извлекается операцией;

• TIME — условное время, за которое выполняется та или иная операция;

• COST — стоимость операций для оптимизатора.

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

Следует стремиться к тому, чтобы минимизировать параметр Cost при выполнении данного запроса. Для этого следует обратить внимание на использование индексов в таблицах запроса, правильное построение связей. Разберем несколько планов запросов: во-первых, план с рисунка.

— Таблица AUTO: извлекаются все строки из таблицы, стоимость операции 7 (COST), извлекается 10 строк, стоимость операции 7.

— Осуществляется обращение к таблице MAN, которое происходит по UNIQUE INDEX SCAN, извлекается 1 строка (ROW), стоимость операций 0 для каждой строки таблицы AUTO.

— Для объединения двух таблиц (операции 1, 2) используются вложенные циклы NESTED LOOP, в результате операции получаем 5 строк, стоимость данной операции 7 складывается из стоимости операций 1 плюс стоимость операций 2.

— Для связанных данных используется операция группировки, 3 строки, общая стоимость 8.

— Результат помещается в запрос (3 строки).

Далее осуществляется операция группировки со стоимостью cost 8.

Ниже представлен список операций, которые используются, и опций доступа к данным.

Разбор запроса на связь двух таблиц AUTO, MAN.

Рисунок 246. План запроса: AUTO, MAN