oracle nologging 及 alter table move

NOLOGGING的操作：

（1）CREATE TABLE … NOLOGGING AS SELECT 语句

（2）INSERT /*+APPEND*/ INTO <表> NOLOGGING SELECT 语句

（3）INSERT /*+PARALLEL(<表>,)*/ INTO <表> NOLOGGING SELECT 语句

（4）ALTER DATABASE [NO] FORCE LOGGING

（5）SQL*LOADER的Direct方法

使用nologging的好处:

• Space is saved in the redo log files.

• The time it takes to create the table is decreased.

• Performance improves for parallel creation of large tables.

Subsequent DML statements (UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING attribute of the table and generate redo.

If you cannot afford to lose the table after you have created it (for example, you will no longer have access to the data used to create the table) you should take a backup immediately after the table is created. In some situations, such as for tables that are created for temporary use, this precaution may not be necessary.

一般来说, nologging对big table的作用更大.

Table Compression

As your database grows in size to gigabytes or terabytes and beyond, consider using table compression. Table compression saves disk space and reduces memory use in the buffer cache. Table compression can also speed up query execution during reads. There is, however, a cost in CPU overhead for data loading and DML. Table compression is completely transparent to applications. It is especially useful in online analytical processing (OLAP) systems, where there are lengthy read-only operations, but can also be used in online transaction processing (OLTP) systems.

You specify table compression with the COMPRESS clause of the CREATE TABLE statement. You can enable compression for an existing table by using this clause in an ALTER TABLE statement. In this case, the only data that is compressed is the data inserted or updated after compression is enabled. Similarly, you can disable table compression for an existing compressed table with the ALTER TABLE...NOCOMPRESS statement. In this case, all data the was already compressed remains compressed, and new data is inserted uncompressed.

You can enable compression for all table operations or you can enable it for direct-path inserts only. When compression is enabled for all operations, compression occurs during all DML statements and when data is inserted with a bulk (direct-path) insert operation. To enable compression for conventional DML, you must set the COMPATIBLE initialization parameter to 11.1.0 or higher.

To enable compression for all operations you must use the COMPRESS FOR ALL OPERATIONS clause. To enable compression for direct-path inserts only, you use the COMPRESS FOR DIRECT_LOAD OPERATIONS clause. The keyword COMPRESS by itself is the same as the clause COMPRESS FOR DIRECT_LOAD OPERATIONS, and invokes the same compression behavior as previous database releases.

Adding and Dropping Columns in Compressed Tables

When you enable compression for all operations on a table, you can add and drop table columns. If you enable compression for direct-path inserts only, you cannot drop columns, and you can add columns only if you do not specify default values.

Examples

The following example enables compression for all operations on the table transaction, which is used in an OLTP application:

CREATE TABLE transaction ( ... ) COMPRESS FOR ALL OPERATIONS;

The next two examples enable compression for direct-path insert only on the sales_history table, which is a fact table in a data warehouse:

CREATE TABLE sales_history ( ... ) COMPRESS FOR DIRECT_LOAD OPERATIONS; CREATE TABLE sales_history ( ... ) COMPRESS;

Compression and Partitioned Tables

You can enable or disable compression at the partition level. You can therefore have a table with both compressed and uncompressed partitions. If the compression settings for a table and one of its partitions disagree, the partition setting has precedence for the partition. In the following example, all partitions except the northeast partition are compressed.

CREATE TABLE sales (saleskey number, quarter number, product number, salesperson number, amount number(12, 2), region varchar2(10)) COMPRESS PARTITION BY LIST (region) (PARTITION northwest VALUES ('NORTHWEST'), PARTITION southwest VALUES ('SOUTHWEST'), PARTITION northeast VALUES ('NORTHEAST') NOCOMPRESS, PARTITION southeast VALUES ('SOUTHEAST'), PARTITION western VALUES ('WESTERN'));

Determining If a Table Is Compressed

In the *_TABLES data dictionary views, compressed tables have ENABLED in the COMPRESSION column. For partitioned tables, this column is null, and the COMPRESSION column of the *_TAB_PARTITIONS data dictionary view indicates the partitions that are compressed. In addition, the COMPRESS_FOR column indicates whether the table is compressed FOR ALL OPERATIONS or for DIRECT LOAD ONLY.

SQL> SELECT table_name, compression, compress_for FROM user_tables; TABLE_NAME COMPRESS COMPRESS_FOR ---------------- -------- ------------------ T1 DISABLED T2 ENABLED DIRECT LOAD ONLY T3 ENABLED FOR ALL OPERATIONS

Alter table的操作：

The ALTER TABLE...MOVE statement enables you to relocate data of a non-partitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota. This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE. You can also use the ALTER TABLE...MOVE statement with a COMPRESS clause to store the new segment using table compression.

One important reason to move a table to a new tablespace (with a new datafile) is to eliminate the possibility that old versions of column data—versions left on now unused portions of the disk due to segment shrink, reorganization, or previous table moves—could be viewed by bypassing the access controls of the database (for example with an operating system utility). This is especially important with columns that you intend to modify by adding transparent data encryption.

Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.

If the table includes LOB column(s), this statement can be used to move the table along with LOB data and LOB index segments (associated with this table) which the user explicitly specifies. If not specified, the default is to not move the LOB data and LOB index segments.

alter table xx move compress for all operations;

alter table xx move tablespace xx;

alter table xx move nocompress;

如果表中有数据,则需要rebuild index, 否则则不必.

alter table move compress的技术本质是通过在新的表空间或当前表空间中分配新的extents来存放压缩后的数据而实现的。而原来分配给该表的这些extents 只释放供重用但不会被收缩(shrik high-water-mark).从这个角度来说,如果我们需要对一个大表做alter table move compress的动作的话，那么你就必须要确保目标表空间上存在额外的空间，从而保证这个动作的顺利执行.