Re: Create index ->Updating the table?

In <1993Oct8.125900.4364_at_dde.dk> mj_at_dde.dk (Martin Jensen) writes:

>First the setup:
>Running Oracle 6.0.36, having different tablespaces, say A, B and C.
>Have an 8 milion row table in tablespace A, have the indexes on that table
>on tablespace B, and the rollback and temporary segments on tablespace C.
>Being the only user on that system.
 

>Creating the first index on the table, we say through the monitor that:
>Of cource the tablespaces B and C should be written to, but also tablespace A
>was being updated - why? There is only need to read the columns to index?

My guess would be that you are seeing data block cleanup as per the current discussion in this newsgroup.

>The funny thing that really puzzles me is that generating the second and
>third index, nothing was updated in tablespace A.

This is consistent with the above. After the first pass there should be no "dirty" blocks left to cleanup.

>Can anybody explain this behaviour?

It's a guess. But if I'm right then doing BEGIN; UPDATE foo SET col1=col1; COMMIT; after building the second index should produce an impressive number of writes to tablespace A on the third index build.

graeme

--
Disclaimer:	The author's opinions are his own, and not necessarily
		those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
      -m------- Graeme Sargent                 Voice: +44 (0)252 373035
    ---mmm----- Senior Database Consultant     Fax  : +44 (0)252 373135
  -----mmmmm--- Pyramid Technology Ltd.        Telex: Tell who???
-------mmmmmmm- Farnborough, Hants  GU14 7PL   Email: graeme_at_pyra.co.uk
---------------------------------------------------------------------------
    We have the technology.  The tricky bit is learning how to use it.