Отличительные особенности СУБД Oracle от других СУБД

Если Вы всерьёз решили заниматься освоением СУБД Oracle, то в первую очередь я Вам посоветую найти книгу Oracle для профессионалов. Книга 1. Архитектура и основные особенности. Том Кайт. (Есть ещё Книга 2, тоже полезно.) А если Вы пока не понимаете, нужно ли оно Вам, то можно просто попробовать поработать с этой СУБД, на самые ключевые особенности СУБД я постараюсь обратить внимание в этой заметке.

HINT: Если Вы вообще ни разу не работали с базами данных, то лучше сначала ознакомиться с теорией реляционных баз данных и попробовать сделать, например ежедневник, или телефонный справочник на MS Access (да-да, в академических целях лучше использовать наипростейший инструмент! ну и найти какую-нибудь книгу типа Введение в базы данных – по ссылке Вы найдёте кусочек текста). Ну или, если Вы без Delphi совсем жить не можете, то можно это сделать с использованием того же BDE и Paradox; у меня была книга “Программирование баз данных в Delphi 7. Валерий Фаронов”. Однако замечу, что с наскока, без знания теории, сделать что-то дельное сразу не получится, поэтому лучше помучить Access – это единая программа, где можно и проектировать структуру БД, и “рисовать” формочки и, не зная SQL, “рисовать” запросы к БД и получать отчёты.

Ну ещё порекламирую книгу Delphi. Профессиональное программирование. Дмитрий Осипов., часть II которой посвящена базам данных (там тоже есть теория).

HINT: Если Вы не знаете, какую СУБД выбрать, то дам такой совет. Во-первых, надо исходить из специфики конкретной задачи, Oracle – это конечно здорово, но есть и другие СУБД. Во-вторых продублирую пару ссылок из предыдущей заметки: Oracle vs MSSQL2k и Oracle 10g vs SQL Server 2005. Ну и в-третьих, нужно знать специфику СУБД, чтобы под эту СУБД разрабатывать клиентские приложения; если Вы выбираете серьёзную СУБД типа Oracle, то надо быть готовым к тому, что в будущем миграция на другую СУБД может оказаться крайне затруднительной (см. пункт 7).

Итак, ключевые особенности СУБД Oracle

1. Пользователь БД = Схема БД

Эту особенность можно критиковать, однако в Oracle оно вот так сложилось исторически. Пользователь БД не может существовать без схемы БД, и наоборот: схема БД привязана к конкретному пользователю. Создавая пользователя БД (create user <user_name>), в БД автоматически создастся схема <user_name>. При попытке удалить пользователя (drop user), произойдёт попытка удалить и его схему.

В предыдущей заметке меня спросили: “Мы подключились как scott - мы теперь видим его таблицы или все таблицы? И как увидеть тогда чужие...”.

Да, подключаясь под пользователем scott, мы по умолчанию попадаем в схему scott и видим все объекты этой схемы. Чтобы можно было обратиться к объекту чужой схемы, можно пойти разными путями.

а). Перед именем объекта явно указывать имя схемы, например:

select * from scott.emp

б). Воспользоваться синонимами и создать так называемый public synonym:

create public synonym emp for scott.emp

Ключевое слово public говорит о том, что этот синоним будет доступен из любой схемы. И тогда из чужой схемы можно будет писать:

select * from emp

Тут Oracle увидит, что синоним emp ссылается на scott.emp и запрос будет почти эквивалентен запросу из пункта а).  “Почти” – потому что есть приоритеты поиска объектов: Oracle сначала попробует найти объект emp в текущей схеме, а если не получится, то только тогда будет использован синоним.

в). Можно просто взять и переключиться в другую схему:

alter session set current_schema = <sсhema_name>

Сделать это можно, например, в триггере на подключение к БД. Кстати этот вариант (по отношению к варианту б) Том Кайт называет как “меньшее из двух зол” (Synonyms vs Alter session current_schema).

г). Реализовать свой способ хранения и авторизации пользователей. Т.е. я имею ввиду следующее: сначала происходит Oracle авторизация (все пользователи ходят в БД под одним именем и паролем, сразу попадая в нужную схему), а потом уже делать дополнительную свою проверку: пользователь вводит логин/пароль и тут мы уже сами смотрим, есть ли такой пользователь в нашей схеме, или нет.

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

