Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: sp_mergedummyupdate VS UPDATE TABLE

Доброго дня всем .

Можно ли использовать простой
UPDATE  TABLE  Set col1 = col1 where ... 

вместо
EXEC sp_mergedummyupdate 
?

sp_mergedummyupdate работает крайне медленно , например для пометки 200 тыс. строк требуется 4,5-5 часов времени.
Тот же объем с UPDATE - 10 минут.

Поделитесь опытом.
Ответ:
_pH_
Поделитесь опытом.

BOL - How to: Perform a Dummy Update for a Merge Article (Replication Transact-SQL Programming)

Merge replication uses triggers as part of the replication process; when an update is made to published table, an update trigger fires. In some cases, data can be updated without the trigger firing, such as during the WRITETEXT and UPDATETEXT operations. In these cases, you need to add a dummy UPDATE statement explicitly to replicate the change. You can add a dummy UPDATE statement using replication stored procedures.

To add a dummy UPDATE statement
1. Execute the operation (for example, UPDATETEXT) on a row in a merge published table that requires a dummy update.

2. At the server (Publisher or Subscriber) on the database where the change was made, execute sp_mergedummyupdate (Transact-SQL). Specify the table on which the change was made for @source_object, and the unique identifier of the changed row for @rowguid.

3. Synchronize the subscription to replicate the changed row.
Вопрос: SQL Server 2016 - Проблема с настройкий Database mail

Добрый день
Таким образом настраиваю компонент Database Mail:

+
EXECUTE msdb.dbo.sysmail_add_account_sp
		@account_name = 'Mailing',
		@description = N'Почтовый аккаунт Службы уведомлений',
		@email_address = 'sql@mail.ru',
		@display_name = N'Служба уведомлений',
		@replyto_address = 'sql@mail.ru',
		@mailserver_name = 'smtp.mail.ru',
		@port = 25,
		@username = 'sql@mail.ru',
		@password = '',
		@enable_ssl = 1;

EXECUTE msdb.dbo.sysmail_add_profile_sp
		@profile_name = 'Mailer';
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
		@profile_name = 'Mailer',
		@account_name = 'Mailing',
		@sequence_number = 1;
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
		@profile_name = 'Mailer',
		@principal_id = 0,
		@is_default = 1;


Создаю письмо
+
EXEC msdb.dbo.sp_send_dbmail
		@profile_name = 'Mailer',
		@recipients = 'test@gmail.com',
		@body = N'SQL Server Database Mail',
		@subject = N'Тестовое сообщение',
		@query = 'SELECT TOP 10';


Письма не отправляются
При выполнении
SELECT * FROM msdb.dbo.sysmail_allitems


Получаю список писем, все со статусом fail:



И что самое странное, когда создаю отправку тестового письма, не появляются записи в журнале:


При выполнении
EXECUTE msdb.dbo.sysmail_help_status_sp
сообщает STARTED

На 2012 настраивал аналогично и проблем не было..
Не подскажете, в чем может быть проблема?
Ответ:
hulk77
Нет таких проблем с компонентом Database Mail?



hulk77
Кто-то уже использует 2016й?

RTM версия непригодна для продакшена. Баги в QueryStore которые ложат все производительность сервера. Есть проблемы с рестором БД в которой есть InMemory. Вы баг с Database Mail нашли. Если расписывать полностью, то долгий список получится. Увы, но даже RC3 было по стабильнее.

Поставил Update #1. Вроде как стало лучше. Сейчас тестируем активно.
Вопрос: БД аэропорта, где выбрать On Delete/Update Cascade/Restrict

Какие могут быть варианты, чтобы при изменении части данных, не изменились бы нежелательные к изменению. Где выбрать On Delete/Update Cascade/Restrict, а где оставить как есть. Спасибо.



