ORACLE7. Руководство разработчика приложений. 2.1

Интернет-магазин

Лицензионное ПО

Курсы обучения

Сертификация

Обучение и семинары

Каталог курсов

Новости

Статьи

Вопросы и ответы

Бесплатные семинары

Онлайн-курсы

Курсы Microsoft On-Demand

Кафедра МФТИ

Центр Тестирования

IT-Сертификации

Новости

Статьи

Программные продукты

Каталог ПО

Лицензиатор ПО

Схемы лицензирования

Новости

Вопросы и ответы

Статьи

Каталог DOWNLOAD

Форумы

Свободное ПО/Open Source

Каталог свободного ПО

Системы автоматизации

ERP-система iRenaissance

Документооборот

ERP-форум

О компании

Новости

Отзывы заказчиков

Лицензии

Наши координаты

Программа партнерства

Наши вакансии

Новое на сайте

ИТ-Юмор

ИТ-глоссарий

RSS-лента

Архив

Самый быстрый способ заполнить таблицу

Владимир
Пржиялковский,
преподаватель УКЦ Interface Ltd.

Нередко перед пользователем Oracle стоит задача загрузить в БД большую таблицу. Если таблица действительно велика, то есть содержит по сегодняшним меркам миллион или более записей, то критичным становится время загрузки, за которое и приходится бороться. О чем в таких ситуациях полезно помнить?

В Oracle существует только два способа заполнения таблицы. Первый, самый распространенный, это вставка, то есть последовательное выполнение непосредственных операций уровня SQL INSERT. По этому способу работают:

  • программа imp;
  • программа SQL*Loader.

Менее известно (хотя тайны в этом абсолютно никакой нет), что есть и второй способ – прямой загрузки, – который может применяться при работе с программой SQL*Loader (то есть SQL*Loader позволяет загружать данные и первым, и вторым способом). Именно о нем и пойдет речь.

SQL*Loader – программа-загрузчик для эффективного наполнения БД большими объемами данных. Она поставляется в любой поставке СУБД Oracle. Архитектура этой программы – живая иллюстрация милых сердцу ветеранов классических загрузчиков, создававшихся лет 20 назад для занесения данных с магнитных лент в базу. Однако “унаследованный” – вовсе не синоним словам “ненужный” или “плохой”. Появившаяся в версии 6 Oracle, программа SQL*Loader дожила до нынешней 9i, пополнившись в этой версии “третьего тысячелетия” собственным скромным набором, но – усовершенствований. Такая эпикурейская жизнеспособность была бы маловероятна в отсутствии постоянного потребительского спроса.

Долгое время единственным видом исходных данных для него оставался текст, но сегодня SQL*Loader в состоянии также загружать и файлы произвольной структуры – имеется в виду, что в LOB-поля таблиц. Возможны использование разных форматов текстовых данных, предобработка загружаемой информации, выбор загрузки разными способами. Последним он нам сейчас и интересен — для повышения скорости загрузки SQL*Loader может использовать:

(а) параллельную загрузку данных (здесь не рассматриваемую) и

(б) прямую загрузку

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

Прямая загрузка в SQL*Loader есть не что иное, как самостоятельное расписывание блоков поступающими из загрузочного файла данными и передача блоков в уже готовом виде ядру СУБД. Такой способностью в Oracle обладает только SQL*Loader, больше никто. Универсальный, но достаточно громоздкий механизм Oracle по выполнению вставки отдельной строки (подыскивание свободного блока, включающее работу со списками и так далее; генерация записей для отката и прочая “кухня”) остается побоку, что и помогает выполнять эту специфичную серию вставок существенно быстрее.

Для того, чтобы загрузка выполнялась таким способом, в контрольный файл загрузчика нужно вставить строку

(Контрольный файл загрузки задает название и формат файла с загрузочными данными, параметры и режимы загрузки).

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

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

OPTIONS (DIRECT=TRUE)
UNRECOVERABLE
LOAD DATA
INFILE ‘c:loaderdataemployee. txt’
INTO TABLE emp1
(
empno INTEGER EXTERNAL TERMINATED BY ‘,’,
ename CHAR TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘»‘,
job CHAR TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘»‘,
mgr INTEGER EXTERNAL TERMINATED BY ‘,’,
hiredate DATE «DD/MM/YYYY» TERMINATED BY ‘,’,
sal DECIMAL EXTERNAL TERMINATED BY ‘,’,
comm DECIMAL EXTERNAL TERMINATED BY ‘,’,
deptno INTEGER EXTERNAL TERMINATED BY ‘,’
)

Сами данные, загружаемые с использованием такого контрольного файла, могут выглядеть примерно так:

