CODEWITHSUNDEEP
databaseoracle-databaseindexing
user1579805
user1579805

Reputation: 23

How to load big amount of data fast and keep database responsive?

The situation :

Ok so i look for options etc., and i came to conclusion that i should disable indexes for the table during loadtime ok :

*alter index your_index unusable;*

But during load other processes and systems can search data in table A and insert data into table A. So i need to use :

*alter system set skip_unusable_indexes = true;*

After data is loaded i rebuild index and clear flags.

Ok here comes a questions :

  1. will queries use my index( when it is set unusable ) - we are talking about big tables, without index they will timeout

  2. if 1 is true what happens with newly inserted records( i mean the ones inserted through other processes not bulk-load ) before i rebuild index, are they returned when queried? Is it possible to somehow partially rebuild indexes/disable them for one session and keep for other?

I mean it shouldn`t be such uncommon situation in big systems - lots of data to load, but system needs to stay responsive.

To summarize - partitioning and partition exchange looks like the way to go

Upvotes: 0

Views: 1571

Answers (2)

OraNob
OraNob

Reputation: 694

If your data is coming from file format I'm wondering if the use of SQL*Loader is the solution since it will offer you all the options you are seeking ie. disabling of indexes / constraints, commit frequency etc etc.

It is a high performance data loading tool. It may or may not suit your needs but it offers you another option.

You situation is not something I have encountered before in that large data loads I have handled have been done when the system is either inactive or at a low period in terms of users online.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231671

If the index is unusable, queries will not be able to use it. If your queries will time out if they cannot use the index and the queries need to happen at the same time that you are loading data, you can't mark the index unusable. You'll have to incur the overhead of index maintenance during your loads.

Depending on the situation, you may benefit from partitioning the table in such a way that the large loads can be done into a staging table, the index(es) are built on the staging table, and a partition exchange is done to exchange a new, empty partition in the table for the staging table you just populated. Of course, without knowing more about the data, it's impossible to know whether partitioning the table in this way would be possible or whether it would have other negative implications for query performance.

Upvotes: 2

Related Questions