Код SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
-- tables
-- Table: Bilet
CREATE TABLE vios2476.Bilet (
    nomer_bileta CHAR(10)  NOT NULL,
    klass INT  NOT NULL,
    cena INT  NOT NULL,
    Klient_kod_klienta INT NOT NULL,
    Reis_Id_reisa INT  NOT NULL,
    CONSTRAINT Bilet_pk PRIMARY KEY (nomer_bileta)
);
 
 
-- Table: Klient
CREATE TABLE vios2476.Klient (
    kod_klienta INT  NOT NULL,
    vardas CHAR(30)  NOT NULL,
    familija CHAR(30)  NOT NULL,
    mesto_zhitelstva CHAR(50)  NOT NULL,
    CONSTRAINT Klient_pk PRIMARY KEY (kod_klienta)
);
 
 
-- Table: Samolet
CREATE TABLE vios2476.Samolet (
    bortovoj_nomer INT  NOT NULL,
    model VARCHAR(20)  NOT NULL,
    vremya_eksploatacii INT  NOT NULL,
    norma_bagaza INT  NOT NULL,
    kolichestvo_mest INT  NOT NULL,
    CONSTRAINT Samolet_pk PRIMARY KEY (bortovoj_nomer)
);
 
 
-- Table: Reis
CREATE TABLE vios2476.Reis (
    Id_reisa INT  NOT NULL,
    vremya_pribytija CHAR(10)  NOT NULL,
    data_reisa CHAR(10)  NOT NULL,
    Dannyje_reisa_id_dannyh_reisa INT  NOT NULL,
    Samolet_bortovoj_nomer INT  NOT NULL,
    CONSTRAINT Reis_pk PRIMARY KEY (Id_reisa)
);
 
 
-- Table: Dannyje_reisa
CREATE TABLE vios2476.Dannyje_reisa (
    id_dannyh_reisa INT  NOT NULL,
    mesto_vyleta CHAR(30)  NOT NULL,
    mesto_naznacenija CHAR(30)  NOT NULL,
    vremya_poleta CHAR(10)  NOT NULL,
    vremya_vyleta CHAR(10)  NOT NULL,
    CONSTRAINT Dannyje_reisa_pk PRIMARY KEY (id_dannyh_reisa)
);
 
 
ALTER TABLE Bilet ADD CONSTRAINT Bilet_Klient 
    FOREIGN KEY (Klient_kod_klienta)
    REFERENCES Klient (kod_klienta)
  --  NOT DEFERRABLE 
  --  INITIALLY IMMEDIATE 
;
 
-- Reference:  Bilet_Reis (table: Bilet)
 
 
ALTER TABLE Bilet ADD CONSTRAINT Bilet_Reis 
    FOREIGN KEY (Reis_Id_reisa)
    REFERENCES Reis (Id_reisa)
  --  NOT DEFERRABLE 
  --  INITIALLY IMMEDIATE 
;
 
-- Reference:  Reis_Samolet (table: Reis)
 
 
ALTER TABLE Reis ADD CONSTRAINT Reis_Samolet 
    FOREIGN KEY (Samolet_bortovoj_nomer)
    REFERENCES Samolet (bortovoj_nomer)
  --  NOT DEFERRABLE 
   -- INITIALLY IMMEDIATE 
;
 
-- Reference:  Reis_Dannyje_reisa (table: Reis)
 
 
ALTER TABLE Reis ADD CONSTRAINT Reis_Dannyje_reisa 
    FOREIGN KEY (Dannyje_reisa_id_dannyh_reisa)
    REFERENCES Dannyje_reisa (id_dannyh_reisa)
    
 --   NOT DEFERRABLE 
  --  INITIALLY IMMEDIATE 
;
Ответ: Mathematiker, везде где внешний ключ NOT NULL должно быть ON CASCADE, (ON UPDATE если предусматривается изменение значение первичных ключей).
Если внешний ключ ALLOW NULL, тогда должно быть условие SET NULL
Вопрос: ORA-01103: database name 'DB' in control file is not 'DBTST'

Привет всем,

Хотел восстановить из бекапа производственной бд(на сервере PROD1) на тестовую бд (на сервере TEST2).
Производ. БД Имя: DB (Server: PROD1)
Тест БД Имя: DBTST (Server: TEST2)


Создал необходимые директории
Восстановил SPFILE из бекапа и создал PFILE из него и поменял локации/отредактировал.
Стартовал БД в режиме NOMOUNT из pfile (отредактированного)
Восстановил Контрольные файлы из бекапа

Создал SPFILE из pfile (отредактированного)

