Здравствуйте, уважаемые коллеги. Представляю Вашему вниманию новый выпуск рассылки.

Содержание рассылки:

• Новости
• Дополнения к предыдущему выпуску
• Обнаруженные ошибки
• Оператор выборки данных SELECT

Новости

• Добавлена возможность указывать имена полей или алиасов вместо порядкового номера поля в ORDER BY для запросов с UNION, INTERSECT и EXCEPT.
• В EXECUTE IMMEDIATE добавлена новая опция WITH RESULT SET {ON | OFF}, позволяющая указать, будет ли динамический SQL возвращать набор данных. Эта опция позволяет точно контролировать выполнение динамического SQL. Но самое главное - она позволяет ASA узнать при компиляции хранимых процедур, будут ли они возвращать набор данных. До этого патча любое использование оператора динамического SQL в хранимой процедуре приводило к тому, что ASA считала ее процедурой, возвращающей данные, хотя реально, процедура могла и не возвращать данные, если динамический SQL ничего не возвращал.

Есть и вторая интересная новость: вышел долгожданный драйвер для ASA, позволяющий в Borland Delphi напрямую работать с ASA через dbExpress. Размер 55 кб.

Некоторые вещи были обнаружены мной после выхода предыдущего выпуска рассылки:

• Пользовательские функции, оказывается, в ASA полностью идентичны хранимым процедурам. Если посмотреть на системную таблицу SYSPROCEDURE, где хранятся их описания, то видно, что никакого флага принадлежности объекта к процедуре или функции в этой таблице нет. Если Вам необходимо динамически сгенерить скрипт удаления функций из базы данных, то Вы можете применить оператор DROP PROCEDURE. Так же процедуры, не возвращающие наборы данных, могут быть использованы в качестве функций, но я бы не рекомендовал это делать, так как эта возможность явно не документирована.

• Индексы можно создавать на любые вычисляемые поля. Соотвественно, если вычисляемое поле рассчитывается с помощью UDF, то получается, что индексы можно создавать не только на системные, но и на пользовательские функции. К чести оптимизатора ASA он достаточно грамотно может использовать такие индексы, даже если в запросе используется не само имя вычисляемого поля, а его выражение. Например, в таблице Table1 есть вычисляемое поле CalcField COMPUTE(UDF_Calc(Field1)):

  -- Делаем индекс по вычисляемому полю CREATE INDEX idx_Test ON Table1 (CalcField); -- Запрос 1 SELECT * FROM Table1 ORDER BY CalcField; -- Запрос 2 SELECT * FROM Table1 ORDER BY UDF_Calc(Field1); -- Запрос 3 SELECT * FROM Table1 WHERE UDF_Calc(Field1) = 1; -- Запрос 4 SELECT * FROM Table1 WHERE UDF_Calc(Field2) = 1;

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

Мной была обнаружена критическая ошибка сервера. Она возникает, если в таблицу заносится запись со значением NULL в поле NOT NULL, в триггере BEFORE этому полю присваивается NOT NULL значение и существует триггер AFTER. Без триггера AFTER ошибки не происходит. В ближайшее время я выложу этот баг для команды разработчиков ASA, думаю, в следующем EFB эта ошибка будет исправлена. В дополнение, если у кого то есть собственная коллекция обнаруженных багов ASA, не исправленных в текущей версии EFB, то можно высылать их описания мне по почте. Можно будет включить в рассылку описания обнаруженных русскоязычными разработчиками багов, централизованно отсылать их в Sybase и отслеживать исправления ошибок в EFB. Более подробно со мной пообщаться можно по почтовому ящику ascrus@mail.ru

Оператор выборки данных в ASA обладает достаточно большой функциональностью. Я не буду рассматривать его реализацию на диалекте TSQL, она полностью совместима с реализацией SELECT в MSSQL и ASE. Гораздо интересней будет описать отличия его реализации на WatcomSQL от TSQL:

