Google Answers Logo
View Question
Q: Oracle Indexes ( No Answer,   3 Comments )
Subject: Oracle Indexes
Category: Computers
Asked by: santarita-ga
List Price: $10.00
Posted: 24 Oct 2006 23:59 PDT
Expires: 23 Nov 2006 22:59 PST
Question ID: 776654
I have an Oracle table with 5m rows. It is essentially a "VIEW" of
several other tables joined together to report off of. To be clear, it
is not a view, it is a table. There are about 30 columns, with 4
indexes. It takes 48 hours to rebuild this table from the core
transaction tables. However if I remove the indexes it takes 30
minutes. Question is, is it reasonable for the sytem to take 48 hours
to rebuild the indexes again to exactly the same size they were before
I truncated the table?
There is no answer at this time.

Subject: Re: Oracle Indexes
From: harrysnet-ga on 25 Oct 2006 17:12 PDT
What happens if you:

1) remove the 4 indices
2) recreate the table
3) create the 4 indices again from scratch

In particular how long does step (3) take? My guess is a 
lot less than 48 hours. Anyway, given even the 30 minutes 
to create your new table, I would guess that your table 
includes triggers. If so these would be executed every time 
a line is inserted, and can have any effect on the time it 
takes depending on the code they contain, with possible huge 
differences depending on the presence or absence of indexing. 

If that is the case you need an Oracle expert to take a look 
at your complete code to advice you on what happens and how 
to improve it.

In this case I would also recommend that you try the experiment 
of trying to create the table with the triggers removed, both 
with and without the indices. Expect to see very big improvements 
in this case also (I don't know if this would break your 
application though, if it does not it would be ideal if you could 
deploy your application without these triggers)
Subject: Re: Oracle Indexes
From: smar-ga on 19 Nov 2006 01:56 PST
Technology Is For re usability. should not delete present or existing
data and create new one.

Simply write a code to create new table and write code in such a way
that it automatically transfers the data to another table. (for this
process you should have programming knowledge.) If you write program,
and if executed, you can simply execute it, all data will be safe.

(Business Consultant)
Subject: Re: Oracle Indexes
From: singbat-ga on 23 Nov 2006 06:15 PST
if i did the quick math right, that's about 7 seconds per index entry.
 not fast. but not only, not impossible, but not unheard of.

the better question, imho, is not about 'reasonable' but about can you
improve on the time.  gut answer: yes.

might be good to know the data types of the columns and the types of
the indexes.  different index types will have different performance
and applicability profiles.  the data types on the columns will have a
very substantial impact, and clearly will also interplay with the type
of index used.

the amount of data held by the columns, other jobs on the box, amount
of ram dedicated to Oracle, etc, etc. are all "incidental" factors
that may have a significant relationship to the performance you are
seeing.  none of these items are "in the weeds" -- if you work through
the obvious issues and still don't have the time down to what makes
sense in your business context you should get in touch with an Oracle
DBA who can get into the configuration and system details at a
fine-grained level.  that, however, becomes time consuming and is
probably outside the scope of the $10 question...  :-)

good luck!

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy