Статья:
Q&A — К вопросу об идентификаторах

Автор(ы): Иван Бодягин
Дата: 07.02.2004
Уникальная идентификация записей в таблице, является практически основой реляционных СУБД. Вообще в реляционной теории предполагается, что если две записи ни чем друг от друга не отличаются, то это явная избыточность, и количество таких записей можно сократить до одной. Собственно вопросам этой самой идентификации, каковых возникает на удивление много, и посвящен этот FAQ.

Авторы:
Иван Бодягин

Аннотация:
Уникальная идентификация записей в таблице, является практически основой реляционных СУБД. Вообще в реляционной теории предполагается, что если две записи ни чем друг от друга не отличаются, то это явная избыточность, и количество таких записей можно сократить до одной. Собственно вопросам этой самой идентификации, каковых возникает на удивление много, и посвящен этот FAQ.

С одной стророны, узнал из статьи кое-что новое для себя, за это автору спасибо. А с другой — ничего не сказано про уникальную идентификацию в других бд! Термин sequence вообще не был упомянут, хотя сиквенсы используются в большинстве БД.

Здравствуйте, Евгений Коробко, Вы писали:

ЕК> А с другой — ничего не сказано про уникальную идентификацию в других бд! Термин sequence вообще не был упомянут, хотя сиквенсы используются в большинстве БД.
Если есть желание дописать — велкам. Присылайте на адрес в профайле doc'и в формате RSDN ML

Автор(ы): The RSDN Group
Дата: 27.10.2002

, я дополню и включу в соавторы.
У каждого сервера куча нюансов, все описывать одному — замаешься.

ЕК>> А с другой — ничего не сказано про уникальную идентификацию в других бд! Термин sequence вообще не был упомянут, хотя сиквенсы используются в большинстве БД.

оракл упоминается — уже хорошо, вот еще бы в статьи про блокировки&эскалацию упомянули бы реализацию оракла и у Gt_ бы наступило счастье

M>Если есть желание дописать — велкам. Присылайте на адрес в профайле doc'и в формате RSDN ML

Автор(ы): The RSDN Group
Дата: 27.10.2002

, я дополню и включу в соавторы.
M>У каждого сервера куча нюансов, все описывать одному — замаешься.

а какие еще варианты кроме sequence и автоикремента ?

Gt_

Здравствуйте, <Аноним>, Вы писали:

А>а какие еще варианты кроме sequence и автоикремента ?

GUID.

А вам выслал текст про сиквенсы.

Здравствуйте, Аноним, Вы писали:

А> вот еще бы в статьи про блокировки&эскалацию упомянули бы реализацию оракла и у Gt_ бы наступило счастье
А смысл? Её там нет, по ряду объективных причин, хотя очевидно, не помешала бы.

А>а какие еще варианты кроме sequence и автоикремента ?
Ну во-первых сиквенсы тоже разные бывают.
А во вторых есть еще IB'шные генераторы, Субэйсовский IDENTITY, который совершенно по другим принципам работает, Access'овский автоинкремент, на который без слез не взглянешь и так далее... Вообщем есть где развернуться.
Там такой зоопарк — двух одинаковых реализаций не встретишь, только ключевые слова отчасти пересекаются.

Здравствуйте, Евгений Коробко, Вы писали:

ЕК>А вам выслал текст про сиквенсы.
Да, я получил, но если честно, то даже на набросок не тянет... Во-первых Oracle И Postgree лучше разделить, сиквенсы там все-таки отличаются. и есть замечания даже по тому небольшому тексту.
Я вечером отпишу подробнее...

Это не статья, это некоторое дополнение. А работа с сиквенсами практически идентично в Oracle и PostgreSQL

Здравствуйте, Иван Бодягин, Вы писали:

ИБ>Упростить постраничный вывод вряд ли возможно. Существуют СУБД, в которых введен специальный синтаксис для вывода данных постранично, но это не более чем syntactic sugar, так как производятся те же действия, что и в примерах выше, просто часть реализации остается за кадром.

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

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

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

возникает вопрос, как организовать постраничный вывод для разных видов информации?
для новостей, статей, пользователей, ...

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

вариант как должен поступать код для разных баз данных:
MS SQL:
1 — добавляем TOP после SELCT и при чтении выкидываем первые ненужные записи
(разбор запроса и орагизация тройного select не беру — слишком сложно автоматически разбирать и перестраивать запрос)
2 — заменяем все между SELECT и FROM на count(*)
минусы: надо проматывать первые записи руками и делать второй запрос
(если работаем в .Net, то первые записи вообще придется качать на клиента)