Лично мне довелось опробовать первые три пункта, а четвёртый у нас стоит на повестке дня. Могу сказать, что везде есть свои плюсы и минусы. Однако для начала, всё же, лучше пользоваться либо пунктом а), либо вообще об этом пока не думать и “ходить” в БД под одним пользователем. В предыдущих и последующих заметках я не буду явно ссылаться на какую-либо схему в запросах.

 

Ради спортивного интереса Вы можете выполнить такие запросы:

select * from all_tables where owner = 'SCOTT'
select * from all_users
select * from all_objects

 

2. Пару слов о транзакциях

а). В Oracle нет необходимости объявлять начало транзакции каким-бы то ни было способом явно. Другими словами, если Вы выполняете insert, update или delete, то транзакция начинается автоматически. Ну и не забываем, что транзакция заканчивается либо командой commit (зафиксировать транзакцию) либо rollback (откатить транзакцию). Есть ещё возможность выполнять команды с автокоммитом (т.е. commit выполнится автоматически, если операция выполнилась успешно).

Кстати, если во время выполнения транзакции произошла ошибка, то Oracle автоматически выполнит откат (rollback). Это нам позволит в Delphi-коде вместо чего-то такого:

StartTransaction;
try
  DoSomething;
  CommitTransaction;
except
  RollbackTransaction;
  raise;
end;

писать просто:

  DoSomething;
  CommitTransaction;

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

в). Oracle поддерживает Автономные транзакции.

г). В Oracle существуют точки сохранений (savepoint). Кое-что я о них уже писал ранее: здесь и здесь.

 

3. Последовательности (сиквенсы)

В Oracle отсутствует понятие автоинкрементного поля, использующегося в некоторых СУБД для создания суррогатного ключа. Вместо этого, Oracle предлагает использовать последовательности (sequence). Последовательность – это именованный объект БД, обладает довольно простым набором свойств: текущее значение (число, атрибут currval), следующее значение (число, атрибут nextval), начальное значение (число, обычно оно равно 1), максимальное значение (тоже число, обычно оооочень большое), инкремент (число, обычно равно 1, может быть отрицательным) и признак - возможность сброса последовательности в начальное значение, при достижении текущего значения максимума. На практике последовательности используют примерно так:

insert into my_table(id, field1, field2)
  values (my_sequence.nextval, value1, value2);

Здесь my_sequence – это и есть последовательность,  nextval -  атрибут, возвращающий очередное значение последовательности.

У последовательностей есть парочка особенностей:

а). Последовательности “живут” как бы вне транзакций. Т.е. если у последовательности запросить nextval, это приведёт к изменению текущего значения последовательности, и если затем сделать откат транзакции, то текущее значение последовательности уже не будет восстановлено. Ну или другими словами: если две сессии выполняют длительные транзакции и обращаются к одной и той же последовательности, то:

• во-первых, не будет никаких блокировок (из-за пользования общим ресурсом) и сессии будут выполняться параллельно;
• во-вторых, обе сессии получат разные значения nextval.

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

б). Надо помнить, что в DML операциях N обращений к nextval на уровне одной записи таблицы приведёт всего лишь к 1 изменению последовательности.  При этом currval вернёт такое же значение. Наглядный пример:

create sequence my_sequence;
--sequence MY_SEQUENCE created.
create table my_table(id1 integer, id2 integer, id3 integer, id4 integer);
--table MY_TABLE created.

insert into my_table(id1, id2, id3, id4)
  values (my_sequence.currval, my_sequence.nextval, my_sequence.currval, my_sequence.nextval);
--1 rows inserted.
insert into my_table(id1, id2, id3, id4)
  values (my_sequence.currval, my_sequence.nextval, my_sequence.currval, my_sequence.nextval);
--1 rows inserted.
insert into my_table(id1, id2, id3, id4)
  values (my_sequence.currval, my_sequence.nextval, my_sequence.currval, my_sequence.nextval);
--1 rows inserted.

select * from my_table;

ID1	ID2	ID3	ID4
1	1	1	1
2	2	2	2
3	3	3	3

 

4. Пустая строка и NULL

