1

I have a table with 100 million rows and I want to make inserts to this table faster. Unfortunatelly, I cant use insert with APPEND hint, because locking table is forbidden. Table has two indexes, so, will inserts be faster if i try to rebuild index or coalesce? Or maybe I should SHRINK this table ? And will coalesce or rebuild index online lock this table?

Jdzel
  • 159
  • 1
  • 3
  • 17
  • *why* are you not allowed to lock the table? – BobC Feb 09 '17 at 10:22
  • Why do you think rebuilding the indexes would make inserts faster? (Hint: it won't) –  Feb 09 '17 at 10:25
  • Because there are regular inserts/updates which modifies this table almost every 10 minutes. – Jdzel Feb 09 '17 at 10:25
  • Again: why do you think rebuilding the index would change anything for the performance of the insert? Indexes usually don't need rebuilding in Oracle anyway and certainly not to improve the insert performance. Also: `rebuild index` will also lock the table. So if you are not allowed to lock the table `rebuild index` is not an option (and useless anyway) –  Feb 09 '17 at 10:27
  • But what if you could load your 100M rows within your 10 min window? – BobC Feb 09 '17 at 10:27
  • @a_horse_with_no_name, so, if rebuilding is useless for performance, may be there are another variants? – Jdzel Feb 09 '17 at 10:28
  • @BobC, usually I insert about 50k-100k rows and I want that it will takes about 5-7 minutes – Jdzel Feb 09 '17 at 10:31
  • What is the source of your rows? Another table/query? Flat files? – BobC Feb 09 '17 at 11:06
  • My point about the APPEND / locking is, what if we could load the data in seconds - would it be "allowed" to lock the table for those few seconds? – BobC Feb 09 '17 at 11:07
  • Disable indexes, insert, re-enable them – Jucan Feb 09 '17 at 11:21
  • @BobC, source of rows - temporary table: insert TABLE1 as select * from TEMP_TABLE. – Jdzel Feb 09 '17 at 11:24
  • 1
    Is your target table partitioned? How much CPU do you have? How much IO bandwidth do you have? Is there a requirement to execute queries while the data is being loaded? – BobC Feb 09 '17 at 11:31
  • @BobC no, there are no partition. But I have primary key and two other indexes. – Jdzel Feb 09 '17 at 14:23

0 Answers0