My question is related to Oracle DB performance and ideally finding the better method of the two paths when creating a backup table
- Create a new table as a copy of an existing
- Merging data to an existing (empty table - The two tables are identical)
CodePudding user response：
If it is a small table, it doesn't matter - both will be fast. Though, CTAS (create table as select) is probably the most usual way to create a "copy" of existing table.
If a table is very large, I don't know how it (CTAS) compares to merge; you should test it.
However, a backup table? Are you sure that's the right way to backup a table? I'd rather think of a proper (RMAN) database backup, or - at least - export (using Export Data Pump) into a file that resides in a filesystem (and can be stored elsewhere, e.g. onto an external hard disk drive, DVD and similar (does anyone use tapes any more? We do)).
Because, if database breaks down, along with your "original" table, that "backup" table will be lost as well.