|
|
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. SMAR (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! |
If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you. |
Search Google Answers for |
Google Home - Answers FAQ - Terms of Service - Privacy Policy |