Затем:
SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 452984832 bytes
Fixed Size 1291144 bytes
Variable Size 130026616 bytes
Database Buffers 318767104 bytes
Redo Buffers 2899968 bytes
ORA-01103: database name 'DB' in control file is not 'DBTST'

Почему происходит такая ошибка? Как решается такая проблема?
Получается невозможно восстановить БД (из бекапа) под другим именем? Нужно под таким же именем как на продакшне?

С уважением!
Ответ:
Вячеслав Любомудров
Hrundel
пропущено...



Перевоздать контрольник тут не вариант так как база будет стендбай.
Ога, с новым DBNAME
Hrundel
Надо бьло при востановлении

file_name_convert сразу правильно настроить.
Который для обычного RESTORE / RECOVER не используется
Hrundel
А сейчас только ручками контрольник править если надо.
А UPDATE/INSERT/DELETE напрямую в датафайлах
Hrundel
очень ленивые давно на расположение файлов забивают.


Черт я топик перепутал. Тут у булато то тест, то коннект не проходит то стендбай.
Вопрос: Запись в БД (MySQL5.5+) с выбором Insert или Update и выборкой кодов с таблиц-справочников

Добрый день.

Приходит прайс в XML (точнее куча файлов по 100 записей (пока что, но будет где-то по 1000). Имеем такие данные: ИД (он же штрихкод), ИД категории товара (конфеты, фрукты, ...), Наименование, Количество, ИД единицы измерения (шт., гр., кг., ...), Цена, другая информация (много полей, в т.ч. с значениями со справочников). Категории товара и Единицы измерения приходят отдельным файлом. Это все "добро" нужно раскинуть в три таблицы (товары(catalog), Категории товаров(kategor) и Единицы измерения(ediz), по такому критерию - если такого товара еще нету в базе, то его нужно добавить, а если есть - обновить цену; то же самое с категориями и единицами измерения, но только нужно еще получить их ИД для подстановки при вставке записи о товаре.
Самый простой вариант реализации (по записям):
1. Запрос на получение ИД категории товара.
2. Если пустой, тогда запрос на добавление категории + Получение ИД.
3. Получение ИД единицы измерения,
4. Если пустой - вставка + Получение ИД.
...
n. Запрос на получение ИД товара
n+1. Если пустой, то вставка, если не пустой - обновление.
Можно еще сначала обновить все справочники, а потом пускать использовать подзапросы при вставке товаров. Но все это как-то громоздко и кажется мне не по феншую (к примеру применение ON DUPLICATE KEY UPDATE влечет за собой увеличение счетчика автоинкремента даже при обновлении записи).
Собственно интересует механизм уменьшения запросов к БД без увеличения нагрузки. Примеры приветствуются
Ответ:
<?xml version="1.0" encoding="UTF-8"?>
<prices>
	<tovar idimp="15e9b8c48d">
		<name>Пиво Жигулевское</name>
		<kategor idimp="bgr07ed95">Алкогольные напитки</kategor>
		<edizm idimp="f93e1128c83a">Ящик</edizm>
		<col>5</col>
		<price>240.5</price>
	</tovar>
	<tovar idimp="65lc8we24s">
		<name>Груши</name>
		<kategor idimp="fd512dfser3">Фрукты</kategor>
		<edizm idimp="f93e1128c83a">ящик</edizm>
		<col>3</col>
		<price>1055.47</price>
	</tovar>
	<tovar idimp="dys51te3z89v">
		<name>Ананас</name>
		<kategor idimp="fd512dfser3">Фрукты</kategor>
		<edizm idimp="dskut7fd2d">шт.</edizm>
		<col>5</col>
		<price>195.7</price>
	</tovar>
	<tovar idimp="k8eg5r6t5">
		<name>Топор</name>
		<kategor>Инструменты</kategor>
		<edizm idimp="dskut7fd2d">шт.</edizm>
		<col>10</col>
		<price>159.99</price>
	</tovar>
	<tovar idimp="lekrw6kdr">
		<name>Яблоки красные</name>
		<kategor idimp="fd512dfser3">Фрукты</kategor>
		<edizm idimp="zve5sr0ht6">кг</edizm>
		<col>50</col>
		<price>55.2</price>
	</tovar>
</prices>


CREATE DATABASE `test`;

USE `test`;

CREATE TABLE `kategor` ( `id` INT NOT NULL AUTO_INCREMENT , `kname` VARCHAR(70) NOT NULL , `descr` VARCHAR(155) NULL , `id_kimport` VARCHAR(15) NULL , PRIMARY KEY (`id`), INDEX `kname` (`kname`)) ENGINE = InnoDB;

CREATE TABLE `ediz` ( `id` INT NOT NULL AUTO_INCREMENT , `iname` VARCHAR(10) NOT NULL , `id_iimport` VARCHAR(15) NULL , PRIMARY KEY (`id`), INDEX `iname` (`iname`)) ENGINE = InnoDB;

CREATE TABLE `catalog` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `id_kategor` INT NOT NULL , `cname` VARCHAR(150) NOT NULL , `kol` INT NOT NULL DEFAULT '0' , `id_ediz` INT NOT NULL , `price` DECIMAL(15.2) NOT NULL,
`id_cimport` VARCHAR(15) NULL, DEFAULT '0' , PRIMARY KEY (`id`), INDEX `indName` (`cname`)) ENGINE = InnoDB;

INSERT INTO `test`.`ediz` (`id`, `iname`, `id_iimport`) VALUES (NULL, 'шт.', 'dskut7fd2d');
INSERT INTO `kategor` (`id`, `kname`, `descr`, `id_kimport`) VALUES (NULL, 'Фрукты', NULL, 'fd512dfser3');
INSERT INTO `test`.`catalog` (`id`, `id_kategor`, `cname`, `kol`, `id_ediz`, `price`, `id_cimport`) VALUES (NULL, '1', 'Ананас', '10', '1', '180', 'dys51te3z89v');


На выходе должны получить такое:
Таблица единиц измерения
idinameid_iimpor
1шт.dskut7fd2d
2ящикf93e1128c83a
3кгzve5sr0ht6

синим выделен ИД-импорта, которого нету при добавлении "Топор", но в таблице он должен остаться

Таблица категорий
idknamedescrid_kimport
1Фруктыfd512dfser3
2Алкогольные напиткиbgr07ed95
3Инструментыjdhr69etr3


Таблица товаров
idid_kategorcnamekolid_edizpriceid_cimport
11Ананас105195.7dys51te3z89v
22Пиво Жигулевское52240.515e9b8c48d
31Груши321055.4765lc8we24s
43Топор101159.99k8eg5r6t5
51Яблоки красные50355.2lekrw6kdr
Вопрос: Update. Сколько update-ов может быть в одном скрипте-файле?

Здравствуйте.

Исходные данные:
SQL Server 2012. SQL Managment Studio.
Есть таблица 195000 строк.
Нужно по поиску ключевых слов обновить данные.
Пример:
update TAble1 set Find1 = 'Есть вода и хлеб' where TextCol like '%хлеб%' and TextCol like '%вода%'
update TAble1 set Find1 = 'Есть вода' where TextCol not like '%хлеб%' and TextCol like '%вода%'
Запросов(update-ов) около 6000.
Есть сложные вопросы, около 50 условий like или not like.

Выполнение:
Выполнение долгое.
Оставлял работать на ночь.

Результат:
на утро у меня автоматически вышел из соединения.( т.е. я не мог посмотреть сообщения -сколько он обновил и количество потраченного времени).
Посмотрев результат обнаружил, что выполнены примерно штук 500 запросов сначала, и 10 с конца
(в последних 10 условия на поиск простые : " where TextCol like '%Йод%' " ).

Проблема: почему-то не выполнились запросы из середины(около 5500 штук).
Проверил: выборочно запустил некоторые запросы из середины списка - Среди них некоторые строки обновились(если находились ключевые слова). Получается, что за ночь серединные запросы не были выполнены.

Есть ли какие-то ограничения в SQL server на количество запросов(update) в одном скрипте(файле)?
Если они существуют, возможно ли как-то настроить, например, автоматический последовательный запуск нескольких скриптов?
Ответ:
avpetrov27
Посмотрев результат обнаружил, что выполнены примерно штук 500 запросов сначала, и 10 с конца
(в последних 10 условия на поиск простые : " where TextCol like '%Йод%' " ).

может, выпонилось все, но одно другое переобновило.
может, остальные апдэйты из середины пакета завершились с ошибками.
раз все было в одном пакете и последний апдэйт выполнился,
ошибки не batch aborted
(т.е. один statement обломался, но пакет дальше выполняется)
вот примеры на эти 2 случая:
if OBJECT_ID('dbo.t') is not null drop table dbo.t;
create table dbo.t (id int identity, col1 varchar(10), col2 varchar(100));
go

insert into dbo.t(col2) values ('aaa'), ('bbb'), ('cccc');
go

-- (1)
update dbo.t 
set col1 = 'exists bbb' 
where col2 like '%bbb%'; 

-- (2)
update dbo.t 
set col1 = 'exists a' 
where col2 like '%a%'; 

-- (3)
update dbo.t 
set col1 = 'exists aaa' 
where col2 like '%aaa%'; 
go

select *
from dbo.t;
go

--id	col1	col2
--1	exists aaa	aaa
--2	exists bbb	bbb
--3	NULL	cccc

-- (2) - ??? -- update over update

-- (4)
update dbo.t 
set col1 = 'exists bbb' 
where col2 like '%bbb%'; 

-- (5)
update dbo.t 
set col1 = 'exists cccc' 
where col2 like '%cccc%'; 

-- (6)
update dbo.t 
set col1 = 'exists a' 
where col2 like '%a%'; 
go

select *
from dbo.t;

--id	col1	col2
--1	exists a	aaa
--2	exists bbb	bbb
--3	NULL	cccc

-- (5) - ??? -- error

можно логировать успешные апдэйты, используя output:
if OBJECT_ID('dbo.res') is not null drop table dbo.res;
create table dbo.res (id_statement int, id int, col1_old varchar(10), col1_new varchar(10));
go

-- (1)
update dbo.t 
set col1 = 'exists bbb' 
output 1, inserted.id, deleted.col1, inserted.col1 into dbo.res(id_statement, id, col1_old, col1_new)
where col2 like '%bbb%'; 

-- (2)
update dbo.t 
set col1 = 'exists a' 
output 2, inserted.id, deleted.col1, inserted.col1 into dbo.res(id_statement, id, col1_old, col1_new)
where col2 like '%a%'; 

-- (3)
update dbo.t 
set col1 = 'exists aaa' 
output 3, inserted.id, deleted.col1, inserted.col1 into dbo.res(id_statement, id, col1_old, col1_new)
where col2 like '%aaa%'; 
go

-- (4)
update dbo.t 
set col1 = 'exists bbb' 
output 4, inserted.id, deleted.col1, inserted.col1 into dbo.res(id_statement, id, col1_old, col1_new)
where col2 like '%bbb%'; 

-- (5)
update dbo.t 
set col1 = 'exists cccc' 
output 5, inserted.id, deleted.col1, inserted.col1 into dbo.res(id_statement, id, col1_old, col1_new)
where col2 like '%cccc%'; 

-- (6)
update dbo.t 
set col1 = 'exists a' 
output 6, inserted.id, deleted.col1, inserted.col1 into dbo.res(id_statement, id, col1_old, col1_new)
where col2 like '%a%'; 
go

select *
from dbo.res;

--id_statement	id	col1_old	col1_new
--1	2	exists bbb	exists bbb
--2	1	exists a	exists a
--3	1	exists a	exists aaa
--4	2	exists bbb	exists bbb
--6	1	exists aaa	exists a


заодно отсутствующие id_statement укажут на statement-ы, завершившиеся с ошибкой
Вопрос: Тригер Update View

Всем привет! При написании тригера столкнулась с такой проблемой...
Допустим хочу обновить только строки (3 и 5):
 UPDATE Empl_sal
SET l_name='NEW Chek'
where sal='$9000' and L_NAME='NEW AA';

(2 rows updated.)

Before update
select * From  EMPL_SAL; 

AA NEW AA $700000
AA NEW AA $90
AAA NEW AA $9000
AAA HARTSTEIN $9000
PAT NEW AA $9000
SUSAN MAVRIS $6
HERMANN BAER $10000
SHELLEY HIGGINS $12008
WILLIAM NEW AA $15000


After update
AA NEW CHEK $700000
AA NEW CHEK $90
AAA NEW CHEK $9000
AAA NEW CHEK $9000
PAT NEW CHEK $9000
SUSAN MAVRIS $6
HERMANN BAER $10000
SHELLEY HIGGINS $12008
WILLIAM NEW CHEK $15000


Как мне в тригере верно выстроить верное обновление значений? Помогите, пожалуйста. что-то запуталась.

Вот данные...
View
create OR REPLACE view Empl_sal (l_name, sal) as
SELECT Upper(last_name) "l_name",  ltrim(to_char(salary,'$9999999') )"sal"
FROM EMPLOYEES_3
WITH CHECK OPTION;

TRIGGER
create or replace TRIGGER  upd_Empl_sal_trg
FOR  UPDATE ON Empl_sal
COMPOUND TRIGGER
INSTEAD OF EACH ROW IS 
BEGIN
IF UPDATING ('l_name')THEN
UPDATE EMPLOYEES_3 SET last_name = :NEW.l_name WHERE UPPER(last_name) =:OLD.l_name;
UPDATE EMPLOYEES_3 SET last_name = :NEW.l_name WHERE ltrim(to_char(salary,'$9999999'))= :OLD.sal;
elsif
UPDATING ('sal')  THEN
UPDATE EMPLOYEES_3 SET SALARY =:NEW.sal WHERE  ltrim(to_char(salary,'$9999999') )= :OLD.sal;
UPDATE EMPLOYEES_3 SET SALARY =:NEW.sal WHERE  UPPER(last_name) = UPPER(:OLD.l_name);
END IF;
END INSTEAD OF EACH ROW;
END upd_Empl_sal_trg;
Ответ: Dimitry Sibiryakov,

В таблице есть первичный ключ (номер_сотрутника), но во вью его по заданию не должно быть (имя, фам, зп) Привязать номер не к чему.
Вы так имеете ввиду?
..UPDATE EMPLOYEES_3 SET last_name = :NEW.l_name WHERE UPPER(last_name) =:OLD.l_name 
and employee_id =employee_id; 

....
так попробовала. Такая же ерунда получается.
Вопрос: merge; "update set ... where [condition]" vs "using (select * from t where [condition])"

день добрый.

есть два merge - "медленный" и "быстрый". в "медленном" набор обновляемых строк фильтруется через when matched then update set ... where ..., в "быстром" условие переносится на уровень фильтра таблицы-источника, поэтому hash join проходит быстрее.

+ script
cl scr 
--------------------------------------------------------------------------------
set time off timi off echo on

drop table t_src purge;
drop table t_dst purge;

create table t_src (id, value) as 
    select 
        rownum, 
        rownum 
    from dual
    connect by rownum <= 1e6;
    
create table t_dst (id, value) as 
    select 
        id, 
        case when id < 1e6 then value end
    from t_src;

exec dbms_stats.gather_table_stats(user, 't_src');
exec dbms_stats.gather_table_stats(user, 't_dst');
    
select * from t_dst where id = 1e6;
--------------------------------------------------------------------------------    
set autotrace on explain timi on

merge into t_dst d 
    using t_src s on (s.id = d.id)
    when matched then update set d.value = s.value 
        where d.value is null;

set autotrace off timi off

select * from t_dst where id = 1e6;

rollback;
--------------------------------------------------------------------------------
set autotrace on explain timi on
    
merge into (select * from t_dst where value is null) d 
    using t_src s on (s.id = d.id) 
    when matched then update set d.value = s.value 
        where d.value is null;
    
set autotrace off timi off

select * from t_dst where id = 1e6;

rollback;
--------------------------------------------------------------------------------
set time on timi on echo off 

+ spool
SQL> drop table t_src purge;

Table dropped.

SQL> drop table t_dst purge;

Table dropped.

SQL>
SQL> create table t_src (id, value) as
  2      select
  3          rownum,
  4          rownum
  5      from dual
  6      connect by rownum <= 1e6;

Table created.

SQL>
SQL> create table t_dst (id, value) as
  2      select
  3          id,
  4          case when id < 1e6 then value end
  5      from t_src;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 't_src');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user, 't_dst');

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t_dst where id = 1e6;

        ID      VALUE
