-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> ... I don't want to take GO:000006 into account (two parents in which
> I am intested in). That menas, whenever I ask for children of two nodes,
> I want a DISTINCT SET of children.
To start with, you can avoid the Java and do this in SQL:
SELECT child FROM gograph WHERE parent='GO:0000002' OR parent='GO:0000005'
EXCEPT
(SELECT child FROM gograph WHERE parent='GO:0000002'
INTERSECT
SELECT child FROM gograph WHERE parent='GO:0000005');
And yes, I would certainly start by normalizing things a little bit:
CREATE SEQUENCE goid_seq;
CREATE TABLE goID (
idname TEXT,
id INTEGER NOT NULL DEFAULT nextval('goid_seq')
);
INSERT INTO goid(idname) SELECT DISTINCT parent FROM gograph;
INSERT INTO goid(idname)
SELECT DISTINCT child FROM gograph WHERE NOT EXISTS (SELECT 1 FROM goID WHERE idname =
child);
CREATE TABLE gomap (
parent INTEGER,
child INTEGER
);
INSERT INTO gomap SELECT
(SELECT id FROM goid WHERE idname=parent),
(SELECT id FROM goid WHERE idname=child)
FROM gograph
As far as the binaryInteraction table, a little more information is needed:
how are each of these tables being populated? Why the distinct? Is it because
there may be duplicate rows in the table? The reason I as is that it might be
better to ue triggers to compute some of the information as it comes in,
depending on which tables are changes and how often.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200307151035
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE/FBGrvJuQZxSWSsgRAlEfAKCL4ttDdTRHxPRW9N00nowPh1/q/QCgqrkv
e7Ncj4al4aJ4ihktEyweJJo=
=Z/rk
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
