SQL-Ex blog

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

Похожие атрибуты оператора Create Table для SQL Server и Oracle

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

Скрытый/невидимый столбец

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

• В SQL Server это доступно только в темпоральных таблицах (Temporal Tables) с указанием HIDDEN на уровне столбца.


• В Oracle это достигается указанием INVISIBLE на уровне столбца для всех типов таблиц, за исключением EXTERNAL, CLUSTER или TEMPORARY.

Шифрованный столбец

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

• В SQL Server это осуществляется с помощью ENCRYPTED WITH, что может быть DETERMINISTIC (всегда производится одно и то же значение, позволяя создавать индекс и выполнять соединения) или RANDOMIZED, при этом доступен только один алгоритм - AEAD_AES_256_CBC_HMAC_SHA_256.


• В Oracle используется ENCRYPT USING, имеются следующие алгоритмы: AES256, AS192, AES128, 3DES168, ARIA128, ARIA192, ARIA256, GOST256 и SEED128. Это позволяет вам задавать разные пароли для каждого столбца, выбрать алгоритм целостности SHA-1 (который снижает производительность и не сжимается) или NOMAC, и позволяет добавить SALT (по умолчанию, если не указано) или NO SALT (всегда производит одно и то же значение, позволяя индексирование и соединения); следует отметить, что это является устаревшим по сравнению с шифрованием табличного пространства.

Виртуальный/вычисляемый столбец

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

• В SQL Server это выполняется с помощью синтаксиса <имя_столбца> AS <вычисляемое_выражение>.


• В Oracle используется <имя_столбца> <тип_данных> GENERATED ALWAYS AS <вычисляемое_выражение> VIRTUAL, и вы можете указать редакцию функции PL/SQL, которая используется для вычислений или которая не должна использоваться.

Столбец Identity

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

• В SQL Server это делает с помощью <имя_столбца> IDENTITY (<начальное_значение>, <приращение>), и вы можете указать NOT FOR REPLICATION, чтобы не инкрементировать значения во время репликации.


• В Oracle это достигается с помощью GENERATED ( ALWAYS | BY DEFAULT ON NULL ) AS IDENTITY START WITH ( <целое> | LIMIT VALUE ) INCREMENT BY <целое> MAXVALUE ( <целое> | NOMAXVALUE ) MINVALUE ( <целое> | NOMINVALUE ) ( CYCLE | NOCYCLE ) ( CACHE <целое> | NOCACHE ) ( ORDER | NOORDER ), некоторые из этих опций те же, что и для последовательностей.

Столбец ROWGUIDCOL или ROWID

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

• В SQL Server это используется в репликации и FileStream заданием ROWGUIDCOL на уровне столбца.


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

Столбец больших объектов

Для хранения большого количества данных.

• В SQL Server это делается с помощью FILESTREAM (сохраняется в файловой системе, требует отдельной файловой группы), FileTable (доступной извне, как если бы она находилась в файловой системе, допускает полнотекстовый и семантический поиск, не допускает файлы с отображением в памяти) и Remote Blob Store (файл сохраняется отдельно от базы данных, хранится только ссылка, позволяет управлять доступом и допускает шифрование). Отметим, что имеются также большие/неограниченного-размера типы полей, но они могу хранить максимум 2 Гб, которые могут быть сохранены в отдельной файловой группе с помощью предложения TEXTIMAGE_ON. Этими типами являются: TEXT, IMAGE, XML, VARCHAR(MAX), VARBINARY(MAX), GEOMETRY и GEOGRAPHY.


• В Oracle это называется LOB и может хранить до 128 Тб данных, может секционироваться и обеспечивается любым из следующих типов данных: BLOB (двоичный), CLOB (символьный), NCLOB (национальный набор символов), BFILE (внешний файл, только на чтение), VARRAY STORE AS LOB (или когда размер превышает 4000), XMLType, ORDAudio, ORDDoc, ORDImage, ORDVideo. Отметим, что тип LONG является устаревшим, хранилище может быть задано как BASICFILE (обратная совместимость) или SECUREFILE (высокая производительность, дедупликация, сжатие, шифрование), а также позволяет задавать кэширование и журнализацию.