MySql
1 — добавляем SQL_CALC_FOUND_ROWS после SELECT и в конец запроса дописываем LIMIT Page, PageSize
2 — берем общее число записей функцией FOUND_ROWS()
плюсы: минимальные изменения в тексте запроса, второй запрос вообще не нужен
(самый удобный способ не только для меня, но и для сервера, потому что он сможет сделать всякие оптимизации запроса)

Oracle
1 — открываем курсор, проматываем первые ненужные записи, читаем страницу
2 — заменяем все между SELECT и FROM на count(*)
плюсы: в первом случае нет изменений текста запроса
минусы: серверный курсор несколько дороговат для такой операции
и опять же изменения запроса в пункте 2

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

вот такие соображения

Здравствуйте, Banch, Вы писали:

если ты админ (а речь насколько я понимаю именно про них, и простых смертных мы не трогаем) и готов мириться с потерей производительности (а она всегда будет при реализации универсальных решений) то вот тебе способ — качай всё на клиента и уж на клиенте резвись как хочешь. если неохота, то есть универсальный (в рамках SQL-92) способ пронумеровать набор данных (и постраничный вывод становится делом техники) — танцуй от него.

задача определяет инструмент решения задачи ... чтож тут такого??? тут либо шашечки, либо ехать...

В египте интернет дурацкии поетому критика потом будет...




Merle

Здравствуйте, Banch, Вы писали:

B>логично сделать промежуточный слой доступа к базе, в него будет передаваться запрос, номер и размер страницы; возвращаться должен resultset и общее количество записей
Ну... Немного не так.
Собственно практически классика — промежуточный слой состоит из...
1ый слой: Функция возвращающая бизнес-объект, не важно, просто объект, список объектов, класс, еще что-то. В обязанности этого слоя не входит непосредственное обращение к базе он выполняет некоторые внутренние преобразования и проверки. Даже если никаких проверок не надо и получить объект можно непосредственно из базы, все равно все внешние запросы должны идти через этот промежуточный слой.
2й слой: Функция выполняющая непосредственный запрос к базе, и получающая реляционные данные.
Соответственно никаких запросов передавать не надо. Из вне идет только вызов функции возвращающий нужный бизнес-объект, с указанием номера страницы и, возможно, количества записей на страницу.

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


B>вариант как должен поступать код для разных баз данных:
B>MS SQL:
Ну собственно вариантов запросов к MSSQL'ю больше двух и они описаны в статье..

B>(если работаем в .Net, то первые записи вообще придется качать на клиента)
Не а, ничего качать не придется..

B>(самый удобный способ не только для меня, но и для сервера, потому что он сможет сделать всякие оптимизации запроса)
Как я уже говорил — просто писать меньше, то есть не более чем syntactic sugar, причем не всегда удачный. Ни каких оптимизаций сервер не применяет. Возможно теоретически они и могут быть, но пока что о таковых мне не известно.

Здравствуйте, Merle, Вы писали:

M>Сам запрос находится либо в хранимке, либо в коде функции обращающейся непосредственно к базе.
проблема возникает когда нет строго очерченных бизнес объектов
могут добавляться новые, причем в требованиях указано — без написания спец кода (хранимок и прочего ...)

M>Ну собственно вариантов запросов к MSSQL'ю больше двух и они описаны в статье..
эт поняно
я привел наиболее мне подходящий

B>>(если работаем в .Net, то первые записи вообще придется качать на клиента)
M>Не а, ничего качать не придется..
ADO.NET поступает именно так — выкачивает весь resultset на клиента
я это читал в RSDN журнале и сам видел по тестам
или я ошибаюсь?

M>Ни каких оптимизаций сервер не применяет. Возможно теоретически они и могут быть, но пока что о таковых мне не известно.
7.2.10 How MySQL Optimizes LIMIT

Здравствуйте, Banch, Вы писали:

B>проблема возникает когда нет строго очерченных бизнес объектов
B>могут добавляться новые, причем в требованиях указано — без написания спец кода (хранимок и прочего ...)
Это уже совсем другая задача. К тому же что есть "спец код"? Ничто не мешает писать те же заросы в обычном коде точно таким же образом.
Да и вообще, СУБД к подобным задачам, с импровизацией на вольную тему, в принципе очень плохо приспособлены, особенно блокировочники.

B>ADO.NET поступает именно так — выкачивает весь resultset на клиента
B>я это читал в RSDN журнале и сам видел по тестам
B>или я ошибаюсь?
Ну, если в тупую использовать ADO.NET, то конечно. Нет, я имел вииду, что надо использовать запросы описаные в статье. При этом ничего лишнего на клиента не отправляется.

B>7.2.10 How MySQL Optimizes LIMIT
Ну нет...
Все эти оптимизации не выходят за рамки того же TOP в MSSQL'е, и даже наоборот — TOP по умнее будет. Так что еще раз повторюсь, MySQL'евский LIMIT — это всего лишь синтаксис.

