Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: October 19, 2001 - 11:17 pm UTC

Last updated: February 17, 2005 - 9:08 am UTC

Version: 817

Viewed 1000+ times

You Asked

Hi Tom
I have a batch process that loads some tables using inserts.

The statements I am concerned about are of the following order
drop index <indexontemp>;

insert into temp ...;

create index <indexontemp> on temp;

So what we are doing is ,first dropping the index on the table being inserted into then do the Insert & then create it back on the same table.
All this is in the same process.

SO dont you think then it doesnt matter to drop the index on the first place as it is being created again. So the time for this process to finish would be the same if the index was left on the table & was updated in the Insert process?
Also is there some overhead for creating index as compared to an already existing index being updated through the insert process?

I would greatly appreciate if you could help me get some clarity.

Thanks

and Tom said...

It depends. If the table is going to get LOTS of rows -- not maintaining the indexes makes alot of sense.

I myself might set the indexes UNUSABLE (in the event of a failure AFTER the drop, before the rebuild -- that way I don't "lose" an index by accident!).

It could look like this:


ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t as select * from all_objects where 1=0;
Table created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> create index t_idx1 on t(object_name);
Index created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> create index t_idx2 on t(object_id);
Index created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> exec :start := dbms_utility.get_time;
PL/SQL procedure successfully completed.

ops$tkyte@ORA717DEV.US.ORACLE.COM> alter index t_idx1 unusable;
Index altered.

ops$tkyte@ORA717DEV.US.ORACLE.COM> alter index t_idx2 unusable;
Index altered.

ops$tkyte@ORA717DEV.US.ORACLE.COM> alter session set skip_unusable_indexes=true;
Session altered.

ops$tkyte@ORA717DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;
16899 rows created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA717DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;
16899 rows created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA717DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;
16899 rows created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA717DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;
16899 rows created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA717DEV.US.ORACLE.COM> alter index t_idx1 rebuild nologging;
Index altered.

ops$tkyte@ORA717DEV.US.ORACLE.COM> alter index t_idx2 rebuild nologging;
Index altered.

ops$tkyte@ORA717DEV.US.ORACLE.COM> exec dbms_output.put_line( (dbms_utility.get_time-:start) || ' hsecs' );
2309 hsecs
PL/SQL procedure successfully completed.

compare that now with leaving the indices in place:

ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t as select * from all_objects where 1=0;
Table created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> create index t_idx1 on t(object_name);
Index created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> create index t_idx2 on t(object_id);
Index created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> exec :start := dbms_utility.get_time;
PL/SQL procedure successfully completed.

ops$tkyte@ORA717DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;
16899 rows created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA717DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;
16899 rows created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA717DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;
16899 rows created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA717DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;
16899 rows created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA717DEV.US.ORACLE.COM> exec dbms_output.put_line( (dbms_utility.get_time-:start) || ' hsecs' );
4686 hsecs

PL/SQL procedure successfully completed.

so, there are some efficiencies to be gained by rebuilding, instead of maintaining the index data structures during a mass load. This is only true of course for a mass load -- the proportion of newly loaded data to existing data must be sizable (no, i won't define sizable as your mileage may vary, benchmark it on your stuff and see)

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Reader

Reader, October 20, 2001 - 8:26 am UTC

Tom,

"
I myself might set the indexes UNUSABLE (in the event of a failure AFTER the drop, before the rebuild -- that way I don't "lose" an index by accident!).
"

What kind of failure that we forsee here

Thanks

Tom Kyte
October 20, 2001 - 8:42 am UTC

say after you drop the index (that commits, they are gone)
and after you load the data and commit it

the power goes out.. your load succeeded to the point it got to but it didn't finish.

Now, the users start using the data without any indexes! performance is down the tubes and you are wondering why. you find the missing indexes after a couple of hours and put them back and everything is great.

if you set the indexes unusable -- you will get ERRORS immediately, notifying you the data load failed, you fix it right away, you don't waste time looking for the reason, you are told the reason.

A reader, October 21, 2001 - 3:03 am UTC

Tom
Is there a gain in using
alter index <idx> unusable versus

drop index idx;

Also if we use the unusable statement then is it mandatory to use the
alter session set skip_unusable_indexes=true;


Tom Kyte
October 21, 2001 - 9:09 am UTC

Sorry that wasn't clear.

The gain is: you don't "accidently lose the index" if the load fails. With the drop, you might accidently lose it, with the set unusable -- you cannot "lose it".

You'll know right away that the index is in need of help, your users will call you and tell you when they get the error. Instead of everyone full scanning your table all day while you sit there and try to figure out why performance when down the tubes, you'll get some calls in the morning and fix it straight away.

You set skip_unusable_indexes=true when you want to ignore the fact that the indexes are unusable, so -- yes, for the load, we must.

DBA

ilya, October 22, 2001 - 4:04 am UTC

Tom
The way to load data you showed is OK, however comparison
you made is incorrect since meaning of few APPENDs in
second example is : to rebuild indexes several times before
you've done with load ( remember APPEND rebuilds indexes at the end ).

Tom Kyte
October 22, 2001 - 8:08 am UTC 

No, it is NOT incorrect.

What is incorrect?  

Append rebuilds the indexes at the end -- with logging.  You want to do it without -- do it my way.  

Read the example, it clearly shows 

A) there is a difference
B) there is a big difference

Regardless of the number of times I do the append -- i don't care, its going to be a large difference.  Look:


ops$tkyte@ORA717DEV.US.ORACLE.COM> variable start number
ops$tkyte@ORA717DEV.US.ORACLE.COM> 
ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t as select * from all_objects;

Table created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;
ops$tkyte@ORA717DEV.US.ORACLE.COM> commit;
ops$tkyte@ORA717DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;
ops$tkyte@ORA717DEV.US.ORACLE.COM> commit;
ops$tkyte@ORA717DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;
ops$tkyte@ORA717DEV.US.ORACLE.COM> commit;

ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t1 as select * from all_objects where 1=0;
Table created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> create index t1_idx1 on t1(object_name);
Index created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> create index t1_idx2 on t1(object_id);
Index created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t2 as select * from all_objects where 1=0;
Table created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> create index t2_idx1 on t2(object_name);
Index created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> create index t2_idx2 on t2(object_id);
Index created.


ops$tkyte@ORA717DEV.US.ORACLE.COM> column value new_val V
ops$tkyte@ORA717DEV.US.ORACLE.COM> select a.name, b.value
  2    from v$statname a, v$mystat b
  3   where a.statistic# = b.statistic#
  4     and a.name = 'redo size'
  5     and b.value > 0
  6  /

NAME                                VALUE
------------------------------ ----------
redo size                          964432


ops$tkyte@ORA717DEV.US.ORACLE.COM> exec :start := dbms_utility.get_time;
PL/SQL procedure successfully completed.

ops$tkyte@ORA717DEV.US.ORACLE.COM> alter index t1_idx1 unusable;
Index altered.

ops$tkyte@ORA717DEV.US.ORACLE.COM> alter index t1_idx2 unusable;
Index altered.

ops$tkyte@ORA717DEV.US.ORACLE.COM> alter session set skip_unusable_indexes=true;
Session altered.

ops$tkyte@ORA717DEV.US.ORACLE.COM> set autotrace on statistics
ops$tkyte@ORA717DEV.US.ORACLE.COM> insert /*+ append */ into t1 select * from t;
67932 rows created.


Statistics
----------------------------------------------------------
       3914  recursive calls
        568  db block gets
       2661  consistent gets
        928  physical reads
      70988  redo size
        998  bytes sent via SQL*Net to client
        825  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
      67932  rows processed

ops$tkyte@ORA717DEV.US.ORACLE.COM> set autotrace off
ops$tkyte@ORA717DEV.US.ORACLE.COM> alter index t1_idx1 rebuild nologging;
Index altered.

ops$tkyte@ORA717DEV.US.ORACLE.COM> alter index t1_idx2 rebuild nologging;
Index altered.

ops$tkyte@ORA717DEV.US.ORACLE.COM> exec dbms_output.put_line( (dbms_utility.get_time-:start) || ' hsecs' );<b>
1366 hsecs</b>

PL/SQL procedure successfully completed.

ops$tkyte@ORA717DEV.US.ORACLE.COM> select a.name, b.value-&V delta_redo_size
  2    from v$statname a, v$mystat b
  3   where a.statistic# = b.statistic#
  4     and a.name = 'redo size'
  5     and b.value > 0
  6  /
old   1: select a.name, b.value-&V delta_redo_size
new   1: select a.name, b.value-    964432 delta_redo_size

NAME                           DELTA_REDO_SIZE
------------------------------ ---------------<b>
redo size                               171708</b>


ops$tkyte@ORA717DEV.US.ORACLE.COM> select a.name, b.value
  2    from v$statname a, v$mystat b
  3   where a.statistic# = b.statistic#
  4     and a.name = 'redo size'
  5     and b.value > 0
  6  /

NAME                                VALUE
------------------------------ ----------
redo size                         1136140

ops$tkyte@ORA717DEV.US.ORACLE.COM> exec :start := dbms_utility.get_time;
PL/SQL procedure successfully completed.

ops$tkyte@ORA717DEV.US.ORACLE.COM> set autotrace on statistics
ops$tkyte@ORA717DEV.US.ORACLE.COM> insert /*+ append */ into t2 select * from t;
67932 rows created.


Statistics
----------------------------------------------------------
       7386  recursive calls
      11266  db block gets
       5028  consistent gets
       2214  physical reads<b>
   14820480  redo size</b>
        999  bytes sent via SQL*Net to client
        825  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          2  sorts (disk)
      67932  rows processed

ops$tkyte@ORA717DEV.US.ORACLE.COM> set autotrace off
ops$tkyte@ORA717DEV.US.ORACLE.COM> exec dbms_output.put_line( (dbms_utility.get_time-:start) || ' hsecs' );<b>
2888 hsecs</b>

PL/SQL procedure successfully completed.

ops$tkyte@ORA717DEV.US.ORACLE.COM> select a.name, b.value-&V delta_redo_size
  2    from v$statname a, v$mystat b
  3   where a.statistic# = b.statistic#
  4     and a.name = 'redo size'
  5     and b.value > 0
  6  /
old   1: select a.name, b.value-&V delta_redo_size
new   1: select a.name, b.value-   1136140 delta_redo_size

NAME                           DELTA_REDO_SIZE
------------------------------ ---------------<b>
redo size                             14820480</b>


171k versus 14m, 13 sec vs 28 sec.  One big insert only (same number of rows), same relative amount of time (it was 2x faster above in the original example, it is still 2x faster in this new example -- only each individual example is faster cause I did it as a single statement).


So, comparision correct, valid and OK.

Good example of Index impact on Bulk Updates, but what about Unique Indexes?

Brad Worsfold, October 22, 2001 - 1:06 pm UTC

Excellent answer Tom, but what about Unique indexes that are used to guarentee data? I suppose there is not much other than guarenteeing the data before it goes into the table some how.

I would be interested in any other ideas around improving data loads and unique indexes.

Tom Kyte
October 22, 2001 - 2:04 pm UTC

1) disable the constraint
2) load the data
3) enable the constraint with EXCEPTIONS INTO to record the rowids of rows that violate the constraint
4) clean up those rows
5) enable the constraint

