At 12:08 PM 4/8/2004 +1000, [EMAIL PROTECTED] wrote:
A little while ago a list reader suggested a kind of index (from ms
access, if I recall... I don't recall the term they used) that is not
external. Instead the index changes the order in which the table itself is
organised.
I believe you're thinking of a 'clustered index'. This puts the data
into the order of the index. There can be, of course, only one clustered
index per table.
Since at least some of the data in the table is moved around on every insert,
regular indexs need to take this into account (usually by indirection, rather
then modifying all of the indexes with each insert).
If a table could be ordered according to an index, rather than having an
external index, I think it would significantly improve the time and space
performance of my databases. I don't know whether my experience would be
shared by other users. It it were something that could go into 3.0 it
would at least do me some good.
I'm not sure it would be good for your application. Clustered indexes are
good for OLAP (On-Line Analytical Processing) and other systems where data
is rarely inserted (or is inserted in batches at off-hours) and most of the
interactive activity is selects (with very few inserts).
Clustered indexes can really slow the performance for OLTP (On-Line
Transaction Processing) and other systems where data is added/deleted in a
mixed fashion. Every time a record is inserted, data is possibly moved on
the disk (with page splits causing even more slowdowns).
If your system is entirely historic data, that would be great - but if your
system is inserting meter readings in (near) real-time, you'd probably
get a big performance hit.
There are many pros and cons. A google search turns up articles (usually for
MS SQL Server) on both side - some people saying 'always' and some 'never'.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
