Create Index vs Rebuild Index [message #490792] |
Thu, 27 January 2011 06:36 |
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 #490805 is a reply to message #490793] |
Thu, 27 January 2011 07:52 |
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
|
|
|