Руководство по переходу с Oracle на SQL Server и с SQL Server на Oracle

«Зависимость от поставщика» — пугающее слово для многих руководителей бизнеса. С другой стороны, в отрасли уже широко распространено понимание того, что полная «независимость от поставщика» недостижима. И это особенно верно в случае с базами данных.

Двумя наиболее распространенными корпоративными РСУБД являются Oracle Database и Microsoft SQL Server (для краткости в оставшейся части статьи я буду называть их «Oracle» и «SQL Server» соответственно). Конечно, IBM Db2 конкурирует с Oracle на постоянно сокращающихся, но все еще важных во многих областях платформах мэйнфреймов. А быстро развивающиеся альтернативы с открытым исходным кодом, такие как PostgreSQL, завоевывают прочную основу в динамических средах на обычном оборудовании низкого и среднего уровня и в Интернете.

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

И Oracle, и SQL Server являются «старой школой», частично совместимыми с ANSI реализациями СУБД. Если оставить в стороне процедурные расширения — PL/SQL и Transact-SQL имеют разный синтаксис, но, как правило, их легко переводить — и более новые объектно-ориентированные варианты будущего, код SQL может выглядеть обманчиво похожим. И это опасная медовая ловушка.

Двумя наиболее важными моментами для любого проекта миграции между Oracle и SQL Server (в любом направлении) являются транзакции и тесно связанные с ними временные таблицы , которые являются ключевым инструментом в разрешении области транзакций. Мы также рассмотрим вложенные транзакции — те, которые существуют в рамках другой транзакции, — потому что они являются ключевой частью реализации аудита безопасности пользователей в Oracle. Но в SQL Server аудит безопасности пользователей требует другого подхода из-за его поведения COMMIT в этом контексте.

Понимание структуры транзакций: сравнение Oracle и SQL Server с высоты десяти тысяч футов

Транзакции Oracle являются неявными. Это означает, что вам не нужно начинать транзакцию — вы всегда находитесь в транзакции. И эта транзакция открыта до тех пор, пока вы не выполните оператор фиксации или отката. Да, вы можете запустить транзакцию явно, определить безопасные точки отката и установить внутренние/вложенные транзакции; но важно то, что вы никогда не находитесь «не в транзакции» и всегда должны выполнять фиксацию или откат. Также обратите внимание, что выдача оператора языка определения данных (DDL) ( CREATE , ALTER и т. д.; в транзакции это можно сделать с помощью динамического SQL) фиксирует транзакцию, в которой он был выдан.

В отличие от Oracle, SQL Server имеет явные транзакции. Это означает, что если вы явно не запустите транзакцию, все ваши изменения будут зафиксированы «автоматически» — сразу же после обработки вашего оператора, так как каждый оператор DML ( INSERT , UPDATE , DELETE ) создает транзакцию сам по себе и фиксирует ее, если не произойдет ошибка. вне.

Это результат различия в реализации хранения данных — как данные записываются в базу данных и как ядро ​​базы данных их считывает.

В Oracle операторы DML изменяют записи непосредственно в файле данных. Старая копия записи (или подстановка пустой записи, в случае INSERT ) записывается в текущий файл отката, и на записи отмечается точное время изменения.

Когда выдается SELECT , она обрабатывается на основе данных, которые были изменены до того, как она была выдана. Если какие-либо записи были изменены после выдачи SELECT , Oracle использует более старую версию из файла отката.

Именно так Oracle реализовал согласованность чтения и неблокирующее чтение/запись. По этой же причине длительные запросы к очень активным транзакционным базам данных иногда приводили к печально известной ошибке ORA-01555, snapshot too old: rollback segment ... too small . (Это означает, что файл отката, необходимый запросу для более старой версии записи, уже был повторно использован.) Вот почему правильный ответ на вопрос «Как долго должна длиться моя транзакция Oracle?» «Столько, сколько нужно, и не больше».

Реализация SQL Server отличается: ядро ​​базы данных записывает и читает только непосредственно в/из файлов данных. Каждый оператор SQL ( SELECT / INSERT / UPDATE / DELETE ) является транзакцией, если только он не является частью явной транзакции, объединяющей несколько операторов вместе, что позволяет откатывать изменения.

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

И на какую конструкцию SQL в Oracle и SQL Server влияет разница в реализации транзакций? Временные таблицы.

Временные таблицы в Oracle и SQL Server

Когда стандарт ANSI SQL определяет локальные и глобальные временные таблицы, в нем явно не указывается, как они должны быть реализованы. И Oracle, и SQL Server реализуют глобальные временные таблицы. SQL Server также реализует локальные временные таблицы. В Oracle 18c также реализованы «настоящие» локальные временные таблицы (которые они называют «частными временными таблицами»). Это делает перевод кода SQL Server в Oracle 18c заметно проще, чем в более старых версиях, — завершая ранее добавленное Oracle несколько связанных такие функции, как автоматическое увеличение столбцов идентификаторов.