• Расширенная реализация JOIN
  Поддерживаются 3 вида способов соединения таблиц в JOIN - по связи таблиц (KEY JOIN) , по совпадающим именам полей (NATURAL JOIN) и по указанным в запросе (обычные JOIN стандарта ANSISQL):

  -- Запрос 1 - обычный INNER JOIN SELECT * FROM fin_code c   INNER JOIN fin_data d ON d.code = c.code; -- Запрос 2 - обычный LEFT JOIN с фильтром по году SELECT * FROM fin_code c   LEFT JOIN fin_data d ON d.code = c.code AND d.year = 2000; -- KEY INNER JOIN, аналогичен запросу 1 SELECT * FROM fin_code c   KEY JOIN fin_data d; -- KEY INNER JOIN, аналогичен запросу 1 SELECT * FROM fin_code c   JOIN fin_data d; -- KEY LEFT JOIN, аналогичен запросу 2 SELECT * FROM fin_code c   KEY LEFT JOIN fin_data d ON d.year = 2000; -- KEY LEFT JOIN, аналогичен запросу 2 SELECT * FROM fin_code c   LEFT JOIN fin_data d WHERE d.year = 2000; -- LEFT JOIN, не аналогичен запросу 2, так как -- пропущено ключевое слово KEY и в ON указано -- условие соединения. В данном случае запрос -- является ошибочным SELECT * FROM fin_code c   LEFT JOIN fin_data d ON d.year = 2000; -- NATURAL INNER JOIN, аналогичен запросу 1 SELECT * FROM fin_code c   NATURAL JOIN fin_data d; -- NATURAL LEFT JOIN, аналогичен запросу 2 SELECT * FROM fin_code c   NATURAL LEFT JOIN fin_data d ON d.year = 2000;

  Из вышеприведенных примеров видно, что если ключевое слово ON для JOIN не указано, то такое объединение таблиц будет восприниматься в запросе как KEY JOIN, ASA попробует связать таблицы по FOREIGN KEY и если внешний ключ не будет обнаружен, то ASA возбудит ошибку. Если Вы хотите, чтобы связь происходила по совпадающим именам полей, то всегда необходимо писать ключевое слово NATURAL, хотя я бы не рекомендовал такой рискованный способ соединения таблиц в запросе, так как любое переименование полей в таблице приведет к неправильной работе таких запросов, где она участвует. При явном указании ключевых слов KEY или NATURAL можно писать дополнительные условия соединения в ON. В отличие от NATURAL, KEY JOIN наоборот позволяет не только упростить читабельность запросов, но и при изменении структуры таблиц (переименование полей, изменение внешнего ключа и т.д.) сохранить работоспособность объединения в запросах.

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

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

  /* Сумма продаж продукции на 16.03.2000 с их оценкой, но без учета продукции, сумма которой превышает 1500 */ SELECT p.name, Sum(p.unit_price * p.quantity) as Summa,   CASE WHEN sum(p.unit_price * p.quantity) >= 1000 THEN 'Отлично'     ELSE 'Так себе' END as Descript FROM product p   KEY JOIN sales_order_items i ON i.ship_date = '2000-03-16' GROUP BY p.name HAVING Sum(p.unit_price * p.quantity) <= 1500

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

  Перепишем вышепереведенный запрос:

  /* Сумма продаж продукции на 16.03.2000 с их оценкой, но без учета продукции, сумма которой превышает 1500 */ SELECT p.name, Sum(p.unit_price * p.quantity) as Summa,   CASE WHEN Summa >= 1000 THEN 'Отлично'     ELSE 'Так себе' END as Descript FROM product p   KEY JOIN sales_order_items i ON i.ship_date = '2000-03-16' GROUP BY p.name HAVING Summa <= 1500

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

