Технология поиска, фильтрации и сортировки данных

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

Отличительная особенность операций по поиску, фильтрации и сортировке данных заключается в том, что они осуществляются в режиме открытой таблицы или формы. Забегая несколько вперед, следует отметить главную отличительную особенность этих операций по сравнению с запросами на вы- борку к базам данных — результатом операций по поиску или фильтрации данных является изменение состояния просмотра открытой таблицы (формы), но не самих данных, которые физически остаются в той же таблице и в том же порядке. Например, результатом поиска какой-либо конкретной записи в открытой таблице является установление табличного курсора на ключевое поле искомой записи-строки или «показ» (отображение) в открытой форме полей искомой записи.

Собственно поиск данных реализуется в виде:

• поиска записи по ее номеру;

• поиска записи (записей) по значению (значениям) какого-либо поля;

• поиска записей с помощью фильтров (фильтрация). Поиск записи по ее номеру производится на основе механизма распределения записей по страницам файла данных. Результатом такого поиска является перевод табличного курсора в ключевое поле соответствующей записи или «показ» в форме полей искомой записи.

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

Фильтр представляет собой набор условий, применяемых для отбора подмножества записей. Результатом фильтрации является «показ» (отображение) в открытой таблице или форме только отфильтрованных записей с временным «скрытием» всех остальных записей. При этом остальные записи физически никуда не перемещаются, не удаляются и вновь отображаются в открытой таблице после «снятия» фильтра.

Набор условий, определяющих фильтр, формируется в различных СУБД по-разному, но общепринятым является использование выражений в условиях отбора данных. Под выражением в данном случае понимается структура, подобная обычному математическому выражению. Аргументами выражения могут быть числа, даты, текст, имена полей, которые соединя- ются знаками математических операций, неравенств (+, -, *, /, >, <, =) и логических операций (AND, OR, NOT). При этом текстовые значения и аргументы заключаются в кавычки («Иванов»), даты в символы # (#01.01.98#).

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

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

4.3. Технология проектирования запросов в реляционных СУБД Запросы являются наиболее распространенным видом обработки данных при решении пользователями АИС тематических, логических, статистических и технологических информационных задач. Иначе говоря, для удовлетворения сложных информационных потребностей пользователи «общаются» с базой данных через запросы.Запрос представляет собой спецификацию (предписание) на специальном языке (языке базы данных) для обработки данных. В реляционных СУБД запросы к базе данных выражают- ся, соответственно, на языке SQL.

Формирование запросов в СУБД может осуществляться в специальном редакторе (командный режим) или через наглядно-диалоговые средства (конструкторы) и пошаговые мастера формирования запросов. Сформированный запрос в виде SQL-инструкции сохраняется в файле базы данных и впоследствии специальной командой СУБД может запускаться (открываться) на выполнение.

Все многообразие запросов можно проклассифицировать схемой, приведенной на рис. 7.10.

Запросы на выборку данных (select)

По условиям выборки

Рис. 7.10. Классификационная схема запросов

реляционной базы данных

Сточки зрения решаемых информационных задач и формы результатов исполнения запросов их можно разделить на три группы:

- запросы на выборку данных; - запросы на изменение данных; - управляющие запросы. Запросы на выборку применяются для решения тематических, логических и статистических информационных задач и относятся к одному

С параметрами

С подчиненными запросами

С изменяемым условием

По схеме отбора

На объединение таблиц

Из нескольких таблиц

По информационным задачам и формам представления результата

Из одной таблицы

Запросы на изменение данных

Запросы

На создание таблицы (select into)

На обновление (update)

На добавление (insert into)

На удаление (delete)

На создание индекса (create index)

Управляющие запросы

На добавление поля (alter table)

На создание таблицы (create table)

На удаление таблицы (drop table)

из наиболее часто применяемых видов запросов. Данный вид запросов реализуется SQL-инструкцией SELECT с предложением FROM.

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

Запросы на выборку классифицируются по двум критериям — по формированию условий выборки и по схеме отбора данных.

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

• запросы со статическими (неизменяемыми) условиями отбора;

• запросы с параметрами;

