Hello Michael,

> I have a table defined as:
>
> CREATE TABLE POINT_KVIT
> (
> ID INTEGER NOT NULL,
> BONNR INTEGER DEFAULT 0,
> TEKST VARCHAR( 100) DEFAULT '' COLLATE NONE,
> CONSTRAINT FK_POINT_KVIT PRIMARY KEY (ID)
> );
>
> CREATE ASC INDEX POINT_KVIT_IDX1 ON POINT_KVIT (BONNR);
>
>
> This tables contains some 24 millions records.
>
>
> The selectivity to index POINT_KVIT_IDX1 is 0,00000142520 or less

Bad, if I recall correctly.

> If I do a select like this:
>
> Select
> ID, BonNr, Tekst
> from Point_Kvit
> Where BonNr = 2236136
>
> It fetches 31 records in hardly no time (0.062 secs) with this plan:
> PLAN (POINT_KVIT INDEX (POINT_KVIT_IDX1))
>
>
> If I on the other hand do a select like this:
>
> Select
> ID, BonNr, Tekst
> from Point_Kvit
> Where BonNr = 2236136
> Order by ID
>
> It fetches the same 31 records, but i now uses 2 seconds to do the same
> with this plan:
> PLAN (POINT_KVIT ORDER RDB$PRIMARY240 INDEX (POINT_KVIT_IDX1))
>
>
> My question is, why is there such a big difference in the to above
> Selects?
>
>
> I need to have them ordered, so I get them in the same order they where
> inserted.
> I have for now solved it by doing this select, which is as fast as the
> first one:
>
> Select
> ID, BonNr, Tekst,
> BonNr || ID as P
> from Point_Kvit
> Where BonNr = 2236136
> order by 4
>
> Which also have the same PLAN as the first one.

Have you tried a simple

ORDER BY ID + 0

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com