Тут пролетал запрос, возвращающий

← →
dtm (2003-09-23 17:51) [0]

Уважаемые, напомните, плз. Вроде как его тут публиковал уважаемый Johnmen, но на тот момент я его скопировать не смог. Буду благодарен, если опубликуете его снова.

← →
kaif © (2003-09-23 17:54) [1]

Дырки это как? Поясни.

← →
Vlad © (2003-09-23 17:54) [2]

Странно... поиск по слову "дырки" ничего не дал :)

← →
dtm (2003-09-23 18:02) [3]

Кхм... Шутнички ;)

Генерятся уникальные номера записей. Потом некоторые из них удаляются. Необходимо, чтобы генератор не просто выдавал следующее значение, а выбирал минимальное из не использованных. Требуется это не для ключевого поля, а просто по условию постановки задачи - каждая операция (именно о них речь) должна иметь уникальный номер (не ключевое поле), причём "дырки" в нумерации недопускаются.

← →
Johnmen © (2003-09-23 18:03) [4]

================================================================== SELECT T.ID+1, MIN(T1.ID)-1, MIN(T1.ID)-T.ID-1 FROM TABLE1 T JOIN TABLE1 T1 ON T.ID<T1.ID WHERE T.ID<=1000 AND T1.ID<=1000 GROUP BY T.ID HAVING MIN(T1.ID)-T.ID>1 >Получаем промежутки в которых удалены записи. >В первой колонке будет начальный ID промежутка, во второй - >конечный. >В третьей количество удаленных записей в промежутке. ==================================================================

← →
kaif © (2003-09-23 18:05) [5]

А удалять операции задним числом допускается?

← →
kaif © (2003-09-23 18:09) [6]

2 Johnmen © (23.09.03 18:03) [4]
Очень хитрый запрос... Я пока не врубился. Но на декартово произведение сильно смахивает. А для ID < 100000 заработает? Или лучше не пробовать?

← →
Val © (2003-09-23 18:17) [7]

#8O ну и запросик. А без join, так, можно?
SELECT T.ID+1, MIN(T1.ID)-1, MIN(T1.ID)-T.ID-1
FROM TABLE1 T, TABLE1 T1
WHERE T.ID<=1000 AND T1.ID<=1000 and
T.ID<T1.ID
GROUP BY T.ID
HAVING MIN(T1.ID)-T.ID>1

← →
NickBat © (2003-09-23 18:21) [8]

Хороший запрос. Только сдается мне будет очень медленный. Я такую проблему решал занесением всех удаленных уникальных номеров в отдельную табличку, а потом из нее можно автоматом вытянуть номер, можно пользователю предложить. Все прозрачней и надежней.

← →
Johnmen © (2003-09-23 18:22) [9]

>kaif © (23.09.03 18:09)

Не совсем декартово :), т.к. есть условие соединения.
Для ID < 100000 заработает. Это просто верхняя планка значений ID. Можно выкинуть это WHERE.
Попробовать обязательно стоит ! :)

← →
Johnmen © (2003-09-23 18:27) [10]

>Val © (23.09.03 18:17)

Конечно :)

>NickBat © (23.09.03 18:21)

Зависит от количества записей, естественно.

← →
Val © (2003-09-23 18:30) [11]

>Johnmen © (23.09.03 18:27) [10]
пугает, знаете-ли :)

← →
dtm (2003-09-23 18:38) [12]

2 NickBat

Не могу, если я пользователю буду ещё вываливать диалог выбора удалённого кем-то номера, это обязательно вгонит его в долговременный ступор с глубоким залипанием =(. Со всеми вытекающими для меня как разработчика.

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

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

← →
MsGuns © (2003-09-23 18:39) [13]

Адын мааалэнкый мынус - пропускает, сабака, ведущую дырку.
Савсэм &8*

← →
kaif © (2003-09-23 18:39) [14]

2 Johnmen © (23.09.03 18:22) [9]
:))))

вообще-то я бы наверно на IB хранимую процедуру написал вместо такого запроса...

что-нибудь вроде:

CREATE PROCEDURE ALL_HOLES_IN_T1
RETURNS(
HOLE_START INTEGER,
HOLE_END INTEGETR,
HOLE_WIDTH INTEGER)
AS
DECLARE VARIABLE ID INTEGER;
DECLARE VARIABLE LAST_ID INTEGER;
BEGIN
FOR SELECT ID FROM T1 ORDER BY ID
INTO :ID DO
BEGIN
IF ID - LAST_ID > 1 THEN
BEGIN
HOLE_START = LAST_ID + 1;
HOLE_END = ID - 1;
HOLE_WIDTH = ID - LAST_ID - 1;
SUSPEND;
END
LAST_ID = ID;
END
END

