Блог

В моей базе данных есть две таблицы, это EXP1 и EXP2. Я попытался с помощью приведенного ниже запроса, этот запрос работает, когда обе таблицы имеют одинаковое количество столбцов, но моя таблица EXP1 содержит 1000 столбцов, а EXP2 — 1000 4.

 select *
from 
(
    (select * from exp1
     minus 
     select * from exp2)
    union all
    (select * from exp2
     minus
     select * from exp1)
);
  

ВСТУПЛЕНИЕ: Ниже я показываю, как можно сделать «вручную» то, что инструменты (например, SQL Developer) могут сделать намного быстрее и намного лучше. Мой интерес к этому (и ваш!) двоякий: изучить и использовать некоторые идеи, которые могут помочь во многих других проблемах; и понять, что эти инструменты делают под капотом в первую очередь.

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

Затем предположим, что это сделано. Теперь то, что осталось от двух таблиц, имеет много общих строк, но есть и несколько отличающихся. Строка может существовать в одной таблице, но не в другой, или две строки, по одной из каждой таблицы, могут быть очень похожи, но они могут отличаться только одним или небольшим количеством значений столбца. Логически это все еще одна строка в первой таблице, но не во второй, а другая строка только во второй таблице, но не в первой. Однако, допустим, обе таблицы имеют один и тот же столбец PK — тогда у вас может быть одинаковое значение PK в обеих таблицах, но по крайней мере один из ДРУГИХ столбцов имеет разные значения для этого значения PK в двух таблицах. И вы хотите найти эти различия между двумя таблицами.

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

Когда вы переходите к сравнению строк в двух таблицах на последнем шаге, (A minus B) union all (B minus A) работает, но не очень эффективно. Каждая таблица читается дважды, и minus это дорогостоящий оператор. Более эффективное решение, которое я иллюстрирую ниже, обсуждалось в длинной теме на AskTom несколько лет назад. А именно: соберите все строки из обеих таблиц (с union all ), сгруппируйте по всем столбцам и игнорируйте группы, количество которых равно 2. Это означает строки, которые были найдены в обеих таблицах, поэтому они являются дубликатами в union all ! На самом деле, вы увидите небольшой дополнительный трюк, позволяющий определить, из какой таблицы взяты «недублированные» строки. Добавьте столбец для «имя_таблицы» и в окончательном выборе, после группировки и сохранения групп с count(*) = 1 , выберите max(table_name) . Вам нужна агрегирующая функция (например max() ), потому что вы группируете, но для этих строк каждая группа имеет только одну строку, так что max() на самом деле это просто название таблицы.

Прелесть этого подхода в том, что его можно использовать и для определения общих столбцов! В этом случае мы сравним строки из USER_TAB_COLS представления — мы выбираем имена столбцов, которые отображаются в любой из таблиц, и сохраняем только дублирующие имена столбцов (таким образом, имена столбцов отображаются в обеих таблицах). В этой части решения я также извлекаю column_id , который используется для упорядочивания столбцов. Не волнуйтесь, если вы не знакомы с keep (dense_rank first...) — на самом деле это не так сложно, но и не так важно.

Сначала давайте настроим тестовый пример. Я копирую EMP таблицу из SCOTT схемы в свою собственную схему, реплицирую ее (так что теперь у меня есть две копии с именами EMP1 и EMP2 ) и немного изменяю их. Я удаляю разные столбцы из каждой, я удаляю несколько (разных) строк из каждой и я изменяю одну зарплату в одной таблице. Я не буду показывать результирующие (немного отличающиеся) таблицы, но если вы следите за ними, просто select * из обеих и сравните их, прежде чем продолжить чтение.

Создайте таблицы:

 create table EMP1 as select * from scott.emp;
Table EMP1 created.

select * from EMP1;

EMPNO ENAME      JOB        MGR HIREDATE              SAL   COMM  DEPTNO
----- ---------- --------- ---- ------------------- ----- ------ -------
 7369 SMITH      CLERK     7902 1980-12-17 00:00:00   800             20
 7499 ALLEN      SALESMAN  7698 1981-02-20 00:00:00  1600    300      30
 7521 WARD       SALESMAN  7698 1981-02-22 00:00:00  1250    500      30
 7566 JONES      MANAGER   7839 1981-04-02 00:00:00  2975             20
 7654 MARTIN     SALESMAN  7698 1981-09-28 00:00:00  1250   1400      30
 7698 BLAKE      MANAGER   7839 1981-05-01 00:00:00  2850             30
 7782 CLARK      MANAGER   7839 1981-06-09 00:00:00  2450             10
 7788 SCOTT      ANALYST   7566 1987-04-19 00:00:00  3000             20
 7839 KING       PRESIDENT      1981-11-17 00:00:00  5000             10
 7844 TURNER     SALESMAN  7698 1981-09-08 00:00:00  1500      0      30
 7876 ADAMS      CLERK     7788 1987-05-23 00:00:00  1100             20
 7900 JAMES      CLERK     7698 1981-12-03 00:00:00   950             30
 7902 FORD       ANALYST   7566 1981-12-03 00:00:00  3000             20
 7934 MILLER     CLERK     7782 1982-01-23 00:00:00  1300             10
  

Слегка измените их:

 create table EMP2 as select * from EMP1;
Table EMP2 created.

alter table emp1 drop column hiredate;
Table EMP1 altered.

alter table emp2 drop column comm;
Table EMP2 altered.

delete from EMP1 where ename like 'A%';
2 rows deleted;

delete from EMP2 where sal >= 3000;
3 rows deleted

