Oracle как сравнить две таблицы oracle

Обновлено: 05.07.2024

Как написать SQL, чтобы проверить, что if TableA и TableB that имеют одинаковые первичные ключи, содержат одно и то же значение во всех столбцах.

Это означает, что в этих двух таблицах точно такие же данные.

Вы должны иметь возможность «МИНУС» или «ИСКЛЮЧИТЬ» в зависимости от разновидности SQL, используемого вашей СУБД.

Если запрос не возвращает строк, данные точно такие же.

Отличное предложение. Однако я думаю, что это может не сработать, если tableB имеет дополнительную строку (строки), поэтому вы можете дополнительно сравнить количество строк. В объезд. Это не сработает, если tableA есть лишние строки. Вам понадобится, (A EXCEPT B) INTERSECT (B EXCEPT A) я полагаю, это будет намного менее эффективно, чем стандартное соединение болота. Этот запрос вернет строки с NULLS, если таковые имеются. @Franklin - Нет, он должен возвращать только один набор результатов. Если у вас два, используйте EXCEPT вместо MINUS.

Использование операторов отношения:

Изменение EXCEPT к MINUS для Oracle.

Немного придирчивый момент: вышесказанное зависит от приоритета операторов, который, согласно стандарту SQL, зависит от реализации, поэтому YMMV. Он работает для SQL Server, для которого приоритет:

Выражения в скобках
INTERSECT
EXCEPT и UNION оценивается слева направо.

Для Oracle вам нужно использовать круглые скобки вокруг UNION, круглые скобки вокруг INTERSECT и (как указано) заменить EXCEPT на MINUS. HTH.

У diebuddha есть хороший ответ. В случаях, когда у вас нет МИНУС или ИСКЛЮЧЕНИЕ, один из вариантов - объединить все таблицы между собой, сгруппировать по всем столбцам и убедиться, что всего два:

Я пробовал использовать это (я получил его из блога Джеффа по SQL Server ), но я хотел бы перечислить обе строки из TableA и TableB, чтобы я мог визуально видеть различия в строках. Не могли бы вы объяснить, как это сделать? @ Агент, это отдельный вопрос. Я бы посоветовал перечислить его, чтобы его увидели другие, а не просто комментарий. Выполнено. И готово. Сравнение значений двух таблиц и перечисление разных строк . Надеюсь, я получу отличные результаты. :) Это хорошо работает в ограниченных средах SQL, таких как Visual FoxPro, спасибо! Просто просматриваю это. Стоит отметить, что первичные ключи обеспечивают уникальные записи в таблицах. Это если таблица (или запрос) может иметь повторяющиеся строки, DISTINCT / GROUP BY предлагается для подзапросов в союзе, чтобы обеспечить есть только одна запись в таблице. В противном случае TableA может иметь 2 записи, а TableB может иметь 0 и не удовлетворять условию HAVING.

Вернет все ОДИНАКОВЫЕ ИДЕНТИФИКАЦИИ в обеих таблицах. Чтобы получить различия, измените EXISTS на NOT EXISTS.

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

просто для завершения процесс, сохраненный с использованием метода except для сравнения 2 таблиц и получения результата в той же таблице с 3 состояниями ошибок, таблица ADD, DEL, GAP должна иметь одинаковый PK, вы объявляете 2 таблицы и поля для сравнения 1 или обеих таблиц

Просто используйте как этот ps_TableGap 'tbl1', 'Tbl2', 'fld1, fld2, fld3', 'fld4'fld5'fld6' (необязательно)

Умный подход к использованию NATURAL FULL JOIN для обнаружения одинаковых / разных строк между двумя таблицами.

Пример 1 - флаг статуса:

Пример 2 - фильтрация строк

Дополнение к ответу диетбудды .

Вы можете найти различия двух таблиц, используя комбинацию вставки всего и полного внешнего соединения в Oracle. В sql вы можете извлечь различия с помощью полного внешнего соединения, но кажется, что insert all / first не существует в sql! Следовательно, вместо этого вы должны использовать следующий запрос:

Хотя использование OR в предложении where не рекомендуется и обычно приводит к снижению производительности, вы все равно можете использовать вышеуказанный запрос, если ваши таблицы не являются массивными. Если и есть результат по вышеуказанному запросу, то это как раз различия двух таблиц на основе сравнения полей 1,2,3,4. Для повышения производительности запроса вы также можете фильтровать его по дате (проверьте прокомментированную часть)

Результат нулевой, но источники разные!

У меня была такая же проблема в SQL Server, и я написал этот сценарий T-SQL для автоматизации процесса (на самом деле это упрощенная версия, я записал все различия в единую таблицу для облегчения отчетности).

Обновите MyTable и MyOtherTable именами таблиц, которые вы хотите сравнить.

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

Чтобы сравнить T1 (PK, A, B) и T2 (PK, A, B).

Сначала сравните наборы первичных ключей, чтобы найти недостающие значения ключей с обеих сторон:

Затем перечислите все несоответствия значений:

