Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Insert into self, pause new indexing, then reindex.  Oracle 9i

Posted on 2008-11-04
Medium Priority
Last Modified: 2013-12-19
I would like to know if the following is possible.  I can thing of ways around this, using multiple tables, but I would like to avoid it...

1.  Create Table X
2.  Bulk insert into table X from table Y
3.  Index table X
4.  Pause the indexing of newly inserted rows to table x
5.  Insert into table X from table X
6.  Reindex Table X  (either all or newly added rows, don't care)

The point of #4 is to pause the indexing of the rows being inserted, but not drop the index as it will be useful when reading rows in #5.
Question by:CalDude
LVL 74

Expert Comment

ID: 22878813
you can't "pause" indexing.

You can drop the index before loading, then recreate the index afterward
For bulk loads, this is recommended practice.

Howerver "bulk" is somewhat subjective.
LVL 48

Expert Comment

ID: 22878935
We use dropping the index(s) and after that we rebuild it. Indexes work on the whole table, not on part of them. Index either exist or do not exist.
So you can create index when you need it and drop you to avoid the delay.

The only thing you missed is that only creating index is not enough to use it appropriatelly. It will be ignored by the Optimizer until you collect the statistics over the table and the index (espececially because you are speaking about bulk insert that changes the number of the rows, histograms and depth of the index tree).
LVL 32

Accepted Solution

awking00 earned 800 total points
ID: 22879541
You can alter the index to unusable -
alter index <index_name> unusable;
Then, when inserting new records -
alter session set skip_unusable_indexes=true;
Then, before the self inserts -
alter index <index_name> rebuild [compute statistics];

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses
Course of the Month13 days, 5 hours left to enroll

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question