Home » SQL & PL/SQL » SQL & PL/SQL » Insert and creation of archive redo logs (Oracle 10.2.0.3)
Insert and creation of archive redo logs [message #383381] Wed, 28 January 2009 10:42 Go to next message
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 #383383 is a reply to message #383381] Wed, 28 January 2009 11:01 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
do you have an explain plan of the statement?
Re: Insert and creation of archive redo logs [message #383387 is a reply to message #383381] Wed, 28 January 2009 11:12 Go to previous message
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?
Previous Topic: BUlk insert limit with data blocks
Next Topic: outer join
Goto Forum:
  


Current Time: Fri Feb 07 20:23:35 CST 2025