A и B должны быть одного типа. Вы можете использовать ИНФОРМАЦИОННУЮ СХЕМУ для генерации SELECT. Не забудьте, что COALESCE также включает результаты IS NULL. Вы также можете использовать FULL OUTER JOIN и COALESCE (T1.PK, 0) = COALESCE (T2.PK, 0).

Например, для столбцов типа varchar:

Мы можем сравнить данные из двух таблиц таблиц DB2, используя следующий простой запрос:

Шаг 1: - Выберите все столбцы, которые нам нужно сравнить, из таблицы (T1) схемы (S)

Шаг 2: - Используйте ключевое слово «Минус» для сравнения двух таблиц.

Шаг 3: - Выберите все столбцы, которые нам нужно сравнить, из таблицы (T2) схемы (S)

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

каков наилучший способ различения этих двух таблиц?

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

другим подходом было бы сбросить таблицы в текстовый файл и использовать инструмент diff, такой как WinMerge. При таком подходе вы можете использовать сложный SQL, чтобы сначала превратить таблицы в один и тот же макет.

нет внешнего инструмента. Нет проблем с производительностью union all .

вы можете попробовать использовать операции set: MINUS и INTERSECT

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

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

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

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

вы можете попробовать сравнение данных dbForge для Oracle, a * * бесплатно инструмент GUI для сравнения и синхронизации данных, который может выполнять эти действия по всей базе данных или частично.

записи не должны отображаться.

select * from table1 where table1.col1 in (select table2.col1 from table2)

предполагая, что col1 - это столбец первичного ключа, и это даст все строки table1 соответственно 1.

select * from table1 where table1.col1 not in (select table2.col1 from table2)

надеюсь, что это помогает

с sql oracle 11g+

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

первый - если вы используете Oracle SQL Developer, вы можете запустить описание для обеих таблиц, чтобы сравнить их:

второй - первое решение не может быть идеальным для больших таблиц с большим количеством столбцов. Если вы только хотите увидеть различия в данных между двумя таблицами, тогда, как упоминалось несколькими другими, используя оператор SQL Minus должен выполнить эту работу.

третий - если вы используете Oracle SQL Developer, и вы хотите сравнить структуру таблиц двух таблиц с использованием разных схем, вы можете сделать следующее:

в-четвертых - если таблицы две таблицы, которые вы хотите сравнить, имеют больше столбцов, находятся в та же схема, нет необходимости сравнивать более двух таблиц и непривлекательны для сравнения визуально с помощью команды DESCR вы можете использовать следующее Для сравнения различий в структуре таблицы:

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

Каков наилучший способ разграничения этих двух таблиц?

ОТВЕТЫ

Ответ 1

Нет внешнего инструмента. Нет проблем с производительностью с union all .

Ответ 2

Вы можете попробовать использовать операции набора: MINUS и INTERSECT

Ответ 3

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

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

Ответ 4

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

На самом простом уровне есть "Являются ли данные таблицы точно такими же или нет?", на которые вы можете попытаться ответить простым сравнением счетчиков, прежде чем перейти к чему-то более сложному.

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

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

Ответ 5

Никакие записи не должны отображаться.

Ответ 6

Ответ 7

Вы можете попробовать dbForge Data Compare for Oracle , бесплатный графический интерфейс для сравнения и синхронизации данных, который может выполнять эти действия по всей базе данных или частично.

Ответ 8

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

Первое. Если вы используете Oracle SQL Developer, вы можете запустить описание обеих таблиц, чтобы сравнить их:

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

Третье. Если вы используете Oracle SQL Developer и хотите сравнить структуру таблиц двух таблиц, используя разные схемы, вы можете сделать следующее:

Выберите "Инструменты"
Выберите "База данных Diff"
Выберите "Подключение к источнику"
Выберите "Соединение-получатель"
Выберите "Стандартные типы объектов", которые вы хотите сравнить
Введите "Имя таблицы"
Нажимайте "Далее", пока не дойдете до "Готово"
Нажмите "Готово"
ПРИМЕЧАНИЕ. В шагах 3 и 4 вы должны выбрать различные схемы, в которых существуют объекты, которые вы хотите сравнить.

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

Ответ 9

select * from table1 where table1.col1 in (select table2.col1 from table2)

Предполагая, что col1 является столбцом первичного ключа, и это даст все строки в table1 , соответствующие столбцу table2 .

select * from table1 where table1.col1 not in (select table2.col1 from table2)

Надеюсь, что это поможет

Ответ 10

С sql oracle 11g +

Ответ 11

Я использовал Oracle SQL developer для экспорта таблиц в формат CSV, а затем провел сравнение с помощью WinMerge.

Ответ 12

изменить набор сеансов "_convert_set_to_join" = true;

Другая альтернатива - переписать SQL-запрос вручную [замена оператора минус на подзапрос NOT IN] свидетельствует об улучшении времени выполнения примерно на 30%.