• запросы с подчиненными запросами. По схеме отбора данных запросы на выборку подразделяются также на три группы:

• запросы на выборку данных из одной таблицы;

• запросы на выборку данных в один набор из нескольких таблиц;

• запросы на объединение данных. Запросы на выборку данных из одной таблицы по смыслу и назначению сходны с фильтрацией данных в открытой таблице. Различие заключается лишь в форме представления результата (в частности, запросом на выборку можно отображать не просто подмножество записей исходной таблицы, но и подмножество полей исходной таблицы) и в технологии последующей работы с результатом (над набором данных, как уже отмечалось, можно исполнить другой запрос).

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

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

• запросы на сочетание данных;

• запросы на соединение данных;

• запросы на объединение данных. Запросы на сочетание строятся на основе операции скалярного произведения реляционных таблиц и по смыслу направлены на формирование полного набора сочетании строк-записей, представленных в исходных таблицах. Запросы на сочетание строятся на основе SQL-инструкции SELECT и предложения FROM c пpoстым перечислением отбираемых полей и их таблиц.Запросы на соединение, в свою очередь, подразделяются на запросы на основе внутреннего соединения (INNER JOIN) и запросы на основе правого или левого внешнего соединения (RIGHT JOIN и LEFT JOIN).Запросы на выборку, строящиеся на основе внутреннего соединения,

реализуют рассматриваемую по реляционной модели данных операцию соединения реляционных таблиц.

Запросы на внешнее соединение строятся на основе модификации операции соединения. При левом внешнем соединении (LEFT JOIN) строки итоговой таблицы образуются из всех строк первой (левой) таблицы с «прицеплением» строк второй таблицы, если значения поля соединения совпадают. Если среди строк второй (правой) таблицы нет строк с соответствующим значением поля соединения, то в итоговой таблице присо- единяемые поля заполняются пустыми значениями. При правом внешнем соединении (RIGHT JOIN) строки итоговой таблицы строятся по противоположному правилу.

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

Запросы на объединение данных реализуют операцию объединения реляционных таблиц и решают задачи создания наборов данных, объединяющих однотипные по смыслу записи (по группам однотипных полей) из нескольких таблиц. Строятся запросы на объединение через SQL- инструкцию SELECT—UNION SELECT. При этом запрос состоит из первой инструкции SELECT, в которой перечисляются отбираемые поля и условия отбора записей из первой таблицы, и последующих инструкций UNION SELECT, в которых указываются отбираемые поля и условия отбора записей из других таблиц. Обязательным условием является одинаковое количество отбираемых полей в первой инструкции SELECT и последующих инструк- циях UNION SELECT. При этом типы и длина полей в первой инструкции и последующих инструкциях могут не совпадать.

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

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

Групповые операции. В процессе отбора и обработки данных важное значение имеют группирование данных по значениям какого-либо поля и осуществление тех или иных операций над сгруппированными записями. Групповые операции осуществляются на основе SQL-предложения GROUP BY в сочетании со статистическими функциями SQL.

Запросы по поиску повторов применяются для анализа наличия повторяющихся групп значений по определенному полю и их количественных (статистических) данных. Более сложные статистические

задачи решают перекрестные запросы. Название «перекрестный» отражает принцип формирования и представления результатов таких запросов.

В исходной (базовой) таблице для перекрестного запроса выбираются два поля. По повторяющимся значениям одного поля формируются названия заголовков строк итоговой (сводной) таблицы — «боковик» сводной таблицы. По повторяющимся значениям другого поля образуются названия столбцов итоговой таблицы — «шапка» сводной таблицы. В ячейках сводной таблицы отражаются результаты статистических функций по группам данных в каких-либо полях исходной таблицы.

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

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

Существует четыре разновидности запросов на изменение:

• запросы на удаление;

• запросы на обновление;

• запросы на добавление;

