Insert and creation of archive redo logs [message #383381] |
Wed, 28 January 2009 10:42  |
RudyH
Messages: 1 Registered: January 2009
|
Junior Member |
|
|
Hello
The post count is low, but I thought I would tap into other resources around the world to look for a solution. Searching Google and this forum didn't really provide me what I'm looking for and possibly a 3 word search is tough vs an explination
I do understand how insert affects log writer -> redo logs -> archive redo logs. Explination of my problem:
We have a data warehouse. Simplified process, we use an external table (manipulating what we need through a view) that reads a flat file (CSV). We then issue the following for example the client table:
SQL> INSERT /*+ APPEND */ INTO client D
2 (D.client_id,
....)
33 (SELECT CE.client_id,
....
64 FROM vw_client_external CE
65 MINUS
66 SELECT C.client_id,
....
97 FROM client C);
Flat file = 152 MB, archive logs generated ~ 800 MB. I understand redo logs write both redo and undo, with INSERT hint 'Append' we should only be performing redo, but no change.
Is this large amount of logs a result of just how Oracle does things, or is this something I can greatly reduce? Example importing data vs external tables? Performance is an issue as well, faster = boss is happy!
Easy fix is NOLOGGING set on the table, and is a potential option but I would like to avoid it at this time.
|
|
|
|
Re: Insert and creation of archive redo logs [message #383387 is a reply to message #383381] |
Wed, 28 January 2009 11:12  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You have misunderstood the purpose of the APPEND hint. It simply instructs Oracle not to check for unused blocks below the high water mark when inserting records, but to write full blocks of records above the high watermark instead.
an INSERT /*+ APPEND */ will still generate UNDO - if you get to the end of the insert and issue a ROLLBACK, what would you expect to happen?
What is the structure of the table you are importing into?
|
|
|