| Subject | Re: [firebird-support] does FB support clustered indices? |
|---|---|
| Author | Ann W. Harrison |
| Post date | 2008-10-25T17:38:12Z |
judlian23 wrote:
on data pages, in (approximately) the same order that the records were
inserted.
addresses the semantics of access to data, but not the storage
method or other mechanisms to reduce access time.
added to the last data page in the table. As long as you insert
records in the order that you want them stored, and don't delete
or modify them, you'll get the effect your looking for.
When you delete a record, the space it used will eventually be
reclaimed and made available for reused. When you update a
record, Firebird stores both the old and new versions of the
record - the old one is ordinarily stored as a difference, so
it's small - and when the updates are committed, eventually
the space used for the old version also becomes available,
and will eventually be reused.
So occasionally, Firebird might store a record in space that's
been released rather than at the end of the table. Under the
same circumstances, a clustered index would develop holes -
partially empty pages where the space could not be reused -
that would reduce retrieval performance.
Clustered indexes wouldn't make inserts faster than they are now.
If records are inserted in random order relative to their clustering
key but retrieved in ranges on that key, then a clustered index
could reduce the retrieval time. But that's not what you're
doing.
Good luck,
Ann
> Hi everyone,That's right. Firebird uses only b*tree indexes. All data is stored
>
> I saw on a post from 2006 that FB does not support clustered indices.
on data pages, in (approximately) the same order that the records were
inserted.
> I am currently using Firebird-2.1.1.17910-0_Win32_embed. Does thisIndexes aren't part of the standard. By policy the SQL standard
> version support clustered indices? I did not see it in the SQL:2003
> standard, but then again, that nuances of that version of SQL is a
> little foreign to me.
addresses the semantics of access to data, but not the storage
method or other mechanisms to reduce access time.
>Unless you delete or update records, newly inserted records will be
> What I mean by clustered is that I need records placed in my DB in
> order. I want them sorted by time, and these records will be inserted
> by time, so hopefully inserts would be fast with a clustered index
> because the db just needs to append the record.
added to the last data page in the table. As long as you insert
records in the order that you want them stored, and don't delete
or modify them, you'll get the effect your looking for.
When you delete a record, the space it used will eventually be
reclaimed and made available for reused. When you update a
record, Firebird stores both the old and new versions of the
record - the old one is ordinarily stored as a difference, so
it's small - and when the updates are committed, eventually
the space used for the old version also becomes available,
and will eventually be reused.
So occasionally, Firebird might store a record in space that's
been released rather than at the end of the table. Under the
same circumstances, a clustered index would develop holes -
partially empty pages where the space could not be reused -
that would reduce retrieval performance.
Clustered indexes wouldn't make inserts faster than they are now.
If records are inserted in random order relative to their clustering
key but retrieved in ranges on that key, then a clustered index
could reduce the retrieval time. But that's not what you're
doing.
Good luck,
Ann