apedopax.blogg.se - Oracle 11g tutorial for beginners

Oracle 11g tutorial for beginners update#
Oracle 11g tutorial for beginners free#

It is designed for use with direct path loads only. You will get no compression benefits from conventional path inserts, updates or deletes.

Basic compression is not designed for OLTP operations.

Oracle 11g tutorial for beginners free#

Basic compression is a free option with the Enterprise Edition version of the database.

Some things to consider before using this functionality. A different data set may not have produced such a dramatic result. In this case we cheated and used the same text data, username and created date for each row, making the data highly compressible in the block. Remember, the level of compression will vary depending on the data. What we see here is the compressed partition is almost an order of magnitude smaller for the same number of rows based on the number of blocks used to hold the data, so if storage size is an issue, the overhead in CPU and elapsed time for direct path inserts may be worth it. TABLE_NAME PARTITION_NAME COMPRESS NUM_ROWS BLOCKS EMPTY_BLOCKS Once the statistics are gathered we can check the compression using the _TAB_PARTITIONS views.ĮXEC DBMS_STATS.gather_schema_stats(USER, cascade => TRUE)

V_date test_tab.created_date%TYPE := TO_DATE('', 'DD/MM/YYYY') įrom repeated runs we see direct path inserts into into a compressed table or partition requires more CPU and takes longer compared to an uncompressed table or partition. TEST_TAB : Direct path insert into compressed partition. 'A very repetitive, and therefore very compressible column value', INSERT /*+ APPEND */ INTO test_tab (id, description, created_date, created_by) L_start_cpu := DBMS_UTILITY.get_cpu_time V_date test_tab.created_date%TYPE := SYSDATE TEST_TAB : Direct path insert into non-compressed partition. We will do this separately and capture the elapsed time and CPU time used by each operations. Insert some rows into each partition using a direct path insert. PARTITION test_tab_q1 VALUES LESS THAN (TO_DATE('', 'DD/MM/YYYY')) COMPRESS,Īs mentioned previously, we can alter the compression status of the partition and compress its contents using the ALTER TABLE command.ĪLTER TABLE test_tab MOVE PARTITION test_tab_q2 COMPRESS Ĭreate the following table with one compressed partition and one non-compressed partititon. In the following example the default NOCOMPRESS option is set explicitly at table level (this is not necessary), with the first partition using the COMPRESS keyword to override the default. The compression status can be specified for all partitions explicitly, or a table default can be used, with only exceptions to this default specified at partition level. It is possible to control the compression status of individual partitions in a partitioned table. SELECT partition_name, compression, compress_for The _TAB_PARTITIONS views display the compression status of the individual partitions. PARTITION test_tab_q2 VALUES LESS THAN (MAXVALUE) PARTITION test_tab_q1 VALUES LESS THAN (TO_DATE('', 'DD/MM/YYYY')), Using the COMPRESS keyword at the table level makes this the default for all partitions. Make sure you have enough storage to cope with this.Ī similar action could be performed for an individual partition of a partitioned table.ĪLTER TABLE test_tab MOVE PARTITION test_tab_q2 COMPRESS Partitioned Tables Remember, when you perform a move operation you will have two copies of the table for a period of time.

If you want to compress existing data, you must perform a move operation, so the data gets compressed during the copy. This doesn't affect the compression of existing data, but will affect the compression of new data that is loaded by direct path loads. The compression status of an existing table can be changed using the ALTER TABLE statement.

If you are using a version prior to 11g, reference to the COMPRESS_FOR column will cause the queries in this article to fail, so just remove them. The introduction of alternative modes of compression in Oracle 11g ( described here) came with an additional column called COMPRESS_FOR to indicate the type of compression. The compression status of the table can be displayed using COMPRESSION column in the _TABLES views. The default compression status is NOCOMPRESS. Table compression is enabled by adding the COMPRESS keyword to the end of the table definition, as shown below.

Table Compression Enhancements in Oracle Database 11g Release 1.

Oracle 11g tutorial for beginners update#

It is designed for compressing static data, since it only works for direct path inserts, not single row insert, update and delete operations typical in OLTP systems. The basic compression described here is a free feature in the Enterprise Edition of the database. Oracle 9i onward allows whole tables or individual table partitions to be compressed to reduce disk space requirements.

Home » Articles » 9i » Here Compressed Tables