Avoiding redo log file entries using NOLOGGING table mode and /*+ APPEND */ hint
Date: Wed, 4 Feb 2009 11:59:59 -0800 (PST)
Message-ID: <1a035bc7-ec4a-4723-bfb2-a9a03842e027_at_v42g2000yqj.googlegroups.com>
I'm working on a 9i database in ARCHIVELOG mode. I don't have DBA privs on the database or the SELECT_CATALOG_ROLE (tough to inspect things behind the scenes without this).
I'm trying to reduce the redo log file activity so the disk volume won't fill up during large loads (before filled logs get copied off to another disk volume).
One tip I've read about is using direct path inserts; to create and/or alter a table to NOLOGGING mode, e.g. CREATE TABLE X (...) NOLOGGING; and then to use the /*+ APPEND */ hint on INSERT INTO ... SELECT statements--that this would reduce redo log entries considerably.
My question is: how can I verify, without having DBA privs or the SELECT_CATALOG_ROLE, that redo entries aren't being generated? Would a rollback fail to rollback the records inserted via direct path, for instance?
I've also read that FORCE LOGGING can be set at the database level in Oracle 9iR2. And if that's the case, then my NOLOGGING requests won't be honored.
What are some other methods for reducing redo log entries outside of, say, using SQL*LOADER? Disabling PK and UNIQUE constraints (and so preventing the underlying UNIQUE indexes from being popualted) seems like one good method.
Thanks.
Dana Received on Wed Feb 04 2009 - 13:59:59 CST