Некоторые СУБД различают пустую строку ('') и NULL как разные значения. Для Oracle же пустая строка эквивалента NULL-значению. Это означает, что любое сравнение с пустой строкой вернёт FALSE, например такой запрос:

select * from all_tables where owner <> ''

не вернёт ни одной записи. Правильно писать так:

select * from all_tables where owner is not null

В общем надо владеть понятием троичная логика и помнить, что пустое значение интерпретируется как NULL. А в тех случаях, когда значение переменной или столбца заранее не известно, использовать такие функции как nvl, nvl2, coalesce.

 

5. Временные таблицы

Об этом много подробно написано, например здесь (подробно) или здесь (кратко). Вообще название “временная” не обязует СУБД создавать эту таблицу в пределах сессии на лету. Тут имеется ввиду временность данных, хранящихся в этой таблице: данные временной таблицы доступны только лишь в пределах текущей сессии; эти данные нельзя “подсмотреть” из другой сессии.

Это позволяет реализовывать разные задачи. Например, в интерфейсе приложения пользователь выбирает несколько критериев поиска данных: пусть это будет нестрогий (т.е. с использованием символов “*” и “?”) список наименований, которые он хочет найти. Приложение сохраняет эти критерии во временную таблицу как несколько строк (с заменой “*” на “%” и “?” на “_”), и далее делаем select-запрос по искомой таблице с соединением её с временной таблицей через like (вместо применений нескольких условий like через or в предложении where запроса).

 

6. Аналитические функции в SQL

Аналитические функции – это очень мощный инструмент-расширение стандарта SQL. Строго говоря, можно обойтись и без использования аналитических функций, но тогда запросы будут: а) громоздкими, б) долго выполнимыми. Почитать тут. 

Правда на практике я несколько раз сталкивался с тем, что вместо аналитических функций (в целях производительности) лучше использовать pipelined-функции на PL/SQL.

 

7. PL/SQL – процедурное расширения языка SQL

Почитать (ссылка). PL/SQL используется для написания триггеров, процедур, функций и пакетов – всё это нужно для реализации бизнес логики приложений на стороне БД. Другими словами, PL/SQL позволяет максимально отгородить программный код, управляющий данными, от Ваших приложений. Что в свою очередь облегчает написание последних, превращая их лишь в инструменты ввода/вывода данных.

Ну или скажу по другому: с появлением PL/SQL базы данных перестали быть “тупыми” хранилищами данных. Теперь БД может работать и обрабатывать данные даже без участия внешних приложений: см. пункт 8.

Пакеты PL/SQL – это что-то наподобие модулей (unit) в Delphi. У пакета обязательно есть описательная часть (create package – аналог секции interface в Delphi-модуле) и может существовать так называемое тело пакета (create package body – аналог секции implementation). Описательная часть содержит типы, переменные, константы и прототипы процедур и функций. Тело пакета тоже может содержать объявления типов, переменные, константы и содержит реализацию объявленных процедур и функций.

У PL/SQL пакетов есть одна неочевидная (на мой взгляд) особенность: значения глобальных и локальных переменных пакета существуют лишь только в пределах текущей сессии (наподобие временных таблиц). Т.е. если одна сессия сохранит какое-то значение в глобальную переменную пакета, то в другой сессии эта переменная пакета может иметь совсем другое значение.

Стоит так же заметить, что чем больше у Вас кода на PL/SQL, тем сложнее будет миграция на другую СУБД. Краем уха, мне довелось услышать об OpenSource СУБД PostgreSQL, в которой есть аналогичный PL/SQL язык, под названием PL/pgSQL.

 

8. Задания (job)

В Oracle есть планировщик заданий. Задание – это выполнение какой-либо процедуры по расписанию. Задание может быть разовым (однократным), либо периодическим. Почитать тут (ох уж этот интерфейс.ру).

 

9. Pipelined-функции

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

 

Пожалуй, на этом и закончу

Конечно это ещё не всё. В Oracle есть ещё такие понятия, о которых бы стоило упомянуть отдельно: Nested Tables, Hint, Flashback Query. Есть ещё разные способы организации хранения таблиц (например в индексе). Есть и другие :с). Однако чем дальше, тем это менее важно на начальных этапах знакомства с этой СУБД.

Спасибо за внимание, жду комментариев.