---------- ----------
   1000000

1 row selected.

SQL> --------------------------------------------------------------------------------
SQL> set autotrace on explain timi on
SQL>
SQL> merge into t_dst d
  2      using t_src s on (s.id = d.id)
  3      when matched then update set d.value = s.value
  4          where d.value is null;

1 row merged.

Elapsed: 00:00:04.33

Execution Plan
----------------------------------------------------------
Plan hash value: 3500657092

--------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |       |  1000K|    34M|       |  1912   (2)| 00:00:35 |
|   1 |  MERGE               | T_DST |       |       |       |            |          |
|   2 |   VIEW               |       |       |       |       |            |          |
|*  3 |    HASH JOIN         |       |  1000K|    19M|    21M|  1912   (2)| 00:00:35 |
|   4 |     TABLE ACCESS FULL| T_SRC |  1000K|  9765K|       |   244   (4)| 00:00:05 |
|   5 |     TABLE ACCESS FULL| T_DST |  1000K|  9765K|       |   244   (4)| 00:00:05 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("S"."ID"="D"."ID")

SQL>
SQL> set autotrace off timi off
SQL>
SQL> select * from t_dst where id = 1e6;

        ID      VALUE
---------- ----------
   1000000    1000000

1 row selected.

SQL>
SQL> rollback;

