3 ms·

What you're doing certainly doesn't sound like an easy job for window functions, although it might be possible with RANGE + interval - at least in Oracle 12c.

What you're doing certainly doesn't sound like an easy job for window functions, although it might be possible with RANGE + interval - at least in Oracle 12c. How about Oracle 12c's MATCH_RECOGNIZE, though? http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/1965433.pdf http://www.oracle.com/ocom/groups/public/@otn/documents/webc...

jnordwick 10y ago
Thanks. That was very interesting. Overall, a total band-aid of course, but still better than nothing. I can completely see doing ad hoc queries with that (and I can't wait to try it out) but I fear the performance hit of trying to build a system around it :) It didn't give any examples of queries going both directions, e.g. the down-up V first example. You probably often also want the up-down ^ shapes too. It would be nice if the PATTERN had alternation too "STRT (DOWN+ UP+)|(UP+ DOWN+)" so you didn't need to double your queries. Or else you're probably better off creating a temp on CUR-LAG(1) to give you the sign and then pattern match the sign (if that makes sense). Oracle really does go out of their way to keep SQL modern though. I remember the first time I saw a "CONNECT BY". lol. I had an interview a few months later and was asked to write an SQL query to basically traverse a tree (the gist of it). I being a young smart ass gave the correct answer (you can't) and proceeded to write the START / CONNECT just to show off a little.
- lukaseder 10y ago
  Yes, it's a band-aid. But perhaps it will feel less so, if you're looking at the original invention by Esper Tech (I have no experience with it, just know that I've seen MATCH_RECOGNIZE there first): http://www.espertech.com/esper/release-5.1.0/esper-reference/html/match-recognize.html http://www.espertech.com/esper/release-5.1.0/esper-reference... Hah, yeah CONNECT BY is great for simple recursion, but for more complex (probably less performing) cases, I prefer the standard approach with CTEs...

jnordwick 10y ago

Thanks. That was very interesting. Overall, a total band-aid of course, but still better than nothing. I can completely see doing ad hoc queries with that (and I can't wait to try it out) but I fear the performance hit of trying to build a system around it :) It didn't give any examples of queries going both directions, e.g. the down-up V first example. You probably often also want the up-down ^ shapes too. It would be nice if the PATTERN had alternation too "STRT (DOWN+ UP+)|(UP+ DOWN+)" so you didn't need to double your queries. Or else you're probably better off creating a temp on CUR-LAG(1) to give you the sign and then pattern match the sign (if that makes sense). Oracle really does go out of their way to keep SQL modern though. I remember the first time I saw a "CONNECT BY". lol. I had an interview a few months later and was asked to write an SQL query to basically traverse a tree (the gist of it). I being a young smart ass gave the correct answer (you can't) and proceeded to write the START / CONNECT just to show off a little.

lukaseder 10y ago
Yes, it's a band-aid. But perhaps it will feel less so, if you're looking at the original invention by Esper Tech (I have no experience with it, just know that I've seen MATCH_RECOGNIZE there first): http://www.espertech.com/esper/release-5.1.0/esper-reference/html/match-recognize.html http://www.espertech.com/esper/release-5.1.0/esper-reference... Hah, yeah CONNECT BY is great for simple recursion, but for more complex (probably less performing) cases, I prefer the standard approach with CTEs...