| |
 |
|
ss
Oracle Tips by Burleson |
Two Alternatives for Using
CTAS
It is always recommended that you resequence the table rows when
performing a table reorganization with CTAS because of the huge I/O
benefits. You can use the CTAS statement in one of two ways. Each of
these achieves the same result, but they do it in very different
ways:
* Use CTAS in conjunction with the order by clause.
* Use CTAS in conjunction with a “hint” that identifies the index to
use.
The approach you choose depends on the size of the table involved,
the overall processing power of your environment, and how quickly
you must complete the reorganization.
The details of each CTAS approach are discussed more fully below,
but in either case, when you create the new table, you can speed the
process by using the Oracle nologging option (this was called
unrecoverable in Oracle7). This skips the added overhead of writing
to the redo log file. Of course, you cannot use the redo logs to
roll forward through a nologging operation, and most DBAs take a
full backup prior to using CTAS with nologging. Let’s examine the
two methods and see their respective differences.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