Rollback complete.

SQL> --------------------------------------------------------------------------------
SQL> set autotrace on explain timi on
SQL>
SQL> merge into (select * from t_dst where value is null) d
  2      using t_src s on (s.id = d.id)
  3      when matched then update set d.value = s.value
  4          where d.value is null;

1 row merged.

Elapsed: 00:00:00.93

Execution Plan
----------------------------------------------------------
Plan hash value: 290390879

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |       |     1 |    36 |   493   (5)| 00:00:09 |
|   1 |  MERGE               | T_DST |       |       |            |          |
|   2 |   VIEW               |       |       |       |            |          |
|*  3 |    HASH JOIN         |       |     1 |    20 |   493   (5)| 00:00:09 |
|*  4 |     TABLE ACCESS FULL| T_DST |     1 |    10 |   244   (4)| 00:00:05 |
|   5 |     TABLE ACCESS FULL| T_SRC |  1000K|  9765K|   244   (4)| 00:00:05 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("S"."ID"="T_DST"."ID")
   4 - filter("VALUE" IS NULL)

SQL>
SQL> set autotrace off timi off
SQL>
SQL> select * from t_dst where id = 1e6;

        ID      VALUE
---------- ----------
   1000000    1000000

1 row selected.

SQL>
SQL> rollback;