Архив воспоминаний/версионная строка

Делает возможным отслеживание истории строк в отдельной таблице для выполнения выполнения запросов "воспоминаний" на заданное время в прошлом.

• В SQL Server это делается с помощью SYSTEM_VERSIONING = ON HISTORY_TABLE = <историческая_таблица>, DATA_CONSISTENCY_CHECK = ( ON | OFF ).


• В Oracle используется FLASHBACK ARCHIVE <архив> RETENTION <целое> YEAR | MONTH | DAY.

Временная валидность / Архив / Строка хранения данных

Позволяет определить период, в течение которого каждая строка считается валидной.

• В SQL Server это выполняется с помощью предложений для столбца GENERATED ALWAYS AS ROW START, GENERATED ALWAYS AS ROW END и PERIOD FOR. Или вы можете выполнить чистку старых или устаревших данных на основе политики с помощью DATA_DELETION = ON FILTER_COLUMN = <имя_столбца> RETENTION_PERIOD = <период_времени>.


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

Внешняя таблица

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

• В SQL Server это делается с помощью CREATE EXTERNAL TABLE <имя> (<определения_столбцов>) WITH (LOCATION=<размещение>, DATA_SOURCE=<источник_данных>, FILE_FORMAT=<формат_файла>, <опции_отклонения>), где LOCATION - это папка при запросе Hadoop или Windows Azure Storage, DATA_SOURCE должен существовать как описано здесь, FILE_FORMAT должен существовать для Hadoop и Windows Azure Storage описано здесь, а опции отклонения - это те опции для Hadoop или Windows Azure Storage, которые описаны здесь. Заметим, что это позволяет подключаться к другим РСУБД по естественным протоколам или с помощью OBDC.


• В Oracle это выполняется с помощью CREATE TABLE <имя> (<определения_столбцов>) ORGANIZATION EXTERNAL (TYPE <тип> DEFAULT DIRECTORY <каталог_по_умолчанию> ACCESS PARAMETERS (<параметры доступа>|USING CLOB <подзапрос>) LOCATION (<размещение>)) REJECT LIMIT <целое>|UNLIMITED, где TYPE может быть ORACLE_LOADER, ORACLE_DATAPUMP, ORACLE_HDFS или ORACLE_HIVE, CLOB используется для вывода параметров доступа и их значений, а LOCATION может быть локальным каталогом, но не обязательно. В Oracle они совместимы с другим спецификациями: in memory, partitioned, parallel, encryption и т.п. Дополнительно к поддержке внешних данных, находящихся в файловых системах и источниках Big Data и форматах типа HDFS и Hive, Oracle поддерживает внешние данные, находящиеся в хранилищах объектов посредством пакета DBMS_CLOUD.

Сжатые таблицы

Для использования хранилища меньшего объема и сокращения операций ввода/вывода.

• В SQL Server это достигается с помощью DATA_COMPRESSION = ROW для нескольких повторяющихся значений или PAGE для повторяющихся значений по столбец + шаблоны на всей странице, вы можете делать это по-разному для каждой секции.


• В Oracle для этого используется ROW STORE COMPRESS BASIC (при вставке непосредственного пути) или ADVANCED (при всех операциях DML).

Сжатая таблица с поколоночным хранением

Чтобы сэкономить память для таблиц в памяти, когда данные не часто обновляются.

• В SQL Server это выполняется с помощью DATA_COMPRESSION = ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE (более сильное сжатие, но занимает больше времени). Вы можете выбирать секции и задавать задержку (для использования всего доступного пространства в закрытых дельта-группах строк).


• В Oracle для этого используется COLUMN STORE COMPRESS FOR QUERY или ARCHIVE (более сильное сжатие, больше использования ЦП и больше времени) LOW или HIGH (большее сжатие) ROW LEVEL LOCKING (для производительности). И вы можете выбирать секции.

