Hi!
I've been playing with MATCH_RECOGNIZE feature in 12c and I've encountered situation which I'm not able to explain myself - so I'd like to ask you for support.
Please find the script here:
https://livesql.oracle.com/apex/livesql/s/dyhcdwiuu4tznmvwuunc62kde I simply used table Ticker introduced in the documentation - "SQL for pattern matching". What's the problem? Results are different than I would expect: after match is found, we're skipping to last row of pattern variable B which in turn is starting point of next checking. Since within the pattern I have empty variable STRT at the beginning, each row matches to it, so I would expect to see in the results duplicated rows on: 05-APR-11, 10-APR-11, 13-APR-11, 18-APR-11 because they're matched to two variables - B (from previous check) and STRT. I can see two rows only for 05-APR-11 - I don't understand why they are not displayed for the rest. This is the first question.
Second issue - since each row is matching to STRT - why aren't rows for 14-APR-11, 19-APR-11 and 20-APR-11 returned at all?
Thank you a lot in advance for explanations! I'm trying to deeply understand this feature.
Best regards,
Andrzej
It's the same issue for both questions:
The whole pattern has to match for Oracle to return the rows!
Your pattern is:
pattern ( strt a b+ )
define
a as a.price >= 1
,b as b.price > prev(b.price)
which means:
- Find any row
- Followed by one row that has a price >= 1
- Followed by
at least one row when the price is greater than the previous row's price
On 11 Apr the price increases. So the pattern ends. Because the after match clause is:
after match skip to last b
Oracle starts with the last row for the previous match. This is 10 Apr. The next three rows from here are:
ACME 10-APR-11 25 <=== Matches STRT
ACME 11-APR-11 19 <=== Matches A
ACME 12-APR-11 15 <=== Doesn't match B!
So the whole match fails. It then starts checking again, starting with the next row after the last B (11 Apr). Similar logic applies for the other dates you've specified.
Oracle duplicates 5 Apr because there
is a complete match starting at this point:
ACME 05-APR-11 25 <=== Matches STRT
ACME 06-APR-11 12 <=== Matches A
ACME 07-APR-11 15 <=== Matches B
PS - Thanks for providing a test script using LiveSQL!