Rollback complete.

SQL> --------------------------------------------------------------------------------
SQL> set time on timi on echo off


я полагал, что данные merge эквивалентны, но меня смутило, что в первом merge оракл сам не протолкнул предикат из update where глубже, в фильтр таблицы.

если команды эквивалентны - по какой причине оракл сам не догадался из первого merge сделать второй?
если нет - при каком сценарии я увижу расхождение?

спасибо.
Ответ:
кит северных морей
merge into t_dst d 
    using t_src s on (s.id = d.id)
    when matched then update set d.value = s.value 
        where {1};
этот вариант функционально ближе к такому:
merge into t_dst d 
    using t_src s on (s.id = d.id and {1})
    when matched then update set d.value = s.value 
но без ограничения на изменяемые колонки и условия проверяются только на самом этапе merge в плане. В этой части можно указывать предикаты не только по d, но и по s.

кит северных морей
смутило, что в первом merge оракл сам не протолкнул предикат из update where глубже, в фильтр таблицы
CBO это не умеет, но умеет брать из ON (...)
Вопрос: Очень долгий update на 30М записей

Все пытаюсь бороться с Postgresql, теперь встал вопрос оптимизации update на таблице с постоянным инсертом, после завершения будет около 150М кластеров. Сейчас медленно движемся в районе 35М. Возникают спайки такого плана:

