Re: indexes reserve too much extents

From: Sybrand Bakker <sybrandb_at_hccnet.nl>
Date: Mon, 03 May 2004 19:23:22 +0200
Message-ID: <ervc90ho8r64dn208ee7csb7tcm6ostne9@4ax.com>

On Mon, 3 May 2004 16:47:50 +0200, "Frederic Hoornaert" <Frederic.Hoornaert_at_transics.nospam.com> wrote:

>Because 17.8 Gb for only 6 Gb of data seems to be too much, I have analyzed all tables and all indexes.
>One table with 5.979.487 rows has a size of 163.840 blocks in 10 extents (1 extent = 128M)
> assigned size = 1,25 Gb
>This table has 30 indices which use 1.024.000 blocks => 7,8 Gb
I think you have your answer here. You have 30 ! indexes on 1! table in an OLTP application. Those indexes don't consume 0 space, as you demonstrate. Also 30 indexes is utterly ridiculous and will hurt INSERT, UPDATE and DELETE performance

BTW: sqlloader doesn't have INDEXES=N.

I would redesign the app.

--
Sybrand Bakker, Senior Oracle DBA

Received on Mon May 03 2004 - 12:23:22 CDT