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;

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;

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'));

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

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;

Search an element in a collection ("member of")

If we have to search for an element inside a collection (nested table), we often tend use a for loop and compare each element in the array.
Instead we can make use of inbuilt feature of Nested table "member of":
This searches whether the element exists inside the collection or not and returns True or False.

Below is a small example:

declare
type TYPJOB is table of varchar2(100); -- Nested Table type
V_TYPJOB TYPJOB ;
v_srch_str varchar2(100) := 'CLERK';
begin
select job bulk collect into V_TYPJOB from EMP;
if (V_SRCH_STR member of V_TYPJOB) then
DBMS_OUTPUT.PUT_LINE(v_srch_str || '--exists inside the Collection');
else
dbms_output.put_line(v_srch_str || '--doesnt exists inside the Collection');
end if;
end;

This will be pretty useful if the collection size is large.

Grants on an Object

If you would want to know what all grants does a Object have, we can make use of below procedure. This will mostly help to get grants from PRODUCTION environment, where developers have restricted access,when we do not have direct access to the main schema.
But before that we need to get access to DBMS_METADATA pkg from DBAs.

SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT', 'EMP','SCOTT') from DUAL;

---------
"
GRANT SELECT ON "SCOTT"."EMP" TO "SCOTT"
"