(Пример взят из моего учебного курса по администрированию Oracle).

На что рекомендуется обратить внимание

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

  • первый экстент сегмента и
  • свободное пространство ниже верхней отметки заполнения (high watermark)

По этой причине разумен следующий трюк: при прямой загрузке “с нуля” (когда загружается изначально пустая таблица) выставить в параметрах STORAGE для этой таблицы значение INIT в очень небольшую величину: все равно первый экстент заполняться не будет!

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

  • Тиражирование данных из одной базы
    в другую — в Oracle очень просто
  • Как перенести табличное пространство
    из базы в базу
  • Как восстановить таблицу
  • «Невидимые миру дыры» в табличных пространствах
    Oracle

Дополнительную информацию Вы можете получить в компании Interface Ltd.

Отправить ссылку на страницу по e-mail
Обсудить на форуме Oracle

Таблицы могут храниться в базе данных несколькими способами. Простейший из них это heap таблица. Таблица может хранить строки различной длины в случайном порядке. Может быть какая-либо корреляция между порядком записи и порядком в котором данные хранятся но на это нельзя рассчитывать. Более сложные структуры таблицы, такие как представленные ниже, могут влиять на порядок и группировку строк или использовать случайное распределение

ORACLE7. Руководство разработчика приложений. 2.1

Использование сложных структур таблицы не вляет на SQL. Любой SQL запрос выполненные к таблицам созданным с этими опциями вернёт абсолютно одинаковый результат такой же как будто таблица являются обычными heap таблицами. Но если программист понимает как они работаеют то можно существенно увеличить производительность. Создание таблицы с указанием столбцовДля создания таблицы используется следующий синтаксисКак минимум необходимо указать имя таблицы (по умолчанию таблицы создаются в схеме текущего аккаунта, если вы не указываете другую) и один столбец с типом данных. Практически никто не указывает явно ORGANIZATION HEAP, так как это значение по умолчанию и стандарт SQL. Ключевое слово DEFAULT в определении столбца позволяет указать выражение которое будет использоваться при выполнении команды INSERT если не указано значение для этого столбца. CREATE TABLE SCOTT. EMPHIREDATE DATE DEFAULT TRUNC(SYSDATE),COMM NUMBER(7,2) DEFAULT 0. 03);Создаем таблицу EMP в схеме SCOTT. Этот запрос выполнится если подключен аккаунт SCOTT (и тогда указание схемы необязательно) или другой пользователь у которого есть доступ на создание таблиц в схеме SCOTT. СтолбцыEMPNO число длиной 4 цифры без дробной части. Если будет указана дробная часть при выполнении команды INSERT значение будет округлено к ближайшему целому. ENAME строка с максимальной длиной в 10 символовHIREDATE возможно использовать значение любой даты с указанием времени, если время не указано используется значение полночьSAL предназначен для хранения зарплаты, принимает число длиной 7 цифр. Цифры после семи будут обрезаны а значение округленоCOMM имеет значение по умолчанию 0. 03, которое будет использоваться если явно не указанть значение при выполнении команды INSERTПосле создания таблицы можно записывать данные и выполнять команду SELECT

ORACLE7. Руководство разработчика приложений. 2.1

Обратите внимание что столбцы не указанные при команде INSERT использовали значения указанные в DEFAULT. Если бы не были указаны значения по умолчанию в секции DEFAULT использовалось бы значение NULL. Также обратите внимание что значение SAL было округлено. The DEFAULT clause can be useful, but it is of limited functionality. You cannot use a subquery to generate the default value: you can only specify literal values or functions. Создание таблиц с использованием подзапросовВместо создания таблиц с нуля и затем вставки строк (как мы только что рассмотрели) таблицы можно создавать используя другие таблицы – с помозью подзапросов. Этот метод позволяет определить таблицу и записать данные используя один запрос. Любой запрос может быть использован как источник для описания столбцов и как набор данных (строк). Синтаксис командыЛюбой запрос возвращает двумерный набор строк; результат хранится как новая таблица. Просто пример создания таблицы используя подзапросcreate table employees_copy as select * from employees;Данный запрос создаст таблицу EMPLOYEES_COPY, которая будет полной копией таблицы EMPLOYEES, идентичной как по структуре столбцов и так и хранящихся строк. Все проверки на обязательность значения и значения по умолчанию также применяются к новой таблице, но первичные ключи, уникальные или внешние ключи не создаются (ограничения constraints обсудим далее), потому что эти три типа ограничений требуют создания индексов которые могут быть не нужными пользователю. Рассмотрим более сложный примерcreate table emp_dept as selectlast_name ename,department_name dname,round(sysdate — hire_date) servicefrom employees natural join departments order by dname,ename;Строки в новой таблице это результат объекдинения двух таблиц-источников, и два столбца изменяют название. Новый столбец SERVICE будет заполнен результатом арифметической операции и получено количество дней с момента приёма на работу. Строки буду вставлены в определённом порядке. Этот порядок не будет управляться последующими DML командами, но предполагая что в схеме HR данные по умолчанию новая таблица будет выглядеть следующим образом

