Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Materialised Path Heirarchy

Materialised Path Heirarchy

From: stu <smcgouga_at_nospam.co.uk>
Date: Thu, 27 Mar 2003 11:11:00 -0000
Message-ID: <b5umbg$bhm$1$8300dec7@news.demon.co.uk>


http://www.dbazine.com/tropashko4.html
(about 2 pages down they talk about the materialised path)

I need to set up a hierarchy in a database. I would like to understand the nested set but can not quite get my head round it as quickly as is required. So that aside I would like to ask some questions about the materialised path.

  1. Am i correct in saying an index over the path field could be used if I parse the path on the client and return a query with an SQL IN statement? eg select * from .... where path in ('1' 1.1', '1.2', ...)
  2. Do queries such as select e1.ename from emp e1, emp e2 where e2.path like e1.path || '%' and e2.name = 'FORD' only work for up to 9 nodes in one level? remembering '8' < '9' > '10' when comparing strings.

The only way I can see round this is to always use x characters to represent each level. e.g '0008' < '0009' < '0010'

But this would require hard coding the max number of nodes in each level
(9999 in this case) which i do not want to do.

comments or pointers?

cheers
stu Received on Thu Mar 27 2003 - 05:11:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US