Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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> deReceived on Wed May 04 2005 - 03:40:00 CDT
![]() |
![]() |