emb_blaster wrote:

>
> ...
> Select * from TABLE_A;
>
> or query:
>
> Select PK_CODE, FK_CODE1- fk from TABLE_A;
>
>
> First Question here... a why Select doesn´t use a index? it is because
> there´s no where and/or join clause, or because optimizier decided
> that natural is fastest, or another thing?

First, indexes are used to locate records. Unless the query includes
an indexed term in the WHERE or ON clause, there's nothing to look
for.

Second, Firebird stores multiple versions of records to preserve a
stable view of the database for all transactions. So, the index
will contain multiple entries for a single record if part of the
index key is updated. Firebird indexes don't contain any clue
about the range of transactions to which a particular entry
applies. Adding transaction information to the index would
increase both the size and volatility of indexes. The only way
to determine whether a value in the index is part of your
transaction's view is to look at the record itself.

> .assuming PK_CODE is > 0
>
> Select PK_CODE, FK_CODE1- fk from TABLE_A
> where PK_CODE >0;
>
> result Plan:
> PLAN (TABLE_A INDEX (RDB$PRIMARY29))
>
> there are second question is... why?

That's a weakness in our optimizer - actually a weakness in the
information we keep about indexes. If the optimizer knew the
range of values for the primary key, it could notice that this
query returns all records. On the other hand, there is a
trade-off between the amount of time spent maintaining detailed
information about key ranges and the value of optimizing dumb
queries.

Good luck,

Ann