At the very least, remove DISTINCT from the subselect - it serves no purpose except possibly slowing down Firebird (I don't know whether it slows down Firebird, it just wouldn't surprise me if it did).

Hopefully, this will be enough to speed up the query sufficiently. Alternatively, you can try

select distinct a.psc,a.mesto from adresar a
where not exists(select * from psc b where a.psc = b.psc);

There is no way to avoid NATURAL on adresar.

Sorry to not be of more help, you forgot to tell us anything about which version of Firebird you're running (Firebird 1.0 doesn't like IN (<subselect>) at all), what 'very long time' actually means, and how big the tables are.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Roland Turcan
Sent: 28. februar 2008 15:44
To: firebird-support@yahoogroups.com
Subject: [firebird-support] NATURAL sort

Hello firebird-support@yahoogroups.com!

I have this SQL statement which takes very long time, because it goes
through in "Natural".

Command:

select distinct a.psc,a.mesto from adresar a
where a.psc not in (select distinct b.psc from psc b);

Execution plan:

PLAN SORT ((B INDEX (IDX_PSC_PSC)))
PLAN SORT ((A NATURAL))

Indexes:

CREATE INDEX ADRESAR_PSC ON ADRESAR (PSC);
CREATE INDEX IDX_PSC_PSC ON PSC (PSC);

How to optimize it to get it faster and to use both indexes.

Thanks.

--
Best regards, TRoland
http://www.rotursoft.sk
http://exekutor.rotursoft.sk



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links