Re: nologging question ?

Ted,

Since you copied me on this one I'll do my best, but I was avoiding the issue because I haven't stress-tested it yet. Here are some thoughts though - and if anyone firms them up with targeted tests I'd be glad to hear from them.

By INSERT NOLOGGING I assume you mean the table has the NOLOGGING feature set, and your insert statement has the /*+ append */ hint.

The mechanism for the table is:

    grab space above the high water mark     insert the (probably pre-formatted blocks of) data above the current HWM     on success bump the high water mark.

In this simple case, if the operation fails before the hwm is raised, the roll back doesn't need to happen, the hwm is simply left in its original state.

The interesting problem (that I haven't tested in any way) is what happens when there are indexes on the table ?

I suspect that something analogous to the 'direct load' status of indexes occurs - in other words as soon as the table data is visible the indexes have to be rebuilt (or updated), and if this operation fails on any index then it, and all the remaining indices are simply left in an unusable state, rollback is simply not possible.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk "So much to do - so little time" - Sir Cecil Rhodes.

tedchyn_at_yahoo.com wrote in message <7o6vdm$ilc$1_at_nnrp1.deja.com>...
>sir,
>oracle 8 or above
>Tab_a contains 1m rows and Tab_b has 2000 rows.
>Insert tab_a nologging select * from tab_b.
>
>How would oracle rollback this operation, if nologging bypassing both
>redo and undo operation and above Insert operation fails ?
>
Received on Fri Aug 06 1999 - 10:02:53 CDT