Note in Font

Creation Code 3/9 in Font Note

You can use table compression in both an OLTP as well as a data warehousing environment. You can get the best results by compressing all read-only or historical data, which rarely changes.

There are a couple of variants of the COMPRESS clause: you must use the COMPRESS FOR ALL OPERATIONS clause to enable compression for all operations. In order to enable it only for direct-path inserts (bulk insert operations), specify the COMPRESS FOR DIRECT_LOAD OPERATIONS clause. The clause COMPRESS by itself is equivalent to the COMPRESS FOR DIRECT_LOAD OPERATIONS clause. Note that if you enable compression only for direct-path inserts, you can t drop any columns later on. You can add columns only if you don t specify default values for the columns. These restrictions don t apply when you enable compression for all operations on a table.

The following example shows how to enable compression for all operations on a table, which is what you d want to do in an OLTP setting: SQL> CREATE TABLE test name varchar2(20) address varchar2(50)) COMPRESS FOR ALL OPERATIONS; You can use either of the following statements to enable compression for direct-path inserts only on a table: SQL> CREATE TABLE test name varchar2(20)

address varchar2(50)) COMPRESS; SQL> CREATE TABLE test name varchar2(20) address varchar2(50)) COMPRESS FOR DIRECT_LOAD OPERATIONS As you can see from the examples, the COMPRESS FOR ALL OPERATIONS clause is what you must use for compressing OLTP tables. You can use the following query to find out which tables are compressed in your database: SQL> SELECT table_name, compression, compress_for FROM dba_tables; TABLE_NAME ---------------EMP DEPT SQL> COMPRESS -------ENABLED ENABLED COMPRESS_FOR ------------------DIRECT LOAD ONLY FOR ALL OPERATIONS

The COMPRESS_FOR column shows the type of table compression (all operations or direct load only).

You can drop a table by using the DROP TABLE table_name command. In order to be able to drop a table, the user must own the table (it must be in your schema), or the user must have the DROP ANY TABLE privilege. When you use the DROP TABLE command, however, the table doesn t go away immediately Oracle simply renames the table and stores it in the Recycle Bin, which is in reality simply a data dictionary table. Thus, you can bring back a table you dropped accidentally by using the following command: SQL> FLASHBACK TABLE emp TO BEFORE DROP; The ability to bring back a dropped table is known as the Flashback Drop feature. 16 explains this feature in detail and provides information about managing the Recycle Bin. If you are sure that you ll never need the table, you can get rid of it permanently by using the PURGE option with your DROP TABLE command, as shown here: SQL> DROP TABLE emp PURGE; When you use the preceding PURGE command, the emp table is dropped immediately, and you can t get it back! Again, you ll see a lot more about this command in 16.

The DROP TABLE table_name PURGE command is equivalent to the old DROP TABLE table_name command in pre Oracle Database 10g release databases.

When you drop a table, all indexes you had defined on the table will be dropped as well. If the table you want to drop contains any primary or unique keys referenced by foreign keys of other tables, you must include the CASCADE clause in the DROP TABLE statement, in order to drop those constraints as well: SQL> DROP TABLE emp CASCADE CONSTRAINTS;

The simple tables you saw in the previous sections satisfy most of the data needs of an application, but these aren t the only kind of tables Oracle allows you to create. You can create several kinds of specialized tables, such as temporary tables, external tables, and index-organized tables. In the following sections, you ll examine these important types of tables.