We have a design requirement that involves a Parent table with a 1:n relationship with a child table. The data in these tables will be transmitted to external apps using the OSB.
We have a 20 character globally unique id, called guid, that is unique to each record on the Parent table.
There is a concern about performance if we used the guid as the PK for the Parent table, and the FK for the Child table. The rest of the team wants to use a sequence-generated number as the PK/FK for the Parent/Child, and just have the guid be a unique index on the Parent.
With that, they also want to have external apps provide the generated id when requesting records from the child table, as opposed to the guid. This means, at insert time, the external apps would have to hold onto the sequence generated id that is returned.
I suggested to join the Parent/Child table on the generated sequence, but use the guid to look up the Parent record, but I guess there is a concern about performance involving that as well.
The team wants to create the public function (psudeo-code)
get_child_records(id IN NUMBER) {
Select *
From child_table
Where child_fk = id
}
Instead of
get_child_records(guid in varchar2) {
Select *
From parent_table,
child_table
Where parent_pk = child_fk
and parent_guid = guid
}
I feel that any sequence generated surrogate keys should be kept private and used for backend joins only, and to have to guid used externally (shielding the rest of the world from the generated ids).
What are your thoughts on this?
ask the "team" for the science.
Not hypothesis (hypothesis can be tested), but the numbers, the facts, the solid non-myth based reasoning.
they would have to measure the time it takes to add this unique sequence (in addition to the unique guid) to the parent, time to maintain the unique index on it.
My hypothesis is "this is a 'not smart' idea - to add yet another silly surrogate when a proper one already exists".
This sounds "silly" to me, I don't get their thought process. If the parent has a key (and it does) that is the foreign key in the child table, done - period, nuff said.
I don't like your teams overly procedural approach either, I would create a VIEW, not a procedure, but whatever...
... I feel that any sequence generated surrogate keys should be kept private and used for backend joins only, and to have to guid used externally (shielding the rest of the world from the generated ids)....
and I would say that the sequence should die here, it isn't useful, it only makes the data larger, harder to manage, increases the number of indexes, provides no compelling "performance" case - nothing.
Here is my quick and dirty "what are you TALKING about" example:
ops$tkyte%ORA11GR2> create table t
2 ( guid varchar2(32) unique not null,
3 seq number unique not null,
4 data char(80)
5 )
6 /
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t (guid, seq,data)
2 select rawtohex(sys_guid()), 123456+level, 'x'
3 from dual
4 connect by level <= 100000
5 /
100000 rows created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> declare
2 type array is table of t.guid%type;
3 l_guids array;
4 l_rec1 t%rowtype;
5 l_rec2 t%rowtype;
6 begin
7 select guid bulk collect into l_guids from t;
8 dbms_monitor.session_trace_enable();
9 for i in 1 .. l_guids.count
10 loop
11 select * into l_rec1 from t where guid = l_guids(i);
12 select * into l_rec2 from t where seq = l_rec1.seq;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
I'll let you run that - and you tell us if the guid or the sequence was "amazingly fast compared to the other" (it won't be)
This is premature optimization here - fixing a problem that probably doesn't exist. I hope they spend as much time on other true design issues as they do on this!