that'll be faster then having the constraint enforced as you load.

Yep, that is what you could do alright, but automating this task...

Brad Worsfold, October 22, 2001 - 8:21 pm UTC

Thanks for the followup Tom.

Unfortunately, I would have to automate this somewhat. I guess we could read the UK constraint fields based on the error records and send an email notifying a support person to investigate, delete any related records and then rebuild the UK constraint.

I will have to see if this will help us.

Thanks,

Brad

rebuild vs drop & create

A reader, July 16, 2003 - 4:08 am UTC

Hi

Performance wise which operation is more desirable

rebuild the index or drop it and create it

both can done in nologging, rebuild probably offers more availability if we use online option but speed?

Tom Kyte
July 16, 2003 - 9:25 am UTC

you will find them to be the same.

a rebuild of an unusable index needs to read the base table, just like create.


you would not use an online rebuild after a load since the premise is you set the index to unusable during the load so the index is just a definition, not a real thing at that point.

"Drop index indexname" is take a long time...

A reader, February 15, 2005 - 8:11 pm UTC

I am finding that "drop index indexname" for just 3 indexes is taking over an hour to finish. This is after having imported 500 MB of data into the oracle database. I expected drop index to happen in less than a second. Have you even seen this happen?

Tom Kyte
February 16, 2005 - 7:41 am UTC

