Как синхронизировать большие таблицы?

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

Как синхронизировать большие таблицы?

Понял, я пропустил, что вы сравниваете не только идентификаторы, но и сами данные. Возможно в этом действительно что-то есть.

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

Как синхронизировать большие таблицы?

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

Ваш алгоритм, по сути — классический merge join. Если бы речь шла только об оракле, я бы скептически отнесся бы к идее самостоятельной реализации собственного велосипеда, платформа, наверняка это сделает эффективнее. Слияние наборов выполнил бы одним запросом:

merge into dest_table t
   using (select coalesce(t.id,s.id)
                 ,decode(s.id,null,t.val1,s.val1) val1
                 ,decode(s.id,null,t.val2,s.val2) val2
                 ....
                 ,decode(s.id,null,t.valN,s.valN) valN
                 ,decode(s.id,null,1,0) deleted 
           from dest_table t
            full join src_tmp_table s on t.id = s.id
         ) s
   on (t.id = s.id)
   when matched then update
     set t.val1 = s.val1
         ,t.val2 = s.val2
         ...
         ,t.valN = s.valN
         ,t.deleted = s.deleted
  when not matched then insert values (
      s.id
      ,s.val1
      ,s.val2
      ...
      ,s.valN
      ,s.deleted
  )

Правда, памятуя о проблемах с фулл джойном в ранних версиях оракла, использовать full join поостерегся бы, использовал вместо него группировку объединения двух наборов.

Альтернатива EAV, структура базы?

alekciy, вы продемонстрировали вырожденный случай EAV. В вашем случае понятие entity — вырождено т.к представленно обособленной таблицей. Подобные структуры называют UDA (user defined attributes) и, действительно, в них нет ничего страшного. В том смысле, что в этой модели еще можно использовать средства датабазы для контроля целостности, в отличии от классической EAV, где приходится целостность обеспечивать самостоятельно, а в условиях конкурентного доступа это вемьма и весьма не тривиальная задача, и решить ее эффективнее чем это делает СУБД для классической реляционной модели, порой, бывает просто невозможно.

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

Но в том, что в EAV и в UDA действительно ничего нет ничего страшного, я с Вами полностью согласен. Просто эти структуры имеют ограниченную область применения. И если UDA еще можно где-то использовать, то область применения EAV — ничтожна. Это приложения, работающие с обособленными сущностями, модифицируемые в режиме монопольного доступа. Мне кажется эта область на столко узка, что случай, для которого подход EAV применим, вполне можно назвать исключительным.

Изменение структуры/группировка SELECT`ом

Додумал: Результат outer будет отличаться от результата inner в данном примере лишь в том случае, если существуют записи, где name is null

Изменение структуры/группировка SELECT`ом

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

Попытаюсь объяснить почему. Результат outer join (кстати, какой из трех тут имеется в виду?) по этим критериям ничем не будет отличается от результата inner join. Мы делаем sef join по одному и тому же полю для обоих наборов, у нас просто физически не может случиться ситуации, что левое и правое множество отличаются, когда результат внешнего соединения отличался бы от внутреннего. Да и если бы даже отличался, то благодаря предикатам в where он всяко деградировал бы в inner.

Сумбурно как то вышло, простите. Но, надеюсь, мысль уловить в этом моем потоке сознания, таки можно как-то.

А почему в результате inner join будут отсечены записи, наверное, и так понятно. Иначе, полагаю, вы бы не стали бы указывть outer.

если делать через джойны, то, думаю, как-то так:

select
    names.name as name,
    home.phone as home,
    mobile.phone as mobile
from (select distinct name from tablename)  names
left join tablename mobile on names.name = mobile.name and mobile.phonetype = 'mobile'
left join tablename home on names.name = home.name and home.phonetype = 'home'

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

Изменение структуры/группировка SELECT`ом

* не известен

Изменение структуры/группировка SELECT`ом

Если полный список значений phonetype известен и может меняться в процессе жизни приложения, запрос придется строить динамически, что очень не хорошо.

Уменьшение размера БД

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

Уменьшение размера БД

Однако ж это решение не снимает проблему получения дампа архивной базы данных ))

Уменьшение размера БД

Понял. Почитал голосарий. Оказыавется в MySQL принято называть базой данных то, что в оракле принято называть схемой. Меня это и сбило с толку. В ораклиной терминологии таблцы archive.document и prod.document находятся в одной датабазе, но разных схемах.

Предложенный вами вариант2 мне более по вкусу. Если репликацию делать триггерами, а бизнесс правила не подразумевают удаления документов, то реализоваться получается достаточно просто(просто игнорировать удаления).
Более того, я так понимаю, можно выбрать разные движки для хранения в архивной и оперативной базах и применить различную стратегию индексирования для получения оперативных и аналитических отчетов.

Таблица связей. Выбор значений object_id, option_id которых равны одновременно нескольким значениям

подумалось вот… можно еще внутренним самосоединением, но врядли это лучше

select t1.object_id 
from object_option t1,object_option t2
where t1.object_id = t2.object_id 
and t1.option_id = 1 and t2.option_id = 2<source>

Можно ли сделать это одним sql-запросом?

ruskar, нумеровать можно по разному. Можно пронумеровать и в разрезе автора. Когда автор добавляет статью — перенумеровать все его статьи, т.е. порядковый номер при таком подходе будет уникален лишь в разрезе автора. Думаю это и имел в виду rPman. И запрос его изменять не надо. Вы просто не уловили мысль.

Таблица связей. Выбор значений object_id, option_id которых равны одновременно нескольким значениям

Во-первых, как может быть два primary key?

Здесь один ПК. Он составной.

сколько не искал подвоха, не нашел:

Подвох в том, что ваш запрос вернет и те объекты, для которых определено лишь одно из двух указанных свойств. Автору нужно чтобы возвращались объекты, для которых определено оба свойства.

Таблица связей. Выбор значений object_id, option_id которых равны одновременно нескольким значениям

Лучше — врядли, разве что count не обязательно в select list, можно сразу в хавинге:

SELECT `object_id` FROM `object_option` WHERE `option_id` IN (2,3) GROUP BY `object_id` HAVING COUNT(*)  = 2

И в первичном ключе порядок поменять, чтоб индекс мог использоваться по option_id… ну или по нему выстроить обособленный индекс

Postgres, foreign key to parent(in inheritance mean) table:imposible?

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

Там множество подводных камней. К примеру. одна сессия делает delete from master_table wehre id = 1 и не фиксирует транзакцию. Другая сессия в то же время пытается insert into detail_table(id,..) values (1,...). Триггер не увидит, что запись уже удалена и после коммита первой сессии, данные окажутся рассогласоваными. Для того, чтобы целостность не оказалась нарушена, придется самостоятельно управлять блокировками. В виду того, что логика блокирования реализуется в триггерах, порой, оказывается невозможным управление порядком захвата блокировок(таблица, на которой срабатывает триггер блокируется раньше запуска триггера), что черевато взаимными блокировками…

В общем, не так уж тут все и просто… Для отказа от использования FK в пользу триггерной логики, таки надо много думать о ее целеособразности.

Не работает регистронезависимый поиск по like

% в начале лайк паттерна убивает его раньше )