Clustering and indexes 
Author Message
 Clustering and indexes

Hi...

Technical informix questions:
1. When you have a CLUSTERED index and do an UPDATE,
does informix perform a 'update in place' of the data
space or does it perform a DELETE/INSERT pair?
2. If you have a table which will have a very high
percentage of INSERTS but MAYBE a worst case of 2%
DELETEs/UPDATEs, is there a penalty associated with having
a CLUSTERED INDEX IFF the data is being INSERTed into
the table in ascending key order all the time?

Thanks in advance,
Joe Trubisz
Dept. of Computer Science
SUNY Stony Brook
Stony Brook, NY 11794



Wed, 18 Jun 1902 08:00:00 GMT
 Clustering and indexes

Quote:

> Hi...

> Technical informix questions:
> 1. When you have a CLUSTERED index and do an UPDATE,
> does informix perform a 'update in place' of the data
> space or does it perform a DELETE/INSERT pair?
> 2. If you have a table which will have a very high
> percentage of INSERTS but MAYBE a worst case of 2%
> DELETEs/UPDATEs, is there a penalty associated with having
> a CLUSTERED INDEX IFF the data is being INSERTed into
> the table in ascending key order all the time?

Perhaps a question to ask first is, "is the clustering of a cluster index maintained?". The answer is, "no".

So it's not a good idea to cluster volatile tables Dude.

AB
----------------------------------------------------------------
Get your free email from AltaVista at http://altavista.iname.com



Wed, 18 Jun 1902 08:00:00 GMT
 Clustering and indexes

Quote:

> Hi...

> Technical informix questions:
> 1. When you have a CLUSTERED index and do an UPDATE,
> does informix perform a 'update in place' of the data
> space or does it perform a DELETE/INSERT pair?

Informix updates rows in place unless the row contains a VARCHAR column that
grows with the update such that it no longer fits on its home page.  In the
latter case the row is moved to another page and a forwarding pointer is
placed in its slot on the original location, but the indexes still point
to the original slot.

Quote:
> 2. If you have a table which will have a very high
> percentage of INSERTS but MAYBE a worst case of 2%
> DELETEs/UPDATEs, is there a penalty associated with having
> a CLUSTERED INDEX IFF the data is being INSERTed into
> the table in ascending key order all the time?

No penalty but little advantage from the ordering of the data either.  If
the data is inserted in ascending order, according to the index, it does
not guarantee that the table will maintain its clusteredness.  Because
Informix selects pages onto which to insert rows using the LRU queues, it
is likely that two threads inserting rows, or even sequential rows inserted
by a single thread, will select different pages onto which to append the
new row.  The position of data inserted is not really random but will
appear so and will not perform for sequential scans as well as the table
will if the clustered index is reclustered.  Inserting data in a strictly
ascending manner it will take longer (ie more new rows) before any
performance degradation is noticeable or severe than if data with keys all
over the map were being inserted but there will be some degradation
eventually.  A larger BUFFER cache and good RA parameter settings can
minimize the effects further as can table RESIDENCY for smaller tables.

Art S. Kagel



Wed, 18 Jun 1902 08:00:00 GMT
 Clustering and indexes


Quote:

>Technical informix questions:
>1. When you have a CLUSTERED index and do an UPDATE,
>does informix perform a 'update in place' of the data
>space or does it perform a DELETE/INSERT pair?

Update in place -- the index loses its "clusteredness" over time...

Quote:
>2. If you have a table which will have a very high
>percentage of INSERTS but MAYBE a worst case of 2%
>DELETEs/UPDATEs, is there a penalty associated with having
>a CLUSTERED INDEX IFF the data is being INSERTed into
>the table in ascending key order all the time?

Well, yes, but it might not be significant. It's hard to say.
________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com


Wed, 18 Jun 1902 08:00:00 GMT
 Clustering and indexes

Quote:


> > Hi...

> > Technical informix questions:
> > 1. When you have a CLUSTERED index and do an UPDATE,
> > does informix perform a 'update in place' of the data
> > space or does it perform a DELETE/INSERT pair?

> Informix updates rows in place unless the row contains a VARCHAR column that
> grows with the update such that it no longer fits on its home page.  In the
> latter case the row is moved to another page and a forwarding pointer is
> placed in its slot on the original location, but the indexes still point
> to the original slot.

Therefore it would be a good idea to define a MINIMUM value
for the VARCHAR columns as well. And an additional question
to Joe, will you update the indexed columns or will you update
other columns ?

Quote:

> > 2. If you have a table which will have a very high
> > percentage of INSERTS but MAYBE a worst case of 2%
> > DELETEs/UPDATEs, is there a penalty associated with having
> > a CLUSTERED INDEX IFF the data is being INSERTed into
> > the table in ascending key order all the time?

