In general, I'd say that you're doing things wrongly. However, it really depends on your situation...

Generally, when scanning through a table, there's no quicker way than to use NATURAL. However, if you're only interested in a small subset of records, then it is of course beneficial to not touch the other records and for that purpose indexes are brilliant.

So, to answer your question correctly one would need to know how large a percentage has SALDO = 0. I don't know exactly how large this percentage has to be for an index on ABS(SALDO) to be useful (useful in the meaning that it will not slow down query execution) - myself I avoid indexes if I'm interested in more than 20% of the records, but that's just how I do things on our Firebird 1.5.4 database and I've not investigated it, so 5% or 30% might have been a better value to choose as my threshold.

Let's say that 99% of your records do have SALDO = 0. Then your index would be useful whenever you wanted to get records 'WHERE ABS(Saldo) > 0', but you would want to avoid running 'WHERE ABS(Saldo) = 0' since this would suffer from using the index.

In most cases, I'd say Firebird is right in using indexes for equality comparison whilst not using indexes for difference.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Sergio H. Gonzalez
Sent: 18. september 2008 20:28
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] using indices

Mmmh... I think I understand now that "value <> 0" can't use index... Am I
wrong?

I've changed

> CREATE INDEX FAC_COM_IDX4 ON FAC_COM (SALDO);

for
CREATE INDEX FAC_COM_IDX4 ON FAC_COM COMPUTED BY (ABS(SALDO));

and

> select sum(saldo) from fac_com where (saldo<>0)

for
select sum(saldo) from fac_com where ABS(SALDO)>0

and now I get the plan:

Plan
PLAN (FAC_COM INDEX (FAC_COM_IDX4))

So is that the correct way to do it?

thanks!

-s