Технолог сформировал шаблон соответствия для своего SQL и сделал свои данные undo временными
Обычно я беру три из четырех присланных пользователями вопросов за последние два месяца и представляю эти вопросы и мои ответы здесь в журнале Oracle Magazine в рубрике Ask Tom. Однако в предыдущей раз и в трех последующих я обзорно рассмотрю некоторые ключевые возможности Oracle Database 12c. Все эти возможности как часть презентации "12 Things About Oracle Database 12c" были мною представлены на конференции Oracle OpenWorld 2012 в Сан-Франциско (слайды этой презентации можно найти на сайте asktom.oracle.com в закладке Files). Первые три возможности Oracle Database 12c, которые я рассматривал последний раз, были: усовершенствованные значения по умолчанию, увеличение максимальной размерности типов данных и топ-n запросы. В этом выпуске я рассматриваю новое предложение шаблона соответствия строк и изменения в данных undo временных таблиц, произошедшие в Oracle Database 12c.
Шаблон сопоставления строк
Как известно, язык SQL предоставляет возможность "смотреть" влево и вправо в результирующем наборе. Это достигается путем соединения многих таблиц и применения предикатов к строкам. Но нельзя "видеть" строки ниже и выше в результирующем наборе. Некоторые возможности просмотра выше и ниже были включены в Oracle Database 8i. Для этого предназначаются аналитические оконные функций. С их помощью можно не только "смотреть" влево и вправо в результирующем наборе, но и "видеть" выше и ниже, применяя, например, LAG, LEAD, FIRST_VALUE, LAST_VALUE и NTH_VALUE. Это - чрезвычайно мощные функции, и они отрыли совершенно новый способ анализа данных в языке SQL. Однако наряду с мощными возможностями, эти функции имеют некоторые ограничения.
Часто при обработке требуется распознать образ данных в упорядоченном потоке данных. Например, у Вас есть журнал аудита, содержащий столбцы: имя пользователя, происшедшее событие и временная метка, показывающая, когда имело место событие. Допустим, необходимо найти всех тех, кто использовали "приложение X", затем "приложение Y", после этого "приложение Z" и в итоге вернулись назад в определенную часть "приложения X" . Предыдущие, следующие и события между такими распознаваемыми событиями могут повторяться бесчисленное число раз. Для некоторых пользователей может не быть событий между интересующими действиями, а для других - сотни и тысячи событий. Попытка поиска с помощью аналитических оконных функций быстро потерпит неудачу. Вы не знаете, как много строк назад или вперед в результирующем наборе следует просмотреть. Анализ с помощью функций LAG и LEAD не может реально помочь в этом. В конце концов, Вы будете вынуждены выполнять множественные проходы данных, использовать большое число рефлексивных соединений и повторных сортировок. Запрос, который Вам придется написать, будет чрезвычайно сложным, и стоимость его выполнения чрезмерно высока.
В Oracle Database 12c появился шаблон сопоставления строк, реализованный предложением MATCH_RECOGNIZE. Это предложение позволяет разбивать выбираемый набор данных (например, журнал аудита) на непересекающиеся наборы (по именам пользователей в журнале аудита), сортировать эти наборы (по временной метке в журнале аудита) и затем производить поиск по шаблону, который охватывает множество строк в таких секционированных, отсортированных наборах. Для приведенного выше примера с журналом аудита можно запросить все строки для приложений X, Y и Z. Для этого Вы должны разбить строки на секции по именам пользователей и отсортировать их для каждого пользователя по временной метке. После это Вам может потребоваться найти одну или более строк с приложением X, за которой следует одна или более строк приложения Y, затем одна или более строк приложения Z и, наконец, строка с интересующей Вас определенной частью приложения X. Вы можете произвести всю эту обработку за один проход данных. При этом нет необходимости в выполнении рефлексивных соединений, декартовых произведений и скалярных подзапросов. Запрос относительно просто кодировать. Предложение MATCH_RECOGNIZE очень компактное. Производительность выполнения такого запроса будет намного лучше, чем без предложения MATCH_RECOGNIZE.
Чтобы продемонстрировать рассматриваемую новую возможности я собираюсь проанализировать набор данных о запасах на складе. Часто при анализе таких данных возникает потребность поиска по шаблонам вида V или W. Это позволяет выяснить, в какие момент времени достигались максимальные значения запасов, происходящее вслед за этим падение цен и последующая серия повышения цен. Хотелось бы знать, когда шаблон вида V начал проявляться, когда завершилось падение, и снова было достигнуто верхнее значение. Начну пример с определения простой таблицы:
SQL> create table stocks 2 ( symbol varchar2(10), 3 tstamp date, 4 price number, 5 primary key (symbol,tstamp) 6 ) 7 organization index 8 / Table created.
Теперь добавим немного данных для анализа:
SQL> declare 2 l_data sys.odciNumberList := 3 sys.odciNumberList 4 ( 35, 34, 33, 34, 35, 5 36, 37, 36, 35, 34, 35, 36, 37 ); 6 l_cnt number := l_data.count; 7 begin 8 for i in 1 .. l_cnt 9 loop 10 insert into stocks 11 ( symbol, tstamp, price ) 12 values 13 ('XYZ', sysdate-l_cnt+i, l_data(i) ); 14 end loop; 15 commit; 16 end; 17 / PL/SQL procedure successfully completed.
Поскольку этот набор данных очень мал, я могу отобразить результаты анализа с помощью простой символики ASCII, как это показано в листинге.
Листинг кода 1: Вывод V-образного изменения цены с помощью символов звездочки.
SQL> select symbol, tstamp, price, 2 rpad('*',price,'*') hist 3 from stocks 4 order by symbol, tstamp; SYMBOL TSTAMP PRICE HIST ---------- -------------- ----- -------------------------------------- XYZ 01-SEP-12 35 *********************************** XYZ 02-SEP-12 34 ********************************** XYZ 03-SEP-12 33 ********************************* XYZ 04-SEP-12 34 ********************************** XYZ 05-SEP-12 35 *********************************** XYZ 06-SEP-12 36 ************************************ XYZ 07-SEP-12 37 ************************************* XYZ 08-SEP-12 36 ************************************ XYZ 09-SEP-12 35 *********************************** XYZ 10-SEP-12 34 ********************************** XYZ 11-SEP-12 35 *********************************** XYZ 12-SEP-12 36 ************************************ XYZ 13-SEP-12 37 *************************************
Набор данных содержит только 13 строк, поэтому для проведения анализа подойдет использование грубого силового метода, и я смогу без труда увидеть шаблон, который ищу. Я ясно наблюдаю два раза V-образное изменение этих данных. Первого сентября начинается V-изменение, приводящее к нижней точке третьего сентября и к пику седьмого сентября. Также легко обнаруживается второе V; оно начинается седьмого (начало второго соответствия шаблону изменения данных - это окончание первого; задумайтесь, как вы бы смогли задать это с помощью базового SQL), подъем из нижней точки начинается десятого, а пик снова достигается тринадцатого. Поэтому мне понадобится получить две строки данных, каждая с тремя соответствующими датами. Мне бы хотелось, чтобы Вы подумали, как написать команду SQL в Oracle Database 11g релиз 2 и как вывести две строки с искомыми данными (вывод повторяющейся даты седьмого сентября заставит использовать хитрые методы). Это можно сделать, но довольно сложно. Да и производительность в лучшем случае будет вызывать вопросы.
Запросить данные MATCH_RECOGNIZE с помощью предложения довольно просто. Пример:
SQL> SELECT * 2 FROM stocks MATCH_RECOGNIZE 3 ( PARTITION BY symbol 4 ORDER BY tstamp 5 MEASURES 6 STRT.tstamp AS start_tstamp, 7 LAST(DOWN.tstamp) AS bottom_tstamp, 8 LAST(UP.tstamp) AS end_tstamp 9 ONE ROW PER MATCH 10 AFTER MATCH SKIP TO LAST UP 11 PATTERN (STRT DOWN+ UP+) 12 DEFINE 13 DOWN AS DOWN.price < PREV(DOWN.price), 14 UP AS UP.price > PREV(UP.price) 15 ) MR 16 ORDER BY MR.symbol, MR.start_tstamp; SYMBOL START_TST BOTTOM_TS END_TSTAM ------ --------- --------- --------- XYZ 01-SEP-12 03-SEP-12 07-SEP-12 XYZ 07-SEP-12 10-SEP-12 13-SEP-12
Рассмотрю запрос строка за строкой. В первых двух строках определяется запрос, в котором задается разбиение на секции и упорядочение, за которым следует применение моего шаблона. Такой запрос может быть любым: содержать соединения, агрегирования и т.д. Предложение MATCH_RECOGNIZE начинается с конца строки 2 и содержит строки с 3 по 15.
В строках 3 и 4 задаю секции и упорядочение потока данных. Разбиение на секции основывается на столбце symbol, который определяет склад. В рамках каждого склада данные упорядочиваются по временной метке: от самой старой к самой последней. Стоит заметить здесь, что мой результирующий набор детерминирован. Поскольку главный ключ - это пара SYMBOL, TSTAMP, набор строк будет детерминированным, не изменяющимся от выполнения к выполнению. Этот факт очень важен при поиске по шаблону, так как необходимо быть уверены в том, что строки, отвечающие шаблону поиска, были получены в "стабильном" состоянии, иными словами, в детерминированном состоянии. В противном случае два выполнения одного и того же запроса могут вернуть различные ответы! См. bit.ly/Z6nxLL для дальнейших разъяснений и примеров того, почему такое детерминированное поведение желательно и обычно необходимо для корректных результатов
Теперь я собираюсь немного забежать вперед и рассмотреть в запросе ту часть, в которой находится шаблон:
11 PATTERN (STRT DOWN+ UP+) 12 DEFINE 13 DOWN AS DOWN.price < PREV(DOWN.price), 14 UP AS UP.price > PREV(UP.price)
Мне необходим шаблон данных, соответствующий произвольным строкам, за которыми следуют одна или более строк с падающей ценой, после которых следуют одна или более строк с повышающейся ценой. Поэтому мой шаблон содержит строки с 11 по 14. Я произвожу поиск любой начальной строки, за которой следуют одна или несколько строк "down", после которых следуют одна или несколько строк "up". Для задания "любой строки" я использую корреляционное имя STRT (в качестве такового можно использовать достаточно произвольное имя, оно может быть любым произвольным идентификатором). Поскольку имени STRT нет во фразе DEFINE (секция определения), оно может соответствовать любой строке; следовательно, каждая строка в результирующем наборе потенциально может соответствовать и стать строкой STRT. Но, чтобы строка стала строкой STRT, за ней должна следовать одна или более строк DOWN (на это указывает квалификатор регулярных выражений "+" после DOWN). Теперь строка DOWN определена для данного случая: строка становится DOWN только в том случае, если цена в ней меньше цены в предыдущей строке результирующего набора (напомним, это - детерминировано упорядоченный поток данных). До тех пор, пока в следующих строках цена снижается, как задано в определении, они соответствуют строкам DOWN. Когда, в конце концов, выбирается строка, не отвечающая заданному критерию для строки DOWN , выборка продолжается в надежде на то, что это строка UP. Строка UP определяется как строка, в которой цена выше, чем в предыдущей строке.
Теперь я с моим представленным выше набором данных пройду по шаблону в строках с 11 по 14. Начинаю обработку с первой строки, в которой дата первое сентября. Эта строка удовлетворяет критерию строки STRT, поскольку отсутствуют критерии определения. Первая строка может быть строкой STRT, соответствующей шаблону, только в том случае, если следующая строка - строка DOWN. Поэтому я двигаюсь вперед по результирующему набору, выбираю вторую строку и применяю определения строки DOWN к ней. Цена 34 меньше цены в предыдущей строке (35)? Поскольку это так, мое продвижение все еще соответствует шаблону; я могу перейти к третьей строке и применить определение снова. В результате проверки третья строка также DOWN. Затем беру четвертую строку, которая не является строкой DOWN. Поэтому я завершаю часть проверки, заданную в шаблоне как STRT DOWN+. Теперь следует удостовериться, что четвертая строка согласовывается с определением UP в моем описании. И без сомнения так оно и есть. Я обнаружил по шаблону искомый образец: знаю следующие друг за другом строки DOWN, за которыми следует по крайней мере одна строка UP; но поиск по шаблону может здесь и не закончится. Почти таким же образом, как и в регулярных выражениях, я продолжаю проверять соответствие, чтобы найти максимально большой образец, соответствующий шаблону. Я смотрю пятую, шестую и седьмую строки, и обнаруживаю, что они строки UP . В результате они становятся частью искомого образца. Затем я беру строку восемь, она более не соответствует определению строки UP, так как ее цена не выше, чем в предыдущей строке. Я закончил с первым искомым образцом, и набор строк с первого по седьмое сентября соответствует шаблону поиска. Я готов вывести эту информацию.
Двигаясь назад в предложении MATCH_RECOGNIZE, находим в строке 5 фразу MEASURES. В ней определяются выходные результаты запроса. В нашем случае я запрашиваю строки, включающие временную метку стартовой записи искомого образца. Для этого используется ссылка шаблона STRT.tstamp. Кроме того, выводится LAST(DOWN.tstamp), последняя временная метка строк Down, а также LAST(UP.tstamp) , последняя временная метка строк UP. Эти три величины отражают начало, нижнюю точку и конечную точку шаблона вида V. Строка 9 запроса (ONE ROW PER MATCH) приводит к тому, что выполнение предложения MATCH_RECOGNIZE очень похоже на выполнение агрегированной функции. В нашем примере для относительно большого искомого образца из семи строк в выходные результаты попадает одна строка с тремя показателями. Используя предложение MATCH_RECOGNIZE, можно задать преобразование миллионов строк в десятки, сотни или тысячи строк. Такой объем можно осмыслить и проанализировать.
После обнаружения первого образца я готов к поиску следующего. Вопрос ставится следующим образом: "Откуда начать поиск следующего образца?" Я могу начать поиск со второй строки, но это не будет иметь смысла для данного шаблона, так как я буду снова и снова искать образец вида V внутри найденного V- образца, что не представляет интереса. Откуда я должен был бы начать поиск следующего образца, так это с последней строки первого образца. Я хочу выбирать с того места, где я вышел при первом поиске, и предложение 10 запроса - AFTER MATCH SKIP TO LAST UP - выполняет это. Таким образом, в нашем случае разрешается последней строке первого образца быть потенциально первой строкой следующего образца. И, как Вы можете видеть в выходных результатах, на самом деле седьмое сентября - первая строка следующего образца. Фактически седьмое сентября повторяется дважды в данном результирующем наборе (представьте, что бы Вы должны были сделать в обычном SQL для повторения одной и той же строки в результирующем наборе!)
В завершение подведем итоги. Я выбрал все соответствия шаблону, упорядочил их по обозначению склада (symbol) и стартовой цене (возможно, в оригинале неточность, так как упорядочение по временной метке), затем вывел результаты. Запрос обработал 13 строк и преобразовал их в две строки, в каждой из которых значения трех точек. Становится довольно просто получать образцы по таким шаблонам и интерпретировать их. Можете представить, как продолжить анализ и агрегирование данных, ответив, например, на такие вопросы: "Какой средний период времени занимает V-изменение для данного склада "; "Какое максимальное время?"; "Сколько V-изменений обычно происходит за год?"; "Были ли годы или месяца, когда отсутствовало падение ?" и т.д.
Подробную информацию по синтаксису предложения MATCH_RECOGNIZE см. по ссылке bit.ly/15x5p0o. Особенно полезно для начала ознакомиться с главой "SQL for Pattern Matching" в документе Oracle Database Data Warehousing Guide 12c Release 1 (12.1).
Временные дынные undo (Temporary Undo)
Я рассмотрю новой способ обработки данных undo для глобальных временных таблиц в Oracle Database 12c.
Многие администраторы баз данных (АБД) и разработчики обычно удивляются, обнаружив, что их операции над глобальными временными таблицами, например, INSERT, UPDATE, MERGE и DELETE, генерируют журнальные данные для подката вперед (redo). Размер таких данных redo меньше, чем для обычной, постоянной таблицы, но все же немалого размера. Обычно они задают вопрос: "Откуда берутся эти данные redo?" Ответ состоял в том, что они результат данных undo, которые база данных Oracle сгенерировала для глобальной таблицы. Данные undo для глобальных временных таблиц должны быть сгенерированы, так как они необходимы базе данных на случай, если приложение вызовет операцию отката (rollback), а также для обеспечения целостности чтения результатов. В ситуациях, когда разработчик вставляет некоторую информацию в глобальную временную таблицу, затем выполняет команду SELECT над ней, потом команды UPDATE или DELETE, правила для целостного чтения устанавливают, что команда SELECT не может видеть последствия команд UPDATE или DELETE. Чтобы сделать такое возможным, базе данных требуются данные undo (см. bit.ly/120NB0w для дополнительной информации о целостности чтения, одного из основополагающих принципов Oracle Database).
Поэтому изменения в глобальной временной таблице должны сопровождаться генерацией данных undo, а для защиты табличного пространство типа undo требуется информация redo. В случае аварийного завершения экземпляра базе данных необходима информация redo для восстановления табличного пространства типа undo, что позволяет откатить любую транзакцию, которая обрабатывалась, но не была зафиксирована в момент аварийного завершения экземпляра. И наконец, до Oracle Database 12c табличное пространство типа undo не различалось для данных undo временных и постоянных объектов.
Но начиная с Oracle Database 12c, временные данные undo могут быть сохранены во временном табличном пространстве, а данные undo для постоянных объектов в табличном пространстве типа undo. Это фактически, действительно, что операции над временными таблицами больше не генерируют данные redo. При выполнении большой пакетной (групповой) операции, использующей временные таблицы, можно увидеть, что размер сгенерированных данных redo значительно снизился. Более того, сгенерируется меньше данных undo в табличном пространстве типа undo. И это означает, что Вы в состоянии поддерживать более длительный интервал времени удержания данных undo в меньшем табличном пространстве типа undo.
Другая приятная сторона, вытекающая из этого нового изменения, состоит в том, что теперь глобальные временные таблицы могут быть использованы в базе данных Oracle Active Data Guard, находящейся в режиме "только чтение". Да, можно иметь базу данных в режиме "только чтение", в которой разрешены чтения и запись в глобальные временные таблицы. Поскольку одно из применений глобальных временных таблиц исторически связано с системами отчетов (для хранения промежуточных результатов запросов), это делает более жизнеспособной базу данных отчетов Oracle Active Data Guard в режиме "только чтение". Because one of the uses of a global temporary table has historically been in reporting systems-for storing intermediate query results-this makes a read-only Oracle Active Data Guard reporting database that much more viable.
Для контроля генерации данных undo временных таблиц в Oracle Database 12c, предназначен новый параметр экземпляра (init.ora): temp_undo_enabled. У него два значения: TRUE или FALSE. По умолчанию значение FALSE, и данные undo генерируются так же, как и ранее. Пример:
SQL> alter session set temp_undo_enabled = false; Session altered. SQL> insert into gtt 2 select * 3 from all_objects; 87310 rows created. Statistics --------------------------- … 566304 redo size … SQ> update gtt 2 set object_name = lower(object_name); 87310 rows updated. Statistics ---------------------------- … 8243680 redo size …
Как Вы можете видеть, команда INSERT генерирует примерно полмегабайта данных redo (566,304 bytes), тогда как команда UPDATE свыше 8 MB данных redo (8,243,680 bytes). Но если я включу временные данные undo:
SQL> alter session set temp_undo_enabled = true; Session altered. SQL> insert into gtt 2 select * 3 from all_objects; 87310 rows created. Statistics ------------------------------- … 280 redo size … SQL> update gtt 2 set object_name = lower(object_name); 87310 rows updated. Statistics ------------------------------- … 0 redo size …
the redo is either trivial or nonexistent.
В базе данных Oracle Active Data Guard, находящейся в режиме "только чтение" данные redo будут отсутствовать полностью. Это означает, что Вы потенциально будете отправлять меньше данных redo, и получать удовольствие от использования более длительного периода удержания данных undo в Вашей первичной базе данных.