Но с точки зрения чисто функционального анализа введение частных временных таблиц может быть неоднозначным благом, поскольку проблемы миграции с SQL Server на Oracle кажутся меньшими, чем они есть на самом деле. Это еще одна медовая ловушка, поскольку она сама по себе может создать некоторые новые проблемы. Например, проверка кода во время разработки не может выполняться для частных временных таблиц, поэтому любой код, использующий их, будет неизменно более подвержен ошибкам. Если вы использовали динамический SQL, скажем так: частные временные таблицы столь же сложны для отладки, но без очевидного уникального варианта использования. Поэтому Oracle добавил локальные (частные) временные таблицы только в 18c, а не раньше.

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

Глобальные временные таблицы в Oracle и SQL Server

Глобальная временная таблица Oracle — это постоянный объект словаря данных, явно созданный во время разработки оператором DDL. Он является «глобальным» только потому, что является объектом уровня базы данных и может быть доступен любому сеансу базы данных, имеющему необходимые разрешения. Однако, несмотря на глобальную структуру , все данные в глобальной временной таблице относятся только к сеансу, в котором они работают, и ни при каких обстоятельствах не видны за пределами этого сеанса. Другими словами, другие сеансы могут иметь свои собственные данные в собственной копии той же самой глобальной временной таблицы. Итак, в Oracle глобальная временная таблица содержит локальные данные сеанса, которые в основном используются в PL/SQL для упрощения кода и оптимизации производительности.

В SQL Server глобальная временная таблица — это временный объект, созданный в блоке кода Transact-SQL. Он существует до тех пор, пока открыт сеанс его создания, и он виден — как по структуре, так и по данным — другим сеансам в базе данных. Итак, это глобальный временный объект для обмена данными между сеансами.

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

Итак, как используются локальные временные таблицы в SQL Server и как их следует транслировать в Oracle?

Критическое (и правильное) использование локальных временных таблиц в SQL Server заключается в сокращении или удалении блокировки ресурсов транзакций, особенно:

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

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

Стоит отметить, что обычные табличные выражения (CTE, т. е. операторы WITH <alias> AS (SELECT...) ) в SQL Server являются просто «синтаксическим сахаром». Они преобразуются во встроенные подзапросы перед выполнением SQL. Oracle CTE (с подсказкой /*+ materialize */ ) оптимизированы для производительности и создают временную версию материализованного представления. В пути выполнения Oracle CTE обращаются к исходным данным только один раз. Исходя из этой разницы, SQL Server может работать лучше, используя локальные временные таблицы вместо нескольких ссылок на одно и то же CTE, как это можно сделать в запросе Oracle.

Из-за различий в реализации транзакций временные таблицы также выполняют другую функцию. В результате перемещение временных таблиц SQL Server в Oracle «как есть» (даже с реализацией частных временных таблиц в Oracle 18c) может быть не только вредным для производительности, но и функционально неправильным.

С другой стороны, при переходе с Oracle на SQL Server необходимо обратить внимание на длину транзакции, область видимости глобальных временных таблиц и производительность CTE-блоков с «материализованной» подсказкой.

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

Введите табличные переменные

Разработчики, вероятно, зададутся вопросом: а как насчет табличных переменных? Нужно ли нам вносить какие-либо изменения или мы можем перемещать табличные переменные «как есть» на этапах миграции с Oracle на SQL-Server? Ну, это зависит от того, почему и как они используются в коде.

Давайте посмотрим, как можно использовать как временные таблицы, так и табличные переменные. Я начну с Microsoft SQL Server.

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

Это теория, но практические соображения использования немного сложнее.

Впервые столкнувшись с серьезной оптимизацией Transact-SQL, когда я пришел из глубоко укоренившегося опыта работы с Oracle, я ожидал, что все будет так: табличные переменные находятся в памяти, а временные таблицы — на диске. Но я обнаружил, что версии Microsoft SQL Server до 2014 года не хранят табличные переменные в памяти. Таким образом, полное сканирование таблицы по временной переменной действительно является полным сканированием таблицы на диске. К счастью, SQL Server 2017 и более поздние версии поддерживают декларативную оптимизацию памяти как для временных таблиц, так и для табличных переменных.

Итак, каков вариант использования табличных переменных в Transact-SQL, если все можно сделать так же или лучше с помощью временных таблиц? Ключевое свойство табличной переменной состоит в том, что она является переменной и, как таковая, не зависит от отката транзакции и может быть передана в качестве параметра.

Функции Transact-SQL очень ограничены: поскольку задача функции состоит в том, чтобы возвращать какое-то единственное возвращаемое значение, она — по замыслу — не может иметь побочных эффектов . Transact-SQL рассматривает даже SELECT как побочный эффект, потому что в SQL Server любой доступ к таблице создает неявную транзакцию и связанную с ней блокировку транзакции. Это означает, что внутри функции мы не можем получить доступ к данным в существующей временной таблице или создать временную таблицу. В результате, если нам нужно передать какой-либо набор записей в функцию, мы должны использовать табличные переменные.

