Re: Q: Import and BUFFERS=? and COMMIT=Y

In article <5ptk69$cjh_at_panix.com>, Gary Assa <gsa_at_panix.com> writes
>
>What exactly does this do???? I mean, what should I set the BUFFER equal to?
>
>When I call Oracle, they just tell me word for word what is on the book. No
>help at all.
>
>I set BUFFER=4096 and even tried 1048576 and I see no difference? It still
>seems to commit after every row?
>
>What effect would a LONG RAW from an import file have??
>
>My goal is that I want to commit after 16 or 32 or 64 rows (just like SQL*Load,
>so as not to blow up rollback segs).
>

By default, the import utility performs a database commit after each tables' data is imported. Because of this, a Rollback Segment at least big enough to accomodate each imported table will be needed. In the case of VERY large tables this may be a problem. Hence the COMMIT=Y option.

"COMMIT=Y" implies that you want the import utility to commit the imported data after each buffer size load of data. (I think) the import in addition to this, still does a commit after the last row of each table has been imported.

The question then is "How big should the buffer be?" By increasing the buffer size & setting COMMIT=Y you can try to control the frequency of commits (which incur a time penalty during the import).

I find that setting the buffer size to 10Megabytes (on big UNIX servers with loads of RAM) helps improve performance a bit.

Having said all this note one thing. The lowest level one can perform a commit (within an ORACLE database) is on a row level. ie. you can commit a row of data but not a set of columns within a row. Therefore, if your buffer size is less than the length of a row from a particular table and you have COMMIT=Y you may run into a problem (not really, all you get is an oracle - ORA_ error) because the specified commit will not be possible. As such, tables with LONG RAW columns might (?) be a bit tricky. I've not really done much importing of tables with columns of this type so I can't say for sure.

Does all that make some sense?

-- 
Bola Ogunlana