SQL (53712.7ms) UPDATE "cluster_addresses" SET "cluster_id" = 13801883 WHERE "cluster_addresses"."cluster_id" = $1 [["cluster_id", 14099291]]


postgresql=> \d+ cluster_addresses;
                                               Table "public.cluster_addresses"
   Column   |  Type   |                           Modifiers                            | Storage | Stats target | Description
------------+---------+----------------------------------------------------------------+---------+--------------+-------------
 id         | integer | not null default nextval('cluster_addresses_id_seq'::regclass) | plain   |              |
 cluster_id | integer |                                                                | plain   |              |
 address_id | integer |                                                                | plain   |              |
Indexes:
    "cluster_addresses_pkey" PRIMARY KEY, btree (id)
    "index_cluster_addresses_on_address_id" UNIQUE, btree (address_id)
    "index_cluster_addresses_on_cluster_id" btree (cluster_id)


prometheus=> explain analyze UPDATE "cluster_addresses" SET "cluster_id" = 13801883 WHERE "cluster_addresses"."cluster_id" = 14099291;
                                                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on cluster_addresses  (cost=0.56..369.87 rows=290 width=14) (actual time=5243.940..5243.940 rows=0 loops=1)
   ->  Index Scan using index_cluster_addresses_on_cluster_id on cluster_addresses  (cost=0.56..369.87 rows=290 width=14) (actual time=5243.939..5243.939 rows=0 loops=1)
         Index Cond: (cluster_id = 14099291)
 Planning time: 3.574 ms
 Execution time: 5243.963 ms