ORACLE7. Руководство разработчика приложений. 2.1

Подзапрос конечно может включать секцию WHERE для ограничего строк для добавления в новую таблицу. Для создания таблицы без строк помжно использовать условие WHERE которое никогда не выполняется. create table no_emps as select * from employees where 1=2;Изменение таблицы после созданияДоступно много изменений которые возможны после создания таблицы. Изменения которые затрагивают физическую структуру ложатся на плечи администратора БД, но большинство изменения являются логическими и могут быть применены SQL разработчиками. Раасмотрим примеры (большинтсво из них говорят сами за себя)

Все изменения этих DDL команд содержат встроенный COMMIT. Эти изменения нельзя отменить и они не смогут отработать если имеется активная транзакция к таблице. Также эти операции происходят практически мгновенно за исключением удаления столбца. Удаление столбца может затребовать длительное время так как поскольку удаляется столбец, каждая строка должна быть изменена. Команда SET UNUSED делает столбец недоступным для SQL команд, и часто является более лучшей альтернативой в сочетании с командойALTER TABLE tablename DROP UNUSED COLUMNS;которая удалит все неиспользуемы столбцы за один проход таблицы. После перевода таблицы в режим только-чтения все DML команды не выполнятся. Но таблица может быть удалена, так как удаление всего лишь удалит строки из словаря данных. Удаление и сокращение таблицы (drop и truncate)Команда TRUNCATE TABLE удаляет вссе строки из таблицы и оставляет определение таблицы. Команда DROP TABLE удаляет также и определение таблицы (саму таблицу вместе с данными). Синтаксис командыЕсли схема не указана удалится таблица с этим именем в схеме текущего аккаунта. SQL не выдает предупреждений перед выполнением команды DROP и как и любая команда DDL содержит встроенный COMMIT. То есть удаление таблицы нельзя отменить. При выполнении определенных условий, удаление может быть отменено используя определенные методики. У команды DROP есть несколько ограничений: команда не выполнится если существует транзакция к этой таблице, или если данные этой таблицы используются как внешний ключ другой таблица, тогда для удаления исходной таблица необходимо вначале удалить ссылающуюся таблица или внешний ключ.

Таблицы

Данные в реляционных базах данных хранятся в таблицах. Таблицы — это
ключевой объект, с которыми придется работать в SQL.

Таблицы в БД совсем не отличаются от тех таблиц, с которыми все уже
знакомы со школы — они состоят из колонок и строк.

Каждая колонка в таблице имеет своё имя и свой тип, т. тип данных,
которые будут в ней содержаться. Помимо типа данных для колонки можно
указать максимальный размер данных, которые могут содержаться в этой
таблице.

Например, мы можем указать, что для колонки возраст тип данных — это
целое число, и это число должно состоять максимум из 3-х цифр. максимальное число, которое может содержаться в этой колонке = 999. А с
помощью дополнительных конструкций можно задать и правила проверки
корректности для значения в колонке,- например, мы можем указать, что
для колонки возраст в таблице минимальное значение = 18.

Создание таблицы