update EMP2 set sal = 2950 where empno = 7698;
1 row updated

commit;
  

На этом этапе вам не мешало бы select * from EMP1; and select * from EMP2; и сравнить.

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

 select column_name, 
       min(column_id) keep(dense_rank first order by table_name) as col_id
from   user_tab_cols
where  table_name in ('EMP1', 'EMP2')
group by column_name
having count(*) = 2
order by col_id;

COLUMN_NAME COL_ID
----------- ------
EMPNO            1
ENAME            2
JOB              3
MGR              4
SAL              5
DEPTNO           7

 6 rows selected 
  

Отлично, теперь мы можем сравнить две таблицы, но только после того, как мы «спроецируем» их только по общим столбцам.

 select max(table_name) as table_name, EMPNO, ENAME, JOB, MGR, SAL, DEPTNO
from (
       select 'EMP1' as table_name, EMPNO, ENAME, JOB, MGR, SAL, DEPTNO from EMP1
       union all
       select 'EMP2' as table_name, EMPNO, ENAME, JOB, MGR, SAL, DEPTNO from EMP2
     )
group by EMPNO, ENAME, JOB, MGR, SAL, DEPTNO
having count(*) = 1
order by EMPNO, ENAME, JOB, MGR, SAL, DEPTNO, table_name;

TABLE_NAME EMPNO ENAME      JOB          MGR    SAL   DEPTNO
---------- ----- ---------- --------- ------ ------ --------
EMP2        7499 ALLEN      SALESMAN    7698   1600       30
EMP1        7698 BLAKE      MANAGER     7839   2850       30
EMP2        7698 BLAKE      MANAGER     7839   2950       30
EMP1        7788 SCOTT      ANALYST     7566   3000       20
EMP1        7839 KING       PRESIDENT          5000       10
EMP2        7876 ADAMS      CLERK       7788   1100       20
EMP1        7902 FORD       ANALYST     7566   3000       20

 7 rows selected 
  

Результат — это в значительной степени то, что нам было нужно. Обратите внимание на первый столбец, который сообщает нам, откуда берется «непарная» строка; и обратите внимание на БЛЕЙКА, у которого разная зарплата в двух таблицах (и первый столбец помогает нам увидеть, какая зарплата у него в какой таблице).

Пока это выглядит идеально, но что делать, когда у вас 1000 столбцов? Вы могли бы объединить это на C или Java и т.д., Используя результат приведенного выше запроса «общие столбцы», или вы могли бы сделать все это в Oracle с помощью dynamic SQL.

Насколько я знаю, в Oracle нет установленного ограничения на длину текста инструкции SQL; в документации говорится «Ограничение на то, какой длины может быть инструкция SQL, зависит от многих факторов, включая конфигурацию базы данных, дисковое пространство и память» (и, вероятно, от вашей версии Oracle, которую они не упомянули). В любом случае, это будет более 4000 символов, поэтому нам нужно работать с CLOB. В частности, мы не можем использовать listagg() — нам нужен обходной путь. Я использую xmlagg() ниже. Затем в документации говорится, что если вы объединяете текст и хотя бы один операнд имеет значение CLOB, результатом будет CLOB ; если у вас это не сработает, возможно, вам придется обернуть меньшие фрагменты текста внутри to_clob() . Приведенный ниже запрос «dynamic SQL» выдаст полный текст запроса, который я использовал выше; вы просто скопируете его, вставите обратно в свой интерфейс и выполните. Возможно, вам придется удалить заключающие двойные кавычки или что-то подобное, в зависимости от вашего интерфейса и настроек.

First here is how we can create a (potentially very long) string, the list of common column names, which is repeated five times in the final query — just look again at the «final query» we used to compare the two tables above.

 with
     common_cols ( column_name, col_id ) as (
       select column_name, 
              min(column_id) keep(dense_rank first order by table_name) as col_id
       from   user_tab_cols
       where  table_name in ('EMP1', 'EMP2')
       group by column_name
       having count(*) = 2
     ),
     col_string ( str ) as (
       select rtrim(xmlcast(xmlagg(xmlelement(e, column_name, ', ') order by col_id)
                                                    as clob), ', ') from common_cols
     ) 
select * from col_string;

STR
-----------------------------------
EMPNO, ENAME, JOB, MGR, SAL, DEPTNO
  

И, наконец, полный динамический SQL-запрос (результат — это именно тот запрос, который я использовал для сравнения EMP1 и EMP2 для их общих столбцов ранее):

 with
     common_cols ( column_name, col_id ) as (
       select column_name, 
              min(column_id) keep(dense_rank first order by table_name) as col_id
       from   user_tab_cols
       where  table_name in ('EMP1', 'EMP2')
       group by column_name
       having count(*) = 2
     ),
     col_string ( str ) as (
       select rtrim(xmlcast(xmlagg(xmlelement(e, column_name, ', ') order by col_id) 
                                                    as clob), ', ') from common_cols
     )
select 'select max(table_name) as table_name, ' || str                 || chr(10) ||
       'from ('                                                        || chr(10) ||
       '       select ''EMP1'' as table_name, ' || str || ' from EMP1' || chr(10) ||
       '       union all'                                              || chr(10) ||
       '       select ''EMP2'' as table_name, ' || str || ' from EMP2' || chr(10) ||
       '     )'                                                        || chr(10) ||
       'group by ' || str                                              || chr(10) ||
       'having count(*) = 1'                                           || chr(10) ||
       'order by ' || str || ', table_name;'  as comp_sql_str
from   col_string;