Hi,

-> > Could you give the statistics info from index DELDATE ?

>
> Yep sure...
>
> Index MOV_ORDP_DELDATE (5)
> Depth: 2, leaf buckets: 39, nodes: 25772
> Average data length: 0.00, total dup: 25159, max d
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 1
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 38

Hmm, this doesn't explain to me why NOT NULL is slower as using the index??
I need still some more information. What are the PLANs returned by engine
and give the statistics of all indices from that table.


If there's no index on ID i guess this :

...WHERE (ID=:ID) and (DELDATE is not null)
PLAN (TABLE NATURAL)

...WHERE (ID=:ID) and ((DELDATE >= '31.12.1899') and
(DELDATE <= '31.12.2004'))
PLAN (TABLE INDEX (MOV_ORDP_DELDATE))


With also a index on ID i guess this :

...WHERE (ID=:ID) and (DELDATE is not null)
PLAN (TABLE INDEX (MOV_ORDP_ID))

...WHERE (ID=:ID) and ((DELDATE >= '31.12.1899') and
(DELDATE <= '31.12.2004'))
PLAN (TABLE INDEX (MOV_ORDP_ID, MOV_ORDP_DELDATE))
or
PLAN (TABLE INDEX (MOV_ORDP_DELDATE))
or
PLAN (TABLE INDEX (MOV_ORDP_ID_DELDATE))

and if ID has a index then this could be the problem if it has many
duplicates. So, does ID have many duplicates?

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81