Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 07B174760CC for ; Tue, 3 Sep 2002 18:00:05 -0400 (EDT) Received: from mail-relay.trade-india.com (mail-relay.trade-india.com [203.196.129.235]) by postgresql.org (Postfix) with SMTP id 6E531475CBE for ; Tue, 3 Sep 2002 18:00:00 -0400 (EDT) Received: (qmail 4618 invoked from network); 3 Sep 2002 21:50:43 -0000 Received: from news1.trade-india.com (HELO www.trade-india.com) (130.94.20.27) by infocom-236-129-del.trade-india.com with SMTP; 3 Sep 2002 21:50:43 -0000 Received: (qmail 9023 invoked by uid 99); 3 Sep 2002 21:59:54 -0000 Received: from 203.145.131.140 (SquirrelMail authenticated user mallah) by mail.trade-india.com with HTTP; Wed, 4 Sep 2002 03:29:54 +0530 (IST) Message-ID: <1889.203.145.131.140.1031090394.squirrel@mail.trade-india.com> Date: Wed, 4 Sep 2002 03:29:54 +0530 (IST) Subject: Re: tree structures in sql - my point of view (with request From: To: In-Reply-To: References: <20020903132207.GC24879@depesz.pl> X-Priority: 3 Importance: Normal X-MSMail-Priority: Normal Cc: , X-Mailer: SquirrelMail (version 1.2.6) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/19 X-Sequence-Number: 9296 Yep! ltree is Faaaast , and i use it at the moment. But will it work in INGRES,DB2 or ORACLE ? what if tommorow my boss ask me to use ORACLE? I have similar issues in using intarray & arrays in PGSQL though reasons of shifting to others dbs are diminishing with every major release of PG ;-) regds mallah. > While I don't have a time to comment your message I want to point to contrib/ltree package > which is extremely fast :-) > > http://www.sai.msu.su/~megera/postgres/gist/ltree > > Oleg > On Tue, 3 Sep 2002, Hubert depesz Lubaczewski wrote: > >> hi >> i recently spent some time on tree-structures in sql. >> i started with simple id/parent_id approach, used by nearly everyone, then i stopped at joe >> celko's nested sets, but i found it not very usable. >> then i found my own (maybe someone wrote it before, but i haven't read it, so idea is mine) >> way. >> in my way we have two tables: >> create table data (id serial, name text); >> create table structure (parent_id int8, child_id int8, depth int8); >> >> structure table represents all paths in tree. >> for example for this tree: >> >> sql >> / \ >> postgresql oracle-----__ >> | / | \ >> linux sco linux windows >> / \ >> glibc1 glibc2 >> >> (sorry for used data - it is just template, and personally i don't like oracle). >> so, for this tree we would populate the tables this way: >> data: >> id | name >> ----+------------ >> 1 | sql >> 2 | postgresql >> 3 | oracle >> 4 | linux >> 5 | sco >> 6 | linux >> 7 | windows >> 8 | glibc1 >> 9 | glibc2 >> >> structure: >> parent_id | child_id | depth >> -----------+----------+------- >> 1 | 1 | 0 >> 2 | 2 | 0 >> 3 | 3 | 0 >> 4 | 4 | 0 >> 5 | 5 | 0 >> 6 | 6 | 0 >> 7 | 7 | 0 >> 8 | 8 | 0 >> 9 | 9 | 0 >> 1 | 2 | 1 >> 1 | 3 | 1 >> 1 | 4 | 2 >> 2 | 4 | 1 >> 1 | 5 | 1 >> 1 | 6 | 1 >> 1 | 7 | 1 >> 3 | 5 | 2 >> 3 | 6 | 2 >> 3 | 7 | 2 >> 1 | 8 | 3 >> 1 | 9 | 3 >> 3 | 8 | 2 >> 3 | 9 | 2 >> 6 | 8 | 1 >> 6 | 9 | 1 >> >> (records with depth 0 are technologically not necessary, but they simplify and speedup some >> queries). >> >> with this data layout (easily indexable) you can fetch any data with just one select statement >> (no recursion needed in any case): >> - fetching parent >> - fetching childs >> - fetching "from id up" >> - fetching "from id down" >> also when you need to get indirect parents/childs or when you need only some of the data (from >> me, up, but not more then to my >> grand-grand-grand-father). >> >> i'd like to get some comments on this - how do you see my idea, is it worth it, do you know >> any better way to store trees in sql? >> >> best regards >> >> depesz >> >> > > Regards, > Oleg > _____________________________________________________________ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > > > ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off > all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/