> No penalty but little advantage from the ordering of the data either.  If
> the data is inserted in ascending order, according to the index, it does
> not guarantee that the table will maintain its clusteredness.  Because
> Informix selects pages onto which to insert rows using the LRU queues, it
> is likely that two threads inserting rows, or even sequential rows inserted
> by a single thread, will select different pages onto which to append the
> new row.  

But it's only possible to enter new rows into a page if the page
itself has enough free space inside. If there's only one page inside
the table with enough free space for the new row, this page will be
used. Normally you will have several pages with room inside if you'd use
a flexible row-size ( varchar's ). Otherwise it's only possible to have
pages with room if you remove some rows ( DELETE ).

Quote:
> The position of data inserted is not really random but will
> appear so and will not perform for sequential scans as well as the table
> will if the clustered index is reclustered.  Inserting data in a strictly
> ascending manner it will take longer (ie more new rows) before any
> performance degradation is noticeable or severe than if data with keys all
> over the map were being inserted but there will be some degradation
> eventually.  A larger BUFFER cache and good RA parameter settings can
> minimize the effects further as can table RESIDENCY for smaller tables.

---> in other words, use CLUSTER INDEXES just for large tables, where
"large" means more than 50% of the total Buffer Pool size.

Regards

--
Stefan Weideneder

Phone: +49 89/3565478-2 ---------------
--- Fax: +49 89/3565478-3 -------------

-------- http://www.weideneder.de -----



Wed, 18 Jun 1902 08:00:00 GMT
 Clustering and indexes

Quote:



> > > Hi...

> > > Technical informix questions:
> > > 1. When you have a CLUSTERED index and do an UPDATE,
> > > does informix perform a 'update in place' of the data
> > > space or does it perform a DELETE/INSERT pair?

> > Informix updates rows in place unless the row contains a VARCHAR column that
> > grows with the update such that it no longer fits on its home page.  In the
> > latter case the row is moved to another page and a forwarding pointer is
> > placed in its slot on the original location, but the indexes still point
> > to the original slot.

> Therefore it would be a good idea to define a MINIMUM value
> for the VARCHAR columns as well. And an additional question

Yes it is, Informix will reserve the MINIMUM size so that there will be
fewer rows per page and fewer rows will have to be relocated.

- Show quoted text -

Quote:
> to Joe, will you update the indexed columns or will you update
> other columns ?

> > > 2. If you have a table which will have a very high
> > > percentage of INSERTS but MAYBE a worst case of 2%
> > > DELETEs/UPDATEs, is there a penalty associated with having
> > > a CLUSTERED INDEX IFF the data is being INSERTed into
> > > the table in ascending key order all the time?

> > No penalty but little advantage from the ordering of the data either.  If
> > the data is inserted in ascending order, according to the index, it does
> > not guarantee that the table will maintain its clusteredness.  Because
> > Informix selects pages onto which to insert rows using the LRU queues, it
> > is likely that two threads inserting rows, or even sequential rows inserted
> > by a single thread, will select different pages onto which to append the
> > new row.

> But it's only possible to enter new rows into a page if the page
> itself has enough free space inside. If there's only one page inside

Or if the page is clean, ie no rows on it yet at all.

Quote:
> the table with enough free space for the new row, this page will be
> used. Normally you will have several pages with room inside if you'd use
> a flexible row-size ( varchar's ). Otherwise it's only possible to have
> pages with room if you remove some rows ( DELETE ).

No, since additional extents will be added as needed as long as the dbspace
has room and the fragment/table has fewer than 16million rows.  So data can
always be added to unused pages in an existing extent or a new extent can
be allocated.

Quote:
> > The position of data inserted is not really random but will
> > appear so and will not perform for sequential scans as well as the table
> > will if the clustered index is reclustered.  Inserting data in a strictly
> > ascending manner it will take longer (ie more new rows) before any
> > performance degradation is noticeable or severe than if data with keys all
> > over the map were being inserted but there will be some degradation
> > eventually.  A larger BUFFER cache and good RA parameter settings can
> > minimize the effects further as can table RESIDENCY for smaller tables.

> ---> in other words, use CLUSTER INDEXES just for large tables, where
> "large" means more than 50% of the total Buffer Pool size.

WHAT?  I don't get that at all?!!?!?  Clustering is best used for tables
that are fairly static or where most sequential scans will involve only
older data.  You can expand the number of tables for which it is appropriate
if you can afford to recluster the index frequently enough that you are
still gaining from its use.

Art S. Kagel



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Clustered Composite Index Is Big...too Big?

2. Clustered/Nonclustered Indexes

3. clustered + nonclustered indexes

4. Non-clustered composite index

5. Primary Key v.s. Clustered Unique Index

6. clustered keys/index

7. Clustered Composite Index w/ Int & Varchar

8. cluster table/index

9. cluster table/index

10. Clustering of Index and performance

11. Clustered Hash Index?


 
Powered by phpBB® Forum Software