Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: archive query - revisited

RE: archive query - revisited

From: Miller, Jay <>
Date: Wed, 05 Sep 2001 13:16:40 -0700
Message-ID: <>

Were the indexes also created with nologging?

And for those more knowledgeable than I: If the index was created with nologging and its associated table has date inserted with APPEND will block change vectors for the Indexes be written to redo logs?

If so, perhaps dropping the indexes before load and then recreating them with the NOLOGGING option would miminize your redo (since that seems to be an urgent matter for you). I don't know how feasible this is (size of the tables, # of indexes, need for access to indexes 24x7, etc)

-----Original Message-----
[] Sent: Tuesday, September 04, 2001 11:40 PM To: Multiple recipients of list ORACLE-L

Mohammad and Jay....many thanks for the responses.

Upon running your script Mohammad, I noticed that archive files were being written to the archive directory during a large insert operation and stopped being written once the insert had completed. The insert command uses APPEND, the associated table is indexed but created with nologging. It is however partitioned. I'm still confused as to why logging continues to occur.....could it be that the extents allocated during the insert operation are logged?

The same procedure creates a number of worktables to assist with join queries (not temp tables rather permanent tables) which are subsequently dropped after the query has completed (no indexes - hash joins only). There are no deletes or updates.

Any more clues??

Here's a copy of the table structure:

(See attached file: table.txt)

Here's a copy of the insert statement summarized from the procedure:

(See attached file: insert.txt)

Many thanks

Please see the official ORACLE-L FAQ:
Author: Miller, Jay

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Sep 05 2001 - 15:16:40 CDT

Original text of this message