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: [EMAIL PROTECTED], 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 [EMAIL PROTECTED]) ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