• Расширения с помощью WITH
  Судя по всему, WITH перекочевал в ASA из IBM DB2, однако с учетом того, что он появится в новой версии MSSQL, этот оператор уже постепенно становиться стандартом SQL. Данный оператор расширяет возможности команды SELECT по работе с подзапросами по технологии Common Table Expression (CTE). Чтобы лучше понять его назначение рассмотрим пример:

  SELECT dept_id, n FROM (   SELECT dept_id, count(*) AS n   FROM employee GROUP BY dept_id ) AS a WHERE a.n = (         SELECT max(n)         FROM (           SELECT dept_id, count(*) AS n           FROM employee GROUP BY dept_id         ) AS b       )

  С точки зрения семантики запрос правильный, однако, с точки зрения читабельности и легкости написания запрос нельзя назвать легким. Конечно, если подзапрос на получение кол-ва dept_id по employee используется во многих запросах, легче всего его оформить как представление (view) и дальше уже использовать его, вместо подзапроса. Однако создание представления может быть нежелательно по следующим причинам:

  • Запрос генерируется динамически и вызывается в динамическом SQL из хранимой процедуры. Создание или удаление представления вызывает блокирование системных таблиц, что не желательно делать во время выполнения хранимой процедуры.
  • У подзапроса есть фильтр по динамическим значениям параметров, переданным, например, в хранимую процедуру. Конечно, можно использовать глобальные переменные в представлениях или оформить подзапрос в виде хранимой процедуры, однако в предыдущем выпуске рассылки я уже не рекомендовал без серьезного обоснования использовать эти решения. В данном случае такие решения не подходят.
  • Усложняется читабельность. Чтобы понять логику действия запроса, ссылающегося на представление, придется посмотреть на исходный скрипт представления.
  • Усложняется модифицируемость. Если подзапрос необходимо изменить, то придется изменять представления, что может гарантировать множество проблем, если на представление есть ссылки из других запросов.

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

  WITH CountEmployees(dept_id, n) AS (   SELECT dept_id, count(*)   FROM employee GROUP BY dept_id ) SELECT dept_id, n FROM CountEmployees WHERE n = ( SELECT max(n) FROM CountEmployees )

  В данном примере оператор WITH описывает локальное для данного запроса представление CountEmployees, на которое и ссылается сам запрос. Оператор WITH позволяет описать множество представлений и использовать их в одном запросе. Над такими представлениями можно проводить в запросе любые операции так, как если бы они были реальными представлениями.

  Оператор WITH позволяет реализовать еще одну полезную функцию в запросах - возможность написания иерархических запросов. Думаю, всем приходилось сталкиваться с трудностями реализации таких запросов в ANSISQL. Решения получались трудоемкие и неэффективные. В данном случае WITH позволяет писать такие запросы простым и элегантным способом:

  WITH RECURSIVE   manager ( emp_id, manager_id,             emp_fname, emp_lname, mgmt_level ) AS     ( ( SELECT emp_id, manager_id, -- главный запрос                emp_fname, emp_lname, 0         FROM employee AS e         WHERE manager_id = emp_id )         UNION ALL       ( SELECT e.emp_id, e.manager_id, -- рекурсивный запрос                e.emp_fname, e.emp_lname, m.mgmt_level + 1         FROM employee AS e JOIN manager AS m               ON e.manager_id = m.emp_id AND                  e.manager_id <> e.emp_id AND                  m.mgmt_level < 20 ) ) SELECT * FROM manager ORDER BY mgmt_level, emp_lname, emp_fname

  Работает этот запрос по следующей схеме: в WITH описывается главный запрос "Manager", который возвращает все записи верхнего уровня (т.е. не имеющие родителей). Через UNION ALL к нему подключается рекурсивный запрос, который имеет право ссылаться на главный запрос "Manager" и возвращает все дочерние записи для обрабатываемой записи главного запроса. Обратите внимание на вычисляемое поле "mgmt_level" - в главном запросе оно возвращается как ноль. В рекурсивном же запросе оно устанавливается, как значение этого поля родительской записи с увеличением на единицу. В итоге каждый уровень дерева будет пронумерован. Таким же образом можно организовать сортировку дерева, здесь можно посмотреть мой пример, как это реализуется.

