Home » RDBMS Server » Performance Tuning » Create Index vs Rebuild Index (Oracle 9iR2 on RHEL)
Create Index vs Rebuild Index [message #490792] Thu, 27 January 2011 06:36 Go to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello

For bulk insert I was comparing cost of rebuild vs create index

I carried out the following test
SQL> create table t4 as select * from t1;

Table created.

SQL> create table t5 as select * from t1 where 1=2;

Table created.

SQL> create index i5 on t5(id);

Index created.

SQL> select bytes,extents,blocks from user_segments where segment_name='I5';

     BYTES    EXTENTS     BLOCKS
---------- ---------- ----------
     65536          1          8

SQL> alter index i5 unusable;

Index altered.

SQL> alter table t5 nologging;

Table altered.

SQL> Alter session set skip_unusable_indexes=True;

Session altered.

SQL>  insert /*+ append */ into t5 select * from t1;

563904 rows created.

SQL> commit;

Commit complete.


Now I compared the cost (elapsed time, logical I/O) of the operations
create index i4 on t4(id);
Vs
alter index i5 rebuild online;


Following is the related trace of above 2 steps

create index i4 on t4(id)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      1.17       3.38       9497       7869        335           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.17       3.38       9497       7870        335           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                        257        0.00          0.34
  direct path write                               4        0.00          0.00
  direct path read                               25        0.00          0.00
  log buffer space                               57        0.15          1.71
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       36.57         36.57
********************************************************************************

alter index i5 rebuild online


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0         21          1           0
Execute      1      0.94       2.88       9558       7889        445           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.95       2.91       9558       7910        446           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                        61        0.01          0.01
  db file scattered read                        257        0.00          0.10
  direct path write                               4        0.00          0.00
  direct path read                               19        0.00          0.00
  log buffer space                               54        0.15          1.39
  rdbms ipc reply                                 2        0.04          0.05
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        2.45          2.45
********************************************************************************



So which option we shall pick in such cases?

{Of course I haven't set 'nologging' for the indices but it is same for both indices we are comparing}

Regards,
OraKaran
Re: Create Index vs Rebuild Index [message #490793 is a reply to message #490792] Thu, 27 January 2011 06:52 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
The rebuild index was slightly faster.
It may be because it got more blocks from cache.
Anyway, I would favour the rebuild (as it will reuse the index definition).
Re: Create Index vs Rebuild Index [message #490805 is a reply to message #490793] Thu, 27 January 2011 07:52 Go to previous message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Many Thanks Frank

Anyway, I would favour the rebuild (as it will reuse the index definition).


Thanks. That is convincing

However if it were an 'update' operation, what would be your choice?
because for update
rebuild will have existing data but again update may cause change of blocks in cases where index keys are changed.
Thus increasing I/O
Right?

Regards,
OraKaran
Previous Topic: Optimizer question/ Date handling
Next Topic: Long Running Query
Goto Forum:
  


Current Time: Fri Apr 19 21:05:04 CDT 2024