Соображения Oracle относительно использования (глобальных) временных таблиц и переменных коллекций (эквивалент табличных переменных Transact-SQL в Oracle PL/SQL) отличаются. Переменные коллекции Oracle находятся в памяти, тогда как временные таблицы расположены во временных табличных пространствах. Функции Oracle разрешают доступ только для чтения к таблицам, постоянным или временным; простой SELECT в Oracle никогда не блокирует ресурсы.

В Oracle выбор использования переменных коллекции или временных таблиц основан на ожидаемом объеме данных, продолжительности, в течение которой эти данные должны храниться, а также на выделении и доступности памяти или диска. Кроме того, переменные-коллекции — это стандартный способ передать набор строк в качестве выходных данных основной программе.

Поскольку большинство элементов синтаксиса SQL очень похожи между SQL Server и Oracle, преобразование блоков кода с табличными переменными из SQL Server Transact-SQL в Oracle PL/SQL является более простым и синтаксически более щадящим процессом. Он может пройти базовый проверочный тест, но не будет функционально правильным, если не будут предприняты шаги по повторной реализации временной таблицы, как описано выше. С другой стороны, код, перенесенный из Oracle в SQL Server, требует дополнительных шагов модификации только для того, чтобы быть синтаксически допустимым. Кроме того, чтобы быть функционально корректным, необходимо подробно рассмотреть случаи использования временных таблиц и CTE.

Внутренние транзакции («Вложенные транзакции»)

Что касается проблем миграции с Oracle на SQL Server, то следующей важной областью, на которую следует обратить внимание, являются вложенные транзакции.

Как и в случае с временными таблицами, если код Transact-SQL включает любую транзакцию, вложенную или нет, или код Oracle включает любые вложенные транзакции, мы говорим не просто о переносе простого кода, а о функциональной повторной реализации.

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

Вложенные транзакции в Oracle

Вложенные транзакции Oracle полностью атомарны и не зависят от внешней области. Нет фактического применения вложенных транзакций в простых интерактивных запросах Oracle SQL. Когда вы работаете с Oracle в интерактивном режиме, вы просто вручную фиксируете свои изменения, когда видите, что достигли состояния. Если вы внесли какие-то изменения, которые пока не можете зафиксировать, пока не сделаете последний — скажем, неопределенный для вас — шаг, который, возможно, нужно будет откатить, но вы хотите сохранить уже проделанную вами работу, вы создадите безопасную точку для отката к ней без фиксации или отката всей транзакции.

Итак, где используются вложенные транзакции? В коде PL/SQL. В частности, в автономных процедурах, объявленных с помощью PRAGMA AUTONOMOUS_TRANSACTION . Это означает, что при вызове этого кода (как именованной хранимой процедуры или анонимно) транзакция фиксируется или откатывается независимо от транзакции, вызвавшей этот код.

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

Вложенные транзакции в коде SQL Server Transact-SQL совершенно разные.

Вложенные транзакции в SQL Server

В Transact-SQL то, будет ли зафиксирована внутренняя транзакция, полностью зависит от самой внешней транзакции. Если внутренняя транзакция откатывается, она просто откатывается. Но если внутренняя транзакция была зафиксирована, она все еще не полностью зафиксирована, так как ее можно откатить, если откатится любой уровень ее транзакции внешней области.

Итак, какая польза от внутренних транзакций, если их фиксации можно отменить, откатив внешнюю транзакцию? Ответ такой же, как и в случае с локальными временными таблицами: снятие блокировки ресурсов. Отличие состоит в том, что это не снятие глобальной блокировки, а блокировка в рамках непосредственной внешней (прямой «родительской») транзакции. Он используется в сложном коде Transact-SQL для высвобождения внутренних ресурсов для внешней транзакции. Это инструмент оптимизации производительности и управления ресурсами.

Поскольку внутренние/вложенные транзакции Oracle и SQL Server имеют разное (возможно, даже противоположное) поведение и совершенно разные варианты использования, миграция с одной платформы на другую требует не только перезаписи, но и полной перестройки любой области, содержащей вложенные блоки транзакций. .

Другие факторы

Являются ли эти соображения, связанные с временными таблицами и транзакциями, единственными моментами, которые необходимо учитывать при миграции с Oracle на SQL Server? Хотя они могут быть самыми важными, определенно есть и другие, у каждого из которых есть свои особенности, о которых стоит рассказать. Ниже приведена остальная часть того, что я считаю наиболее неправильно понятыми темами:

1. Столбцы идентификаторов в SQL Server
2. Последовательности в Oracle
3. Синонимы в Oracle
4. Отфильтрованные индексы
5. Согласованность чтения (только Oracle для SQL Server)
6. Использование инструментов миграции

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

Временные таблицы, переменные таблицы/коллекции и вложенные транзакции: 3 основные проблемы миграции

Я начал с временных таблиц, табличных переменных/коллекций и вложенных транзакций, потому что это наиболее распространенные и очевидные точки отказа в проектах преобразования. Любая нетривиальная система в базе данных Oracle или Microsoft SQL Server, несомненно, будет использовать некоторые из них, и использование этих элементов очень тесно связано со специфическим дизайном поддержки транзакций соответствующими реализациями СУБД.

Читайте во второй части!