упорядочить таблицу с более 40млн строками :(

Ork Yason · Сообщение **Ork Yason** » 31 май 2007, 16:27

есть таблица биллинга
в день туда добавляется до 200тыщ строк
структура:
время нач, время_кон, код_юзера, айпи, код_направления_траффика, входящий, исходящий, деньги

после одной давней поломки, забыл сделать примари кэй на первые 4 поля...

биллинг иногда поглючивает и выдает две строки, отличающиеся только траффиком и деньгами

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

WildSery · Сообщение **WildSery** » 31 май 2007, 16:47

А что именно смущает? Только объём?

Ork Yason · Сообщение **Ork Yason** » 31 май 2007, 16:53

я не могу придумать метод перебрать все это не сильно загружаю рабочую машину

запрос определяющий наличие одинаковых строк в одном из дней - может выполнятся более 10минут (п4 3000)

а у меня статистика более чем за год!

я думаю просто пойти по базе по строчно и сравнивать две строки рядом, если дублируются - то создать таблицу с их копиями, а потом удалить их из основной...
но я даже не представляю сколько это времени займет... а это критично...

WildSery · Сообщение **WildSery** » 31 май 2007, 17:02

Обычно делают поле ID для суррогатного PK. Ну да ладно.
Есть ли возможность на копии базы на другой машине выполнить

Код: Выделить всё

select 1 from SuperTable order by время нач, время_кон, код_юзера, айпи

и замерить сколько времени займёт сортировка таблицы? Мне кажется, дождаться можно.
А если дождаться можно - пишем ХП, которая в FOR SELECT с такой сортировкой выбирает данные, проверяет на совпадение с предыдущей (запомненной) строкой и если дубликат - апдейт одной (если там сложить или что надо) и делит дубликата.

Ork Yason · Сообщение **Ork Yason** » 31 май 2007, 17:18

суррогатный не пойдет

весь смысл в том чтобы сделать привзяку время-юзер-айпи - на это заточено много запросов - которые тормозят из-за объема и отстутсвия уникального индекса

ок, пасиба, пробую с группировкой...

WildSery · Сообщение **WildSery** » 31 май 2007, 19:18

Ork Yason писал(а):ок, пасиба, пробую с группировкой...

Не нужна группировка. Только сортировка. И то только для того, что если как есть читать, никто не гарантирует, что нужные записи подряд будут.

CyberMax · Сообщение **CyberMax** » 01 июн 2007, 01:57

А иметь суррогатный PK на Integer (BigInt) и иметь уникальный ключ по "время нач, время_кон, код_юзера, айпи" вера не позволяет? А на строку этого биллинга ты что, через четыре поля ссылаешься?

Ork Yason · Сообщение **Ork Yason** » 01 июн 2007, 10:00

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

CyberMax · Сообщение **CyberMax** » 01 июн 2007, 10:46

Ork Yason писал(а):запрос определяющий наличие одинаковых строк в одном из дней - может выполнятся более 10минут (п4 3000)

Приведи его вместе с планом.

Ork Yason · Сообщение **Ork Yason** » 01 июн 2007, 11:13

Код: Выделить всё

select
    ut.dt_from,
    code_user,
    ip,
    count(*)
from
    user_traff ut
where
    ut.p_date = "08.05.2007"
group by
    ut.dt_from,
    code_user,
    ip
having
    count(*) > 1

План
PLAN SORT ((UT INDEX (USER_TRAFF_IDX2)))

индекс по полю P_DATE
в поле dt_from - это дата + время
а p_date - это дата (сделано для отчетов, типа итоги по всему месяцу, с разбивкой по дням)

CyberMax · Сообщение **CyberMax** » 01 июн 2007, 11:35

Сделай составной индекс по полям ut.dt_from, code_user и ip. Запусти запрос и расскажи результат в виде плана и статистики.

Ork Yason · Сообщение **Ork Yason** » 01 июн 2007, 12:24

сделал

индекс делал около 20минут

индекс использовал

время подготовки запроса 109мс
выполнения 31сек

индексированных чтений 396412
других нет

Сообщение **kdv** » 01 июн 2007, 21:56

по-моему лучше всего создать новую БД с правильным индексом и перелить туда данные, например IBPump-ом.