Ramon,
That is not true. Setting NOLOGGING at the object
level only reduces the amount of redo generated for
bulk INSERT operations with the /*+ APPEND */ hint,
certain partition administration operations and of
course during the creation of the object itself. It
does not eliminate generation of redo during a delete
operation.
Attached is a sample output from a couple of delete
commands, one with LOGGING and the other with
NOLOGGING:
Cheers,
Gaja
--- Ramon Estevez <[EMAIL PROTECTED]>
wrote:
> Hi Greg,
>
> Yes, you should get a improve performance due to the
> nologging option in the
> delete wont write redo log information.
>
>
>
> Ram�n Est�vez
>
>
>
> *809-565-3121 x 225
>
>
>
> * [EMAIL PROTECTED]
>
>
>
>
>
> -----Mensaje original-----
> De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En
> nombre de Greg Solomon
> Enviado el: Monday, 25 June, 2001 9:23 AM
> Para: Multiple recipients of list ORACLE-L
> Asunto: NOLOGGING
>
>
> Hi All
>
> If I have a delete which I know I never want to roll
> back, can I get a
> performance gain by using the nologging option ?
>
> Delete nologging is not, to the best of my
> knowledge, documented on 8.1.6
> ... but the query executes OK.
>
> However, when I used autotrace, the delete with
> nologging seemed to give the
> same or worse results than a normal delete.
>
> Greg
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Greg Solomon
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Ramon Estevez
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
=====
Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101
__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
SQL> select table_name, logging from user_tables;
TABLE_NAME LOG
------------------------------ ---
IDX_OBJ YES
IOX_EXPLAIN YES
MY_TAB
PLAN_TABLE YES
PROD_OBJECTS YES
TEST_OBJECTS YES
TEST_TAB YES
7 rows selected.
SQL> set autotrace traceonly
SQL> delete from test_objects where rownum < 11;
10 rows deleted.
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=540 Card=306828 Byte
s=920484)
1 0 DELETE OF 'TEST_OBJECTS'
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'TEST_OBJECTS' (Cost=540 Card=3
06828 Bytes=920484)
Statistics
----------------------------------------------------------
243 recursive calls
48 db block gets
37 consistent gets
22 physical reads
5704 redo size
850 bytes sent via SQL*Net to client
568 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> alter table test_objects nologging;
Table altered.
SQL> rollback;
Rollback complete.
SQL> select table_name, logging from user_tables;
TABLE_NAME LOG
------------------------------ ---
IDX_OBJ YES
IOX_EXPLAIN YES
MY_TAB
PLAN_TABLE YES
PROD_OBJECTS YES
TEST_OBJECTS NO
TEST_TAB YES
7 rows selected.
SQL> delete from test_objects where rownum < 11;
10 rows deleted.
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=540 Card=306828 Byte
s=920484)
1 0 DELETE OF 'TEST_OBJECTS'
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'TEST_OBJECTS' (Cost=540 Card=3
06828 Bytes=920484)
Statistics
----------------------------------------------------------
0 recursive calls
46 db block gets
1 consistent gets
0 physical reads
5628 redo size
854 bytes sent via SQL*Net to client
568 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> set autotrace off
SQL> spool off
