Re: Simple query makes Oracle hang

> > I have a problem in running a simple SQl query.
> >
> > This is very strange that the following query makes all oracle front
> > end tool makes "not responding". including sql plus and Oracle worksheet.
> >
> > select file_name from Filatable
> > where filetypeID= 2
> > and
> > FileCategory NOT IN ( select filecategory from fileCat)
> >
> > But if omit the not operator that query runs fine.
> >
> > select file_name from Filatable
> > where filetypeID= 2
> > and
> > FileCategory IN ( select filecategory from fileCat)
> >
> > I am badly struck with that problem.
> >
> > any help would do a lot
> >
> > Thanks in advance.
> > Faheem Rao

Try the following statement:

select ft.file_anme
from filetable ft
where

    ft.filetypeID = 2 and
    not exists (select 'x'

                from fileCat fc
                where fc.filecategory = ft.filecategory
                );

Be sure to create an index on table fileCat for column filecategory. This should speed up your statement and provides the same result as your statement with 'NOT IN'.

If you have many different filetypes in filetable then you should consider (if you didn't before) an index on that column, too.

BTW.: I would prefer 'exists' always instead of 'IN'

Let me know if it works for you.

Regards, Stephan Received on Tue Jan 09 2001 - 07:15:37 CST