| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: archive query - revisited
Grant,
I am glad that you found reason for lot of archiving.
Yes, even with nologging, if table/tablespaces are dictionery managed, there
are redologs generated for extent management.
Archiving may be minimized by commiting after certain amount of rows like
rows >= 10000 or whaever which your redologs can handle.
For other tables created through procedure if those are created as unrecoverable/nologging or not. Make it nologging to avoid redo's specially created as select.I cannot open your attachment.... Regards
MOHAMMAD RAFIQ
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: Tue, 04 Sep 2001 19:40:24 -0800
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
Grant
<< table.txt >>
<< insert.txt >>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: rafiq9857_at_hotmail.com 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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Wed Sep 05 2001 - 11:02:14 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |