Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Размышления про усовершенствования SQL.  [new]
dbms_photoshop
Member

Откуда: Ницца
Сообщений: 2829
Добрый вечер!
Вот стоит у меня задача проапдейтить дочернюю таблицу, записи которой ссылаются на определенный айдишник главной.
И дело в том, что задан не этот самый айдишник главной, а уникальный код по которому он определяется.

Это элементарно реализуется, используя скалярный подзапрос:
UPDATE t_detail t
   SET val = null
 WHERE t.h_h_id = (SELECT h_id FROM t_header WHERE code = '666');
Но в ранних версиях, когда не было скаляров можно заменить ч-з exists уже с падением производительности:
UPDATE t_detail t
   SET val = null
 WHERE exists (SELECT h_id FROM t_header WHERE code = '666' and h_id = t.h_h_id);

Интересуют такие вопросы:
Когда появился exists?
Можно ли реализовать апдейт одним выражением SQL отличным от указанных двух?

Пост носит характер философских размышлений. Благодарю ответивших.
19 окт 09, 20:04    [7808125]     Ответить | Цитировать Сообщить модератору
 Re: Размышления про усовершенствования SQL.  [new]
Elic
Member

Откуда: 1984. Следующие на оккупацию финно-угром
Сообщений: 23800
dbms_photoshop
используя скалярный подзапрос:
 WHERE t.h_h_id = (SELECT h_id FROM t_header WHERE code = '666');
Но в ранних версиях, когда не было скаляров
RTFM Oracle7 Server SQL Reference Manual: Condition (FAQ)
То же и в 6-ке

dbms_photoshop
Можно ли реализовать апдейт одним выражением SQL отличным от указанных двух?
RTFM/STFF key-preserved table

dbms_photoshop
Пост носит характер философских размышлений.
Предмет сперва надо изучить, чтобы пробовать философствовать
19 окт 09, 20:30    [7808210]     Ответить | Цитировать Сообщить модератору
 Re: Размышления про усовершенствования SQL.  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 7101
dbms_photoshop
Это элементарно реализуется, используя скалярный подзапрос:
UPDATE t_detail t
   SET val = null
 WHERE t.h_h_id = (SELECT h_id FROM t_header WHERE code = '666');
Это самый обычный подзапрос... :-)
Он был "испокон веков"...
Не путайте с Scalar Subquery Expressions - это появилось много позже... :-)
dbms_photoshop
Интересуют такие вопросы:
Когда появился exists?
В Oracle v5, (1985-1986) судя по всему, этой конструкции не было (посмотрел доку).
В Oracle v6 - не помню и доки под рукой нет... :-(
В Oracle7 (1992-1993) точно уже была! :-)
dbms_photoshop
Можно ли реализовать апдейт одним выражением SQL отличным от указанных двух?
Если у T_DETAIL имеется первичный ключ, то можно сделать так:
UPDATE (-- key preserned view
        SELECT t.val AS t_val 
        FROM t_detail t, t_header H
        WHERE H.h_id = t.h_h_id
          AND H.code = '666')
SET val = null;

dbms_photoshop
Пост носит характер философских размышлений. Благодарю ответивших.
Серьезные философы тщательнЕе вникают в предмет философствования... :-)
19 окт 09, 20:30    [7808212]     Ответить | Цитировать Сообщить модератору
 Re: Размышления про усовершенствования SQL.  [new]
Elic
Member

Откуда: 1984. Следующие на оккупацию финно-угром
Сообщений: 23800
SQL*Plus
dbms_photoshop
Когда появился exists?
В Oracle v6 - не помню
Был.
19 окт 09, 20:34    [7808237]     Ответить | Цитировать Сообщить модератору
 Re: Размышления про усовершенствования SQL.  [new]
-2-
Member

Откуда:
Сообщений: 11942
SQL*Plus
Если у T_DETAIL имеется первичный ключ
Всегда думал, что декларативный уник требуется на колонки, по которым джоинится вторая таблица, а не обновляемая. То есть на H.h_id или на (H.h_id, H.code).

И еще merge можно использовать.
19 окт 09, 20:35    [7808239]     Ответить | Цитировать Сообщить модератору
 Re: Размышления про усовершенствования SQL.  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 7101
Elic
SQL*Plus
dbms_photoshop
Когда появился exists?
В Oracle v6 - не помню
Был.
Ну, значит появился примерно в 1988 году...
19 окт 09, 20:36    [7808246]     Ответить | Цитировать Сообщить модератору
 Re: Размышления про усовершенствования SQL.  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 7101
-2-
SQL*Plus
Если у T_DETAIL имеется первичный ключ
Всегда думал, что декларативный уник требуется на колонки, по которым джоинится вторая таблица, а не обновляемая. То есть на H.h_id или на (H.h_id, H.code).

