Couple of Oracle tips/trick that amused me today.
1) I ran into the issue of creating very specific unique constraint on couple of fields in certain table. This uniqueness should work in case when one of the fields has value 1.
So let's assume that we have 2 fields FK, VALUE (0|1). This pair must be unique in case of value 1 (FK, 1) and should not if value is 0 (FK, 0).
There is no way to create "check constraint" or "unique constraint", either you cannot write trigger on it because it's not allowed to access to the same table from the trigger. Digging a bit I found cool index that helped me with this issue. Oracle called it functional index and in my particular case it looks like following and works like a charm:
create unique index uix_single on table (
case when value = 1 then fk else null end,
case when value = 1 then value else null end
);
2) Another interesting task that I have been solving is keeping graphs in Oracle and loading them as fast as it's possible. There are number of graphs but in my case I used DAGs (direct acyclic graphs) and what actually surprised me is that Oracle supports it. They can even handle cyclic graphs and have special keywords for that: connect by and start with. But the most interesting things happen when you need not just a single edge that connects two nodes, but rather a set of edges and so called connection points (as separate entities). So every node could have several connection points and edges combine all this stuff together.
Connect by does not work in this case because they expect child node id to be the same as parent node one, but apparently we have connection points and their ids could be quite different (say you have set of input connection points and another set of output ones and only input can be connected with output).
The solution is elegant and I would say trivial. Edges should still connect nodes, not the connection points but connection point ids need to be included into the edge. This allows using connect byand at the same time keeping relations between connection points (regardless of denormalization)
1) I ran into the issue of creating very specific unique constraint on couple of fields in certain table. This uniqueness should work in case when one of the fields has value 1.
So let's assume that we have 2 fields FK, VALUE (0|1). This pair must be unique in case of value 1 (FK, 1) and should not if value is 0 (FK, 0).
There is no way to create "check constraint" or "unique constraint", either you cannot write trigger on it because it's not allowed to access to the same table from the trigger. Digging a bit I found cool index that helped me with this issue. Oracle called it functional index and in my particular case it looks like following and works like a charm:
create unique index uix_single on table (
case when value = 1 then fk else null end,
case when value = 1 then value else null end
);
2) Another interesting task that I have been solving is keeping graphs in Oracle and loading them as fast as it's possible. There are number of graphs but in my case I used DAGs (direct acyclic graphs) and what actually surprised me is that Oracle supports it. They can even handle cyclic graphs and have special keywords for that: connect by and start with. But the most interesting things happen when you need not just a single edge that connects two nodes, but rather a set of edges and so called connection points (as separate entities). So every node could have several connection points and edges combine all this stuff together.
Connect by does not work in this case because they expect child node id to be the same as parent node one, but apparently we have connection points and their ids could be quite different (say you have set of input connection points and another set of output ones and only input can be connected with output).
The solution is elegant and I would say trivial. Edges should still connect nodes, not the connection points but connection point ids need to be included into the edge. This allows using connect byand at the same time keeping relations between connection points (regardless of denormalization)
