Re: Simple query makes Oracle hang

From: áÌÅËÓÅÊ ïÓÁÄÞÉÊ <shuttle_at_alfanafta.kiev.ua>
Date: Fri, 5 Jan 2001 10:35:23 +0200
Message-ID: <93eier$f40$1@news.lucky.net>

> 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
>

It is not very strange, because it usually meet when you work with any SQL Server.

Your queries work in two differently ways.

In query #2 optimizator Oracle just join two tables, that work is very fast.

In query #1, optimizator Oracle run subquery with FileCat for each rows in main query.
Usually when optimizator Oracle work badly, query like this work for a long time.

You must recreate your query or write optimizator options in query.

For detail help contact with me shuttle_at_alfanafta.kiev.ua

Sorry for my English.                                          Alex P.

Osadchiy Received on Fri Jan 05 2001 - 02:35:23 CST