Здравствуйте, Иван Бодягин, Вы писали:

ИБ>Статья:

Боюсь, в следующем фрагменте Вы допустили фактическую ошибку — малозначимую в контексте статьи, но тем не менее..

Oracle
Здесь можно отделаться более простым запросом, но тоже не совсем тривиальным. Эта СУБД дает некоторый доступ к своей внутренней информации, и внутри у нее записи пронумерованы. Но проблема в том, что сервер нумерует строки для своих нужд до сортировки, поэтому приходится делать вложенный запрос с сортировкой.

SELECT RowNum, U.* FROM
(SELECT * FROM user_tables ORDER BY tablespace_name) U

Такое впечатление, что Вы путаете оракловые понятия rowid и rownum. rowid — это не "внутри записи пронумерованы", но в первом приближении похожая вещь. По сути, это закодированный адрес записи в файлах БД. Что касается rownum, он никак не связан с нумерацией записей "внутри". Просто, формируя dataset (отрабатывая выражения в select-части), сервер может пронумеровать возвращаемые строки. Смысла в этом обычно действительно немного — хотя, для генерации какого-нибудь html может оказаться полезным.

Здравствуйте, <Аноним>, Вы писали:

А>Просто, формируя dataset (отрабатывая выражения в select-части), сервер может пронумеровать возвращаемые строки.
Ну, примерно это я и имел ввиду, просто не очень четко сформулировал.
В любом случае, спасибо за поправку..

DECLARE @Page int, @PageSize int, @MaxRecord varchar(10), @Count varchar(10)

-- номер страницы
SET @Page = 100

-- размер страницы
SET @PageSize = 20

SET @MaxRecord = cast((@Page * @PageSize + @PageSize) as varchar(10))
SET @Count = cast(@PageSize as varchar(10))

EXECUTE ('SELECT * FROM
(SELECT TOP ' + @Count + ' * FROM
(SELECT TOP ' + @MaxRecord + ' * FROM sysobjects
ORDER BY name ASC) SO1
ORDER BY name DESC) SO2
ORDER BY name')

В данном коде есть баг (фича? ). Если номер страницы указать больше, чем есть данных, то возвращается всё равно последняя страница, а не пустая выборка. Это не очень удобно, когда paging делается без предварительной выборки кол-ва записей, а используя возвращённое кол-во записей.

Здравствуйте, Иван Бодягин, Вы писали:

Немного критики.

1. Основной упор в изложении статьи делается на MS SQL server. Я никоим образом не считаю себя специалистом по Oracle, но у меня создалось такое впечатление, что про факты об Oracle упоминается скорее обзорно. Идентификация записей (rowid) присутствует и в Oracle, но об этом ничего не сказано. Для нумерации в Oracle удобно использовать аналитические функции, например:

SELECT 
  row_number() over (order by tablespace_name) as rownumber, 
  U.* 
FROM user_tables U 
ORDER BY tablespace_name

2.

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

В реляционной теории рассматривается множество кортежей (строк) отношения (таблицы), а добавление кортежа в отношение суть операция объединения множеств. Поэтому проблема дубликатов невозможна в принципе, а о сокращении, имхо, говорить несколько некорректно.

С точки зрения программиста MS SQL статья несомненно полезна.

Здравствуйте, Mystic, Вы писали:

M>1. Я никоим образом не считаю себя специалистом по Oracle, но у меня создалось такое впечатление, что про факты об Oracle упоминается скорее обзорно.
Да это вообщем-то и не скрывалось.
Этот FAQ задумывался как расширяемый по разным серверам, если у кого вдруг возникнет такое желание. Возиться с Ораклом было откровенно лень.

M> Идентификация записей (rowid) присутствует и в Oracle, но об этом ничего не сказано.
Там много про что не сказано.

M>Для нумерации в Oracle удобно использовать аналитические функции, например:
Можно, было бы желание.
Расписывать все это дело можно долго и с удовольствием, но пока должным уровнем энтузиазма никто не запасся.

M>В реляционной теории рассматривается множество кортежей (строк) отношения (таблицы), а добавление кортежа в отношение суть операция объединения множеств. Поэтому проблема дубликатов невозможна в принципе, а о сокращении, имхо, говорить несколько некорректно.
Строго говоря — верно, но одной из задачь было изложить как можно проще, чтобы пагубно не повлиять на неокрепшие умы. И с этой точки зрения формулировка, думаю, вполне корректна... В крайнем случае можно изменить на "... с точки зрения вселенского Дао ..."

M>С точки зрения программиста MS SQL статья несомненно полезна.
Да она вообще полезна, судя по количеству вопросов на эту тему в форуме.