ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / IBM DB2, WebSphere, IMS, U2 [игнор отключен] [закрыт для гостей] / select double records
8 сообщений из 8, страница 1 из 1
select double records
    #35990611
jack_nsk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
jack_nsk
Гость
всем привет!

около 2 дней не могу обмануть оптимизатор
задача - выбрать дублирующиеся записи из таблицы для дальнейшей обработки,
что удалить, что отредактировать...
в таблице около 2 миллионов записей
около 200 000 дублей
запрос
Код: plaintext
1.
2.
3.
SELECT CARD_NUMBER
   FROM DISCOUNT.DB2_PERSON
group by CARD_NUMBER
having count(CARD_NUMBER) >  1
выполняется около 8 секунд и дает около 70 000 записей
по полю CARD_NUMBER - естественно существует индекс

сначала попробовал самым неэффективным вариантом, через подзапрос с IN
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT min(CARD_NUMBER) as cardnumber, REGION
   FROM DISCOUNT.DB2_PERSON
   where CARD_NUMBER in
(
SELECT CARD_NUMBER
   FROM DISCOUNT.DB2_PERSON
group by CARD_NUMBER
having count(CARD_NUMBER) >  1 
)
длительность работы запроса - 50min

затем попробовал несколько вариантов через объединение таблиц
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
with t2 as (
SELECT CARD_NUMBER
   FROM DISCOUNT.DB2_PERSON
group by CARD_NUMBER
having count(CARD_NUMBER) >  1 )
SELECT persons.CARD_NUMBER,persons.REGION
   FROM t2 left join DISCOUNT.DB2_PERSON persons
on persons.CARD_NUMBER = t2.CARD_NUMBER
длительность работы запроса - 30min

решил попытаться через временные таблицы
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
CREATE PROCEDURE DISCOUNT_DOUBLE ()
	DYNAMIC RESULT SETS  1 
P1: BEGIN

	DECLARE cursor2 CURSOR WITH RETURN FOR
		SELECT session.double_ids.double_id, 
		  persons.REGION
		  FROM session.double_ids left join DISCOUNT.DB2_PERSON AS persons
		  on (persons.CARD_NUMBER = session.double_ids.double_id);

	DECLARE GLOBAL TEMPORARY TABLE session.double_ids (double_id int) on commit preserve rows with replace;		  
		                                
   insert into session.double_ids(double_id)
    SELECT int(persons.CARD_NUMBER) as double_id 
		  FROM DISCOUNT.DB2_PERSON AS persons
		  GROUP BY persons.CARD_NUMBER
		  HAVING COUNT(persons.CARD_NUMBER) >  1 ;			                                

   OPEN cursor2;
END P1
длительность работы запроса - 30min

ощущение такое, что оптимизатор "теряет" индекс и делает выборку через полный перебор таблицы

на своем сервере с IB эту задачу решил бы через вложенный FOR, на птичке тоже слетает оптимизатор, когда пытался сделать JOIN с подселектом группировки
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
for
  SELECT CARD_NUMBER
    FROM DISCOUNT.DB2_PERSON
    group by CARD_NUMBER
   having count(CARD_NUMBER) >  1 
       into :double_id
do begin
  for
    SELECT CARD_NUMBER, REGION
      FROM DISCOUNT.DB2_PERSON
      where CARD_NUMBER = :double_id
  do
    suspend;
  end
end
по моим предположениям такой запрос должен работать максимум 2-3 минуты

что же делать в подобной ситуации ?
из неперепробованного - построение индекса по временной таблице... :)
или использование конструкции FOR, но опыта с дб2 практически никакого
...
Рейтинг: 0 / 0
18.05.2009, 07:31
| Ответить | Цитировать | Написать  
select double records
    #35990763
Mark Barinstein
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Mark Barinstein
Гость
Здравствуйте.

Если вам любую запись из дублей по CARD_NUMBER надо выбрать, то
Код: plaintext
1.
2.
3.
4.
5.
select *
from (
SELECT rownumber() over(partition by CARD_NUMBER) rn_, p.*
FROM DISCOUNT.DB2_PERSON p
) t
where rn_> 1
...
Рейтинг: 0 / 0
18.05.2009, 09:57
| Ответить | Цитировать | Написать  
select double records
    #35990984
jack_nsk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
jack_nsk
Гость
спасибо, Mark!