are you using a dictionary managed tablespace and did you let the index get into thousdands of extents? if so, quite possible.

(locally managed tablespaces are the only thing to use)

max, February 17, 2005 - 2:20 am UTC

does oracle update an index during updates that access an indexed column but leave that column's value unchanged?

Tom Kyte
February 17, 2005 - 9:08 am UTC 

no it does not.


ops$tkyte@ORA9IR2> create table t as select
  2  rownum x, rownum y, rownum+1 z
  3  from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx on t(x);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @trace
ops$tkyte@ORA9IR2> alter session set events '10046 trace name context forever, level 12';
 
Session altered.
 
ops$tkyte@ORA9IR2> update t set x = y;
 
27885 rows updated.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2> update t set x = z;
 
27885 rows updated.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.



update t set x = y
                                                                                                                                                         
                                                                                                                                                         
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          0          0           0
Execute      1      0.41       0.62          0        139      28684       27885
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.41       0.65          0        139      28684       27885
********************************************************************************

<b>oracle does a current mode get for each row it updates -- (it reads the table using consistent (query) reads -- when it finds a row of interest, it gets that rows block in current mode, updates it and gives it back.  ).  

so, current about equal to rows, nothing was touched in the index...</b>


update t set x = z
                                                                                                                                                         
                                                                                                                                                         
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      1.12       1.66         61        368     142499       27885
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.12       1.66         61        368     142499       27885

<b>there, we changed the value of the indexed column -- the additional current mode gets -- to modify the leaf blocks and effectively split/double the size of the index (it doesn't reuse the space within the same transaction -- we probably about doubled the index space)</b>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library