Hint for desktop-Oracle developers - Insert into table with NOLOGGING
Here's the trick.
I've got a table with 2,2M rows for performance testing purposes, but now I wanted to export the data to a new, partitioned table. So logical would be just to INSERT INTO, right?
Well in my case it wasn't, because the redo that would have been created as a result, would have been too much for my PC (maybe it wouldn't have been, but I didn't want to waste my time). So I searched a bit because I remembered a way to make a "direct-path insert" with no redo log.
The answer was, as always, in the fine documentation of Oracle. LOGGING is the default, as in production you surely wouldn't want to lose any data, but in development there is no need for that kind of strickness.
SQL> alter table partitionedTable nologging;
Table altered.
SQL> insert into partitionedTable select * from noPartitionsWhatsoever nologging;
2198152 rows created.
SQL> alter table partitionedTable logging;
Table altered.
I've got a table with 2,2M rows for performance testing purposes, but now I wanted to export the data to a new, partitioned table. So logical would be just to INSERT INTO, right?
Well in my case it wasn't, because the redo that would have been created as a result, would have been too much for my PC (maybe it wouldn't have been, but I didn't want to waste my time). So I searched a bit because I remembered a way to make a "direct-path insert" with no redo log.
The answer was, as always, in the fine documentation of Oracle. LOGGING is the default, as in production you surely wouldn't want to lose any data, but in development there is no need for that kind of strickness.
SQL> alter table partitionedTable nologging;
Table altered.
SQL> insert into partitionedTable select * from noPartitionsWhatsoever nologging;
2198152 rows created.
SQL> alter table partitionedTable logging;
Table altered.
posted by Martti at 3:46 AM
4 Comments:
Umm, that just used conventional path but gave the table in the SELECT statement an unusual alias.
What you need is:
insert /*+ APPEND */ into partitionedtable
select * from nopartitionsphatsoever;
"nologging" in insert made on difference, thats right. But the alter table made.
Here are two distinct goals:
1. appending data directly into the end of table (without searching free blocks in the middle)
2. avoiding redo and undo information for performance and time saving (on the account of recoverability)
Now I was interested in the latter one, You made the comment about the first one. True - /*+ APPEND */ hint allows to INSERT data quicker, but doesn't avoid creating redo and undo.
To achieve that, I need to declare that my TABLESPACE or my TABLE or my PARTITION is in NOLOGGING mode with CREATE TABLE or ALTER TABLE.
I found some more information on that in the documentation - http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#i1009882
and on AskTom -
http://asktom.oracle.com/pls/ask/f?p=4950:8:::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:485221567528
The APPEND hint is also the way you invoke direct path INSERT.
In SQL*Plus,
SET AUTOTRACE ON STAT
and then try the INSERT with and without the APPEND hint. You will see that redo is greatly reduced when using APPEND.
btw I forgot to mention that the INSERT /*+ APPEND */ test assumes the table is defined NOLOGGING, which of course your table was. My home 10g setup is in noarchivelog mode, which it makes it a little tricky to compare logging with nologging, because noarchivelog mode makes everything nologging anyway. INSERT /*+ APPEND */ still makes a huge difference though.
Post a Comment
<< Home