Таблицы в памяти

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

• В SQL Server для этого используется MEMORY_OPTIMIZED=ON DURABILITY=SCHEMA_ONLY или SCHEMA_AND_DATA.


• В Oracle для этого служит INMEMORY. Вы можете дифференцировать приоритет наполнения данными среди разных таблиц с помощью PRIORITY ( NONE | LOW | MEDIUM | HIGH | CRITICAL ), и можете распределять данные по экземплярам RAC с помощью DISTRIBUTE ( AUTO | BY ROWID RANGE | BY PARTITION | BY SUBPARTITION ), и можете дублировать данные по экземплярам RAC с помощью DUPLICATE (на одном дополнительном экземпляре) или DUPLICATE ALL (на всех экземплярах).

Временная таблица

Это временная таблица, которая видима только в текущей сессии.

• В SQL Server вам достаточно только указать префикс символа номера - #<имя_таблицы>.


• В Oracle вы указываете CREATE PRIVATE TEMPORARY TABLE, и вы можете указать, должна ли таблица удаляться в конце транзакции с помощью ON COMMIT DROP DEFINITION или в конце сессии с помощью ON COMMIT PRESERVE DEFINITION.

Глобальная временная таблица

Это временная таблица, видимая во всех сессиях.

• В SQL Server задается префиксом в виде двойного значка номера - ##<имя_таблицы>. Данные могут быть доступны в любой сессии, а таблица удаляется, когда сессия, с которой была создана таблица, завершается и завершается последний активный оператор T-SQL, ссылающийся на эту таблицу.


• В Oracle вы задаете CREATE GLOBAL TEMPORARY TABLE, но разделяется только определение таблицы (данные принадлежат только своей сессии), и вы можете указать, удаляются ли строки в конце транзакции с помощью ON COMMIT DELETE ROWS или же в конце сессии с помощью ON COMMIT PRESERVE ROWS.

Секцонная таблица

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

• В SQL Server это делается с помощью ON <схема_разбиения> ( <столбец_разбиения> ), что требует схемы разбиения и функции разбиения.


• В Oracle это делается с помощью PARTITION(SET) BY ( RANGE | (CONSISTENT) HASH | LIST | REFERENCE | SYSTEM ) SUBPARTITION BY ( RANGE | HASH | LIST ) ( INTERNAL | EXTERNAL ) OVERFLOW. Каждый тип имеет разние характеристики и применение.

Пространственная таблица

Для обслуживания геометрических географических координат и выполнения операций над ними.

• В SQL Server для этого применяюются типы данных GEOMETRY и GEOGRAPHY.


• В Oracle используется тип данных SDO_GEOMETRY.

Только в Oracle

Табличное пространство

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

Группа табличных пространств

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

Столбец с ограниченной областью

Для использования меньшего пространства путем хранения указателей или ссылок на существующую строку, а не её значения. Для этого используется SCOPE IS, которое может включать WITH ROWID для улучшения производительности запросов.

Столбцы сопоставления зон

Зоной (zone) является набор непрерывных блоков данных на диске, в которой отслеживается минимальное и максимальное значение столбца (столбцов) в зоне. Используется для сокращения операций ввода/вывода и улучшения производительности посредством устранения карты зоны. Для применения используется WITH MATERIALIZED ZONEMAP.

Столбцы с кластеризацией атрибутов

Для кластеризации данных из нескольких таблиц в непосредственной физической близости для уменьшения операций ввода/вывода и улучшеня производительности. Выполняется с помощью CLUSTERING <таблица1> JOIN <таблица2> ON <условие_соединения> BY LINEAR | INTERLEAVED ORDER <столбец> YES | NO ON LOAD YES | NO ON DATA MOVEMENT WITH | WITHOUT MATERIALIZED ZONEMAP <зона>, где INTERLEAVED используется для многомерного многостолбцового z-упорядочивания, ON LOAD - для выполнения во время последовательной или параллельной вставки или слияния по прямому пути, ON DATA MOVEMENT - для выполнения во время обслуживания переопределения или секционирования.