Oracle® Database
SQL Reference
10g Release 2 (10.2)
B14200-01
June 2005
For an UPDATE statement, the view must not be created WITH CHECK OPTION,
and all columns updated must be extracted from a key-preserved table.
19 окт 09, 20:41    [7808275]     Ответить | Цитировать Сообщить модератору
 Re: Размышления про усовершенствования SQL.  [new]
Elic
Member

Откуда: 1984. Следующие на оккупацию финно-угром
Сообщений: 23800
-2-
SQL*Plus
Если у T_DETAIL имеется первичный ключ
Всегда думал, что декларативный уник требуется на колонки, по которым джоинится вторая таблица, а не обновляемая.
И правильно думал :)
19 окт 09, 20:42    [7808278]     Ответить | Цитировать Сообщить модератору
 Re: Размышления про усовершенствования SQL.  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 7101
Elic
RTFM/STFF key-preserved table
Да, именно key-preserved table
Не key preserved view.

SQL Reference
A key-preserved table is one for which every primary
key or unique key value in the base table is also unique in the join view.
19 окт 09, 20:45    [7808290]     Ответить | Цитировать Сообщить модератору
 Re: Размышления про усовершенствования SQL.  [new]
Elic
Member

Откуда: 1984. Следующие на оккупацию финно-угром
Сообщений: 23800
SQL*Plus
Да, именно key-preserved table
SQL Reference
A key-preserved table is one for which every primary
key or unique key value in the base table is also unique in the join view.
Похоже, ты всё ещё на ручнике :) "С толкача":
create table tmp_parent(id int primary key, x int);
create table tmp_child(id int, parent_id int, y int);
update (select c.y from tmp_parent p, tmp_child c where p.x = 1 and c.parent_id = p.id) set y = 2;

0 rows updated.
19 окт 09, 20:56    [7808319]     Ответить | Цитировать Сообщить модератору
 Re: Размышления про усовершенствования SQL.  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 7101
Elic
SQL*Plus
Да, именно key-preserved table
SQL Reference
A key-preserved table is one for which every primary
key or unique key value in the base table is also unique in the join view.
Похоже, ты всё ещё на ручнике :) "С толкача":
create table tmp_parent(id int primary key, x int);
create table tmp_child(id int, parent_id int, y int);
update (select c.y from tmp_parent p, tmp_child c where p.x = 1 and c.parent_id = p.id) set y = 2;

0 rows updated.

Да, утверждая, что необходимо наличие Primary key в у T_DETAIL я ошибся.
Спасибо за поправку.

Я не на ручнике... :-)))
"С толкача" заводят мотор, когда что-то не так с аакумулятором... :-))
Если же "с толкача" снимать с "ручника", то можно повредить тормозную систему, что есть вредно... :-))

SQL Reference
A key-preserved table is one for which every primary
key or unique key value in the base table is also unique in the join view.
Здесь всё нормально.
Требуется обеспечить, чтобы значения, которые уникальны в реальной таблице,
оставались уникальными во view.
Да, это обеспечивается наличием Primary Key или Unique в таблице, с которой выполняется JOIN
(В приведенном примере в TMP_PARENT).
В этом примере у TMP_CHILD нет Primary Key, но TMP_CHILD.ROWID во VIEW будут уникальны.
19 окт 09, 21:34    [7808426]     Ответить | Цитировать Сообщить модератору
 Re: Размышления про усовершенствования SQL.  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 7101
Неожиданный эффект:
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 19 21:45:54 2009
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

SQL> create table tmp_parent(id int primary key, x int);
Table created.

SQL> create table tmp_child(id int, parent_id int, y int);
Table created.
SQL> CREATE VIEW tmp_parent_child
  2  AS
  3  select c.y from tmp_parent p, tmp_child c where p.x = 1 and c.parent_id = p.id
  4  ;
View created.

SQL> COLUMN owner FORMAT A6;
SQL> COLUMN table_name FORMAT A18;
SQL> COLUMN column_name FORMAT A12;
SQL> COLUMN INSERTABLE FORMAT A10;
SQL> COLUMN UPDATABLE FORMAT A10;
SQL> COLUMN DELETABLE FORMAT A10;

SQL> -- Первичный ключ включен UPDATABLE = YES
SQL> select * from user_updatable_columns
  2  WHERE table_name = 'TMP_PARENT_CHILD';

OWNER  TABLE_NAME         COLUMN_NAME  UPDATABLE  INSERTABLE DELETABLE
------ ------------------ ------------ ---------- ---------- ----------
SCOTT  TMP_PARENT_CHILD   Y            YES        YES        YES

SQL> -- Update выполняется без ошибок
SQL> update tmp_parent_child set y = 2;
0 rows updated.

SQL> -- ВЫключаем первичный ключ
SQL> ALTER TABLE tmp_parent DISABLE PRIMARY KEY;
Table altered.

SQL> -- Первичный ключ выключен, однако UPDATABLE всё еще YES
SQL> select * from user_updatable_columns
  2  WHERE table_name = 'TMP_PARENT_CHILD';

