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

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL question

Re: SQL question

From: Andreas Mosmann <keineemails_at_gmx.de>
Date: Wed, 04 May 2005 10:40:00 +0200
Message-ID: <1115196000.93@user.newsoffice.de>


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
Received on Wed May 04 2005 - 03:40:00 CDT

Original text of this message

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