Заменяемый тип столбца

Чтобы указать, что объектно-ориентированные столбцы или атрибуты в той же иерархии являются заменяемыми друг для друга (полиморфизм). Это осуществляется с помощью ELEMENT IS OF TYPE ( ONLY <тип> ) (NOT) SUBSTITUTABLE AT ALL LEVELS.

Разделяемая таблица

Для разбиения больших таблиц на блоки данных или секции, которые размещаются на отдельных серверах, распределяя нагрузку на ресурсы и улучшая пропускную способность запросов и время отклика. Доступно только в Azure SQL Server, но в Oracle вы указываете SHARDED и можете сделать выбор между RANGE или LIST разделением, дублировать (DUPLICATE) данные во всех блоках, решить, какие таблицы связаны друг с другом через PARENT, уменьшая количество соединений нескольких блоков, и группировать таблицы из различных табличных пространств с помощью TABLESPACE SET.

Таблица блокчейн

Для создания защищенных от несанкционированного доступа таблиц. Это доступно только в Azure SQL Server для таблиц LEDGER, но в Oracle вы указывате BLOCKCHAIN и можете задать IMMUTABLE, чтобы сделать ее свободной, только для вставки.

Общая таблица приложений

Для обмена данными между родительской базой данных и подчиненными базами данных. Вы указываете SHARING и можете решить что разделять: METADATA, DATA или EXTENDED DATA (каждая PDB может иметь свои собственные данные, отделенные от данных CDB).

Таблица, оптимизированная для памяти

Отличается от таблиц в памяти (in-memory). Она хранит только первичный ключ в памяти как хэш-индекс (оптимизированный для памяти пул), чтобы не ыполнять ввод/вывод и избежать конкуренции за ресурсы. Достигается с помощью MEMOPTIMIZE FOR READ | WRITE, где READ используется для быстрого поиска, а WRITE - для быстрой вставки.

Сжатая в памяти таблица (in-memory)

Для оптимизации памяти и сокращения времени выполнения для таблиц в памяти. Это выполняется при помощи MEMCOMPRESS FOR DML (низкая степень сжатия) | QUERY (лучшая производительность) | CAPACITY (лучшая степень сжатия) LOW | HIGH.

Таблица управления жизненным циклом информации (ILM)

Для автоматизации сжатия устаревших данных, перемещения данных между слоями хранения и изменения свойств хранения в памяти. В SQL Server вы можете только архивировать холодные данные в Azure (растягивание таблицы) с помощью REMOTE_DATA_ARCHIVE=ON MIGRATION_STATE=OUTBOUND|PAUSED. В Oracle сжатие достигается указанием его, как и для других типов таблиц, и включая ( SEGMENT | GROUP | ROW ) AFTER <период_времени> OF ( NO ACCESS | NO MODIFICATION | CREATION ), многоуровневость достигается с помощью TIER TO <табличное_пространство> (READ ONLY) ( SEGMENT | GROUP ) AFTER <период_времени> OF ( NO ACCESS | NO MODIFICATION | CREATION ), а для памяти - с помощью указания ( SET | MODIFY | NO ) INMEMORY <свойства_в_памяти> AFTER <период_времени> OF ( NO ACCESS | NO MODIFICATION | CREATION ).

Кластеризованные таблицы

Для хранения вместе связанных строк из нескольких таблиц, которые нечасто обновляются и не полностью сканируются, уменьшая операции ввода/вывода, время и размер хранилища. Выполняется с помощью CLUSTER <имя_кластера> ( столбец (столбцы) ) SORT.

Кэшрованная таблица

Для указания того, как хранить блоки в буферном кэше, за исключением непосредственного чтения или параллельного сканирования таблицы. Выполняется с помощью CACHE (частый доступ для небольших поисковых таблиц), NOCACHE (нечастый доступ, для LOB указывает, что он не кэшируется) или CACHE READS (только для LOB, кэширует на чтение, но не на запись).

