What's the NOLOGGING clause for when creating tables? Some of our
tables (particularly intermediate and output set tables) have been
created with the NOLOGGING clause, which I assume means that in the
case of a system failure (crash), these tables won't be recoverable.
This is acceptable, since the process would be restarted anyway once
oracle was restarted. On the plus side, I assume inserts and updates
are faster. Is this generally a good idea and something that people
would recommend in general?

Thanks

Dean

On Dec 8, 3:47 pm, "dean" <deanbrow...@yahoo.com> wrote:
> What's the NOLOGGING clause for when creating tables? Some of our
> tables (particularly intermediate and output set tables) have been
> created with the NOLOGGING clause, which I assume means that in the
> case of a system failure (crash), these tables won't be recoverable.
> This is acceptable, since the process would be restarted anyway once
> oracle was restarted. On the plus side, I assume inserts and updates
> are faster. Is this generally a good idea and something that people
> would recommend in general?
>
> Thanks
>
> Dean

NOLOGGING is only applicable to certain operations like INSERTS in
APPEND mode and direct path loads using SQL*Loader.
Ordinary INSERTS and UPDATES will be logged.
On a properly tuned system the belief INSERTS and UPDATES are faster
without logging should be qualified as an unproven superstition.
Oracle added the FORCE LOGGING clause for standby situations, to
override any NOLOGGING operations. Any idea why? Do you *really* think
NOLOGGING is recommended? Or do you like quick job changes?

--
Sybrand Bakker
Senior Oracle DBA

>
> NOLOGGING is only applicable to certain operations like INSERTS in
> APPEND mode and direct path loads using SQL*Loader.
> Ordinary INSERTS and UPDATES will be logged.
> On a properly tuned system the belief INSERTS and UPDATES are faster
> without logging should be qualified as an unproven superstition.
> Oracle added the FORCE LOGGING clause for standby situations, to
> override any NOLOGGING operations. Any idea why? Do you *really* think
> NOLOGGING is recommended? Or do you like quick job changes?
>
> --
> Sybrand Bakker
> Senior Oracle DBA

Ok thanks a lot Sybrand. Evidently I misunderstood the use.

So are there any specific table creation clauses we should be using
then, in this kind of situation? We use a 9.2 and 10g mix, depending on
clients, and we have many so-called output-set tables that just hold
data for reports and internal calculations. Such tables can easily be
recreated, but they also contain more data on a storage level than any
other tables. If we don't care about any recoverability, is there
anything else worth investigating?

The tables are initially filled using SQL loader, which I'm not
interested in speeding up. But after that, we do allow smaller edits
and inserts - a few tens to a few thousand rows at a time during user
interaction.

Thanks, Dean

dean wrote:
> So are there any specific table creation clauses we should be using
> then, in this kind of situation? We use a 9.2 and 10g mix, depending on
> clients, and we have many so-called output-set tables that just hold
> data for reports and internal calculations. Such tables can easily be
> recreated, but they also contain more data on a storage level than any
> other tables. If we don't care about any recoverability, is there
> anything else worth investigating?
>
> The tables are initially filled using SQL loader, which I'm not
> interested in speeding up. But after that, we do allow smaller edits
> and inserts - a few tens to a few thousand rows at a time during user
> interaction.
>
> Thanks, Dean


Sorry to say so, but it looks like you would better use globally
temporary tables, and/or other mechanisms like PL/SQL tables and REF
cursors, to avoid persistent storage.
Using persistent storage is a typical SQL server habit of abusing
databases to do things procedurally, where they should have been done
non-procedurally (ie in one statement).
That said, *I* would probably just dump the application and start all
over again.

--
Sybrand Bakker
Senior Oracle DBA