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

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

Последовательности в Oracle и столбцы идентификаторов в SQL Server

В сообществе баз данных существует давний разрыв между двумя лагерями: патриотами естественных ключей и сторонниками искусственных (или «суррогатных») ключей.

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

Последовательность Oracle — это первоклассный объект уровня базы данных. Напротив, столбец идентификации SQL Server представляет собой тип столбца, а не объект.

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

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

С другой стороны, когда нескольким процессам требуется доступ к NEXTVAL (следующее возрастающее значение) из последовательности, последовательность становится критическим ресурсом с однократным доступом. Это эффективно сделает все процессы, обращающиеся к нему, строго последовательными, превратив любую многопоточную (одно- или многосерверную) реализацию в однопоточный процесс с длительным временем ожидания и высоким потреблением памяти/низким использованием ЦП.

Такие реализации действительно случаются. Решение этой проблемы состоит в том, чтобы определить рассматриваемый объект последовательности с разумным значением кеша — это означает, что определенный диапазон значений (будь то 100 или 100 тысяч) выбирается в кеше для вызывающего процесса, записанного в словаре данных по мере использования. , и становятся доступными для этого конкретного процесса без необходимости доступа к словарю данных каждый раз, когда NEXTVAL .

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

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

Столбцы идентификаторов в SQL Server

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

IDENTITY — это «дочерний» объект таблицы. Он не имеет доступа к ресурсам за пределами таблицы и гарантированно будет последовательным, если не будет преднамеренно манипулировать. И он предназначен именно для этой задачи, а не для семантической совместимости с Oracle.

Поскольку Oracle реализовал функциональность IDENTITY в версии 12.1, естественно задаться вопросом, как она обходилась без нее раньше, почему она реализована сейчас и почему SQL Server нуждался в ней с самого начала (с самого начала Sybase SQL Server).

Причина в том, что в Oracle всегда была функция ключа идентификации: ROWID с типом данных ROWID или UROWID . Это значение не является числовым ( ROWID и UROWID являются собственными типами данных Oracle) и однозначно идентифицирует запись данных.

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

Если Oracle ROWID так важен, как SQL Server выжил без него все эти годы и выпуски? Используя столбцы IDENTITY в качестве первичных (суррогатных) ключей.

Важно отметить разницу в реализации структуры индексов между Oracle и SQL Server.

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

Все шаблоны проектирования в Oracle, основанные на использовании ROWID (например, дедупликация данных), должны быть реализованы на основе столбцов IDENTITY при миграции на SQL Server.

Хотя переход от использования IDENTITY в SQL Server к использованию IDENTITY в Oracle может привести к функционально правильному коду, это не оптимально, поскольку на стороне Oracle ROWID будет работать намного эффективнее.

То же самое верно и при простом преобразовании синтаксиса SQL для перемещения последовательностей Oracle в SQL Server: код будет работать, но использование IDENTITY является наиболее предпочтительным вариантом с точки зрения простоты кода и производительности.

Отфильтрованные индексы в Microsoft SQL Server

Несколько лет назад Microsoft SQL Server 2008 представил ряд важных функций, которые превратили его в действительно первоклассную корпоративную базу данных. Тот, который не раз спасал мой день, был отфильтрованным индексом.

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

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

Конечно, фильтрованные индексы имеют некоторые ограничения. Они не могут включать столбцы LOB, существуют ограничения на условия, которые могут включать предложения WHERE в сами индексы, и они увеличивают объем хранилища базы данных. Но при условии, что вариант использования соответствует этим параметрам, компромиссы с хранилищем обычно весьма незначительны по сравнению со значительным повышением производительности, которое могут обеспечить отфильтрованные индексы.

Как насчет отфильтрованных индексов в базе данных Oracle?

Позже я оказался в большой команде компании из списка Fortune 500 в качестве разработчика/администратора баз данных в проекте миграции с SQL Server на Oracle. Код, окружающий исходную базу данных — SQL Server 2008 — был плохо реализован, а производительность была скудной, что делало преобразование обязательным: ежедневное задание внутренней синхронизации выполнялось более 23 часов. В ней не было отфильтрованных индексов, но в новой системе — Oracle 11g — я видел несколько случаев, когда отфильтрованные индексы были бы очень полезны. Но в Oracle 11g нет отфильтрованных индексов!

Фильтрованные индексы также не реализованы в последней версии Oracle 18c.

Но наша задача как технических специалистов — максимально использовать то, что у нас есть. Поэтому я реализовал эквивалент отфильтрованных индексов в своей системе Oracle 11g (и тот же метод, который я использовал позже в 12c). Идея основана на том, как Oracle обрабатывает NULL , и может использоваться в любой версии Oracle.

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

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

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

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

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

Как обрабатывать конверсии

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

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

Демистификация проблем миграции с SQL Server на Oracle / Oracle на SQL Server

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

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

* SQL Server 2019 (или «15.x») не существует достаточно долго для широкого внедрения на предприятиях.