create table hello(
text_to_hello varchar2(100)

После выполнения данной sql-команды в базе данных будет создана таблица
под названием hello. Эта таблица будет содержать всего одну колонку
под названием text_to_hello. В этой колонке мы можем хранить только
строковые значения(т. любой текст, который можно ввести с клавиатуры)
длинной до 100 байт.

Обратите внимание на размер допустимого текста в колонке
text_to_hello. 100 байт — это не одно и то же, что и 100 символов! Для
того, чтобы сказать базе данных Oracle, что длина строки может быть 100
символов, нужно было определить столбец следующим образом:

text_to_hello varchar2(100 char)

Создание таблицы с несколькими полями

В таблице может много столбцов. Например, можно создать таблицу с тремя,
пятью или даже 100 колонками. В версиях oracle с 8i по 11g максимальное
количество колонок в одной таблице достигает 1000.

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

Например, создадим таблицу cars, в которой будем хранить марку
автомобиля и страну-производитель:

create table cars(
model varchar2(50 char),
country varchar2(70 char)

Эта таблица может содержать, например, такие данные:

Следует обратить внимание на последние 2 строки в таблице cars — они
не полные. Первая из них содержит данные только в колонке model,
вторая — не содержит данных ни в одной из колонок. Эта таблица может
даже состоять из миллиона строк, подобных последней — и каждая строка не
будет содержать в себе абсолютно никаких данных.

Значения по умолчанию

При создании таблицы можно указать, какое значение будет принимать
колонка по умолчанию:

create table cars(
model varchar2(50 char),
country varchar2(50 char),
wheel_count number(2) default 4
)

Понятие NULL. Not-null колонки

Ячейки в таблицах могут быть пустыми, т. не содержать значения. Для
обозначения отсутствия значения в ячейке используется ключевое слово
NULL. Null могут содержать ячейки с любым типом данных.

Рассмотрим таблицу cars из предыдущего примера. В каждой из трех ее
колонок может храниться Null(даже в колонке wheel_count, если указать
значение Null явно при вставке).

Для того, чтобы запретить Null-значения в колонке при создании таблицы,
к описанию колонки добавляется not null:

create table cars(
model varchar2(50 char) not null,
country varchar2(50 char),
wheel_count number(2) default 4
)

Теперь БД гарантирует, что колонка model не будет пустой, по
крайней мере до тех пор, пока флаг not null включен для этой колонки.

Также можно указать, что колонка wheel_count тоже не должна содержать
Null:

create table cars(
model varchar2(50 char) not null,
country varchar2(50 char),
wheel_count number(2) default 4 not null
);

comment on table cars is ‘Список автомобилей’;

comment on column cars. model is ‘Модель авто, согласно тех. паспорту’;
comment on column cars. country is ‘Страна-производитель’;
comment on column cars. wheel_count is ‘Количество колес’;

comment on table cars is »;

Самый быстрый способ заполнить таблицу

Менее известно (хотя тайны в этом абсолютно никакой нет), что есть и второй способ — прямой загрузки, — который может применяться при работе с программой SQL*Loader (то есть SQL*Loader позволяет загружать данные и первым, и вторым способом). Именно о нем и пойдет речь.

SQL*Loader — программа-загрузчик для эффективного наполнения БД большими объемами данных. Она поставляется в любой поставке СУБД Oracle. Архитектура этой программы — живая иллюстрация милых сердцу ветеранов классических загрузчиков, создававшихся лет 20 назад для занесения данных с магнитных лент в базу. Однако «унаследованный» — вовсе не синоним словам «ненужный» или «плохой». Появившаяся в версии 6 Oracle, программа SQL*Loader дожила до нынешней 9i, пополнившись в этой версии «третьего тысячелетия» собственным скромным набором, но — усовершенствований. Такая эпикурейская жизнеспособность была бы маловероятна в отсутствии постоянного потребительского спроса.

Долгое время единственным видом исходных данных для него оставался текст, но сегодня SQL*Loader в состоянии также загружать и файлы произвольной структуры — имеется в виду, что в LOB-поля таблиц. Возможны использование разных форматов текстовых данных, предобработка загружаемой информации, выбор загрузки разными способами. Последним он нам сейчас и интересен — для повышения скорости загрузки SQL*Loader может использовать:

Прямая загрузка в SQL*Loader есть не что иное, как самостоятельное расписывание блоков поступающими из загрузочного файла данными и передача блоков в уже готовом виде ядру СУБД. Такой способностью в Oracle обладает только SQL*Loader, больше никто. Универсальный, но достаточно громоздкий механизм Oracle по выполнению вставки отдельной строки (подыскивание свободного блока, включающее работу со списками и так далее; генерация записей для отката и прочая «кухня») остается побоку, что и помогает выполнять эту специфичную серию вставок существенно быстрее.

По этой причине разумен следующий трюк: при прямой загрузке «с нуля» (когда загружается изначально пустая таблица) выставить в параметрах STORAGE для этой таблицы значение INIT в очень небольшую величину: все равно первый экстент заполняться не будет!

Ссылки по теме

  • Линейка продуктов Oracle 10g
  • Обратиться в Interface Ltd. за дополнительной информацией/по вопросу приобретения продуктов
  • Приобреcти продукты Oracle в электронном магазине ITShop.ru
  • Курсы по продуктам фирмы Oracle

SQL — Урок 3. Создание таблиц и наполнение их информацией

Все значения полей обязательны для заполнения, значит надо добавить тип NOT NULL.

Первый столбец, как вы помните из концептуальной модели нашей БД, является первичным ключом (т. его значения уникальны, и они
однозначно идентифицируют запись). Следить за уникальностью самостоятельно можно, но не рационально. Для этого в SQL есть
специальный атрибут — AUTO_INCREMENT, который при обращении к таблице на добавление данных высчитывает
максимальное значение этого столбца, полученное значение увеличивает на 1 и заносит его в столбец. Таким образом, в этом столбце
автоматически генерируется уникальный номер, а следовательно тип NOT NULL излишен. Итак, присвоим атрибут столбцу с первичным
ключом:

Итак, таблица готова, и ее окончательный вариант выглядит так:

Теперь разберемся со второй таблицей — topics (темы). Рассуждая аналогично, имеем следующие поля:

id_topic int (10) AUTO_INCREMENT
topic_name varchar(100) NOT NULL
id_author int (10) NOT NULL
PRIMARY KEY (id_topic)

FOREIGN KEY (имя_столбца_которое_является_внешним_ключом) REFERENCES имя_таблицы_родителя (имя_столбца_родителя);

Укажем, что id_author — внешний ключ:

Обратите внимание, внешних ключей у таблицы может быть несколько, а первичный ключ в MySQL может быть только один. В первом уроке мы удалили нашу БД forum, пришло время создать ее вновь.

Запускаем сервер MySQL (Пуск — Программы — MySQL — MySQL Server 5. 1 — MySQL Command Line Client), вводим пароль, создаем
БД forum (create database forum;), выбираем ее для использования (use forum;) и создаем три наших таблицы:

Обратите внимание, одну команду можно писать в несколько строк, используя клавишу Enter (MySQL автоматически подставляет
символ новой строки ->), и только после разделителя (точки с запятой) нажатие клавиши Enter приводит к выполнению запроса.

Помните, если вы сделали что-то не так, всегда можно удалить таблицу или всю БД с помощью оператора DROP. Исправлять что-то
в командной строке крайне неудобно, поэтому иногда (особенно на начальном этапе) проще писать запросы в каком-нибудь редакторе,
например в Блокноте, а затем копировать и вставлять их в черное окошко.

Итак, таблицы созданы, чтобы убедиться в этом вспомним о команде show tables:

Теперь становятся понятны значения всех полей структуры, кроме поля DEFAULT. Это поле значений по умолчанию. Мы могли бы
для какого-нибудь столбца (или для всех) указать значение по умолчанию. Например, если бы у нас было поле с названием
«ЖенатыЗамужем» и типом ENUM (‘да’, ‘нет’), то было бы разумно сделать одно из значений значением по умолчанию. Синтаксис был бы следующий:

married enum (‘да’, ‘нет’) NOT NULL default(‘да’)

это ключевое слово пишется через пробел после указания типа данных, а в скобках указывается значение по умолчанию.

Но вернемся к нашим таблицам. Теперь нам необходимо внести данные в наши таблицы. На сайтах, вы обычно вводите информацию в
какие-нибудь html-формы, затем сценарий на каком-либо языке (php, java. ) извлекает эти данные из формы и заносит их в БД. Делает он это посредством SQL-запроса на внесение данных в базу. Писать сценарии на php мы пока не умеем, а вот отправлять SQL-запросы
на внесение данных сейчас научимся.

Для этого используется оператор INSERT. Синтаксис можно использовать двух видов. Первый вариант используется
для внесения данных во все поля таблицы:

INSERT INTO имя_таблицы VALUES (‘значение_первого_столбца’,’значение_второго_столбца’,. , ‘значение_последнего_столбца’);

Второй вариант используется для внесения данных в некоторые поля таблицы:

INSERT INTO имя_таблицы (‘имя_столбца’, ‘имя_столбца’) VALUES (‘значение_первого_столбца’,’значение_второго_столбца’);

Если бы у нас были поля с типом NULL, т. необязательные для заполнения, мы бы тоже могли их проигнорировать. А вот если
попытаться оставить пустым поле со значением NOT NULL, то сервер выдаст сообщение об ошибке и не выполнит запрос. Кроме того,
при внесении данных сервер проверяет связи между таблицами. Поэтому вам не удастся внести в поле, являющееся внешним ключом, значение,
отсутствующее в связанной таблице. В этом вы убедитесь, внося данные в оставшиеся две таблицы.

Но прежде внесем информацию еще о нескольких пользователях. Чтобы добавить сразу несколько строк, надо просто перечислять
скобки со значениями через запятую:

Итак, у нас есть 3 таблицы, в которых есть данные. Встает вопрос — как посмотреть, какие данные хранятся в таблицах. Этим
мы и займемся на следующем уроке.

Предыдущий урок
Вернуться в раздел
Следующий урок

Теперь нажмите кнопку, что бы не забыть адрес и вернуться к нам снова.