OWNER  TABLE_NAME         COLUMN_NAME  UPDATABLE  INSERTABLE DELETABLE
------ ------------------ ------------ ---------- ---------- ----------
SCOTT  TMP_PARENT_CHILD   Y            YES        YES        YES

SQL> -- Попытка UPDATE вызывает ошибку
SQL> -- ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> update tmp_parent_child set y = 2;
update tmp_parent_child set y = 2
                            *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

SQL> -- Теперь UPDATABLE стало NO
SQL> select * from user_updatable_columns
  2  WHERE table_name = 'TMP_PARENT_CHILD';

OWNER  TABLE_NAME         COLUMN_NAME  UPDATABLE  INSERTABLE DELETABLE
------ ------------------ ------------ ---------- ---------- ----------
SCOTT  TMP_PARENT_CHILD   Y            NO         NO         NO
То есть изменения в user_updatable_columns только после удачного/неудачного UPDATE

Аналогичное поведение наблюдается при включении первичного ключа TMP_PARENT

На других версиях Oracle сегодня проверить не успеваю...

Как сие прокомментирует почтенная публика?
19 окт 09, 21:54    [7808468]     Ответить | Цитировать Сообщить модератору
 Re: Размышления про усовершенствования SQL.  [new]
-2-
Member

Откуда:
Сообщений: 11942
SQL*Plus
Как сие прокомментирует почтенная публика?
Предположение. После alter table вьюха стала инвалидной, но не поменяла определение колонок. После обращения к ней откомпилировалась.
19 окт 09, 23:05    [7808677]     Ответить | Цитировать Сообщить модератору
 Re: Размышления про усовершенствования SQL.  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 7101
-2-
SQL*Plus
Как сие прокомментирует почтенная публика?
Предположение. После alter table вьюха стала инвалидной, но не поменяла определение колонок. После обращения к ней откомпилировалась.
Да, действительно. Вы правы.
Выключение первичного ключа инвалидировало VIEW.
SQL> -- Статус VIEW до выключения первичного ключа
SQL> SELECT object_name, object_type, status
  2  FROM user_objects
  3  WHERE object_name = 'TMP_PARENT_CHILD';

OBJECT_NAME        OBJECT_TYPE  STATUS
------------------ ------------ -------
TMP_PARENT_CHILD   VIEW         VALID

SQL> -- ВЫключаем первичный ключ
SQL> ALTER TABLE tmp_parent DISABLE PRIMARY KEY;

Table altered.

SQL> -- Статус VIEW после выключения первичного ключа
SQL> SELECT object_name, object_type, status
  2  FROM user_objects
  3  WHERE object_name = 'TMP_PARENT_CHILD';

OBJECT_NAME        OBJECT_TYPE  STATUS
------------------ ------------ -------
TMP_PARENT_CHILD   VIEW         INVALID
20 окт 09, 11:14    [7810038]     Ответить | Цитировать Сообщить модератору
 Re: Размышления про усовершенствования SQL.  [new]
dbms_photoshop
Member

Откуда: Ницца
Сообщений: 2829
Спасибо за исчерпывающие ответы.
Да, перепутал single row subquery & scalar subquery.

Возник еще один вопрос по скалярам.
Читаю в документации:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/expressions010.htm#sthref2680
...
However, scalar subqueries are not valid expressions in the following places:
...
In START WITH and CONNECT BY clauses
...

Выполняю:
SQL> SELECT * FROM v$version WHERE rownum = 1;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
SQL> SELECT LEVEL, (SELECT 'hello world' FROM dual) val
  2    FROM dual
  3   START WITH 1 = (SELECT 1 id FROM dual)
  4  CONNECT BY LEVEL <= (SELECT 3 id FROM dual);
 
     LEVEL VAL
---------- -----------
         1 hello world
         2 hello world
         3 hello world
Насколько я понимаю запрос выполняться не должен? Или опять где-то ошибся? :)
20 окт 09, 13:32    [7811299]     Ответить | Цитировать Сообщить модератору
 Re: Размышления про усовершенствования SQL.  [new]
Elic
Member

Откуда: 1984. Следующие на оккупацию финно-угром
Сообщений: 23800
dbms_photoshop
Читаю в документации:
Такое часто бывает: что-то сделали, а документацию не подправили :) В доке на 11gR1 ограничение помягчело, а в 11gR2 - совсем пропало.
Радоваться надо, что работает больше, чем заявлено, а не меньше :)
20 окт 09, 14:05    [7811594]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить
 
Лучший учебный центр Microsoft!
Новейшие курсы Microsoft SQL Server 2014!
ОЧЕНЬ привлекательные цены на курсы Oracle — от 26 тыс.руб.!
Все курсы по базам данных: Microsoft SQL Server 2014, Oracle, IBM DB2, Access, MySql