At 05:15 01/10/2004 -0700, you wrote:
... big snip ...

> Count, at worst, _should_ do an index space scan on the primary key
> rather than a table space scan. Min and max should be able to operate
> equally well against ascending and descending indeces. There typically
> is only one or two more I/O (10 to 20 milliseconds) involved in locating
> the last entry in a balanced index versus the first entry. These are
> obvious optimizations, and when I have time I will dig into the optimizer
> and identify the places to correct this, I will post the suggested change
> back here. Note that "improve the optimizer" is on the todo list on the
> Firebird sourceforge page.
> /dj

I though that because FB indexes are compressed (RLE Encoded AFAIR) one
could not traverse on the reverse way.

Do we still needs compressed indexes ?
What we have:
1.) More entries per index page (very good)
2.) Index uses less space (disk space should be treated as high priority
nowadays ?)
3.) ????

An option on create index statement to not compress could be a choice ?

The index could be stored compressed on disk-data-page, but once read the
cached-page could store the uncompressed version of the same page ? If so,
once those pages are in memory could we use the index on both directions ?

> In this case, may also be identifying a minor weakness in the
> optimizer. Without changing the basic design premise, Count(*) should
> run against the index with the smallest footprint that is guaranteed to
> include pointers to all rows rather than the table space itself.

I have asked once why FB does not use just the index when some select asks
just for indexed columns like:

select name from person where name like 'Ale%'

if you have an index on column "name" the engine could just scan the index
to find the data you want, and don't need to access each data-page.

But because of MGA, FB HAVE to look on the data-page to get transaction #
for each record to determine if this record is or isn't visible to such
transaction. So I think count(*) have to read each record for the same
reason, therefore a table scan is more cheap...



I think that with the new C++ code we will get interesting things bringed
by inheritance.

I thought we could have diferent kinds of tables (memory table for
example), indexes (compressed, uncompressed, b*tree, hash, clustered (a la
MSSQL), etcs...).

See you...


Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br

----------


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004


[Non-text portions of this message have been removed]