буду в курсе, что так можно сделать

нужны все записи дублей, чтобы понять, какую из них можно удалить
...
Рейтинг: 0 / 0
18.05.2009, 11:12
| Ответить | Цитировать | Написать  
select double records
    #35991033
Mark Barinstein
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Mark Barinstein
Гость
jack_nskнужны все записи дублей, чтобы понять, какую из них можно удалить Ну, тогда либо
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT p.*
   FROM DISCOUNT.DB2_PERSON p
join 
(
SELECT CARD_NUMBER
   FROM DISCOUNT.DB2_PERSON
group by CARD_NUMBER
having count(CARD_NUMBER) >  1 
) g on p.CARD_NUMBER=g.CARD_NUMBER
, либо с темповой таблицей для групп, уникально проиндексированной по этому единственному полю CARD_NUMBER.
...
Рейтинг: 0 / 0
18.05.2009, 11:21
| Ответить | Цитировать | Написать  
select double records
    #35991271
jack_nsk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
jack_nsk
Гость
длительность выполнения запроса - 42 минуты...
стабильно! :)
версия Express-C LUW
можно конечно предположить, что размер запроса слишком велик для явы,
на которой написан Дата Студия...
можно сделать поправку на то, что селект работает на вью,
которые построены на лотусовых таблицах, среплицированных в дб2...
оппробую ещё процедуру поправить, чтобы проиндексировать временную таблицу после её заполнения
...
Рейтинг: 0 / 0
18.05.2009, 12:26
| Ответить | Цитировать | Написать  
select double records
    #35991519
jack_nsk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
jack_nsk
Гость
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
CREATE PROCEDURE DISCOUNT_DOUBLE ()
	DYNAMIC RESULT SETS  1 
P1: BEGIN

	DECLARE cursor2 CURSOR WITH RETURN FOR
		SELECT session.double_ids.double_id, persons.REGION
		  FROM session.double_ids left join DISCOUNT.DB2_PERSON AS persons
		  on (persons.CARD_NUMBER = session.double_ids.double_id);

	DECLARE GLOBAL TEMPORARY TABLE session.double_ids (double_id int) on commit preserve rows with replace;		  
		                                
   insert into session.double_ids(double_id)
    SELECT int(persons.CARD_NUMBER) as double_id 
		  FROM DISCOUNT.DB2_PERSON AS persons
		  GROUP BY persons.CARD_NUMBER
		  HAVING COUNT(persons.CARD_NUMBER) >  1 ;
		  
	CREATE  INDEX session."double_id" ON session.double_ids (double_id ASC);
		  			                                

   OPEN cursor2;
END P1
стабильно! - длительность выполнения 30 минут... :)
не смертельно, но любопытство гложет -
в каком месте спотыкается оптимизатор ?
...
Рейтинг: 0 / 0
18.05.2009, 13:37
| Ответить | Цитировать | Написать  
select double records
    #35991609
Mark Barinstein
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Mark Barinstein
Гость
jack_nsk
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
CREATE PROCEDURE DISCOUNT_DOUBLE ()
	DYNAMIC RESULT SETS  1 
P1: BEGIN

	DECLARE cursor2 CURSOR WITH RETURN FOR
		SELECT session.double_ids.double_id, persons.REGION
		  FROM session.double_ids left join DISCOUNT.DB2_PERSON AS persons
		  on (persons.CARD_NUMBER = session.double_ids.double_id);

	DECLARE GLOBAL TEMPORARY TABLE session.double_ids (double_id int) on commit preserve rows with replace;		  
		                                
   insert into session.double_ids(double_id)
    SELECT int(persons.CARD_NUMBER) as double_id 
		  FROM DISCOUNT.DB2_PERSON AS persons
		  GROUP BY persons.CARD_NUMBER
		  HAVING COUNT(persons.CARD_NUMBER) >  1 ;
		  
	CREATE  INDEX session."double_id" ON session.double_ids (double_id ASC);
		  			                                

   OPEN cursor2;
END P1
стабильно! - длительность выполнения 30 минут... :)
не смертельно, но любопытство гложет -
в каком месте спотыкается оптимизатор ?
0. Соберите статистику на таблицу и её индексы:
call sysproc.admin_cmd('runstats on DISCOUNT.DB2_PERSON and indexes all')

1.
У вас какого типа поле CARD_NUMBER?
Т.е. зачем вы приведение типов для групп делаете?