Тогда тормоз будет зависеть линейно от числа записей, а не квадратично.

← →
kaif © (2003-09-23 18:41) [15]

Пардон, скобки как всегда забыл:
IF (ID - LAST_ID > 1) THEN

← →
Sandman25 © (2003-09-23 18:53) [16]

[4] Johnmen © (23.09.03 18:03)

Это похоже на вот это?

select t1.id + 1, t2.id - 1 from t1, t t2 where t2.id = (select min(id) from t where id > t1.id) and t1.id <> t2.id - 1

← →
Sandman25 © (2003-09-23 18:54) [17]

select t1.id + 1, t2.id - 1 from t t1, t t2 where t2.id = (select min(id) from t where id > t1.id) and t1.id <> t2.id - 1

← →
NickBat © (2003-09-23 18:57) [18]

> если я пользователю буду ещё вываливать диалог выбора удалённого кем-то

Зачем диалог?

Есть таблица с удаленными номерами Т1, из нее выбираешь наименьший и вставляешь в основную таблицу Т, из Т1 удаляешь.

← →
MsGuns © (2003-09-23 19:02) [19]

>kaif © (23.09.03 18:39) [14]
Фуй, как скучно ! Никакой фантазии
То ли дело Johnmen ;)))

← →
dtm (2003-09-23 19:09) [20]

[18] NickBat

Как один из вариантов покатит. Вполне, я думаю.

← →
Zacho © (2003-09-23 19:11) [21]

Ну, и свой вариант такого запроса приведу :
SELECT MIN(T.ID)+1
FROM MY_TABLE T
LEFT JOIN MY_TABLE T1
ON T.ID+1=T1.ID
WHERE T1.ID IS NULL
Возвращает первое "свободное" ID.

← →
MsGuns © (2003-09-23 19:12) [22]

select t1.iid + 1, t2.iid - 1
from INV t1, INV t2
where t2.iid = (select min(iid) from INV where iid > t1.iid)
and t1.iid <> t2.iid - 1
union
select 1, t3.iid-1
from INV t3
where t3.iid=(select min(iid) from INV) and (t3.iid>1)

А вот так возвращает даже ведущую дырочку.

← →
kaif © (2003-09-23 19:12) [23]

2 MsGuns © (23.09.03 19:02) [19]
Согласен. Johnmen - профи.
Но я подхожу к программированию как к ремеслу по удовлетворению юзера, а не как к искусству для искусства. Хотя, может, это и неправильно...
Аргумент в пользу запроса Johnmen - переносимость на любой сервер.
Хотя как именно дырки будут устраняться, мне пока непонятно. Лично мне если бы юзеры такую задачу поставили (нумеровать операции с возможностью удалять старые и заполнять дырки), я бы просто вообще убил поле нумерации операций и нумеровал бы их при печати или выводе на экран.

← →
Sandman25 © (2003-09-24 10:05) [24]

[22] MsGuns © (23.09.03 19:12)
Точно! А я и забыл про первую дырку.

[23] kaif © (23.09.03 19:12)
>переносимость на любой сервер.
Нет. На Informix не работает.

← →
Val © (2003-09-24 10:48) [25]

Sandman25 © (24.09.03 10:05) [24]
Нет. На Informix не работает.
Почему? Если заменить join на доп. условие тоже не работает?

← →
Sandman25 © (2003-09-24 11:02) [26]

[25] Val © (24.09.03 10:48)
>Почему? Если заменить join на доп. условие тоже не работает?

Тогда работает.

← →
Sandman25 © (2003-09-24 11:11) [27]

Я тут протестировал скорость запросов в таблице c 165 тыс. записей. Запрос
select t1.id + 1, t2.id - 1 from t t1, t t2 where t2.id = (select min(id) from t where id > t1.id) and t1.id <> t2.id - 1 выполнился за 55 секунд, запрос
SELECT T.ID+1, MIN(T1.ID)-1, MIN(T1.ID)-T.ID-1 FROM TABLE1 T JOIN TABLE1 T1 ON T.ID<T1.ID WHERE T.ID<=1000 AND T1.ID<=1000 GROUP BY T.ID HAVING MIN(T1.ID)-T.ID>1 выполнялся более 3 минут, и я его прервал.
Я думаю, замедление связано с использованием GROUP BY и HAVING.

PS. По id есть индекс, первый запрос я выполнял раньше второго, поэтому выигрыш за счет кэширования исключен.

