A couple of months ago, Nienke Gijsen invited me to speak about materialized views at the upcoming Planboard DBA Symposium. Because I had the pleasure of presenting before, I knew the conference is always well organized and a pleasure to visit. So of course I accepted the invitation. We agreed I'd talk on "just" incremental refreshes of materialized views using materialized view logs and about my experience at client sites with MV logs. Since the central theme of all presentations is about sharing real world Oracle database related stories to help fellow DBA's, this fitted nicely.
During the day I visited four other interesting presentations:
- Edwin Biemond, about using Puppet to get to a fully working database and Fusion Middleware environment in minutes,
- Eric Zonneveld, about using I/O Resource Management on Exadata in a huge database environment at KPN,
- Yuri van Buren, about using AWR reports and ADDM reports together to get the best view on what might be wrong within the database,
- Bernhard de Cock Buning, who used a poll to talk about the four most voted for topics related to 12c: Multiple indexes on same column list, Adaptive SQL Plans, SQL row limits and Flex ASM.
My own presentation about materialized view logs can be downloaded from the "Presentations and Papers" section, and via these two links: powerpoint (pptx) and scripts (zip).
Thanks Nienke, Eric and Corne for organizing such a fine conference again.
Pages
▼
Wednesday, June 18, 2014
Monday, March 31, 2014
APEX World 2014
The fifth edition of OGh APEX World took place last Tuesday at Hotel Figi, Zeist in the Netherlands. Again it was a beautiful day full of great APEX sessions. Every year I think we've reached the maximum number of people interested in APEX and we'll never attract more participants. But, after welcoming 300 attendees last year, 347 people showed up this year. Michel van Zoest and Denes Kubicek also wrote about the event. Below is a short summary my experiences.
Each year, Joel Kallman, head of the APEX development team, sends one of his team members to our conference. This year Joel Kallman himself agreed to come and deliver the keynote. The title was Oracle Application Express 5.0 - By APEX Developers, for APEX Developers. This summarizes the essence of the next major release very well. All new features seem to focus on making the life of an APEX developer even easier than it already was. The interactive page designer, one universal theme, uploading zipped files, builtin syntax highlighting and much more. Also, the design of the tool itself has improved with all the fresh new flat icons in iOS7 style. Can't wait to download the new version.
The first parallel session I visited was Dimitri Gielis' session "Things I do in every APEX project". I was eager to hear tips from someone as experienced with APEX as Dimitri. I think all people in the room, from beginner to expert, will have heard a few good tips they can use for themselves. The only unfortunate thing was the sessions length: only 50 minutes. It looked like Dimitri had lots more tips to share. Maybe a part two in the future?
Peter Raganitsch talked about "Going Public - What to consider when the world has access to your APEX Application." After a great introduction of some of the quirks around the APEX url, authentication and the session identifier, he showed several possibilities for having human readable URL's, applicable only to public (non-authenticated) applications. I've read about several alternatives in the past, but his method of using a RESTful webservice in the APEX listener (nowadays: Oracle Rest Data Services) seems the best I've encountered yet. If you're interested, you should check out his blog for a 3 part series on this subject.
The next speaker came all the way from Canada to the Netherlands for us to talk about APEX & HTML5: Martin Giffy D'Souza. After a brief introduction about how the web evolved, and speed skating (the Dutch sure made an impression at the other side of the ocean), Martin demonstrated a few of the new native HTML5 tags. These tags makes the life of a developer much easier. Fewer plugins are needed and fewer compatibility issues should arise. Martin is a great presenter and his demonstrations were impressive, especially of the canvas- and video-tag. He left out some of the technical details of his solutions, but you can check them out on the OGh site, once the demonstrations and Martin's demo app are uploaded. On Twitter, Martin asked if he could bring anything from Canada for someone and received two reactions: maple syrup and a moose. The two respondents were in the room and received their presents on stage. The moose was a bit difficult to get through customs, so he brought a nice cuddly moose instead.
Dan McGhan's (from the USA) session was about "Using AngularJS in Oracle Application Express". He decided to explore a change in approach and use the MEAN stack (MongoDB, Ember, Angular & Node). In this session he showed how to use AngularJS for his planning task application, gradually expanding it, so we learned step-by-step what to do. The main thought behind the process was to describe the state and let the UI "just" reflect that state, without having to do roundtrips to the database, as we would normally do with AJAX in APEX. It is all done at the client side. This session won the price for most difficult session, but I liked it very much for the brave effort of presenting us something so refreshingly different.
Nick Buytaert and Christel van Aerschot talked about single-click deployment in APEX development. Even though I already listened to Nick's ODTUG webinar about the same subject, I had to attend this session, since it is a subject close to my heart. Fortunately, the session contained quite a number of differences with the webinar. Jenkins was replaced by the CI Server Bamboo and git took the place of Subversion. Both are somewhat new for me and I was especially impressed by the user interface of Bamboo. Nick and Christel told their story very well and the demos went fine, except for a brief "oops, I now installed it in production" moment.
I decided to skip the 6th slot to be able to chat with several people. I talked with Nick, Christel and David from Contribute about some of their challenges and experiences with single-click deployment and continuous delivery. After the conference a group of almost 20 people wrapped up with a dinner at the neighboring restaurant.
If you attended the event and are a member of OGh, you have received an email with a poll about the future of APEX World. Please make sure you fill it in, if you haven't already.
Each year, Joel Kallman, head of the APEX development team, sends one of his team members to our conference. This year Joel Kallman himself agreed to come and deliver the keynote. The title was Oracle Application Express 5.0 - By APEX Developers, for APEX Developers. This summarizes the essence of the next major release very well. All new features seem to focus on making the life of an APEX developer even easier than it already was. The interactive page designer, one universal theme, uploading zipped files, builtin syntax highlighting and much more. Also, the design of the tool itself has improved with all the fresh new flat icons in iOS7 style. Can't wait to download the new version.
The first parallel session I visited was Dimitri Gielis' session "Things I do in every APEX project". I was eager to hear tips from someone as experienced with APEX as Dimitri. I think all people in the room, from beginner to expert, will have heard a few good tips they can use for themselves. The only unfortunate thing was the sessions length: only 50 minutes. It looked like Dimitri had lots more tips to share. Maybe a part two in the future?
Peter Raganitsch talked about "Going Public - What to consider when the world has access to your APEX Application." After a great introduction of some of the quirks around the APEX url, authentication and the session identifier, he showed several possibilities for having human readable URL's, applicable only to public (non-authenticated) applications. I've read about several alternatives in the past, but his method of using a RESTful webservice in the APEX listener (nowadays: Oracle Rest Data Services) seems the best I've encountered yet. If you're interested, you should check out his blog for a 3 part series on this subject.
The next speaker came all the way from Canada to the Netherlands for us to talk about APEX & HTML5: Martin Giffy D'Souza. After a brief introduction about how the web evolved, and speed skating (the Dutch sure made an impression at the other side of the ocean), Martin demonstrated a few of the new native HTML5 tags. These tags makes the life of a developer much easier. Fewer plugins are needed and fewer compatibility issues should arise. Martin is a great presenter and his demonstrations were impressive, especially of the canvas- and video-tag. He left out some of the technical details of his solutions, but you can check them out on the OGh site, once the demonstrations and Martin's demo app are uploaded. On Twitter, Martin asked if he could bring anything from Canada for someone and received two reactions: maple syrup and a moose. The two respondents were in the room and received their presents on stage. The moose was a bit difficult to get through customs, so he brought a nice cuddly moose instead.
Dan McGhan's (from the USA) session was about "Using AngularJS in Oracle Application Express". He decided to explore a change in approach and use the MEAN stack (MongoDB, Ember, Angular & Node). In this session he showed how to use AngularJS for his planning task application, gradually expanding it, so we learned step-by-step what to do. The main thought behind the process was to describe the state and let the UI "just" reflect that state, without having to do roundtrips to the database, as we would normally do with AJAX in APEX. It is all done at the client side. This session won the price for most difficult session, but I liked it very much for the brave effort of presenting us something so refreshingly different.
Nick Buytaert and Christel van Aerschot talked about single-click deployment in APEX development. Even though I already listened to Nick's ODTUG webinar about the same subject, I had to attend this session, since it is a subject close to my heart. Fortunately, the session contained quite a number of differences with the webinar. Jenkins was replaced by the CI Server Bamboo and git took the place of Subversion. Both are somewhat new for me and I was especially impressed by the user interface of Bamboo. Nick and Christel told their story very well and the demos went fine, except for a brief "oops, I now installed it in production" moment.
I decided to skip the 6th slot to be able to chat with several people. I talked with Nick, Christel and David from Contribute about some of their challenges and experiences with single-click deployment and continuous delivery. After the conference a group of almost 20 people wrapped up with a dinner at the neighboring restaurant.
If you attended the event and are a member of OGh, you have received an email with a poll about the future of APEX World. Please make sure you fill it in, if you haven't already.
Monday, January 6, 2014
Tabibitosan
I answered a few SQL questions on Stack Overflow recently, which I could solve easily by using the Tabibitosan method. It's such an elegant and efficient technique, I think it's worth giving it an extra bit of attention through this post. I'm certainly not the first to write about it: it was introduced to me by Japanese Oracle ACE Aketi Jyuuzou on OTN. He wrote a special forum post explaining his technique here, which also contains lots of examples. I have also included it in my SQL Masterclass. And Boneist and bluefrog have written about Tabibitosan in the past as well.
In its simplest form, the Tabibitosan technique lets you group consecutive rows easily, using just one level of analytic functions. And with a bit of imagination, you can also do some primitive SQL pattern matching avant la lettre. I'll show examples of both in this post.
The key of the technique is to map rows belonging to the same pattern to the same number, which can then be used for grouping or partitioning. To illustrate, let's start with a simple example to group consecutive numbers.
The table:
With the question: show me all the groups of integer values that are in sequence without gaps. For each group show the starting number and end number. So the expected result set is this:
Tabibitosan works by calculating an extra column, grp in my case, by subtracting row_number() from the value that defines the sequence, nr in my case. If the interval of the values in sequence is 1, then subtracting row_number() will result in a constant value for the group members. This query shows you the core of the technique:
It doesn't matter what the grp value is exactly. What matters is that it's the same constant value for all group members. This then allows for easy partitioning or grouping:
You can see another example of this simple form of Tabibitosan in this Stack Overflow question.
Tabibitosan only works if the difference of adjacent column values in the first operand of the minus operator (here: nr) equals 1. If it's not 1, you'll have to come up with an expression to make it 1. For example, let's see how a similar example works if we'd now like to group rows for consecutive months. Here's the setup:
And this is the expected result set:
We can use the same technique, if only the interval between two months can be mapped to 1, which is what the MONTHS_BETWEEN function gives us. So let's use that function to calculate the grp value:
I used an arbitrary date here (January 1, 2010). Any date on the first day of the month would be good. With the grp value calculated, the Tabibitosan query is again easy:
When the column that defines the order doesn't always increase with a constant number, the row_number() analytic function for the first operand of the minus operator comes in handy for the solution. Since I've found this one to be quite common, I'm also showing this case here. The example below is taken from this Stack Overflow question, where I'm comparing dates but excluding saturdays and sundays. The table and its contents look like this:
The query needs to return the start date and end date of each stay in a country. So the expected result set is this:
By subtracting a partitioned row_number() from a regular full-set row_number() we can calculate the grp value:
Note that just using the grp value for the final grouping, could lead to overlap of groups from different countries. So we need the country for the final grouping as well. The full query becomes:
Please note that these Tabibitosan queries contain only two levels and look pretty clean. Also, the performance is nice: just one full table scan and two sorts for the two analytic functions, followed by the unavoidable HASH GROUP BY and SORT ORDER BY for the grouping and the sorted display:
And here is another example from Stack Overflow using Tabibitosan with two row_numbers.
If it's becoming too hard to come up with a function that maps the differences between group members to the number 1, then you have an alternative which I'll call the max-on-case-row-number technique, by lack of a better name. I first saw this technique on AskTom. I'm going to repeat the same example as in the link to allow easy comparison.
The table:
The goal is to sum the amounts where the time is within 3 seconds of each other. So, the expected result set is this:
First (the case-row-number part), we'll compute a new column rn, and assign a row_number to the rows that mark a new group: the first row and the ones where the previous row has a gap larger than 3 seconds. All other rows don't get a rn value:
Second (the max-on part), we'll use the analytic function MAX to compute the grp value, which gives the rows where "rn is null" the same value as the first value of the group, as you can see in this query:
Now that we have a suitable grp value calculated, the last part -the grouping- is easy:
Compared to Tabibitosan, the max-on-case-row-number technique has similar performance characteristics: only one full table scan and a few sort operations on top. To compare it with Tabibitosan, you'd need to compare the sort operations. This is the plan for the query above:
There are three analytic functions in the query (lag, row_number and max), but all three order by time, so effectively there is only one WINDOW SORT operation. For calculating the max, Oracle discovers the intermediate result set is already sorted by time, so it does a WINDOW BUFFER instead of a WINDOW SORT. The outer HASH GROUP BY and SORT ORDER BY, are because of the "group by grp order by min(time)".
As an alternative, the Tabibitosan solution would need to map the rows within 3 seconds of each other to consecutive numbers, and leave a larger gap for the other rows. This is the best I could come up with:
You can see the similarities with the max-on-case-row-number. Note that I had to include rowid in the ordering, because time alone is not unique. The plan is exactly the same:
If you look closely to the expression with the three analytic functions, you'll notice that "count(*) over (order by time,rowid)" equals "row_number() over (order by time,rowid)" and thus they can be eliminated, which leads to a simpler non-Tabibitosan query:
but with the same performance characteristics again:
If you can find an expression to map the distance of same group members to a constant number, then grouping or partitioning your data using Tabibitosan will lead to a simple looking two-level query. Since alternatives will be more complex and sometimes will require an extra sort, it is definitely worth its place in your toolbox.
In its simplest form, the Tabibitosan technique lets you group consecutive rows easily, using just one level of analytic functions. And with a bit of imagination, you can also do some primitive SQL pattern matching avant la lettre. I'll show examples of both in this post.
The key of the technique is to map rows belonging to the same pattern to the same number, which can then be used for grouping or partitioning. To illustrate, let's start with a simple example to group consecutive numbers.
The table:
SQL> create table mytable (nr) 2 as 3 select 1 from dual union all 4 select 2 from dual union all 5 select 3 from dual union all 6 select 6 from dual union all 7 select 7 from dual union all 8 select 11 from dual union all 9 select 18 from dual union all 10 select 19 from dual union all 11 select 20 from dual union all 12 select 21 from dual union all 13 select 22 from dual union all 14 select 25 from dual 15 / Table created.
With the question: show me all the groups of integer values that are in sequence without gaps. For each group show the starting number and end number. So the expected result set is this:
MIN(NR) MAX(NR)
---------- ----------
1 3
6 7
11 11
18 22
25 25
Tabibitosan works by calculating an extra column, grp in my case, by subtracting row_number() from the value that defines the sequence, nr in my case. If the interval of the values in sequence is 1, then subtracting row_number() will result in a constant value for the group members. This query shows you the core of the technique:
SQL> select nr
2 , row_number() over (order by nr) rn
3 , nr - row_number() over (order by nr) grp
4 from mytable
5 /
NR RN GRP
---------- ---------- ----------
1 1 0
2 2 0
3 3 0
6 4 2
7 5 2
11 6 5
18 7 11
19 8 11
20 9 11
21 10 11
22 11 11
25 12 13
12 rows selected.
It doesn't matter what the grp value is exactly. What matters is that it's the same constant value for all group members. This then allows for easy partitioning or grouping:
SQL> with tabibitosan as
2 ( select nr
3 , nr - row_number() over (order by nr) grp
4 from mytable
5 )
6 select min(nr)
7 , max(nr)
8 from tabibitosan
9 group by grp
10 order by grp
11 /
MIN(NR) MAX(NR)
---------- ----------
1 3
6 7
11 11
18 22
25 25
5 rows selected.
You can see another example of this simple form of Tabibitosan in this Stack Overflow question.
Tabibitosan only works if the difference of adjacent column values in the first operand of the minus operator (here: nr) equals 1. If it's not 1, you'll have to come up with an expression to make it 1. For example, let's see how a similar example works if we'd now like to group rows for consecutive months. Here's the setup:
SQL> create table mytable (startdate) 2 as 3 select date '2013-01-01' from dual union all 4 select date '2013-02-01' from dual union all 5 select date '2013-03-01' from dual union all 6 select date '2013-06-01' from dual union all 7 select date '2013-07-01' from dual union all 8 select date '2013-10-01' from dual union all 9 select date '2013-12-01' from dual union all 10 select date '2014-01-01' from dual 11 / Table created.
And this is the expected result set:
MIN(STARTD MAX(STARTD ---------- ---------- 01-01-2013 01-03-2013 01-06-2013 01-07-2013 01-10-2013 01-10-2013 01-12-2013 01-01-2014
We can use the same technique, if only the interval between two months can be mapped to 1, which is what the MONTHS_BETWEEN function gives us. So let's use that function to calculate the grp value:
SQL> select startdate 2 , months_between(startdate,date '2010-01-01') mb 3 , months_between(startdate,date '2010-01-01') 4 - row_number() over (order by startdate) grp 5 from mytable 6 / STARTDATE MB GRP ---------- ---------- ---------- 01-01-2013 36 35 01-02-2013 37 35 01-03-2013 38 35 01-06-2013 41 37 01-07-2013 42 37 01-10-2013 45 39 01-12-2013 47 40 01-01-2014 48 40 8 rows selected.
I used an arbitrary date here (January 1, 2010). Any date on the first day of the month would be good. With the grp value calculated, the Tabibitosan query is again easy:
SQL> with tabibitosan as 2 ( select startdate 3 , months_between(startdate,date '2010-01-01') 4 - row_number() over (order by startdate) grp 5 from mytable 6 ) 7 select min(startdate) 8 , max(startdate) 9 from tabibitosan 10 group by grp 11 order by grp 12 / MIN(STARTD MAX(STARTD ---------- ---------- 01-01-2013 01-03-2013 01-06-2013 01-07-2013 01-10-2013 01-10-2013 01-12-2013 01-01-2014 4 rows selected.
When the column that defines the order doesn't always increase with a constant number, the row_number() analytic function for the first operand of the minus operator comes in handy for the solution. Since I've found this one to be quite common, I'm also showing this case here. The example below is taken from this Stack Overflow question, where I'm comparing dates but excluding saturdays and sundays. The table and its contents look like this:
SQL> create table mytable (date_worked,country)
2 as
3 select to_date('1-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
4 select to_date('4-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
5 select to_date('5-Nov-13','dd-Mon-yy'), 'India' from dual union all
6 select to_date('6-Nov-13','dd-Mon-yy'), 'India' from dual union all
7 select to_date('7-Nov-13','dd-Mon-yy'), 'India' from dual union all
8 select to_date('8-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
9 select to_date('11-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
10 select to_date('12-Nov-13','dd-Mon-yy'), 'India' from dual union all
11 select to_date('13-Nov-13','dd-Mon-yy'), 'India' from dual union all
12 select to_date('14-Nov-13','dd-Mon-yy'), 'India' from dual union all
13 select to_date('15-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
14 select to_date('18-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
15 select to_date('19-Nov-13','dd-Mon-yy'), 'India' from dual union all
16 select to_date('20-Nov-13','dd-Mon-yy'), 'India' from dual union all
17 select to_date('21-Nov-13','dd-Mon-yy'), 'India' from dual union all
18 select to_date('22-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
19 select to_date('25-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
20 select to_date('26-Nov-13','dd-Mon-yy'), 'India' from dual union all
21 select to_date('27-Nov-13','dd-Mon-yy'), 'India' from dual union all
22 select to_date('28-Nov-13','dd-Mon-yy'), 'India' from dual union all
23 select to_date('29-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual
24 /
Table created.
The query needs to return the start date and end date of each stay in a country. So the expected result set is this:
COUNTRY START_DATE END_DATE -------------- ---------- ---------- United Kingdom 01-11-2013 04-11-2013 India 05-11-2013 07-11-2013 United Kingdom 08-11-2013 11-11-2013 India 12-11-2013 14-11-2013 United Kingdom 15-11-2013 18-11-2013 India 19-11-2013 21-11-2013 United Kingdom 22-11-2013 25-11-2013 India 26-11-2013 28-11-2013 United Kingdom 29-11-2013 29-11-2013 9 rows selected.
By subtracting a partitioned row_number() from a regular full-set row_number() we can calculate the grp value:
SQL> select date_worked 2 , country 3 , row_number() over (order by date_worked) x 4 , row_number() over (partition by country order by date_worked) y 5 , row_number() over (order by date_worked) 6 - row_number() over (partition by country order by date_worked) grp 7 from mytable 8 / DATE_WORKE COUNTRY X Y GRP ---------- -------------- ---------- ---------- ---------- 01-11-2013 United Kingdom 1 1 0 04-11-2013 United Kingdom 2 2 0 05-11-2013 India 3 1 2 06-11-2013 India 4 2 2 07-11-2013 India 5 3 2 08-11-2013 United Kingdom 6 3 3 11-11-2013 United Kingdom 7 4 3 12-11-2013 India 8 4 4 13-11-2013 India 9 5 4 14-11-2013 India 10 6 4 15-11-2013 United Kingdom 11 5 6 18-11-2013 United Kingdom 12 6 6 19-11-2013 India 13 7 6 20-11-2013 India 14 8 6 21-11-2013 India 15 9 6 22-11-2013 United Kingdom 16 7 9 25-11-2013 United Kingdom 17 8 9 26-11-2013 India 18 10 8 27-11-2013 India 19 11 8 28-11-2013 India 20 12 8 29-11-2013 United Kingdom 21 9 12 21 rows selected.
Note that just using the grp value for the final grouping, could lead to overlap of groups from different countries. So we need the country for the final grouping as well. The full query becomes:
SQL> with tabibitosan as 2 ( select date_worked 3 , country 4 , row_number() over (order by date_worked) 5 - row_number() over (partition by country order by date_worked) grp 6 from mytable 7 ) 8 select country 9 , min(date_worked) start_date 10 , max(date_worked) end_date 11 from tabibitosan 12 group by country 13 , grp 14 order by start_date 15 / COUNTRY START_DATE END_DATE -------------- ---------- ---------- United Kingdom 01-11-2013 04-11-2013 India 05-11-2013 07-11-2013 United Kingdom 08-11-2013 11-11-2013 India 12-11-2013 14-11-2013 United Kingdom 15-11-2013 18-11-2013 India 19-11-2013 21-11-2013 United Kingdom 22-11-2013 25-11-2013 India 26-11-2013 28-11-2013 United Kingdom 29-11-2013 29-11-2013 9 rows selected.
Please note that these Tabibitosan queries contain only two levels and look pretty clean. Also, the performance is nice: just one full table scan and two sorts for the two analytic functions, followed by the unavoidable HASH GROUP BY and SORT ORDER BY for the grouping and the sorted display:
----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.01 | 3 | | | | | 1 | SORT ORDER BY | | 1 | 2 | 9 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)| | 2 | HASH GROUP BY | | 1 | 2 | 9 |00:00:00.01 | 3 | 1088K| 1088K| 1233K (0)| | 3 | VIEW | | 1 | 21 | 21 |00:00:00.01 | 3 | | | | | 4 | WINDOW SORT | | 1 | 21 | 21 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)| | 5 | WINDOW SORT | | 1 | 21 | 21 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)| | 6 | TABLE ACCESS FULL| MYTABLE | 1 | 21 | 21 |00:00:00.01 | 3 | | | | -----------------------------------------------------------------------------------------------------------------------
And here is another example from Stack Overflow using Tabibitosan with two row_numbers.
If it's becoming too hard to come up with a function that maps the differences between group members to the number 1, then you have an alternative which I'll call the max-on-case-row-number technique, by lack of a better name. I first saw this technique on AskTom. I'm going to repeat the same example as in the link to allow easy comparison.
The table:
SQL> create table mytable (time,quantity)
2 as
3 select trunc(sysdate) + to_dsinterval('0 12:22:01'), 100 from dual union all
4 select trunc(sysdate) + to_dsinterval('0 12:22:03'), 200 from dual union all
5 select trunc(sysdate) + to_dsinterval('0 12:22:04'), 300 from dual union all
6 select trunc(sysdate) + to_dsinterval('0 12:22:06'), 200 from dual union all
7 select trunc(sysdate) + to_dsinterval('0 12:22:45'), 100 from dual union all
8 select trunc(sysdate) + to_dsinterval('0 12:22:46'), 200 from dual union all
9 select trunc(sysdate) + to_dsinterval('0 12:23:12'), 100 from dual union all
10 select trunc(sysdate) + to_dsinterval('0 12:23:12'), 200 from dual
11 /
Table created.
The goal is to sum the amounts where the time is within 3 seconds of each other. So, the expected result set is this:
MIN(TIME) MAX(TIME) SUM(QUANTITY) ------------------- ------------------- ------------- 05-01-2014 12:22:01 05-01-2014 12:22:06 800 05-01-2014 12:22:45 05-01-2014 12:22:46 300 05-01-2014 12:23:12 05-01-2014 12:23:12 300
First (the case-row-number part), we'll compute a new column rn, and assign a row_number to the rows that mark a new group: the first row and the ones where the previous row has a gap larger than 3 seconds. All other rows don't get a rn value:
SQL> select time 2 , quantity 3 , case 4 when time - lag(time,1,date '0001-01-01') over (order by time) > 3/24/60/60 then 5 row_number() over (order by time) 6 end rn 7 from mytable 8 / TIME QUANTITY RN ------------------- ---------- ---------- 05-01-2014 12:22:01 100 1 05-01-2014 12:22:03 200 05-01-2014 12:22:04 300 05-01-2014 12:22:06 200 05-01-2014 12:22:45 100 5 05-01-2014 12:22:46 200 05-01-2014 12:23:12 100 7 05-01-2014 12:23:12 200 8 rows selected.
Second (the max-on part), we'll use the analytic function MAX to compute the grp value, which gives the rows where "rn is null" the same value as the first value of the group, as you can see in this query:
SQL> with case_row_number as 2 ( select time 3 , quantity 4 , case 5 when time - lag(time,1,date '0001-01-01') over (order by time) > 3/24/60/60 then 6 row_number() over (order by time) 7 end rn 8 from mytable 9 ) 10 select time 11 , quantity 12 , max(rn) over (order by time) grp 13 from case_row_number 14 / TIME QUANTITY GRP ------------------- ---------- ---------- 05-01-2014 12:22:01 100 1 05-01-2014 12:22:03 200 1 05-01-2014 12:22:04 300 1 05-01-2014 12:22:06 200 1 05-01-2014 12:22:45 100 5 05-01-2014 12:22:46 200 5 05-01-2014 12:23:12 100 7 05-01-2014 12:23:12 200 7 8 rows selected.
Now that we have a suitable grp value calculated, the last part -the grouping- is easy:
SQL> with case_row_number as 2 ( select time 3 , quantity 4 , case 5 when time - lag(time,1,date '0001-01-01') over (order by time) > 3/24/60/60 then 6 row_number() over (order by time) 7 end rn 8 from mytable 9 ) 10 , max_on_case_row_number as 11 ( select time 12 , quantity 13 , max(rn) over (order by time) grp 14 from case_row_number 15 ) 16 select min(time) 17 , max(time) 18 , sum(quantity) 19 from max_on_case_row_number 20 group by grp 21 order by min(time) 22 / MIN(TIME) MAX(TIME) SUM(QUANTITY) ------------------- ------------------- ------------- 05-01-2014 12:22:01 05-01-2014 12:22:06 800 05-01-2014 12:22:45 05-01-2014 12:22:46 300 05-01-2014 12:23:12 05-01-2014 12:23:12 300 3 rows selected.
Compared to Tabibitosan, the max-on-case-row-number technique has similar performance characteristics: only one full table scan and a few sort operations on top. To compare it with Tabibitosan, you'd need to compare the sort operations. This is the plan for the query above:
------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 3 | | | | | 1 | SORT ORDER BY | | 1 | 8 | 3 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)| | 2 | HASH GROUP BY | | 1 | 8 | 3 |00:00:00.01 | 3 | 1034K| 1034K| 737K (0)| | 3 | VIEW | | 1 | 8 | 8 |00:00:00.01 | 3 | | | | | 4 | WINDOW BUFFER | | 1 | 8 | 8 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)| | 5 | VIEW | | 1 | 8 | 8 |00:00:00.01 | 3 | | | | | 6 | WINDOW SORT | | 1 | 8 | 8 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)| | 7 | TABLE ACCESS FULL| MYTABLE | 1 | 8 | 8 |00:00:00.01 | 3 | | | | ------------------------------------------------------------------------------------------------------------------------
There are three analytic functions in the query (lag, row_number and max), but all three order by time, so effectively there is only one WINDOW SORT operation. For calculating the max, Oracle discovers the intermediate result set is already sorted by time, so it does a WINDOW BUFFER instead of a WINDOW SORT. The outer HASH GROUP BY and SORT ORDER BY, are because of the "group by grp order by min(time)".
As an alternative, the Tabibitosan solution would need to map the rows within 3 seconds of each other to consecutive numbers, and leave a larger gap for the other rows. This is the best I could come up with:
SQL> with x as 2 ( select time 3 , quantity 4 , case 5 when time - lag(time,1,date '0001-01-01') over (order by time,rowid) > 3/24/60/60 then 6 'Y' 7 end gap_indicator 8 from mytable 9 ) 10 , tabibitosan as 11 ( select time 12 , quantity 13 , count(*) over (order by time,rowid) 14 + count(gap_indicator) over (order by time,rowid) 15 - row_number() over (order by time,rowid) grp 16 from x 17 ) 18 select min(time) 19 , max(time) 20 , sum(quantity) 21 from tabibitosan 22 group by grp 23 order by min(time) 24 / MIN(TIME) MAX(TIME) SUM(QUANTITY) ------------------- ------------------- ------------- 05-01-2014 12:22:01 05-01-2014 12:22:06 800 05-01-2014 12:22:45 05-01-2014 12:22:46 300 05-01-2014 12:23:12 05-01-2014 12:23:12 300 3 rows selected.
You can see the similarities with the max-on-case-row-number. Note that I had to include rowid in the ordering, because time alone is not unique. The plan is exactly the same:
------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 3 | | | | | 1 | SORT ORDER BY | | 1 | 8 | 3 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)| | 2 | HASH GROUP BY | | 1 | 8 | 3 |00:00:00.01 | 3 | 1034K| 1034K| 735K (0)| | 3 | VIEW | | 1 | 8 | 8 |00:00:00.01 | 3 | | | | | 4 | WINDOW BUFFER | | 1 | 8 | 8 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)| | 5 | VIEW | | 1 | 8 | 8 |00:00:00.01 | 3 | | | | | 6 | WINDOW SORT | | 1 | 8 | 8 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)| | 7 | TABLE ACCESS FULL| MYTABLE | 1 | 8 | 8 |00:00:00.01 | 3 | | | | ------------------------------------------------------------------------------------------------------------------------
If you look closely to the expression with the three analytic functions, you'll notice that "count(*) over (order by time,rowid)" equals "row_number() over (order by time,rowid)" and thus they can be eliminated, which leads to a simpler non-Tabibitosan query:
SQL> with x as 2 ( select time 3 , quantity 4 , case 5 when time - lag(time,1,date '0001-01-01') over (order by time,rowid) > 3/24/60/60 then 6 'Y' 7 end gap_indicator 8 from mytable 9 ) 10 , y as 11 ( select time 12 , quantity 13 , count(gap_indicator) over (order by time,rowid) grp 14 from x 15 ) 16 select min(time) 17 , max(time) 18 , sum(quantity) 19 from y 20 group by grp 21 order by min(time) 22 / MIN(TIME) MAX(TIME) SUM(QUANTITY) ------------------- ------------------- ------------- 05-01-2014 12:22:01 05-01-2014 12:22:06 800 05-01-2014 12:22:45 05-01-2014 12:22:46 300 05-01-2014 12:23:12 05-01-2014 12:23:12 300 3 rows selected.
but with the same performance characteristics again:
------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 3 | | | | | 1 | SORT ORDER BY | | 1 | 8 | 3 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)| | 2 | HASH GROUP BY | | 1 | 8 | 3 |00:00:00.01 | 3 | 1034K| 1034K| 735K (0)| | 3 | VIEW | | 1 | 8 | 8 |00:00:00.01 | 3 | | | | | 4 | WINDOW BUFFER | | 1 | 8 | 8 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)| | 5 | VIEW | | 1 | 8 | 8 |00:00:00.01 | 3 | | | | | 6 | WINDOW SORT | | 1 | 8 | 8 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)| | 7 | TABLE ACCESS FULL| MYTABLE | 1 | 8 | 8 |00:00:00.01 | 3 | | | | ------------------------------------------------------------------------------------------------------------------------
If you can find an expression to map the distance of same group members to a constant number, then grouping or partitioning your data using Tabibitosan will lead to a simple looking two-level query. Since alternatives will be more complex and sometimes will require an extra sort, it is definitely worth its place in your toolbox.