• INTERSECT - сложение запросов

  Данные оператор позволяет выбрать все записи запроса, которые существуют в другом запросе.

  SELECT FIELD1, FIELD2 FROM Table1 INTERSECT DISTINCT SELECT FIELD1, FIELD2 FROM Table2

  В приведенном примере будут возвращены сгруппированные по полям Field1, Field2 записи таблицы Table1, значения которых существуют в таблице Table2. При сравнении полей между этими таблицами, значение NULL будет так же учитываться, т.е. если Table1.Field1 = NULL и Table2.Field1 = NULL, то условие Table1.Field1=Table2.Field1 будет считаться истинным (в отличие, например, от INNER JOIN, EXISTS() и IN()).

  Если необходимо получить записи Table1 с повторяющимися значениями, значения которых столько же раз повторяются в таблице Table2, то необходимо воспользоваться оператором INTERSECT ALL. Например, в следующем примере можно подсчитать, сколько раз записи таблицы Table1 повторяются в таблице Table2:

  WITH t AS (   SELECT FIELD1, FIELD2   FROM Table1   INTERSECT ALL   SELECT FIELD1, FIELD2   FROM Table2 ) SELECT Field1, Field2, Count(Field1) as CountRecord FROM t GROUP BY Field1, Field2

• EXCEPT - вычитание запросов

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

  SELECT FIELD1, FIELD2 FROM Table1 EXCEPT DISTINCT SELECT FIELD1, FIELD2 FROM Table2

  В приведенном примере будут возвращены сгруппированные по полям Field1, Field2 записи таблицы Table1, значения которых не существуют в таблице Table2. Как и в INTERSECT при сравнении полей будут учитываться поля с NULL значениями (в отличие от NOT EXISTS() и NOT IN()).

  Так же как и у INTERSECT записи можно вычитать без группировки, написав EXCEPT ALL. Хочу подчеркнуть, что результат действия EXCEPT и EXCEPT ALL будет разный (то же самое относиться и к оператору INTERSECT). Например, допустим, есть 2 таблицы со значениями:

  "TABLE1" Field1 Field2   1    a   1    a   2    NULL   3    c   4    d "TABLE2" Field1 Field2   1    a   2    NULL   3    NULL

  Выполняем запросы:

  -- Запрос с группировкой SELECT FIELD1, FIELD2 FROM Table1 EXCEPT DISTINCT SELECT FIELD1, FIELD2 FROM Table2 Результат выполнения: Field1 Field2   3    c   4    d -- Запрос без группировки SELECT FIELD1, FIELD2 FROM Table1 EXCEPT ALL SELECT FIELD1, FIELD2 FROM Table2 Результат выполнения: Field1 Field2   1    a   3    c   4    d

  Видно, что в запросе без группировки вернулось на одну запись больше. Это объясняется тем, что в таблице Table1 было 2 записи с одинаковым значением полей (1, 'a'), а в таблице Table2 существовала всего одна запись с такими значениями. После проведения вычитания оставшаяся запись в таблице Table1 и была возвращена.

  Если подвести итог по сложению и вычитанию запросов, то можно сказать что INTERSECT DISTINCT и EXCEPT DISTINCT складывают и вычитают по значениям, полученным после группировки записей, а INTERSECT ALL и EXCEPT ALL позаписно. С учетом того, что в данных случаях по группам обработать записи гораздо легче, чем по всему множеству, быстрее и эффективнее будут выполняться операторы DISTINCT, а не ALL. Что непривычно, так как в остальных случаях групповой запрос всегда потребует больше времени на выполнение (например, UNION всегда будет медленнее UNION ALL).

• Продолжение описания оператора SELECT
• Оператор UPDATE
• Оператор DELETE

До встречи в следующей рассылке, с уважением, ASCRUS.