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

Posted on 2008-11-04
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 73

    Expert Comment

    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 47

    Expert Comment

    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 31

    Accepted Solution

    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

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    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.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now