← →
Sandman25 © (2003-09-24 11:13) [28]

Забыл написать, что я убрал ограничение на ID, поэтому во втором случае запрос был
SELECT T.ID+1, MIN(T1.ID)-1, MIN(T1.ID)-T.ID-1 FROM TABLE1 T, TABLE1 T1 WHERE T.ID<T1.ID GROUP BY T.ID HAVING MIN(T1.ID)-T.ID>1

Как говорил мне тесть "дыры, дырки и дырочки они у женщин, а это - отверстия".

Согласен с kaif ©, что рассматриваемый вопрос лежит не в плоскости практического применения, а с скорее в теоретической, не привязанной к реальным построениям систем.
А по поводу начальной дырки, так нет такого понятия. Поясню.
Есть интервал с дырками. Конечное значение интервала - максимальное значение ID. А вот минимальное почему же 0, или 1 ??? Вовсе нет. Минимальное - минимум ID. И никак иначе. В противном случае придем к нарушению понятия "дырки в интервале". По определению интервал не может начинаться с дырки.
Конечно можно считать минимумом 0 или 1. Но это уже искусственный частный случай.
И, конечно же, самое быстрое здесь это ХП.

(Не может быть, что я столько слов тут написал :))

>Sandman25 © (24.09.03 11:11)

Хороший запрос. И по идее, действительно, должен работать бастрее.
Посмотрел планы и того и другого. В общем, понятно, почему быстрее.

У тебя сама постановка алгоритмики и способа решения неправильная.

Следует сделать иначе - ничего не удалять,
а просто помечать записи(документы) как "удалено, недоступно, заблокировано".

И никаких "дырок" бы не появилось.
Вот так-то.

>Следует сделать иначе - ничего не удалять,
а просто помечать записи(документы) как "удалено, недоступно, заблокировано".

Расходуется лишняя память, замедляется поиск и вставка записей.

Каждому свое.
Извращайся до посинения,
если неспособен оценить трудоемкость/затраты решений.

А если хочешь экономить память и дисковое пространство - не используй версионники, C/S, и Windows вместе с Delphi.
Используй Zx Spectrum 48, все 49152 байта RAM твои...

>Johnmen © (24.09.03 11:43) [31]
Ты же сам говоришь, что это вопрос скорее теоретический и тут же принимаешься критиковать "начальную" дырку с чисто практической т.зр.
;))
Если же иметь в виду "вакантность" UID, то не вижу никакой разницы, откуда его выбирать, т.е. будет это 1 или 1001 - нет для физической модели БД никакой разницы Другими словами, мне больше по душе итоговая последовательность

1,2,3,56,57,58,59,101,102

нежели

56,57,58,59,60,61,62,101,102

Чисто эстетически.

[34] Alex_Raider © (24.09.03 12:48)

Вы принципиальный сторонник отказа от оптимизации? Если клиента что-то не устраивает, пуст покупает четвертый винт и пятый процессор?

>MsGuns © (24.09.03 12:53)

Нет-нет-нет. Именно с теоретической. По-моему, "начальная дырка" это скорее всего "ближе к практике", если вообще тут можно о практике говорить... =o)

>>Вы принципиальный сторонник отказа от оптимизации? Если >>клиента что-то не устраивает, пуст покупает четвертый винт и >>пятый процессор?

Нет, я сторонник здравого смысла.
:-)

← →
dtm (2003-09-24 18:28) [40]

Alex_Raider [32]
> У тебя сама постановка алгоритмики и способа решения неправильная.
>Следует сделать иначе - ничего не удалять,
а просто помечать записи(документы) как "удалено, недоступно, заблокировано".

;) Всё у меня правильно.
Пользователь вызывают новую операцию, она должна уже на этапе создания иметь уникальный номер - она его и имеет, получая от генератор. Если пользователь отменил во время оформления операцию - номер теряется, так как уже был сгенерирован. Естественно, что в базе ничего не остаётся, так что помечать "удалено, недоступно, заблокировано" просто нечего.
А есть ещё софтина, обрабатывающая информацию об операциях, поступающих из разных концов Беларуси, и по правилам ведения работы заказчика (а они определяются международной конвенцией) номера у документов должны быть по возможности последовательные, то есть без "дырок". Может, "дырки" всё равно будут, но для сертификации программы перед использованием я обязан вложить подобную функцию в программу. Иначе никак.
Так что не надо говорить, что кто-то поставил задачу неправильно...

Тут пролетал запрос, возвращающий Найти похожие ветки