On Fri, Sep 2, 2011 at 19:21, Vander Clock Stephane <[email protected]> wrote: > unfortunatly > > If you override the default NULLs placement, no index will be used for > sorting. In Firebird 1.5, > that is the case with NULLS FIRST. In 2.0 and higher, with NULLS LAST on > ascending and > NULLS FIRST on descending sorts. > > but i need the index :(
What's the performance if you use an (ugly) hack like this: Create a column NULLFLAG (with values 0 and 1) with an index on it and in a trigger set it according to your NULLable column. Then in the SELECT statement do ORDERY BY NULLFLAG, FIELD and Firebird should use both indexes. And maybe you can even omit the NULLFLAG column by using an index on an expression http://www.firebirdsql.org/refdocs/langrefupd20-create-index.html http://www.janus-software.com/fbmanual/manual.php?book=psql&topic=56 like this: CASE WHEN FIELD IS NULL THEN 0 ELSE 1 Just a few random thoughts :) HTH, Markus
