We can use DBMS_METADATA package to get the DDL Scripts for any Objects.
If we need to remove the storage clauses, below can be used.
begin
dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',TRUE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',false);
end;
select dbms_metadata.get_ddl('TABLE','TABLE_NAME','SCHEMA') FROM DUAL;
And all related Indexes script can be obtained by using below query-
select dbms_metadata.get_dependent_ddl('INDEX','TABLE_NAME','SCHEMA') from dual;
Google Search
Thursday, October 25, 2012
Truly Generic Code
Got this wonderful code written by Steven Feuerstein, in his website
http://www.plsqlchallenge.com/pls/apex/f?p=10000:659:417200574319978:CLEAR_HA:NO::P659_QUIZ_ID,P659_COMP_EVENT_ID,P659_QUESTION_ID:3564,2426,&cs=37432F63779BD40D386050B1A90113B43
Dynamic SQL is a programming methodology for generating and running SQL statements at run time. It is useful when writing general-purpose and flexible programs like ad hoc query systems, when writing programs that must run database definition language (DDL) statements, or when you do not know at compilation time the full text of a SQL statement or the number or data types of its input and output variables.
Oracle PL/SQL offers very few opportunities for "reflection": obtaining at runtime information about data structures used by our programs.
CREATE OR REPLACE PROCEDURE gen_bulk_statements (
base_name_in IN VARCHAR2
, query_in IN VARCHAR2
)
IS
c_type_names DBMS_DESCRIBE.varchar2_table;
TYPE col_info_rt IS RECORD (
col_name all_tab_columns.column_name%TYPE
, col_type all_tab_columns.data_type%TYPE
);
TYPE col_info_aat IS TABLE OF col_info_rt
INDEX BY PLS_INTEGER;
l_columns col_info_aat;
PROCEDURE load_type_translators
IS
c_varchar2 CONSTANT PLS_INTEGER := 1;
c_number CONSTANT PLS_INTEGER := 2;
c_binary_integer CONSTANT PLS_INTEGER := 3;
c_long CONSTANT PLS_INTEGER := 8;
c_rowid CONSTANT PLS_INTEGER := 11;
c_date CONSTANT PLS_INTEGER := 12;
c_raw CONSTANT PLS_INTEGER := 23;
c_longraw CONSTANT PLS_INTEGER := 24;
c_opaque CONSTANT PLS_INTEGER := 58;
c_char CONSTANT PLS_INTEGER := 96;
c_refcursor CONSTANT PLS_INTEGER := 102;
c_urowid CONSTANT PLS_INTEGER := 104;
c_mlslabel CONSTANT PLS_INTEGER := 106;
c_clob CONSTANT PLS_INTEGER := 112;
c_blob CONSTANT PLS_INTEGER := 113;
c_bfile CONSTANT PLS_INTEGER := 114;
c_cfile CONSTANT PLS_INTEGER := 115;
c_object_type CONSTANT PLS_INTEGER := 121;
c_nested_table CONSTANT PLS_INTEGER := 122;
c_varray CONSTANT PLS_INTEGER := 123;
c_timestamp CONSTANT PLS_INTEGER := 180;
c_timestamp_tz CONSTANT PLS_INTEGER := 181;
c_interval_ym CONSTANT PLS_INTEGER := 182;
c_interval_ds CONSTANT PLS_INTEGER := 183;
c_timestamp_ltz CONSTANT PLS_INTEGER := 231;
c_record CONSTANT PLS_INTEGER := 250;
c_indexby_table CONSTANT PLS_INTEGER := 251;
c_boolean CONSTANT PLS_INTEGER := 252;
BEGIN
c_type_names ( c_varchar2 ) := 'VARCHAR2';
c_type_names ( c_number ) := 'NUMBER';
c_type_names ( c_binary_integer ) := 'BINARY_INTEGER';
c_type_names ( c_long ) := 'LONG';
c_type_names ( c_rowid ) := 'ROWID';
c_type_names ( c_date ) := 'DATE';
c_type_names ( c_raw ) := 'RAW';
c_type_names ( c_longraw ) := 'LONG RAW';
c_type_names ( c_char ) := 'CHAR';
c_type_names ( c_mlslabel ) := 'MLSLABEL';
c_type_names ( c_record ) := 'RECORD';
c_type_names ( c_indexby_table ) := 'INDEX-BY TABLE';
c_type_names ( c_boolean ) := 'BOOLEAN';
c_type_names ( c_object_type ) := 'OBJECT TYPE';
c_type_names ( c_nested_table ) := 'NESTED TABLE';
c_type_names ( c_varray ) := 'VARRAY';
c_type_names ( c_clob ) := 'CLOB';
c_type_names ( c_blob ) := 'BLOB';
c_type_names ( c_bfile ) := 'BFILE';
END load_type_translators;
FUNCTION columns_for_query ( query_in IN VARCHAR2 )
RETURN col_info_aat
IS
cur PLS_INTEGER := DBMS_SQL.open_cursor;
l_count PLS_INTEGER;
l_from_dbms_sql DBMS_SQL.desc_tab;
retval col_info_aat;
BEGIN
-- Parse the query and then get the columns.
DBMS_SQL.parse ( cur, query_in, DBMS_SQL.native );
DBMS_SQL.describe_columns ( cur, l_count, l_from_dbms_sql );
DBMS_SQL.close_cursor ( cur );
-- Now move the "raw" data to my list of names and types.
-- In particular, convert the integer type to the string description.
FOR colind IN 1 .. l_from_dbms_sql.COUNT
LOOP
retval ( colind ).col_name := l_from_dbms_sql ( colind ).col_name;
retval ( colind ).col_type :=
CASE
WHEN c_type_names ( l_from_dbms_sql ( colind ).col_type ) IN
( 'VARCHAR2', 'CHAR' )
THEN c_type_names ( l_from_dbms_sql ( colind ).col_type )
|| '(32767)'
ELSE c_type_names ( l_from_dbms_sql ( colind ).col_type )
END;
END LOOP;
RETURN retval;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( DBMS_UTILITY.format_error_stack );
DBMS_SQL.close_cursor ( cur );
RAISE;
END columns_for_query;
PROCEDURE initialize ( columns_out OUT col_info_aat )
IS
BEGIN
load_type_translators;
columns_out := columns_for_query ( query_in );
END initialize;
PROCEDURE gen_declarations ( columns_in IN col_info_aat )
IS
BEGIN
DBMS_OUTPUT.put_line ( 'DECLARE TYPE ' || base_name_in
|| '_rt IS RECORD ('
);
FOR colind IN 1 .. columns_in.COUNT
LOOP
DBMS_OUTPUT.put_line ( CASE
WHEN colind = 1
THEN NULL
ELSE ','
END
|| columns_in ( colind ).col_name
|| ' '
|| columns_in ( colind ).col_type
);
END LOOP;
DBMS_OUTPUT.put_line ( ');' );
DBMS_OUTPUT.put_line ( 'TYPE '
|| base_name_in
|| '_ct IS TABLE OF '
|| base_name_in
|| '_rt INDEX BY PLS_INTEGER;'
);
END gen_declarations;
PROCEDURE gen_bulk_query_and_insert ( columns_in IN col_info_aat )
IS
BEGIN
-- First, with collections of records.
DBMS_OUTPUT.put_line ( 'l_dataset ' || base_name_in || '_ct;' );
DBMS_OUTPUT.put_line ( 'CURSOR '
|| base_name_in
|| '_cur IS '
|| query_in
|| ';'
);
DBMS_OUTPUT.put_line ( 'BEGIN' );
DBMS_OUTPUT.put_line ( 'OPEN ' || base_name_in || '_cur;' );
DBMS_OUTPUT.put_line ( 'FETCH '
|| base_name_in
|| '_cur BULK COLLECT INTO l_dataset;'
);
DBMS_OUTPUT.put_line ( 'END;' );
-- Now break out the individual collections, necessary if you are going to
-- use these in a FORALL statement.
DBMS_OUTPUT.put_line ( 'DECLARE' );
FOR colind IN 1 .. columns_in.COUNT
LOOP
DBMS_OUTPUT.put_line ( 'TYPE '
|| columns_in ( colind ).col_name
|| '_aat IS TABLE OF '
|| columns_in ( colind ).col_type
|| ' INDEX BY PLS_INTEGER;'
);
DBMS_OUTPUT.put_line ( 'l_'
|| columns_in ( colind ).col_name
|| ' '
|| columns_in ( colind ).col_name
|| '_aat;'
);
END LOOP;
DBMS_OUTPUT.put_line ( 'CURSOR '
|| base_name_in
|| '_cur IS '
|| query_in
|| ';'
);
DBMS_OUTPUT.put_line ( 'BEGIN' );
DBMS_OUTPUT.put_line ( 'OPEN ' || base_name_in || '_cur;' );
DBMS_OUTPUT.put_line ( 'FETCH ' || base_name_in
|| '_cur BULK COLLECT INTO '
);
FOR colind IN 1 .. columns_in.COUNT
LOOP
-- Display other attributes, as desired.
DBMS_OUTPUT.put_line ( CASE
WHEN colind = 1
THEN NULL
ELSE ','
END
|| 'l_'
|| columns_in ( colind ).col_name
);
END LOOP;
DBMS_OUTPUT.put_line ( ';' );
-- Now the insert statement.
DBMS_OUTPUT.put_line ( 'FORALL indx IN '
|| 'l_'
|| columns_in ( 1 ).col_name
|| '.FIRST .. '
|| 'l_'
|| columns_in ( 1 ).col_name
|| '.LAST'
);
DBMS_OUTPUT.put_line ( 'INSERT INTO ' || base_name_in || ' VALUES (' );
FOR colind IN 1 .. columns_in.COUNT
LOOP
-- Display other attributes, as desired.
DBMS_OUTPUT.put_line ( CASE
WHEN colind = 1
THEN NULL
ELSE ','
END
|| 'l_'
|| columns_in ( colind ).col_name
);
END LOOP;
DBMS_OUTPUT.put_line ( '); END;' );
END gen_bulk_query_and_insert;
BEGIN
initialize ( l_columns );
IF l_columns.COUNT > 0
THEN
gen_declarations ( l_columns );
gen_bulk_query_and_insert ( l_columns );
END IF;
END gen_bulk_statements;
/
http://www.plsqlchallenge.com/pls/apex/f?p=10000:659:417200574319978:CLEAR_HA:NO::P659_QUIZ_ID,P659_COMP_EVENT_ID,P659_QUESTION_ID:3564,2426,&cs=37432F63779BD40D386050B1A90113B43
Dynamic SQL is a programming methodology for generating and running SQL statements at run time. It is useful when writing general-purpose and flexible programs like ad hoc query systems, when writing programs that must run database definition language (DDL) statements, or when you do not know at compilation time the full text of a SQL statement or the number or data types of its input and output variables.
Oracle PL/SQL offers very few opportunities for "reflection": obtaining at runtime information about data structures used by our programs.
CREATE OR REPLACE PROCEDURE gen_bulk_statements (
base_name_in IN VARCHAR2
, query_in IN VARCHAR2
)
IS
c_type_names DBMS_DESCRIBE.varchar2_table;
TYPE col_info_rt IS RECORD (
col_name all_tab_columns.column_name%TYPE
, col_type all_tab_columns.data_type%TYPE
);
TYPE col_info_aat IS TABLE OF col_info_rt
INDEX BY PLS_INTEGER;
l_columns col_info_aat;
PROCEDURE load_type_translators
IS
c_varchar2 CONSTANT PLS_INTEGER := 1;
c_number CONSTANT PLS_INTEGER := 2;
c_binary_integer CONSTANT PLS_INTEGER := 3;
c_long CONSTANT PLS_INTEGER := 8;
c_rowid CONSTANT PLS_INTEGER := 11;
c_date CONSTANT PLS_INTEGER := 12;
c_raw CONSTANT PLS_INTEGER := 23;
c_longraw CONSTANT PLS_INTEGER := 24;
c_opaque CONSTANT PLS_INTEGER := 58;
c_char CONSTANT PLS_INTEGER := 96;
c_refcursor CONSTANT PLS_INTEGER := 102;
c_urowid CONSTANT PLS_INTEGER := 104;
c_mlslabel CONSTANT PLS_INTEGER := 106;
c_clob CONSTANT PLS_INTEGER := 112;
c_blob CONSTANT PLS_INTEGER := 113;
c_bfile CONSTANT PLS_INTEGER := 114;
c_cfile CONSTANT PLS_INTEGER := 115;
c_object_type CONSTANT PLS_INTEGER := 121;
c_nested_table CONSTANT PLS_INTEGER := 122;
c_varray CONSTANT PLS_INTEGER := 123;
c_timestamp CONSTANT PLS_INTEGER := 180;
c_timestamp_tz CONSTANT PLS_INTEGER := 181;
c_interval_ym CONSTANT PLS_INTEGER := 182;
c_interval_ds CONSTANT PLS_INTEGER := 183;
c_timestamp_ltz CONSTANT PLS_INTEGER := 231;
c_record CONSTANT PLS_INTEGER := 250;
c_indexby_table CONSTANT PLS_INTEGER := 251;
c_boolean CONSTANT PLS_INTEGER := 252;
BEGIN
c_type_names ( c_varchar2 ) := 'VARCHAR2';
c_type_names ( c_number ) := 'NUMBER';
c_type_names ( c_binary_integer ) := 'BINARY_INTEGER';
c_type_names ( c_long ) := 'LONG';
c_type_names ( c_rowid ) := 'ROWID';
c_type_names ( c_date ) := 'DATE';
c_type_names ( c_raw ) := 'RAW';
c_type_names ( c_longraw ) := 'LONG RAW';
c_type_names ( c_char ) := 'CHAR';
c_type_names ( c_mlslabel ) := 'MLSLABEL';
c_type_names ( c_record ) := 'RECORD';
c_type_names ( c_indexby_table ) := 'INDEX-BY TABLE';
c_type_names ( c_boolean ) := 'BOOLEAN';
c_type_names ( c_object_type ) := 'OBJECT TYPE';
c_type_names ( c_nested_table ) := 'NESTED TABLE';
c_type_names ( c_varray ) := 'VARRAY';
c_type_names ( c_clob ) := 'CLOB';
c_type_names ( c_blob ) := 'BLOB';
c_type_names ( c_bfile ) := 'BFILE';
END load_type_translators;
FUNCTION columns_for_query ( query_in IN VARCHAR2 )
RETURN col_info_aat
IS
cur PLS_INTEGER := DBMS_SQL.open_cursor;
l_count PLS_INTEGER;
l_from_dbms_sql DBMS_SQL.desc_tab;
retval col_info_aat;
BEGIN
-- Parse the query and then get the columns.
DBMS_SQL.parse ( cur, query_in, DBMS_SQL.native );
DBMS_SQL.describe_columns ( cur, l_count, l_from_dbms_sql );
DBMS_SQL.close_cursor ( cur );
-- Now move the "raw" data to my list of names and types.
-- In particular, convert the integer type to the string description.
FOR colind IN 1 .. l_from_dbms_sql.COUNT
LOOP
retval ( colind ).col_name := l_from_dbms_sql ( colind ).col_name;
retval ( colind ).col_type :=
CASE
WHEN c_type_names ( l_from_dbms_sql ( colind ).col_type ) IN
( 'VARCHAR2', 'CHAR' )
THEN c_type_names ( l_from_dbms_sql ( colind ).col_type )
|| '(32767)'
ELSE c_type_names ( l_from_dbms_sql ( colind ).col_type )
END;
END LOOP;
RETURN retval;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( DBMS_UTILITY.format_error_stack );
DBMS_SQL.close_cursor ( cur );
RAISE;
END columns_for_query;
PROCEDURE initialize ( columns_out OUT col_info_aat )
IS
BEGIN
load_type_translators;
columns_out := columns_for_query ( query_in );
END initialize;
PROCEDURE gen_declarations ( columns_in IN col_info_aat )
IS
BEGIN
DBMS_OUTPUT.put_line ( 'DECLARE TYPE ' || base_name_in
|| '_rt IS RECORD ('
);
FOR colind IN 1 .. columns_in.COUNT
LOOP
DBMS_OUTPUT.put_line ( CASE
WHEN colind = 1
THEN NULL
ELSE ','
END
|| columns_in ( colind ).col_name
|| ' '
|| columns_in ( colind ).col_type
);
END LOOP;
DBMS_OUTPUT.put_line ( ');' );
DBMS_OUTPUT.put_line ( 'TYPE '
|| base_name_in
|| '_ct IS TABLE OF '
|| base_name_in
|| '_rt INDEX BY PLS_INTEGER;'
);
END gen_declarations;
PROCEDURE gen_bulk_query_and_insert ( columns_in IN col_info_aat )
IS
BEGIN
-- First, with collections of records.
DBMS_OUTPUT.put_line ( 'l_dataset ' || base_name_in || '_ct;' );
DBMS_OUTPUT.put_line ( 'CURSOR '
|| base_name_in
|| '_cur IS '
|| query_in
|| ';'
);
DBMS_OUTPUT.put_line ( 'BEGIN' );
DBMS_OUTPUT.put_line ( 'OPEN ' || base_name_in || '_cur;' );
DBMS_OUTPUT.put_line ( 'FETCH '
|| base_name_in
|| '_cur BULK COLLECT INTO l_dataset;'
);
DBMS_OUTPUT.put_line ( 'END;' );
-- Now break out the individual collections, necessary if you are going to
-- use these in a FORALL statement.
DBMS_OUTPUT.put_line ( 'DECLARE' );
FOR colind IN 1 .. columns_in.COUNT
LOOP
DBMS_OUTPUT.put_line ( 'TYPE '
|| columns_in ( colind ).col_name
|| '_aat IS TABLE OF '
|| columns_in ( colind ).col_type
|| ' INDEX BY PLS_INTEGER;'
);
DBMS_OUTPUT.put_line ( 'l_'
|| columns_in ( colind ).col_name
|| ' '
|| columns_in ( colind ).col_name
|| '_aat;'
);
END LOOP;
DBMS_OUTPUT.put_line ( 'CURSOR '
|| base_name_in
|| '_cur IS '
|| query_in
|| ';'
);
DBMS_OUTPUT.put_line ( 'BEGIN' );
DBMS_OUTPUT.put_line ( 'OPEN ' || base_name_in || '_cur;' );
DBMS_OUTPUT.put_line ( 'FETCH ' || base_name_in
|| '_cur BULK COLLECT INTO '
);
FOR colind IN 1 .. columns_in.COUNT
LOOP
-- Display other attributes, as desired.
DBMS_OUTPUT.put_line ( CASE
WHEN colind = 1
THEN NULL
ELSE ','
END
|| 'l_'
|| columns_in ( colind ).col_name
);
END LOOP;
DBMS_OUTPUT.put_line ( ';' );
-- Now the insert statement.
DBMS_OUTPUT.put_line ( 'FORALL indx IN '
|| 'l_'
|| columns_in ( 1 ).col_name
|| '.FIRST .. '
|| 'l_'
|| columns_in ( 1 ).col_name
|| '.LAST'
);
DBMS_OUTPUT.put_line ( 'INSERT INTO ' || base_name_in || ' VALUES (' );
FOR colind IN 1 .. columns_in.COUNT
LOOP
-- Display other attributes, as desired.
DBMS_OUTPUT.put_line ( CASE
WHEN colind = 1
THEN NULL
ELSE ','
END
|| 'l_'
|| columns_in ( colind ).col_name
);
END LOOP;
DBMS_OUTPUT.put_line ( '); END;' );
END gen_bulk_query_and_insert;
BEGIN
initialize ( l_columns );
IF l_columns.COUNT > 0
THEN
gen_declarations ( l_columns );
gen_bulk_query_and_insert ( l_columns );
END IF;
END gen_bulk_statements;
/
Wednesday, September 12, 2012
Rebuild Local Indexes-Query
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;
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;
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;
Monday, August 6, 2012
Query from a Specific partition
To query from a specific partition, we used provide partition name.
Like below:
select * from Table_1 partition (Q1_2012);
But it is hard to remember partition names and also we cannot use it inside a procedure/functions, since it is difficult to construct partition names at run time.
--> To facilitate the access to specific partition, Oracle Database 11g offers a new syntax for partitioning SQLs:
select * from Table_1 partition for (to_date('02-Feb-2012','dd-mon-yyyy'));
This method allows us to query from a specific partition without explicitly stating the partition name.
--> This can be useful while loading the data too.One can dynamically pass the date and oracle finds the partition it belongs to.
insert into SALES /*+ APPEND*/
partition for (to_date(''||vstart_id||'', ''''YYYYMM''''))
select PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD
from SRC_SALES
where time_id between to_date(:vstart_id, ''''YYYYMM'''') and last_day(to_date(:vstart_id, ''''YYYYMM'''')) '';
--> While Dropping Partitions
alter table SALES drop partition for (to_date('02-feb-2012','DD-MON-YYYY'));
Like below:
select * from Table_1 partition (Q1_2012);
But it is hard to remember partition names and also we cannot use it inside a procedure/functions, since it is difficult to construct partition names at run time.
--> To facilitate the access to specific partition, Oracle Database 11g offers a new syntax for partitioning SQLs:
select * from Table_1 partition for (to_date('02-Feb-2012','dd-mon-yyyy'));
This method allows us to query from a specific partition without explicitly stating the partition name.
--> This can be useful while loading the data too.One can dynamically pass the date and oracle finds the partition it belongs to.
insert into SALES /*+ APPEND*/
partition for (to_date(''||vstart_id||'', ''''YYYYMM''''))
select PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD
from SRC_SALES
where time_id between to_date(:vstart_id, ''''YYYYMM'''') and last_day(to_date(:vstart_id, ''''YYYYMM'''')) '';
--> While Dropping Partitions
alter table SALES drop partition for (to_date('02-feb-2012','DD-MON-YYYY'));
Thursday, July 19, 2012
Analytic Functions
If you have to learn Analytic functions in Oracle, then the below link is the best place to start with.
I learnt it with this, the explanation is simple and through concepts makes this blog to come up first when googled.
http://orafaq.com/node/55
I learnt it with this, the explanation is simple and through concepts makes this blog to come up first when googled.
http://orafaq.com/node/55
Thursday, June 7, 2012
Update JOIN
It’s a common requirement that we will have to update a table using values from another table.
Something like
update t set value =
(
select value from s where t.key = s.key_to_t and s.fld_name='A'
)
Using above techinque we must remember that all the rows in T which did not have a match in S, will be updated to NULL. Which is not desired.
Hence we need to use the below technique:
update t set value =
(
select value from s where t.key = s.key_to_t and s.fld_name='A'
)
where exists (select 1 from s s1 where t.key = s1.key_to_t and s1.fld_name='A');
By this we make sure that only the rows which match the criteria is updated and remaining are not touched.
But if the table is huge , the where exists clause may slowed down the query.
We can also use another method:
create table t ( key int, value int );
create table s ( key_to_t int, value int, fld_name varchar2(1), unique(value,fld_name) );
insert into t values ( 1, null );
insert into t values ( 2, 111 );
insert into s values ( 1, 100, 'A' );
insert into s values ( 1, 100, 'B' );
update (select t.value,s.value new_value
from t, s
where t.key = s.key_to_t
and s.fld_name = 'A')
set value = new_value;
For the above technique we must make sure we have a Primary Key column or Unique Index on the columns.
or
merge into t
using (select * from s where fld_name = 'A') s
on (t.key = s.key_to_t)
when matched then update set value = s.value;
Something like
update t set value =
(
select value from s where t.key = s.key_to_t and s.fld_name='A'
)
Using above techinque we must remember that all the rows in T which did not have a match in S, will be updated to NULL. Which is not desired.
Hence we need to use the below technique:
update t set value =
(
select value from s where t.key = s.key_to_t and s.fld_name='A'
)
where exists (select 1 from s s1 where t.key = s1.key_to_t and s1.fld_name='A');
By this we make sure that only the rows which match the criteria is updated and remaining are not touched.
But if the table is huge , the where exists clause may slowed down the query.
We can also use another method:
create table t ( key int, value int );
create table s ( key_to_t int, value int, fld_name varchar2(1), unique(value,fld_name) );
insert into t values ( 1, null );
insert into t values ( 2, 111 );
insert into s values ( 1, 100, 'A' );
insert into s values ( 1, 100, 'B' );
update (select t.value,s.value new_value
from t, s
where t.key = s.key_to_t
and s.fld_name = 'A')
set value = new_value;
For the above technique we must make sure we have a Primary Key column or Unique Index on the columns.
or
merge into t
using (select * from s where fld_name = 'A') s
on (t.key = s.key_to_t)
when matched then update set value = s.value;
Subscribe to:
Posts (Atom)