Re: SQL question

Sybrand Bakker schrieb am 04.05.2005 in <qesg719bhmukv78akp66t4okcn747hffla_at_4ax.com>:

> On Wed, 04 May 2005 08:49:49 +0200, Andreas Mosmann
> <keineemails_at_gmx.de> wrote:

> Selfreference in one row has never been allowed in Oracle, the
> reference being cyclic.

But you can do so. The only problem I found was the START WITH ... CONNECT BY - clause

Alternativ you could set the parent-ID of the root to NULL, but so each select for all roots will be a full table scan, because indexes dont index NULL- values, isn't it?

example:

a table with employees EMP,

ID, ID_CHIEF, NAME .... ID_CHIEF has a foreign key to EMP.ID

if you want to find out all chiefs you can do either

select
  *
from
  EMP
where
  ID = ID_CHIEF or

select
  *
from
  EMP
where
  ID_CHIEF is NULL.

IIRC the last statement will produce a full table scan, in maybe thousends of employees with maybe 3 Chiefs.

Is this right or did I misunderstand anything completely? (English is a foreign language for me)

> --
> Sybrand Bakker, Senior Oracle DBA

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de