Thanks for the article. It did help some but I am still not sure if
representing a tree is best served using a self referential table.
Wouldn't it be better to separate it into several tables?
Can anyone else comment? Anyone have experience storing data that is
hierarchical in nature (e.g. a tree formation).
Yuji Shinozaki wrote:
>I believe the technique they are using is representing a tree as nested
>sets. It requires that the database is built with properly nested
>left_id's and right_id's, and this technique is often regarded as a
>efficient means for retrieving hierachical information.
>Here is one reference about it:
>As for the inner join clauses they are in effect analogous to
>where's but the optimizer handles them differently. That is where
>(pardon the pun) my understanding dwindles. Perhaps someone else has
>better insight about inner join's vs where's.
>Hope this sheds some light and not too many shadows,
>On Mon, 26 Jan 2004, Jodi Kanter wrote:
>>I have a biochemist telling me that this query below is a typical one
>>for crawling through a taxonomic tree and that this is how I should
>>represent some peptide information we have. Is there anyone on this list
>>familiar with such data? I am weak in the science department but this
>>query looks like it might not be the most efficient approach.
>>I have not been able to run an explain analyze yet as the database
>>structure and data are not in place yet. We are just in the planning
>>stages right now.
>>Any comments, suggestions, concerns, etc. would be much appreciated.
>>Would an experienced DBA recommend a different approach? Can anyone
>>offer some insight into the usefulness of INNER joins and the use of
>>BETWEEN? I am concernec about performance as well since I expect this
>>table to get large.
>> FROM taxon_name
>> INNER JOIN taxon AS tax_b USING(taxon_id)
>> INNER JOIN taxon AS tax_v ON (tax_v.left_id BETWEEN
>>tax_b.left_id AND tax_b.right_id )
>> INNER JOIN annot ON (tax_v.taxon_id = annot.taxon_id)
>> INNER JOIN protein ON (protein.prot_id= annot.prot_id)
>> WHERE annot.pref = 1
>> AND taxon_name.taxon_id=207245
>>//Jodi L Kanter
>>BioInformatics Database Administrator
>>University of Virginia
>Yuji Shinozaki Computer Systems Senior Engineer
>ys2n(at)virginia(dot)edu Advanced Technologies Group
>(434)924-7171 Information Technology & Communication
>http://www.people.virginia.edu/~ys2n University of Virginia
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
//Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
In response to
pgsql-admin by date
|Next:||From: Rick van Dijk||Date: 2004-01-29 14:35:33|
|Subject: Re: Are there programs to graph database schema?|
|Previous:||From: Frank Way||Date: 2004-01-29 14:09:34|
|Subject: Re: How to determine which file contains which block |