Таблица/секция/подсекция только на чтение

Осуществялется с помощью READ ONLY на уровне таблицы/серции/подсекции.

Параллельная таблица

Для определения параллельного создания таблицы и степени параллелизма по умолчанию для запросов и операторов DML INSERT, UPDATE, DELETE и MERGE на таблице. Выполняется с помощью PARALLEL, который выбирает степерь параллелизма, равную числу доступных процессоров на всех участвующх экземплярах, умноженное на значение параметра инициализации PARALLEL_THREADS_PER_CPU.

Таблица отслеживания зависимостей на уровне строк

Для того, чтобы каждая строка имела 6-байтовый системный номер изменений (SCN), больший или равный времени фиксации последней транзакции, которая изменяла строку. Это улучшает параллельное распространение в реплицируемой среде, но запрещает сжатие. Достигается с помощью ROWDEPENDENCIES.

Таблица перемещений строк

Позволяет строкам перемещаться при сжатии или обслуживании секционирования, но делает недоступными статичные ROWID. Это достигается с помощью ENABLE ROW MOVEMENT.

Таблица логической репликации

Для дополнительной журнализации, которая автоматически захватывается GoldenGate для репликации. Достигается с помощью ENABLE LOGICAL REPLICATION ( ALL KEYS | ALLOW NOVALIDATE KEYS ).

Объектная таблица

Объектно-ориентированная таблица, базирующаяся на типе объекта, а не на коллекции столбцов. Выполняется с помощью CREATE TABLE OF <тип_объекта> ( <свойства_объекта> ), где столбцы соответствуют атрибутам <типа_объекта>, а каждая строка является объектом с уникальным идентификатором объекта OBJECT_ID, генерируемым системой.

Таблица XML

Создается с помощью CREATE TABLE OF XMLTYPE ( <свойства_объекта> ) ( BINARY XML | CLOB | OBJECT RELATIONAL ) XMLSCHEMA <схема> ELEMENT <элемент>, где CLOB не рекомендуется, а OBJECT RELATIONAL позволяет определить индексы и улучшить производительность запросов.

Вложенная таблица

Для связи с подчиненными элементами данных с базовой таблицей, используя ИД объекта (указатель). Создается с помощью CREATE TABLE <имя> ( <столбцы> ) NESTED TABLE ( <вложенный_элемент> | COLUMN_VALUE ) ( LOCAL | GLOBAL ) STORE AS <таблица_хранения> RETURN AS ( LOCATOR | VALUE ), где LOCAL разбивает вложенную таблицу на разделы с базовой таблицей, <таблица_хранения> - там, где будут находиться данные (не могут быть запрошены непосредственно), VALUE возвращает копию таблицы, а LOCATOR возвращает локатор коллекции.

Только в SQL Server

Разреженный столбец

Для оптимизации хранения NULL-значений в столбце. Выполняется с помощью SPARSE в определении столбца. Вы можете также определить XML COLUMN_SET FOR ALL_SPARSE_COLUMNS для улучшения производительности при работе с многочисленными разреженными столбцами.

Маскированный столбец

Чтобы скрыть содержимое столбца. Выполняется с помощью MASKED WITH ( FUNCTION = <функция_маски> ), может быть по умолчанию, электронной почтой, частичной или случайной.

Сохраняемый столбец

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

Графовая таблица

Для управленя коллекцией узлов (вершин) и граней (связей), допуская сопоставление с образцом и навигацию с помощью запросов. В SQL Server создается с помощью CREATE TABLE … AS ( NODE | EDGE ). Запросы имеют новый оператор MATCH, а также имеется функция для SHORTEST_PATH (кратчайшего пути). В Oracle имеется отдельный установщик для Graph Server and Graph Client, база данных создается под новой схемой, а запросы должны выполняться на Java, используя язык PGQL.

Заключение

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