202 reputation
412
bio website growse.com
location
age
visits member for 3 years, 5 months
seen Jan 23 at 10:17

Jan
22
comment How can I use Oracle's CONNECT BY PRIOR to only get specific revisions of hierarchical records?
If I explicitly set the timestamp in the 'START WITH' clause (START WITH "ITEMNNAME"="item1" AND TIMESTAMP=?), the CONNECT BY clause still returns multiple rows for each related. If I could somehow group these by distinct(ITEMNAME) and return the record foreach ITEMNAME with the largest timestamp below a certain value, that'd probably be what I need. I've no idea how to do that though.
Jan
22
awarded  Editor
Jan
22
comment How can I use Oracle's CONNECT BY PRIOR to only get specific revisions of hierarchical records?
I've updated it to hopefully be a bit clearer. The query is asking at a specific point in time, what was the latest view on the hierarchy. So on the first query, the latest value for item1 is row #1, and that links to the item2, the latest value at that time was row #2.
Jan
22
revised How can I use Oracle's CONNECT BY PRIOR to only get specific revisions of hierarchical records?
added 230 characters in body
Jan
22
asked How can I use Oracle's CONNECT BY PRIOR to only get specific revisions of hierarchical records?
Nov
21
awarded  Notable Question
Aug
14
awarded  Yearling
Aug
13
awarded  Notable Question
Jul
2
awarded  Curious
Jun
25
awarded  Popular Question
Jun
5
awarded  Popular Question
Apr
24
awarded  Notable Question
Apr
1
awarded  Popular Question
Mar
19
awarded  Popular Question
Mar
17
awarded  Popular Question
Jun
27
comment Will a delete then insert within a transaction cascade?
Well, I basically did the bulk insert into a temp table and then merged that with the original table. The bulk insert still takes around 6s, and the merge takes around 15-20s. The biggest issue is that I'm currently blanket updating on match, so I'm at least generating an update statement for each row in the table. This surely can be optimized, but I'd have to compare 90 columns for equality, and it didn't seem to be worth it.
Jun
27
awarded  Commentator
Jun
27
accepted Will a delete then insert within a transaction cascade?
Jun
27
comment Will a delete then insert within a transaction cascade?
Merge wins. Fairly epic merge statement with 90 columns, but it works.
Jun
27
comment Will a delete then insert within a transaction cascade?
This sounds like a sensible idea. I'll do some investigating. As mentioned in my above comment though, performance might be an issue, but I guess I'll just have to test.