2.
P1: BEGIN
DECLARE stmt varchar(128);
DECLARE cursor2...
...
CREATE UNIQUE INDEX session....
SET stmt = 'runstats on table session.double_ids and indexes all';
call sysproc.admin_cmd(stmt);
open cursor2;
END

3.
Зачем "session.double_ids left join DISCOUNT.DB2_PERSON"?
Какой в этом смысл?

4. Приведите план запроса
...
Рейтинг: 0 / 0
18.05.2009, 14:02
| Ответить | Цитировать | Написать  
select double records
    #35993070
jack_nsk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
jack_nsk
Гость
Марк, спасибо!
ваши ответы заставили задуматься

первым делом пришлось осознать, что дб2 в части оптимизации плана выполнения запроса работает иначе, чем привычная мне птичка. полистал форум, нашел ссылку
LEO: самонастраивающийся оптимизатор запросов для DB2 , которая много чего объяснила...
особенно то, что "от перемены мест слагаемых" мало что зависит - оптимизатор сам построит план запроса исходя из статистики
но похоже я нашел на ссылке некое указание на причину, относительно которой не получается получить быстрый запрос
Поскольку большая часть агрегатных функций может вычисляться в инкрементальном режиме, по мере сортировки строк, в своем окончательном виде TEMP будет содержать результат выполнения раздела GROUP BY. Оптимальный алгоритм соединения (соединение методом вложенных циклов, соединение через хеширование или соединение слиянием) для последующего соединения Orders и Products критически зависит от размера результата GROUP BY. Оптимизатор запросов может выбрать неоптимальный алгоритм слияния при переоценке или недооценке размера этого результата.
т.к. 10% дублирующихся записей в таблице - это явное безобразие, в нормально спроектированном приложении такого не должно быть, то я сократил количество возвращаемых записей в подзапросе получения дублирующихся номеров карт
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
select a.card_number, 
d.bonus, d.P_POROG,d.POROG,d.DATE_END,d.DATE_END_SUMMA,d.REGION
from (
SELECT CARD_NUMBER
   FROM DISCOUNT.DB2_PERSON_T
group by CARD_NUMBER
having count(CARD_NUMBER) >  2 
) a
join DISCOUNT.DB2_PERSON_T AS d
 on d.card_number=a.card_number
- результат совершенно другой, на выходе 63 000 записей и 1,5-2 минуты работы запроса... :)
в реальности мало кто может работать над выборкой в 200 000 записей, поэтому запросы можно разнести на диапазоны номеров карт, но это уже не моя забота... :)
в центральном офисе вручную через интерфейс лотуса редактируют дубли карт, моя задача только передать список карт моего региона

Mark Barinstein
0. Соберите статистику на таблицу и её индексы:
call sysproc.admin_cmd('runstats on DISCOUNT.DB2_PERSON and indexes all')

в дата студии не получилось выполнить запрос, но я выполнил сбор статистики в контроль центре,
правда результаты сбора статистики контроль центр не выдал

Mark Barinstein
1.
У вас какого типа поле CARD_NUMBER?
Т.е. зачем вы приведение типов для групп делаете?

в реальности тип CARD_NUMBER - double
по моим смутным ощущениям индекс - это целочисленное значение, поэтому пробовал привести результат к целочисленному типу

Mark Barinstein
2.
P1: BEGIN
DECLARE stmt varchar(128);
DECLARE cursor2...
...
CREATE UNIQUE INDEX session....
SET stmt = 'runstats on table session.double_ids and indexes all';
call sysproc.admin_cmd(stmt);
open cursor2;
END

из любопытства обязательно попробую

Mark Barinstein
3.
Зачем "session.double_ids left join DISCOUNT.DB2_PERSON"?
Какой в этом смысл?

по моему опыту иногда внутренний join работает медленнее, чем left join
left join указывает оптимизатору с какой таблицы начинать выборку,
а join - декартово произведение таблиц,
но это справедливо только для моего опыта в пределах FB

Mark Barinstein
4. Приведите план запроса
к сожалению не нашел сходу в контроль центре, как план запроса в текстовом виде получить...
прикладываю картинку
...
Рейтинг: 0 / 0
19.05.2009, 07:16
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / IBM DB2, WebSphere, IMS, U2 [игнор отключен] [закрыт для гостей] / select double records
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]