|
|
|||||||||||||||||||||||||||||
|
Изменение соединенияИсточник: ln
Эта статья посвящена "хитрым" операторам UPDATE, изменяющим данные в одной таблице на основе данных из другой. Попутно обсуждаются причины возникновения ошибки ORA-01779 при выполнении таких действий путем изменения соединения. По мотивам очередного ответа Тома Кайта. Оператор UPDATE и NULL-значенияТом, При изменении столбца с помощью оператора update, значение некоторых записей (которые не надо менять) изменяется на NULL. Я использую следующий оператор: update table name B set columnname = ( select value from lookup O where B.keyname = O.keyname and O.Othercolumn = Other_value); В результате выполняются все необходимые изменения, но изменяются и записи, которые не надо менять: они получают значения Null. Можно ли этого избежать, поскольку нам надо часто изменять записи, но не все сразу. Нет ли способа изменить только те записи, которые нужно, и не сбросить значения в других записях в Null? Ответ Тома КайтаЕсть как минимум 2 способа правильно выполнить такого рода коррелированное изменение. Я покажу свой любимый метод (изменение соединения) и другой метод, который будет работать если нельзя задать ограничение уникальности по LOOKUP(keyname) (что необходимо для успешного изменения соединения). Рассмотрим следующие тестовые таблицы: scott@ORA734.WORLD> create table name 2 ( keyname int, 3 columnName varchar2(25) 4 ) 5 / Table created. scott@ORA734.WORLD> create table lookup 2 ( keyname int PRIMARY KEY, 3 value varchar2(25), 4 otherColumn int 5 ) 6 / Table created. scott@ORA734.WORLD> insert into name values ( 100, 'Original Data' ); 1 row created. scott@ORA734.WORLD> insert into name values ( 200, 'Original Data' ); 1 row created. scott@ORA734.WORLD> insert into lookup values ( 100, 'New Data', 1 ); 1 row created. scott@ORA734.WORLD> commit; Commit complete. А вот параметр "other_value", который вы используете в своем операторе update... scott@ORA734.WORLD> variable other_value number scott@ORA734.WORLD> exec :other_value := 1 PL/SQL procedure successfully completed. scott@ORA734.WORLD> select * from name; KEYNAME COLUMNNAME ---------- ------------------------- 100 Original Data 200 Original Data Далее мы изменяем соединение. Можно изменять значение столбцов только в одной из таблиц, а другие таблицы, которые мы не изменяем, должны быть "защищены ключом" - мы должны иметь гарантию, что при соединении NAME со второй таблицей будет возвращено не более одной записи. Для этого столбец keyname в таблице LOOKUP должен быть либо первичным ключом, либо быть связанным ограничением уникальности... scott@ORA734.WORLD> update 2 ( select columnName, value 3 from name, lookup 4 where name.keyname = lookup.keyname 5 and lookup.otherColumn = :other_value ) 6 set columnName = value 7 / 1 row updated. scott@ORA734.WORLD> select * from name; KEYNAME COLUMNNAME ---------- ------------------------- 100 New Data 200 Original Data Обратите внимание, изменилась только интересующая нас строка - остальные данные не затронуты... scott@ORA734.WORLD> rollback; Rollback complete. scott@ORA734.WORLD> select * from name; KEYNAME COLUMNNAME ---------- ------------------------- 100 Original Data 200 Original Data Следующий способ сработает безо всяких ограничений - не нужно первичного ключа млм ограничения уникальности для таблицы lookup (но лучше точно знать, что подзапрос вернет 0 или 1 запись!). Оператор очень похож на ваш update, но имеет конструкцию where, чтобы изменялись только строки, для которых есть соответствия... scott@ORA734.WORLD> update name 2 set columnName = ( select value 3 from lookup 4 where lookup.keyname = name.keyname 5 and otherColumn = :other_value ) 6 where exists ( select value 7 from lookup 8 where lookup.keyname = name.keyname 9 and otherColumn = :other_value ) 10 / 1 row updated. scott@ORA734.WORLD> select * from name; KEYNAME COLUMNNAME ---------- ------------------------- 100 New Data 200 Original Data А можно ли добиться того же для нескольких столбцов?Том, Мне не удалось сделать следующее. Я создал две таблицы t и t1 с помощью select * from user_objects. В таблицах t и t1 - по 117 записей. Затем я взял один object_name(PRIMARY_SALESFORE) в таблице t и изменил три записи - object_type, object_id и created. ADHOC@VOYAGER> select t.object_id,t1.object_id,t.object_type,t1.object_type, 2 t.created,t1.created 3 from t,t1 4 where t1.object_name=t.object_name 5 and t.object_name='PRIMARY_SALESFORCE'; OBJECT_ID OBJECT_ID OBJECT_TYPE OBJECT_TYPE CREATED CREATED --------- --------- ------------- ------------- --------- --------- 2222 17927 ORATAB TABLE 02-APR-02 01-APR-02 Теперь при попытке изменить эти три поля в таблице t на основе t1 я получаю следующее сообщение об ошибке. Что я делаю не так? 1 update 2 (select t.object_id,t1.object_id,t.object_type,t1.object_type, 3 t.created,t1.created 4 from t,t1 5 where t1.object_name=t.object_name 6 and t.object_name='PRIMARY_SALESFORCE') 7 set t.object_id=t1.object_id, 8 t.object_type=t1.object_type, 9* t.created=t1.created ADHOC@VOYAGER> / update * ERROR at line 1: ORA-00904: invalid column name При попытке изменить всего один столбец в T я тоже получаю сообщение об ошибке: ADHOC@VOYAGER> update 2 (select t.object_id toid,t1.object_id t1oid 3 from t,t1 4 where t.object_name=t1.object_name 5 and upper(t.object_name)='PRIMARY_SALESFORCE') 6 set toid=t1oid; update * ERROR at line 1: ORA-01779: cannot modify a column which maps to a non key-preserved table Ответ Тома КайтаТаблицы T и T1 "невидимы" за пределами подставляемого представления (inline view). Вы это исправили, добавив псевдонимы toid, t1oid. Что касается второй ошибки, пожалуйста, перечитайте представленный выше ответ. НАДО, чтобы столбец object_id таблицы T1 был связан ограничением первичного ключа/уникальности, чтобы каждая строка таблицы T соединялась НЕ БОЛЕЕ, чем с одной строкой таблицы T1. Пусть в таблице T имеется: OBJECT_NAME OBJECT_ID -------------- ---------------- ABC xxxx А в таблице T1: OBJECT_NAME OBJECT_ID ---------------- ------------------ ABC 123 ABC 456 что должно быть в результате: ADHOC@VOYAGER> update 2 (select t.object_id toid,t1.object_id t1oid 3 from t,t1 4 where t.object_name=t1.object_name 5 and upper(t.object_name)='PRIMARY_SALESFORCE') 6 set toid=t1oid; надо ли при соединении брать 123 или 456, и в каких случаях? Поскольку результат этого изменения на 100% НЕОДНОЗНАЧНЫ, мы его не допускаем. Необходимо гарантировать защиту ключом с помощью ограничения первичного ключа/уникальности. Соответствующий индекс в любом случае нужен из соображений производительности. Комментарий читателя от 2 апреля 2002 годаСпасибо, Том. Я знал, что если еще раз (внимательно) перечитаю твой ответ, то смогу найти ошибку в SQL-операторе. Но, в любом случае, не мог бы ты помочь мне добиться желанной цели - изменить три столбца из другой таблицы, имеющий один совпадающий столбец (object_name), уникальный, но не являющийся первичным ключом. Ответ Тома Кайтаupdate t set ( f1, f2, f3 ) = ( select a, b, c from t2 where t2.some_field = t.some_field ) where exists ( select a, b, c from t2 where t2.some_field = t.some_field ) / Этот запрос позволит решить вашу проблему. Если нельзя задать ограничение уникальности (которое нужно ТЕОРЕТИЧЕСКИ, даже если не потребуется практически - если значения в столбце some_field таблицы t2 окажутся НЕ уникальными, представленный запрос закончится сообщением об ошибке "подзапрос вернул более одной строки", потому что оператор update будет НЕОДНОЗНАЧНЫМ) Хороший прием. Комментарий читателя от 9 апреля 2002 годаСпасибо за прекрасный сайт. Я пытаюсь изменить таблицу на основе определенной информации в той же таблице. Запрос выглядит следующим образом: DECLARE CURSOR c1 IS SELECT ROWID FROM siebel.s_synd_data where WHERE ROW_ID LIKE 'DDM%' AND DATA_SRC_CD = 'DECPRF' and POSITION_ID IS NULL; begin for cur in c1 loop UPDATE SIEBEL.S_SYND_DATA A SET A.POSITION_ID = (SELECT B.ROW_ID FROM SIEBEL.S_POSTN B, SIEBEL.S_ASGN_GRP C WHERE A.TERR_ID = C.ROW_ID AND SUBSTR(C.NAME, 1, 5) // '00D' = B.NAME) where rowid=cur.rowid; if mod(c1%rowcount,50000)=0 then commit; end if; end loop; commit; end ; Предполагается, что условию WHERE курсора C1 будет удовлетворять 9 миллионов строк. Мой АБД говорит, что транзакция будет зафиксирована только после изменения 9 миллионов строк, и мне надо фиксировать ее после каждых 50000 - правильно ли это? Что можно сделать, чтобы это изменение выполнялось быстрее - я знаю, что ты ненавидишь такие вопросы, если за ними не стоят определенные критерии, но что, если я создам индекс (по функции)? Ответ Тома КайтаЯ лично делал бы так: update s_synd_data a -- извините, я не могу заставить себя набрать sie.... set a.position_id = ( select b.row_id from s_postn b, s_asng_grp c where c.row_id = a.terr_id and b.name = substr(c.name,1,5) // '00D' ) where row_id like 'DDM%' and data_src_cd = 'DECPRF' and position_id is null; Если бы по столбцу position_id был индекс, я бы его удалил, выполнил изменение и пересоздал индекс без возможности восстановления и с распараллеливанием. Прежде чем вы скажете: "но там же 9 миллионов строк - надо фиксировать по частям", я отвечу - ни в коем случае. Проверьте только, что выделено достаточно сегментов отката (иначе, при использовании вашего подхода, РАНО ИЛИ ПОЗДНО, вы получите сообщение об ошибке ORA-01555 - поищите на сайте обсуждения по слову 01555 или 1555 и поймете, что я имею ввиду). Потребуется не так уж много места в сегментах отката, как может показаться. Процедурный код, вроде вашего:
В своей системе я только что изменил 1099008 строк: ops$tkyte@ORA817DEV.US.ORACLE.COM> update big_table set owner = lower(owner); Elapsed: 00:08:38.66 ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction; USED_UBLK ---------- 13002 Elapsed: 00:00:00.01 ops$tkyte@ORA817DEV.US.ORACLE.COM> select 13002*8/1024 from dual; 13002*8/1024 ------------ 101.578125 Elapsed: 00:00:00.01 ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from big_table; COUNT(*) ---------- 1099008 Elapsed: 00:00:02.41 для этого потребовалось около 100 Мбайт в сегменте отката. Вам следует ожидать порядка гигабайта. Я бы просто убедился (с помощью АБД), что столько места есть, использовал бы оператор set transaction use rollback segment при необходимости (можно просто создать временный сегмент для этого оператора update в каталоге /tmp, например, и потом его удалить). Выполните update. Этого достаточно. Меньше думать, меньше делать, меньше вероятность ошибки... Комментарий читателя от 30 апреля 2002 годаУважаемый Том... В какой версии Oracle появилась возможность выполнять "изменение соединения"? Я всегда использовал для таких запросов второй вариант, с конструкцией EXISTS. Всегда казалось смешным, что приходится один и тот же подзапрос вводить дважды. Тебе надо написать книжку о хитрых приемах в Oracle SQL. Ответ Тома КайтаУверен, что эта возможность появилась в версии 7.3 ops$tkyte@ORA734.WORLD> create table t1 ( x int primary key, y int ); Table created. ops$tkyte@ORA734.WORLD> create table t2 ( x int, y int ); Table created. ops$tkyte@ORA734.WORLD> insert into t1 values ( 1, 1 ); 1 row created. ops$tkyte@ORA734.WORLD> insert into t1 values ( 2, 2 ); 1 row created. ops$tkyte@ORA734.WORLD> insert into t2 values ( 2, null ); 1 row created. ops$tkyte@ORA734.WORLD> insert into t2 values ( 3, null ); 1 row created. ops$tkyte@ORA734.WORLD> update ( select t1.y t1_y, t2.y t2_y 2 from t1, t2 3 where t1.x = t2.x ) 4 set t2_y = t1_y 5 / 1 row updated. ops$tkyte@ORA734.WORLD> select * from t2; X Y ---------- ---------- 2 2 3 Параллельные операторы ЯМД и изменение представления с соединениемADHOC@VOYAGER> update 2 (select t.object_id toid,t1.object_id t1oid 3 from t,t1 4 where t.object_name=t1.object_name 5 and upper(t.object_name)='PRIMARY_SALESFORCE') 6 set toid=t1oid; В представленном выше запросе, который демонстрирует изменение представления с соединением, меня интересует, выполняется ли это изменение параллельно, если таблица t фрагментирована и выполнен оператор alter session enable parallel dml. Я использую аналогичный оператор update и выяснил, что лучшим планом выполнения оператора select будет полный просмотр обеих таблиц и соедитнение хешированием. Ответ Тома КайтаЕсли удовлетворены все остальные критерии (включено распараллеливание, таблицы допускают "распараллеливание") - должно выполняться параллельно. Изменение будет выполняться по фрагментам таблицы T, с полным просмотром каждого фрагмента T и соединением его с таблицей T1 после фильтрации по условию upper(t.object_name). Один из способов убедиться в этом - выполнить изменение, а затем - оператор select * from t where rownum = 1; Если этот оператор сработает, изменение не распараллеливалось, а если нет - было выполнено параллельно. Выполните Commit и сможете выбирать данные. Можно ли это сделать, если задействовано более двух таблиц?Я делал это, когда в соединении задействовано две таблицы. Можно ли это сделать, если необходимо изменить столбец таблицы A в соответствии со значениями столбца в таблице C. Таблицы связаны так: A -> B -> C, где A является главной для всех, а B - главной для C? Ответ Тома КайтаАй-я-яй, такой простой пример - вы же все уже указали - и не попытаться сделать... Ладно. Ответ - да, если соблюдается "защита ключом", другими словами, каждая строка таблицы A появится в результате соединения НЕ БОЛЕЕ ОДНОГО РАЗА. В противном случае - нет. Рассмотрим простой пример: ops$tkyte@ORA920.LOCALHOSTgt; create table a ( x int primary key, y int ); Table created. ops$tkyte@ORA920.LOCALHOST> create table b ( x references a primary key ); Table created. ops$tkyte@ORA920.LOCALHOST> create table c ( x references b primary key, y int ); Table created. ops$tkyte@ORA920.LOCALHOST> insert into a values ( 1, null ); 1 row created. ops$tkyte@ORA920.LOCALHOST> insert into a values ( 2, null ); 1 row created. ops$tkyte@ORA920.LOCALHOST> insert into b values ( 1 ); 1 row created. ops$tkyte@ORA920.LOCALHOST> insert into c values ( 1, 100 ); 1 row created. ops$tkyte@ORA920.LOCALHOST> select * from a; X Y ---------- ---------- 1 2 ops$tkyte@ORA920.LOCALHOST> update ( select a.y a_y, c.y c_y 2 from a, b, c 3 where a.x = b.x and b.x = c.x ) 4 set a_y = c_y; 1 row updated. ops$tkyte@ORA920.LOCALHOST> select * from a; X Y ---------- ---------- 1 100 2 Блокировки. Комментарий читателя от 2 октября 2002 годаТом, В случае: update ( select columnName, value from name, lookup where name.keyname = lookup.keyname and lookup.otherColumn = :other_value ) set columnName = value / будут ли обе таблицы, name и lookup, заблокированы по ходу изменения? Ответ Тома Кайтаops$tkyte@ORA920.LOCALHOST> alter table emp add dname varchar2(30); Table altered. ops$tkyte@ORA920.LOCALHOST> update ( select emp.dname edname, dept.dname ddname 2 from emp, dept 3 where emp.deptno = dept.deptno ) 4 set edname = ddname; 14 rows updated. ops$tkyte@ORA920.LOCALHOST> select type, id1, (select object_name from user_objects where object_id = id1) oname 2 from v$lock 3 where sid = (select sid from v$mystat where rownum=1) 4 and type = 'TM' 5 / TY ID1 ONAME -- ---------- -------------------- TM 31715 EMP Блокируются только строки таблицы, которую вы изменяете. Комментарий читателя от 29 января 2003 годаscott@ORA734.WORLD> update 2 ( select columnName, value 3 from name, lookup 4 where name.keyname = lookup.keyname 5 and lookup.otherColumn = :other_value ) 6 set columnName = value 7 / 1 row updated. scott@ORA734.WORLD> update name 2 set columnName = ( select value 3 from lookup 4 where lookup.keyname = name.keyname 5 and otherColumn = :other_value ) 6 where exists ( select value 7 from lookup 8 where lookup.keyname = name.keyname 9 and otherColumn = :other_value ) 10 / 1 row updated. Том, какой из этих двух запросов лучше с точки зрения производительности? Я предполагаю, что первый, но сомневаюсь. Если я ошибаюсь, то почему? Ответ Тома КайтаОба плохи... Нет, первый лучше Хотя, постойте, второй лучше Оба могут работать быстро, я думаю... Их производительность будет зависеть от используемого оптимизатора, актуальности статистической информации, и т.п. В общем случае, если "NAME" - очень маленькая таблица, а "LOOKUP" - очень большая, (но проиндексированная по keyname/othercolumn) - оператор с where exists вполне можно использовать. Но обычно лучше изменять соединение. Изменение главной таблицы на основе подчиненной (подчиненная - с составным ключом)Не мог бы ты помочь мне сделать то, что я пытаюсь - изменить 2 или более столбца главной таблицы на основе данных в подчиненной, у которой первичный ключ - составной. Пример: Таблица: Food (food_id number primary key food_name varchar2(100) calorie number fibre number total_fat number ) Пример данных в таблице Food: Food_id food_name calorie fibre total_fat 1 apple null null null 2 beer null null null Таблица: Food_Portion (food_id number portion_id number qty number weight number calorie number fibre number total_fat number ) food_id + portion_id - первичный ключ Пример данных в таблице Food_Portion: Food_id Portion_ID qty weight calorie fibre total_fat 1 1 1 10 10 3 3 1 2 2 15 20 5 2 1 3 100 100 60 10 9 2 1 1 10 5 0 0 2 2 .5 6 4 0 0 2 3 100 100 40 0 10 В результате изменения надо установить значения столбцов calorie, fibre и total_fat в таблице food_table равными значениям в таблице food_portion, где food_portion.qty = 100 и food_portion.weight = 100 Гарантируется, что для каждого food_id в таблице food_portion будет только одна строка с qty = 100 и weight = 100 (т.е. оператор select food_id from food_portion where qty = 100 and weight = 100 вернет ровно одну строку для каждого значения food_id) Я выполнял следующий оператор update: update ( select a1, b1 from a, b where a.id = b.id ) set a1 = b1 (this one I haven't tried) Но получил сообщение об ошибке 'ORA-01779: cannot modify a column which maps to a non key-preserved table' Потом я попробовал выполнить: update t set ( f1, f2, f3 ) = ( select a, b, c from t2 where t2.some_field = t.some_field ) where exists ( select a, b, c from t2 where t2.some_field = t.some_field ) update a set a1 = ( select b1 from b where b.id = a.id ) where a.id in ( select id from b ) И оба оператора вернули сообщение об ошибке ORA-01427: single-row subquery returns more than one row Меня интересует, можно ли выполнить такое изменение, не используя PL/SQL? Ответ Тома КайтаЕсли вы указываете: where t2.some_field = t.some_field ) И получаете: ORA-01427: single-row subquery returns more than one row Значит, предположение о единственности строки для каждого food_id не оправдалось. Но, в любом случае, если запрос: select food_id from food_portion where qty = 100 and weight = 100 group by food_id having count(*) > 1; возвращает НОЛЬ строк (нет дублирующихся значений!), то: ops$tkyte@ORA817DEV> update food 2 set (calorie,fibre,total_fat) = 3 (select calorie,fibre,total_fat 4 from food_portion fp 5 where fp.food_id = food.food_id 6 and fp.qty = 100 7 and fp.weight = 100 ) 8 where exists 9 (select calorie,fibre,total_fat 10 from food_portion fp 11 where fp.food_id = food.food_id 12 and fp.qty = 100 13 and fp.weight = 100 ) 14 / Выполняет соответствующее изменение. Очень полезный примерПосле того, как увидел способ изменения соединения, обнаружил, что он хорошо подходит для изменения одного источника данных с просмотром предварительных и окончательных значений: scott@MSORA920>create table dept_demo as select * from dept; Table created. scott@MSORA920>SELECT deptno, dname, INITCAP(dname) Iname, loc, INITCAP(loc) Iloc FROM dept_demo; DEPTNO DNAME INAME LOC ILOC ---------- -------------- -------------- ------------- ------------- 10 ACCOUNTING Accounting NEW YORK New York 20 RESEARCH Research DALLAS Dallas 30 SALES Sales CHICAGO Chicago 40 OPERATIONS Operations BOSTON Boston scott@MSORA920>edit Wrote file afiedt.buf 1 UPDATE( 2 SELECT deptno, dname, INITCAP(dname) Iname, loc, INITCAP(loc) Iloc FROM dept_demo 3* ) SET dname = Iname, loc = Iloc scott@MSORA920>/ 4 rows updated. scott@MSORA920>edit Wrote file afiedt.buf 1* SELECT deptno, dname, INITCAP(dname) Iname, loc, INITCAP(loc) Iloc FROM dept_demo scott@MSORA920>/ DEPTNO DNAME INAME LOC ILOC ---------- -------------- -------------- ------------- ------------- 10 Accounting Accounting New York New York 20 Research Research Dallas Dallas 30 Sales Sales Chicago Chicago 40 Operations Operations Boston Boston scott@MSORA920> Спасибо :) Комментарий читателя от 1 мая 2003 годаТом, Если в твоем исходном примере вместо: Update... SET columnName = Value; Написать: Update... SET Value = columnName; Я получаю сообщение об ошибке "ORA-01779: cannot modify a column which maps to a non key-preserved table" (см. ниже). Разме в запросе columnName не возвращает тоже всего одну запись? Почему так не срабатывает? SQL> CREATE TABLE name ( 2 keyname INT, 3 columnName VARCHAR2(25) 4 ); Table created. SQL> CREATE TABLE lookup ( 2 keyname INT PRIMARY KEY, 3 value VARCHAR2(25), 4 otherColumn INT 5 ); Table created. SQL> insert into name values ( 100, 'Original Data' ); 1 row created. SQL> insert into name values ( 200, 'Original Data' ); 1 row created. SQL> insert into lookup values ( 100, 'New Data', 1 ); 1 row created. SQL> commit; Commit complete. SQL> UPDATE ( 2 SELECT a.columnName, b.value 3 FROM name a, lookup b 4 WHERE a.keyname = b.keyname AND 5 b.otherColumn = 1) 6 SET columnName = value; 1 row updated. SQL> rollback; Rollback complete. SQL> UPDATE ( 2 SELECT a.columnName, b.value 3 FROM name a, lookup b 4 WHERE a.keyname = b.keyname AND 5 b.otherColumn = 1) 6 SET value = columnName; SET value = columnName * ERROR at line 6: ORA-01779: cannot modify a column which maps to a non key-preserved table Ответ Тома КайтаSQL> CREATE TABLE name ( 2 keyname INT, 3 columnName VARCHAR2(25) 4 ); Table created. SQL> CREATE TABLE lookup ( 2 keyname INT PRIMARY KEY, 3 value VARCHAR2(25), 4 otherColumn INT 5 ); insert into name values ( 1, 'x' ); insert into name values ( 1, 'y' ); insert into lookup values ( 1, null, null ); А теперь, какой результат должен получиться после изменения: SQL> UPDATE ( 2 SELECT a.columnName, b.value 3 FROM name a, lookup b 4 WHERE a.keyname = b.keyname AND 5 b.otherColumn = 1) 6 SET value = columnName; Столбец value будет иметь значение x или y? Чтобы избавиться от двусмысленности, необходим первичный ключ по столбцам таблиц "lookup", по которым выполняется соединение - у вас его нет. Ссылки по теме
|
|