Re: Index creation - performance problem

Disklayout is:
temp, system, rbs - disk1
redo logs - disk2
users - disk3
big table - disk4
ind - disk5
ind2 - disk6
ind3 - disk7

Disk array monitor + v$filestat shows that oracle reads & writes to the datafile that contains the big table.
All this happens during import of the user - owner of this table. Yesterday I divided import process into 3 steps :

1. import with indexfile=.... to get file with definition of the indexes
2. import the tables only - indexes=n in the parfile
3. create indexes from the file from step 1 I did not change anything compared to the import in one step, the whole process was even longer. Today I dropped the index on the big table and recreated it from sqlplus and this time oracle made no write to disk4. So it seems that the strange writes happen when index is created during import process , no matter if import was done in 1 or 3 steps. The same problem is with smaller tables, I imported just one table (400 MB) into new database user and oracle still writes do the datafile that contains the table during index creation .

Can you do such an import and check if your database writes to the table during index creation ?

Thx,

Adam

Sybrand Bakker wrote:

> Where is the temporary tablespace of that user? data tablespace? same disk?
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> Adam Roze <arz_at_royalpbk.com> wrote in message
> news:38C6758D.339F0603_at_royalpbk.com...
> > Hi,
> >
> > I am trying to create unique index on a big table.
> > Table is placed on separated datafile/tablespace/disk . Monitoring disk
> > activity shows that oracle reads and writes about the same
> > amount of data ~2.5 MB/s.
> >
> > I understand that oracle has to read the table to create index but why
> > does it write to it ??? Is it possible to avoid ??
> >
> > As the table is big ~6GB it takes an hour to create the index and I
> > hope that elimination of writes would make it faster.
> >
> > Oracle is 7.3.4.5 on Digital Unix 4.0E
> >
> > Any help would be greatly appreciated.
> >
> > Adam
> >
Received on Thu Mar 09 2000 - 00:00:00 CST