Для сред репликации типичным является разделение таких объектов базы данных Oracle, как таблицы и индексы. Эти объекты известны как разделяемые объекты базы данных, поскольку несколько баз данных совестно используют их между собой. Разделяемые объекты базы данных обычно применяются материализованными представлениями и компонентами Oracle Streams, которые поддерживают копии одних и тех же таблиц и прочих объектов в нескольких базах данных. Среды репликации подобного рода стараются хранить общие объекты базы в разных местах в синхронизированном виде. Однако нередко происходит рассинхронизация таких объектов, и тогда таблица в отличающимся количеством строк и/или разными данными сравнивается c такой же таблицей в другой базе. Подобные расхождения данных, вызванные сетевыми проблемами, ошибками пользователей, изменениями в конфигурации, проблемами обновления материализованных представлений и т. д., могут помешать фиксации изменений в базе данных или успешной передаче их другим базам, которые связаны в общую среду репликации.

В Oracle Database 12c и 11g предусмотрен пакет DBMS_COMPARISON, который позволяет сравнивать объекты в различных базах. Если процесс сравнения показывает существенные расхождения между двумя базами данных, с помощью этого же пакета можно свести данные в обеих базах, так что они становятся идентичными. Сравнивать и сводить можно следующие типы объектов:

таблицы;
представления на одиночных таблицах;
материализованные представления;
синонимы перечисленных трех типов объектов.

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

Сравнение данных на примере таблиц

В следующем примере мы сравним простой разделенный объект базы данных (таблицу employees), который принадлежит схеме пользователя HR. Чтобы наглядно продемонстрировать сведение данных, сначала мы изменим данные в трех строках разделенного объекта (таблице departments) удаленной базы данных. Затем с помощью пакета DBMS_COMPARE мы сравним две таблицы в двух базах, после чего воспользуемся им же для объединения отличий, чтобы две таблицы снова стали синхронизированными.

Единственное требование, которое должно быть удовлетворено при использовании пакета DBMS_COMPARE, состоит в том, чтобы сравниваемые таблицы имели, по крайней мере, один столбец, используемый в качестве индекса, по которому можно идентифицировать запись. Такой индексный столбец должен уникально идентифицировать каждую строку, участвующую в сравнении, в том смысле, что индекс должен быть либо первичным, либо уникальным ключом на столбце, не допускающем NULL-значения. В противном случае пакет не сможет сравнить два объекта. Таблица employee имеет столбец первичного ключа в обеих базах данных, так что здесь все в порядке.

1. Создадим связь первичной базы данных (or11) с вторичной базой (tenner). В примере в качестве владельца связи базы данных выступает пользователь system; это гарантирует наличие достаточных привилегий для выполнения процедур пакета DBMS_COMPARISON, а также для доступа и модификаций таблиц в обеих базах. Удаленная база данных называется tenner, поэтому так же мы назовем и ее связь из первичной базы данных or11.

Следующий шаг — получение расхождений между данными идентичных таблиц в двух базах данных.

2. Во вторичной базе проведем некоторые изменения в таблице hr.employees, чтобы данные отличались от данных той же таблицы из первичной базы:

Обеспечив различие таблиц hr.employees в двух базах, можно запустить процедуру CREATE_COMPARISON для обнаружения этих различий.

3. Выполним сравнение таблицы hr.employees с одноименной таблицей второй базы, запустив процедуру CREATE_COMPARISON, как показано ниже:

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

Функция compare использует Scan ID, равный 4, и печатает предложение “Differences were found” (Различия обнаружены).

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

Столбец current_diff_count из DBA_COMPARISON_SCAN_SUMMARY показывает, что есть три строки, отличающиеся между таблицей hr.employees в базе данных or11 и таблицей hr.employees в базе данных tenner. Различие может быть обусловлено тем, что некоторая строка присутствует в одной базе, но отсутствует в другой, или же одна и та же строка в двух базах содержит разные данные.

Синхронизация таблиц и других данных

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

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

2. Выполните процедуру CONVERGE из пакета DBMS_COMPARISON для синхронизации данных между двумя базами:

В этом примере мы решили заменить данные таблицы hr.employees в удаленной базе данными из таблицы hr.employees локальной базы. Таким образом, в качестве опции сведения используется cmp_converge_local_wins, а это означает, что данные из локальной базы получат преимущество перед данными из удаленной базы. Однако можно было бы выбрать и обратный путь, указав cmp_converge_remote_wins — тогда данные таблицы из удаленной базы заменили бы данные таблицы локальной базы.

Процедура converge может модифицировать или удалить данные из одной базы данных, чтобы синхронизировать данные в обеих базах Oracle. Вывод, печатаемый после завершения процедуры converge, демонстрирует, что две строки в удаленной базы были слиты (merged), потому что они отличались от тех же строк в локальной базе. Слияние в данном случае означает, что строки локальной таблицы заменяют строки в удаленной таблице. Одна строка отражена в Remote Rows Deleted. Это строка, которая была найдена в удаленной базе данных, но отсутствовала в локальной. Поскольку мы решили, что данные удаленной базы должны совпадать с данными базы локальной, процедура converge удаляет строку из удаленной базы данных. Предполагая, что никаких других изменений во время синхронизации не произошло, теперь две таблицы в локальной и удаленной базах данных полностью синхронизированы.

Обратите внимание, что можно также сравнивать и сливать различные типы объектов двух баз данных. Например, можно сравнить и слить таблицу в одной базе с материализованным представлением в другой.

Читайте также: