Разработка при помощи Apache Derby -- тройной выигрыш: Разработка баз данных при помощи Apache Derby, Часть 7

Дополнительные понятия базы данных

Данная статья представляет важный этап в овладении фундаментальными понятиями баз данных, связанными с базой данных Apache Derby. В данной статье показано, как изменить SQL-запрос для группирования строк и предоставления суммарной статистической информации о базе данных. Затем представлена концепция представления, которое можно использовать для упрощения разработки базы данных при помощи создания виртуальной таблицы с результатами SQL-запроса. Наконец, будут описаны индексы баз данных, которые можно использовать для поиска определенных строк в таблице. После освоения этих дополнительных понятий баз данных пользователи смогут приступить к разработке Java™-приложений баз данных при помощи Apache Derby.

Роберт Бруннер , ученый-исследователь NCSA, старший преподаватель астрономии, Университет штата Иллинойс, г. Урбана-Шампейн

Роберт Дж. Бруннер (Robert J. Brunner) занимается научными исследованиями в Национальном центре по приложениям для суперкомпьютеров и является старшим преподавателем астрономии в университете штата Иллинойс, город Урбана-Шампейн. Автор нескольких книг и множества статей и практических руководств на различные темы.



12.04.2007

Введение

Готовы? В данной статье представлено несколько дополнительных понятий баз данных, включая группировку результатов SQL-запросов, представления баз данных и индексы баз данных, которые будут использоваться вместе с базой данных Apache Derby. Для выполнения упражнений данной статьи необходимо наличие соответствующим образом инициализированной учебной базы данных Apache Derby. Если упражнения данной статьи выполняются после упражнений предыдущих статей данной серии, то учебная база данных уже имеется. В противном случае, или если используется новая установка, гарантирующая возможность точного выполнения упражнений данной статьи, можно загрузить и запустить сценарий SQL, включенный с данной статьей, как это показано в листинге 1.

Листинг 1. Настройка рабочей среды Derby
rb% mkdir derbyWork
rb% cp derby8.zip derbyWork/
rb% cd derbyWork/
rb% unzip derby8.zip 
Archive:  derby8.zip
  inflating: derby.build.sql         
rb% ls 
derby.build.sql derby8.zip
rb% java org.apache.derby.tools.ij < derby.build.sql > derby.build.out 2> derby.build.err 
rb% java org.apache.derby.tools.ij
ij version 10.1
ij> connect 'jdbc:derby:test' ;
ij>

Листинг 1 начинается с создания новой рабочей папки с именем derbyWork, где будет создана учебная база данных. После развертывания файла сценария SQL и копирования его в папку derbyWork обработайте файл сценария derby.build.sql при помощи инструмента ij, как это показано в учебном руководстве "Разработка при помощи Apache Derby -- тройной выигрыш: Разработка баз данных при помощи Apache Derby, часть3" (сайт developerWorks, май 2006 г.).

Данный пример завершается запуском инструмента ij и вызовом команды connect для установления подключения к учебной базе данных. Если получена ошибка, например, ERROR XJ004: Database 'test' not found., значит поврежден файл сценария SQL. Для устранения проблемы просмотрите файлы derby.build.out и derby.build.err и загляните во вторую статью данной серии или в онлайн-документацию по Derby, представленную в разделе Ресурсу данной статьи. Если все работает в соответствии с ожиданиями, отображается запрос ij>, и можно продолжить вызов SQL-запросов, возвращающих объединения строк, а не отдельные строки.


Группировка результатов

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

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

Данный метод может быть очень эффективным, особенно вместе с агрегатными функциями, поскольку его можно использовать для быстрого формирования суммарной статистики для групп связанных строк. Когда в данной серии впервые были описаны SQL-запросы в части 4, обсуждался порядок, в котором Apache Derby обрабатывает различные компоненты SQL-запроса: FROM, WHERE, GROUP BY, HAVING и, наконец, SELECT. Таким образом, перед выбором в SQL-запросе столбцов или результатов выполнения функций группируются строки, удовлетворяющие предложению WHERE, вместе со следующими указаниями в предложении GROUP BY. Листинг 2 содержит явный пример применения данных правил; в нем выполняется вычисление средней цены и количества товаров, предоставленных каждым поставщиком Bigdog's Surf Shop.

Листинг 2. Группировка результатов при помощи предложений GROUP BY и HAVING
ij> SELECT COUNT(p.itemNumber) AS Number, AVG(p.price) AS Average
        FROM bigdog.products AS p, bigdog.vendors AS v
        WHERE p.itemNumber = v.itemNumber
        GROUP BY v.vendorNumber ;
NUMBER     |AVERAGE    
-----------------------
6          |31.4633    
2          |39.9500    
2          |23.9500    

3 rows selected
ij> SELECT v.vendorNumber, 
        COUNT(p.itemNumber) AS Number, AVG(p.price) AS Average
        FROM bigdog.products AS p, bigdog.vendors AS v
        WHERE p.itemNumber = v.itemNumber
        GROUP BY v.vendorNumber
        HAVING v.vendorNumber > 1 ;
VENDORNUMB&|NUMBER     |AVERAGE    
-----------------------------------
2          |2          |39.9500    
3          |2          |23.9500    

2 rows selected
ij>

Группировка значений NULL

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

Первый запрос в данном примере объединяет таблицу bigdog.products с таблицей bigdog.vendors при помощи столбца itemNumber. Но перед выбором строк из новой объединенной таблицы связанные строки, тем не менее, группируются при помощи предложения GROUP BY v.vendorNumber. Предложение GROUP BY не просто сортирует строки, полученные в результате запроса, но и группирует их, поскольку возвращаться или использоваться в агрегатных функциях могут только сгруппированные данные. Другими словами, можно только выбрать столбцы, перечисленные в предложении GROUP BY, значения из агрегатных функций, применяемых к любому столбцу объединенных таблиц, константы или специальные регистры

Второй запрос дополняет первый с помощью добавления предложения HAVING. Предложение HAVING можно использовать для ограничений операций предложения GROUP BY при помощи ограничения допустимых значений столбцов, которые можно использовать для группирования связанных строк. В этом примере группируются только строки со значением vendorNumber большим 1, то есть результаты показаны только в двух группах. Как и в случае с предложением GROUP BY, в предложении HAVING можно использовать несколько столбцов, но любые столбцы в списке предложения HAVING также должны находиться в списке предложения GROUP BY, иначе механизм базы данных Apache Derby совершает ошибку.


Хорошее представление бесценно

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

Простейший способ достичь такого результата заключается в создании новой таблицы и использовании оператора SQL INSERT INTO вместе с требуемым запросом для явного создания новой таблицы и заполнения ее соответствующими данными, как это показано в части 6 данной серии. Хотя этот метод довольно прост, у него имеется несколько недостатков. Во-первых, в базе данных находятся дублирующие данные, а это требует дополнительных ресурсов. Во-вторых, и, возможно, это более важно, при изменении исходных данных, дублирующие данные не изменяются, их необходимо обновлять. Учитывая важность этой концепции, должен быть лучший способ.

И не удивительно, что такой способ имеется, и называется он представлением. Представление виртуальной таблицы, созданной при выполнении запроса. Создание и удаление представления - довольно простой процесс, как это показано в формальном синтаксисе в листинге 3.

Листинг 3. Формальный синтаксис для работы с представлением
CREATE VIEW schema.viewName
    [ ( columnName1 [, columnName2] * ) ]
AS sqlQuery ;

DROP VIEW schema.viewName ;

Для создания представления используется оператор SQL CREATE VIEW. При создании нового представления можно явно называть столбцы в представлении (например, columnName1, columnName2 и т.д.) или неявно копировать имена столбцов из столбцов, перечисленных в предложении SELECT SQL-запроса, используемого для создания представления. Как и в случае с таблицей, представление следует назначить определенной схеме, в противном случае оно назначается схеме по умолчанию APP. Для удаления представления используется предложение DROP VIEW вместе с полностью классифицированным именем представления. Обе этих операции с представлениями показаны в листинге 4.

Листинг 4. Создание и удаление представления в Apache Derby
ij> CREATE VIEW bigdog.vendorList (Name)
    AS SELECT DISTINCT vendorName FROM bigdog.vendors ;
0 rows inserted/updated/deleted
ij> SELECT * FROM bigdog.vendorList ;
NAME                          
------------------------------
Mikal Arroyo Incorporated     
Quiet Beach Industries        
Luna Vista Limited            

3 rows selected
ij> DROP VIEW vendorList ;
ERROR X0X05: Table 'VENDORLIST' does not exist.
ij> DROP VIEW bigdog.vendorList ;
0 rows inserted/updated/deleted
ij> SELECT * FROM bigdog.vendorList ;
ERROR 42X05: Table 'BIGDOG.VENDORLIST' does not exist.
ij>

Первый оператор в листинге 4 создает новое представление с именем vendorList в схеме bigdog. Это представление состоит из одного столбца с явным именем Name, заполненного определенными именами из столбца vendorName таблицы bigdog.vendors. Затем применяется оператор SELECT, выполняющий запрос к представлению vendorList, показывая, как можно использовать представление такими же способами, как и таблицы.

Поскольку это представление больше не требуется, оно удаляется. Первый используемый оператор SQL не включает полностью квалифицированного имени представления. В этом случае имя схемы отсутствует, что приводит к ошибке. После правильного применения оператора DROP VIEW с использованием полностью классифицированного имени представления bigdog.vendorList, операция удаления выполняется успешно. Для подтверждения, что представления больше не существует, выполняется повторная попытка выполнения исходного оператора SQL SELECT, что приводит к ошибке, так как представление было удалено.

Повторный вызов определения представления: Это виртуальная таблица. Хотя представление практически идентично реальной таблице. Например, представление можно использовать в SQL-запросах непосредственно или как часть объединения. Поскольку представление является виртуальным, его нельзя использовать для непосредственного изменения данных. Но любые изменения в таблицах, содержащих исходные данные, автоматически распространяются на данные в представлении. Такое последнее преимущество использования представлений показано в листинге 5.

Листинг 5. Использование представлений в Apache Derby
ij> CREATE VIEW bigdog.inventory AS SELECT p.price, p.description AS "Item",
    v.vendorName AS "Vendor"
    FROM bigdog.products AS p, bigdog.vendors AS v
    WHERE p.itemNumber = v.itemNumber AND p.price > 40.00 ;
0 rows inserted/updated/deleted
ij> SELECT * FROM bigdog.inventory ;
PRICE   |Item                                    |Vendor                        
--------------------------------------------------------------------------------
99.99   |Beach umbrella                          |Luna Vista Limited            
49.95   |Female bathing suit, one piece, aqua    |Mikal Arroyo Incorporated     

2 rows selected
ij> UPDATE bigdog.products SET price = 44.95 WHERE itemNumber = 8 ;
1 row inserted/updated/deleted
ij> SELECT * FROM bigdog.inventory ;
PRICE   |Item                                    |Vendor                        
--------------------------------------------------------------------------------
99.99   |Beach umbrella                          |Luna Vista Limited            
49.95   |Female bathing suit, one piece, aqua    |Mikal Arroyo Incorporated     
44.95   |Blue-stripe beach towel                 |Luna Vista Limited            

3 rows selected
ij> DROP VIEW bigdog.inventory ;
0 rows inserted/updated/deleted
ij>

В данном примере сначала создается новое представление bigdog.inventory, являющееся результатом объединения двух таблиц. Это новое представление содержит инвентарный список для всех товаров с розничной ценой более US$40. Обратите внимание, что в этом представлении стобцы названы в соответствии с именами столбцов, перечисленных в предложении SELECT запроса, создающего представление. Затем проверяется содержимое этого представления с помощью запроса, извлекающего все строки из представления.

Следующий оператор используется для увеличения цены одного товара в таблице bigdog.products, являющейся одной из базовых таблиц для создания представления inventory. С помощью повторного выполнения запроса SELECT видно, что представление содержит три элемента, что демонстрирует динамическую природу представлений в Apache Derby. Наконец, представление удаляется из базы данных, поскольку больше оно не требуется. Если нужно переименовать представления, то вам не повезло, в данное время это сделать в Apache Derby невозможно. Вместо этого необходимо удалить исходное представление и создать новое с нужным именем.

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


Ускорение работы при помощи индексов

Простой запрос в листинге 5 SELECT * FROM bigdog.inventory ; извлекает все строки из представления inventory. Для извлечения необходимых данных такой тип запроса выполняет операцию, известную как полный просмотр таблицы, то есть просматривается и обрабатывается каждая строка таблицы (или, в данном случае, представления). Просмотр таблицы включает извлечение данных с дисков и размещение их в памяти компьютера, где выполняется анализ строк. Для небольших таблиц, например, таблиц данной серии статей, такая обработка не приводит к значительному ухудшению производительности. Для больших таблиц просмотр может стать дорогой операцией, которой следует избегать по мере возможности. Можно предположить, что поможет использование предложения WHERE, так как оно приводит к извлечению меньшего набора данных; но по-прежнему требуется выполнить поиск в каждой строке таблицы для определения соответствующих строк.

Проблема аналогична поиску определенных цитат или информации в книге. Можно начать сначала и просмотреть всю книгу, отмечая все соответствующие страницы. И так до конца книги. Или можно просмотреть индекс в книге и быстро найти нужные страницы. Обычно гораздо быстрее просмотреть индекс. К счастью, в Apache Derby имеется подобная конструкция. Формальный синтаксис для создания, удаления и переименования индекса представлен в листинге 6.

Листинг 6. Формальный синтаксис для SQL операций с индексами в Apache Derby
CREATE [UNIQUE] INDEX schema.indexName
ON schema.tableName ( columnName [ ASC | DESC ]
    [ , columnName [ ASC | DESC ]] * ) ;

DROP INDEX schema.indexName ;
    
RENAME INDEX indexName TO newIndexName ;

Данные три оператора соответствуют простому формальному синтаксису. Во-первых, оператор CREATE INDEX создает новый индекс в заданной таблице. Длина имени индекса ограничена 128 символами, в Apache Derby это имя должно быть уникальным в пределах заданной схемы. При создании нового индекса можно задать один или несколько столбцов (максимум 16), но любой заданный столбец можно использовать в индексе только один раз. По умолчанию индекс создается в восходящем порядке для каждого столбца, но с помощью ключевого слова DESC можно задать для определенного столбца порядок по убыванию. Ключевое слово UNIQUE определяет, что в таблице не может быть несколько строк с одним значением индекса. Другими словами, каждое значение индекса соответствует одной строке таблицы, которую можно использовать вместе с первичными ключами для обеспечения целостности данных в таблице.

Для удаления индекса используется оператор DROP INDEX вместе с полностью классифицированным именем индекса. С другой стороны, можно переименовать индекс с помощью оператора RENAME INDEX, если только индекс находится в текущей схеме. Таким образом, необходимо постоянно работать в схеме по умолчанию или, что более вероятно, использовать оператор SET SCHEMA, как это показано в листинге 7.

Листинг 7. Использование индексов в Apache Derby
ij> SET SCHEMA bigdog ;
0 rows inserted/updated/deleted
ij> CREATE INDEX productsIndex ON products(itemNumber) ;
0 rows inserted/updated/deleted
ij> RENAME INDEX productsIndex TO pi ;
0 rows inserted/updated/deleted
ij> DROP INDEX pi ;
0 rows inserted/updated/deleted
ij>

Первый оператор в данном примере SET SCHEMA bigdog ; задает схему по умолчанию для текущего подключения к базе данных - схему bigdog. В результате больше не требуется включать имя схемы bigdog в состав полностью классифицированных имен. Следующий шаг заключается в создании нового индекса с именем productsIndex в таблице products, что выполняется при помощи столбца itemNumber, по умолчанию используется восходящий порядок. Далее индекс productsIndex переименовывается в pi. Наконец, индекс удаляется при помощи оператора DROP INDEX. Обратите внимание, как просто создавать эти операторы SQL, поскольку не требуется всегда указывать имя схемы.

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

Чтобы понять это, снова представьте аналогию с книгой, но на этот раз предположим, что в книге всего несколько страниц. В этом случае быстрее просмотреть страницы, чем найти индекс, затем значения в идексе, и, наконец, найти нужные страницы. То же самое справедливо и для индексов баз данных. Если таблица маленькая или если запрос возвращает большое количество строк (скажем, более 25% от всех строк в таблице), использование индекса может замедлить запрос вследствие использования дополнительных ресурсов для поиска в индексе ключевых значений, а затем поиска соответствующих строк в таблице базы данных. Другим важным фактом является то, что индекс может улучшить производительность запроса только в том случае, если столбцы, используемые для создания индекса, также включены в предложения WHERE запроса. В противном случае, по-прежнему необходимо выполнить полный просмотр таблицы.


Заключение

В данной статье представлено три новых понятия баз данных: группирование результатов запроса, представления и индексы. Все эти понятия обеспечивают дополнительную функциональность для создания более эффективных запросов, упрощения разработки приложений или для улучшения производительности существующих запросов. На данном этапе серии "Разработка при помощи Apache Derby" были представлены различные фундаментальные понятия баз данных, которые необходимо усвоить перед тем, как приступать к разработке приложений баз данных. В следующей статье будет начата разработка Java-приложений, использующих базу данных Apache Derby.


Загрузка

ОписаниеИмяРазмер
Derby SQL script for this articlederby.build.sql2KB

Ресурсы

Научиться

Получить продукты и технологии

  • Загрузите Apache Derby с домашней страницы проекта Apache Derby;
  • Добавьте новизны в ваши следующие проекты разработки при помощи пробного ПО IBM, доступного в виде файлов для загрузки и на DVD.

Обсудить

Комментарии

developerWorks: Войти

Обязательные поля отмечены звездочкой (*).


Нужен IBM ID?
Забыли Ваш IBM ID?


Забыли Ваш пароль?
Изменить пароль

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Профиль создается, когда вы первый раз заходите в developerWorks. Информация в вашем профиле (имя, страна / регион, название компании) отображается для всех пользователей и будет сопровождать любой опубликованный вами контент пока вы специально не укажите скрыть название вашей компании. Вы можете обновить ваш IBM аккаунт в любое время.

Вся введенная информация защищена.

Выберите имя, которое будет отображаться на экране



При первом входе в developerWorks для Вас будет создан профиль и Вам нужно будет выбрать Отображаемое имя. Оно будет выводиться рядом с контентом, опубликованным Вами в developerWorks.

Отображаемое имя должно иметь длину от 3 символов до 31 символа. Ваше Имя в системе должно быть уникальным. В качестве имени по соображениям приватности нельзя использовать контактный e-mail.

Обязательные поля отмечены звездочкой (*).

(Отображаемое имя должно иметь длину от 3 символов до 31 символа.)

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Вся введенная информация защищена.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Open source, Information Management
ArticleID=208896
ArticleTitle=Разработка при помощи Apache Derby -- тройной выигрыш: Разработка баз данных при помощи Apache Derby, Часть 7
publish-date=04122007