(5 rows)


Повторно ( видимо после попадания в кеш ) запрос выполняется моментально.

Какие есть варианты оптимизации? Можно ли как-то прогрузить всю таблицу cluster_addresses в память? После 5 дней работы Postgresql жрет около 22GB RAM из 64. Вся база сейчас на отдельном SSD диске.

Postgresql 9.5, конфиг:

max_connections = 50
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 335544kB
maintenance_work_mem = 2GB
max_wal_size = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
Ответ: nateless,

Через буквально пару минут после адпейта конфига, замены индекса и vacuum:

D, [2016-02-29T16:01:55.368858 #5592] DEBUG -- : SQL (1853.1ms) UPDATE "cluster_addresses" SET "cluster_id" = 2632320 WHERE "cluster_addresses"."cluster_id" = $1 [["cluster_id", 5073322]]

Тот же запрос в psql меньше чем через минуту:
explain (analyze, buffers) UPDATE "cluster_addresses" SET "cluster_id" = 2632320 WHERE "cluster_addresses"."cluster_id" =5073322;
                                                                                  QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on cluster_addresses  (cost=0.43..160809.60 rows=192469 width=14) (actual time=227.168..227.168 rows=0 loops=1)
   Buffers: shared hit=14445 dirtied=8829
   ->  Index Scan using index_cluster_addresses_on_cluster_id on cluster_addresses  (cost=0.43..160809.60 rows=192469 width=14) (actual time=227.167..227.167 rows=0 loops=1)
         Index Cond: (cluster_id = 5073322)
         Buffers: shared hit=14445 dirtied=8829
 Planning time: 0.084 ms
 Execution time: 227.181 ms
(7 rows)
Вопрос: Выборка c update

Доброго времени суток.
Бьюсь над одной проблемой уже не первый день, но решения пока нет :(
Суть проблемы: есть удаленный сервер (MySQL+PHP), есть таблица, в таблице есть пара тысяч записей. К ней в одно и тоже время может обратиться несколько пользователей. Нужно чтобы каждый пользователь получил определенное кол-во записей с этой таблицы выбранных случайно. Но ни как не возьму в толк как сделать select+update, чтобы между ними не было другого select+update.
Нашел решения:
1) UPDATE + LAST_INSERT_ID(). Но выборка (в update) идет по порядку, а нужно случайно (более-менее).
2) Нашел, что можно сделать UPDATE + LAST_INSERT_ID() + ORDER BY RAND() + LIMIT. Но что-то эту комбинацию MySQL выполняет не корректно. Часто приходит пустой результат.
Может кто сталкивался, подскажите решение. Заранее благодарен.
Ответ:
Просто_кодер1
как сделать select+update, чтобы между ними не было другого select+update.
SELECT ... FOR UPDATE.