We rebuild the Local indexes using the command
ALTER INDEX IDX_NAME rebuild partition partition_name;
But if we have subpartitions , we need to provide(or rebuild) indexes by providing subpartition name.
Below block can be used to dynamically query with or w/o subpartition.
for PART_IDXNAME in (SELECT PART.INDEX_NAME,NVL(SUBPART.SUBPARTITION_NAME,PART.PARTITION_NAME) PARTITION_NAME, NVL2(SUBPART.SUBPARTITION_NAME,'SUBPARTITION','PARTITION') PTYPE
FROM user_ind_partitions PART, user_ind_subpartitions SUBPART
WHERE
PART.INDEX_NAME = SUBPART.INDEX_NAME(+)
AND PART.index_name IN (SELECT index_name
FROM USER_INDEXES
WHERE table_name = p_table_name
AND PARTITIONED='YES'))
loop
execute immediate ' ALTER INDEX ' || PART_IDXNAME.INDEX_NAME || ' REBUILD ' || PART_IDXNAME.PTYPE || ' ' || PART_IDXNAME.PARTITION_NAME || ' nologging ' ;
end loop;
Wednesday, September 12, 2012
Monday, September 10, 2012
DB Compression: Frequently Asked Questions
DB Compression: Frequently Asked Questions
1. What is DB Compression?
2. What are different compression types?
3. How does Compression work?
4. What happens when we compress on a partition which is already compressed?
5. If we compress a table, Will Index get compressed too?
6. Is it compulsory to compress Indexes too?
7. Is it compulsory to rebuild indexes after Compression?
8. Is it compulsory to gather table stats after compression?
9. Even after compression, table size doesn’t reduce much?
10. What is post load compression?
11. What happens if process fails in middle of Compression?
12. Can we decompress the data, if so How?
13. When compression is happening, is there any downtime, Will the table be accessible?
14. Is it ok if there are Global Indexes?
15. How LOB Columns are handled?
16. How to find out whether a table/Partition is compressed or not?
17. What are performance advantages/disadvantages due to compression?
18. To use Compression option do we need to purchase any extra license?
19. Would it be possible to add or remove the columns from a compressed table?
1. What is DB Compression?
Database compression is a disk space saving feature of Oracle by reducing the size of relational tables. In addition to saving storage space, compression can result in increased I/O performance and reduced memory use in the buffer cache.
2. What are different compression types?
The compression clause can be specified at the tablespace, table or partition level with the following options:
NOCOMPRESS - The table or partition is not compressed. This is the default action when no compression clause is specified.
COMPRESS - This option is considered suitable for data warehouse systems. Compression is enabled on the table or partition during direct-path inserts only.
COMPRESS FOR DIRECT_LOAD OPERATIONS - This option has the same affect as the simple COMPRESS keyword.
COMPRESS FOR ALL OPERATIONS - This option is considered suitable for OLTP systems. As the name implies, this option enables compression for all operations, including regular DML statements. This option requires the COMPATIBLE initialization parameter to be set to 11.1.0 or higher. In 11gR2 this option has been renamed to COMPRESS FOR OLTP and the original name has been deprecated.
3. How does Compression work?
Compression Algorithm eliminates duplicate values within a database block, across rows and multiple columns, by creating symbol table metadata.
Each duplicate value is replaced by a short reference to the symbol table.
Compressed structure is self-contained in the block, no additional IO when accessing compressed data.
4. What happens when we compress on a partition which is already compressed?
The records which already got compressed will not have any changes.
If there are new records inserted which is not compressed those will get compressed.
5. If we compress a table, Will Index get compressed too?
No, Indexes will not get compressed; it has to be compressed separately.
6. Is it compulsory to compress Indexes too?
It is not compulsory to Compress Indexes when we compress a table.
Generally Indexes are created on columns which has unique records , hence compression on the indexes will save very less or no disk space. However we do have option for compresing indexes separately.
7. Is it compulsory to rebuild indexes after Compression?
YES, It is required to re-build indexes after compression.
All the indexes will be in unusable state after the table is compressed.
“MOVE” command might relocate table rows; hence it is compulsory to rebuild indexes.
8. Is it compulsory to Gather table stats after compression?
Table statistics may need to be gathered again after post-load style compression. When many partitions are compressed at one time, or when non-partitioned tables are compressed for the first time table statistics may change significantly due to change in the number of rows stored in DB Blocks, causing Oracle Optimizer to possibly bypass Indexes.
9. Even after compression, table size doesn’t reduce much?
Compresses works by eliminating duplicate values in a database block. Compressed data is stored in a database block itself.
Duplicate values in all the rows and columns in a block are stored once at the beginning of the block in what is called a symbol table for that block. All occurrences of such values are replaced with a short reference to the symbol table.
So if the table doesn’t contain any duplicate values, then compression doesn’t reduce space.
Even if a table contains duplicate values, it is important those are in the same block or else there will not be much space reduction.
10. What is post load compression ?
Post Load Compression is a compression technique where data is not compressed on the fly while being inserted.
Instead, we need to manually execute commands to Compress at regular intervals. Below command is used to compress existing data in the table.
ALTER TABLE t1 MOVE PARTITION p1 COMPRESS;
11. What happens if process fails in middle of Compression?
There is chance of data block getting corrupted.
12. Can we decompress the data, if so How?
Yes, we can uncompress/decompress the compressed data using the below command.
ALTER TABLE tablename MOVE NOCOMPRESS NOLOGGING PARALLEL 4;
Again, it is compulsory to rebuild the Indexes after uncompressing the data.
13. When compression is happening, is there any downtime, Will the table be accessible?
While compressing a table or partition, concurrent write must be avoided. Concurrent read on indexed tables may fail or bypass index if we are querying from the particular partition, resulting in slower performance.
Indexes become UNUSABLE and must be rebuilt after post-load compression.
Compression utilities must be scheduled carefully to run during off-peak times.
14. Is it ok if there are Global Indexes?
A partitioned table which has GLOBAL indexes can be compressed.
15. How LOB Columns are handled?
Compression on LOB columns can be done only in Oracle 11G and above.
LOB columns must be declared as Secure File LOBs to use compression.
16. How to find out whether a table/Partition is compressed or not?
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 views indicates the partitions that are compressed. In addition, the COMPRESS_FOR column indicates the compression method in use for the table or partition.
SELECT table_name,partition_name, compression, compress_for FROM user_tab_partitions where table_name=’TABLE_NAME';
17. What are performance advantages/disadvantages due to compression?
Advantages:
DISK STORAGE COST SAVINGS:
Disk storage cost savings are the most tangible benefit of compression for large databases (savings propagate to DR/Backup systems as well).
Expect to reduce storage space consumption by 50%-80%.
DISK I/O REDUCTION:
Reduced I/O, can improve query performance for I/O bound systems.
MORE EFFICIENT MEMORY UTILIZATON:
Compression can result in more efficient memory utilization as more data fits in the buffer cache.
NETWORK TRAFFIC REDUCTION:
Data Guard Redo Transport Services used to transfer redo can be configured to transmit in a compressed format to reduce network bandwidth consumption.
Systems with NAS based disk storage will benefit from reduced network traffic.
SQL QUERY PERFORMANCE is SAME or BETTER:
SQL Query performance does not degrade when tables are compressed.
Faster full scan/range scan operations.
Limitations:
While compressing a table or partition, concurrent write must be avoided. Concurrent read on indexed tables may fail or bypass index resulting in slower performance.
Indexes become UNUSABLE and must be rebuilt after post-load compression.
May not be suitable for tables which are not partitioned and are loaded via regular INSERT. Incrementally recompressing the table will require the entire table segment to be moved.
18. To use Compression option do we need to purchase any extra license?
Oracle Advanced Compression --> extra cost option on top of enterprise edition.
Direct-Load Table Compression --> No extra cost option, but requires enterprise edition.
19. Would it be possible to add or remove the columns from a compressed table?
It is possible to ADD a column to already compressed table.
But it is not possible to DROP a column.
As an alternative we can use
alter table t drop unused columns;
1. What is DB Compression?
2. What are different compression types?
3. How does Compression work?
4. What happens when we compress on a partition which is already compressed?
5. If we compress a table, Will Index get compressed too?
6. Is it compulsory to compress Indexes too?
7. Is it compulsory to rebuild indexes after Compression?
8. Is it compulsory to gather table stats after compression?
9. Even after compression, table size doesn’t reduce much?
10. What is post load compression?
11. What happens if process fails in middle of Compression?
12. Can we decompress the data, if so How?
13. When compression is happening, is there any downtime, Will the table be accessible?
14. Is it ok if there are Global Indexes?
15. How LOB Columns are handled?
16. How to find out whether a table/Partition is compressed or not?
17. What are performance advantages/disadvantages due to compression?
18. To use Compression option do we need to purchase any extra license?
19. Would it be possible to add or remove the columns from a compressed table?
1. What is DB Compression?
Database compression is a disk space saving feature of Oracle by reducing the size of relational tables. In addition to saving storage space, compression can result in increased I/O performance and reduced memory use in the buffer cache.
2. What are different compression types?
The compression clause can be specified at the tablespace, table or partition level with the following options:
NOCOMPRESS - The table or partition is not compressed. This is the default action when no compression clause is specified.
COMPRESS - This option is considered suitable for data warehouse systems. Compression is enabled on the table or partition during direct-path inserts only.
COMPRESS FOR DIRECT_LOAD OPERATIONS - This option has the same affect as the simple COMPRESS keyword.
COMPRESS FOR ALL OPERATIONS - This option is considered suitable for OLTP systems. As the name implies, this option enables compression for all operations, including regular DML statements. This option requires the COMPATIBLE initialization parameter to be set to 11.1.0 or higher. In 11gR2 this option has been renamed to COMPRESS FOR OLTP and the original name has been deprecated.
3. How does Compression work?
Compression Algorithm eliminates duplicate values within a database block, across rows and multiple columns, by creating symbol table metadata.
Each duplicate value is replaced by a short reference to the symbol table.
Compressed structure is self-contained in the block, no additional IO when accessing compressed data.
4. What happens when we compress on a partition which is already compressed?
The records which already got compressed will not have any changes.
If there are new records inserted which is not compressed those will get compressed.
5. If we compress a table, Will Index get compressed too?
No, Indexes will not get compressed; it has to be compressed separately.
6. Is it compulsory to compress Indexes too?
It is not compulsory to Compress Indexes when we compress a table.
Generally Indexes are created on columns which has unique records , hence compression on the indexes will save very less or no disk space. However we do have option for compresing indexes separately.
7. Is it compulsory to rebuild indexes after Compression?
YES, It is required to re-build indexes after compression.
All the indexes will be in unusable state after the table is compressed.
“MOVE” command might relocate table rows; hence it is compulsory to rebuild indexes.
8. Is it compulsory to Gather table stats after compression?
Table statistics may need to be gathered again after post-load style compression. When many partitions are compressed at one time, or when non-partitioned tables are compressed for the first time table statistics may change significantly due to change in the number of rows stored in DB Blocks, causing Oracle Optimizer to possibly bypass Indexes.
9. Even after compression, table size doesn’t reduce much?
Compresses works by eliminating duplicate values in a database block. Compressed data is stored in a database block itself.
Duplicate values in all the rows and columns in a block are stored once at the beginning of the block in what is called a symbol table for that block. All occurrences of such values are replaced with a short reference to the symbol table.
So if the table doesn’t contain any duplicate values, then compression doesn’t reduce space.
Even if a table contains duplicate values, it is important those are in the same block or else there will not be much space reduction.
10. What is post load compression ?
Post Load Compression is a compression technique where data is not compressed on the fly while being inserted.
Instead, we need to manually execute commands to Compress at regular intervals. Below command is used to compress existing data in the table.
ALTER TABLE t1 MOVE PARTITION p1 COMPRESS;
11. What happens if process fails in middle of Compression?
There is chance of data block getting corrupted.
12. Can we decompress the data, if so How?
Yes, we can uncompress/decompress the compressed data using the below command.
ALTER TABLE tablename MOVE NOCOMPRESS NOLOGGING PARALLEL 4;
Again, it is compulsory to rebuild the Indexes after uncompressing the data.
13. When compression is happening, is there any downtime, Will the table be accessible?
While compressing a table or partition, concurrent write must be avoided. Concurrent read on indexed tables may fail or bypass index if we are querying from the particular partition, resulting in slower performance.
Indexes become UNUSABLE and must be rebuilt after post-load compression.
Compression utilities must be scheduled carefully to run during off-peak times.
14. Is it ok if there are Global Indexes?
A partitioned table which has GLOBAL indexes can be compressed.
15. How LOB Columns are handled?
Compression on LOB columns can be done only in Oracle 11G and above.
LOB columns must be declared as Secure File LOBs to use compression.
16. How to find out whether a table/Partition is compressed or not?
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 views indicates the partitions that are compressed. In addition, the COMPRESS_FOR column indicates the compression method in use for the table or partition.
SELECT table_name,partition_name, compression, compress_for FROM user_tab_partitions where table_name=’TABLE_NAME';
17. What are performance advantages/disadvantages due to compression?
Advantages:
DISK STORAGE COST SAVINGS:
Disk storage cost savings are the most tangible benefit of compression for large databases (savings propagate to DR/Backup systems as well).
Expect to reduce storage space consumption by 50%-80%.
DISK I/O REDUCTION:
Reduced I/O, can improve query performance for I/O bound systems.
MORE EFFICIENT MEMORY UTILIZATON:
Compression can result in more efficient memory utilization as more data fits in the buffer cache.
NETWORK TRAFFIC REDUCTION:
Data Guard Redo Transport Services used to transfer redo can be configured to transmit in a compressed format to reduce network bandwidth consumption.
Systems with NAS based disk storage will benefit from reduced network traffic.
SQL QUERY PERFORMANCE is SAME or BETTER:
SQL Query performance does not degrade when tables are compressed.
Faster full scan/range scan operations.
Limitations:
While compressing a table or partition, concurrent write must be avoided. Concurrent read on indexed tables may fail or bypass index resulting in slower performance.
Indexes become UNUSABLE and must be rebuilt after post-load compression.
May not be suitable for tables which are not partitioned and are loaded via regular INSERT. Incrementally recompressing the table will require the entire table segment to be moved.
18. To use Compression option do we need to purchase any extra license?
Oracle Advanced Compression --> extra cost option on top of enterprise edition.
Direct-Load Table Compression --> No extra cost option, but requires enterprise edition.
19. Would it be possible to add or remove the columns from a compressed table?
It is possible to ADD a column to already compressed table.
But it is not possible to DROP a column.
As an alternative we can use
alter table t drop unused columns;
Subscribe to:
Posts (Atom)