• запросы на создание таблицы. Запрос на добавление осуществляет добавление группы записей из одной или нескольких таблиц в конец другой или группы других таблиц. При этом количество и типы полей при вставке записей должны совпадать. Запросы на добавление могут вставлять записи из текущей (открытой) базы данных в другую (внешнюю) базу данных. В этом случае запросы на добавление реализуют функции экспорта данных, решая задачи по обмену, архивации или резервированию данных. Однако чаще данные запросы применяются для добавления записей из одной таблицы базы данных в другую таблицу. Управляющие запросы. В большинстве современных СУБД проектирование и создание таблиц осуществляются через специальные диалогово-наглядные конструкторы или пошаговые мастера. Тем не менее, как уже отмечалось, в составе языка описания данных DDL имеются ряд SQL-инструкций, на основе которых строятся запросы по созданию/модификации реляционных таблиц или отдельных их элементов. Такие запросы называются управляющими.

Имеется четыре вида управляющих запросов:

• запросы на создание таблицы;

• запросы на добавление в существующую таблицу нового поля или индекса;

• запросы на удаление таблицы или индекса определенного поля таблицы;

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

Запросы на добавление полей или индексов реализуются SQL- инструкцией ALTER TABLE с использованием зарезервированных слов ADD COLUMN (добавить поле) и ADD CONSTRAINT (добавить индекс). Этим же запросом с помощью зарезервированного слова DROP COLUMN можно уда- лить поле из существующей таблицы. Как правило, запросы на добавление полей также используются для создания внешних ключей, задающих связи- отношения между таблицами. С этой целью используются зарезервированные слова FOREIGN KEY и REFERENCES.

Запросы на удаление таблицы или индекса реализуются SQL- инструкцией DROP TABLE с указанием имени удаляемой таблицы или индекса.

Запросы на создание индекса реализуются SQL-инструкцией CREAТЕINDEX с использованием зарезервированного слова UNIQUE для запрета повтора значений в индексируемом поле и необязательного предложения WITH с параметрами DISALLOW NULL и IGNORE NULL для запрета/разрешения нулевых (пустых) значений в индексируемом поле. Зарезервированное слово PRIMARY позволяет определить создаваемый индекс ключом таблицы (при этом создаваемый индекс по умолчанию является уникальным, т.е. повторы значений не допускаются).

Подчиненные (сложные) запросы. Как уже отмечалось, источником данных для запросов могут быть результаты выполнения других запросов. Возможны два варианта построения таких запросов.

Первый вариант реализуется через указание в SQL-инструкциях в качестве имен таблиц и имен полей имен запросов и полей запросов. Синтаксис таких запросов ничем не отличается от обычных запросов, а его исполнение осуществляется в две фазы. По запуску основного запроса сначала неявно запускается запрос, формирующий источник данных, и по завершению его исполнения запускается основной (внешний) запрос.

Второй вариант реализуется через включение в тело внешней (главной) SQL-инструкции внутренней инструкции SELECT. При этом результат исполнения внутренней инструкции SELECT используется для формирования условия отбора записей в главном (внешнем) запросе или в качестве выраже- ния для нового вычисляемого поля. Такие запросы называются подчиненными.

Предикаты ANY и SOME («для некоторых»), являющиеся синонимами, используются для отбора в главной SQL-инст-рукции тех записей, которые удовлетворяют сравнению с какой-либо записью (т. е. хотя бы с одной), из отобранных во внутренней инструкции SELECT.

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

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

В общей схеме обработки запроса выделяют:

• лексический и синтаксический разбор запроса;

• логическую оптимизацию;

• семантическую оптимизацию;

• построение процедурных планов выполнения запросов и выбор оптимального;

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

Логическая оптимизация запроса может включать различные эквивалентные преобразования, «улучшающие» представление запроса. Такие преобразования можно разбить на три группы:

• преобразования предикатов сравнения;

• преобразования порядка реляционных операций (соединения, объединения, выборки);

• приведение запросов с подчиненными запросами к запросам на соединение (JOIN).

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

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

После логической и семантической оптимизации строится процедурный план выполнения запросов. Процедурным планом запроса называется детализированный порядок выполнения операций доступа к базе данных физического уровня. Уже упоминавшаяся многовариантность способов выполнения SQL-инструкций соответственно приводит к набору альтернативных процедурных планов выполнения запросов, среди которых

оптимизатор запросов ядра СУБД должен выбрать оптимальный в соответствии с определенными критериями.