11g Features :::- ADVANCED COMPRESSION

ORACLE ADVANCED COMPRESSION
---------------------------

Oracle Database 9i introduced Table Compression several years ago to allow data to be compressed
during bulk load operations such as direct path load, CREATE TABLE AS SELECT…. (CTAS), etc.
This form of compression was ideally suited for data warehousing environments where most data is
loaded in the database using batch processes. Oracle Database 11g introduces a new feature called
OLTP Table Compression that allows data to be compressed during all types of data manipulation
operations, including conventional DML such as INSERT and UPDATE. In addition, the new feature
significantly improves performance by reducing the overhead of write operations making it suitable
for transactional or OLTP environments as well
It may be noted that Table Compression feature introduced in Oracle Database 9i is a base feature
of Enterprise Edition (EE) and continues to be so even in Oracle Database 11g. The new OLTP Table
Compression feature, however, is a part of the Oracle Advanced Compression

Performance

1) Table Scan Performance 2.5x Faster
2) 3% Storage Saving
3) 3% Fast DML Operation



CREATE TABLE emp (
emp_id NUMBER
, first_name VARCHAR2(128)
, last_name VARCHAR2(128)
) COMPRESS FOR ALL OPERATIONS;

Compression for Unstructured Data

SecureFiles, a new feature in Oracle Database 11g,for storing unstructured content, such as documents, spreadsheets and XML files which is a storage format for large object (LOB) data types to improve performance, reduce space usage,and enhance security.

SecureFiles Deduplication is an intelligent technology that eliminates duplicate copies of SecureFiles data.Consider an email application where 10 users receive an email with the same 1MB attachment. Without SecureFiles Deduplication, the system would store one copy of the file for each of the 10 users – requiring 10MB of storage. If the email application in ourexample had used SecureFiles with Deduplication, it would have stored the 1MB attachment just once. That’s a 90% savings in storage requirements. In addition to the storage savings, SecureFiles Deduplication also increases application performance. Specifically, write and copy operations are much more efficient since only references to the SecureFiles image
are written. Further, read operations may improve if duplicate SecureFiles data already exists in the buffer cache.



Deduplication can be enabled for SecureFiles as below:

CREATE TABLE images (image_id NUMBER,
image BLOB)
LOB(image) STORE AS SECUREFILE
(TABLESPACE lob_tbs DEDUPLICATE);

SecureFiles Compression

CREATE TABLE images (image_id NUMBER,
image BLOB)
LOB(image)STORE AS SECUREFILE
(TABLESPACE lob_tbs COMPRESS);


Different create statement for Table creation with LOB datatype

CREATE TABLEdoc_tab (pkey number(10) not null, document clob)

CREATE TABLEdoc_tab (pkey number(10) not null, document clob)
LOB (document) STORE AS ( TABLESPACE TEST )

create table doc_tab (pkey number(10) not null, document clob)
lob(document) store as doc_tab_document_lobseg
(nocache logging retention);

/* Enabling Secure File ,Compress, Deduplicate */

create table doc_tab (pkey number(10) not null, document clob)
lob(document) store as SECUREFILE doc_tab_document_lobseg_sf
(nocache logging retention auto COMPRESS DEDUPLICATE);

/* Enabling Secure File ,Compress, Deduplicate,Encryption */

create table doc_tab (pkey number(10) not null,document clob)
lob(document) store as SECUREFILE doc_tab_document_lobseg
(nocachelogging retention auto COMPRESS DEDUPLICATE ENCRYPT);

Compression for Backup Data

How to Enable Data Pump Compression

Users have the following options to determine which parts of a dump file set should be compressed:
• ALL enables compression for the entire export operation.
• DATA-ONLY results in all data being written to the dump file in compressed format.
• METADATA-ONLY results in all metadata being written to the dump file in compressed format.
This is the default.
• NONE disables compression for the entire export operation.

expdp hr FULL=y DUMPFILE=dpump_dir:full.dmp COMPRESS;


Recovery Manager Compression

Oracle Advanced Compression introduces new RMAN Compression capabilities that improve RMAN performance while still drastically reducing the storage requirements for backups. Based on the industry standard ZLIB compression algorithm, RMAN compressed backups are up to 40% faster than compressed backups in Oracle Database 10g.



How to Enable RMAN Compression

Syntax for Fast RMAN compression is as below:
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘zlib’;

RMAN compression can be done as shown below:
RMAN> backup as COMPRESSED BACKUPSET database archivelog all;


--------------

Instead of Stragg /// Allow Duplicate

SELECT
deptno,
LTRIM(SYS_CONNECT_BY_PATH(ename, ','), ',') employees
FROM (
SELECT deptno,
ename,
row_number() over(PARTITION BY deptno ORDER BY deptno) rnum,
count(*) over(PARTITION BY deptno) tot
FROM scott.emp
)
WHERE rnum=tot
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum -1 AND PRIOR deptno = deptno;


select tb.deptno , substr ( sys_connect_by_path( tb.EMPNO, ',' ) , 2) as string
from ( select deptno ,EMPNO ,row_number() over ( partition by deptno
order by EMPNO ) as val_index from
scott.emp WHERE EMPNO IS NOT NULL ) tb where
connect_by_isleaf = 1 connect by val_index = prior val_index + 1 and
deptno = prior deptno start with val_index = 1 ;


WITH tab AS
(
SELECT &strg str
FROM DUAL)
SELECT REGEXP_SUBSTR (str, '[^,]+', 1, LEVEL) RESULT
FROM tab
CONNECT BY LEVEL <=
((SELECT SUM (CASE
WHEN SUBSTR (str, LEVEL, 1) = ','
THEN 1
WHEN LENGTH (str) = LEVEL
THEN 1
ELSE 0
END
)
FROM tab
CONNECT BY LEVEL <= LENGTH (str)));


SELECT REPLACE(&strg ,',',CHR(13)) FROM DUAL;


FROM 10G ONWARDS

SELECT deptno,wmsys.wm_concat(ename) d
FROM scott.emp group BY deptno

------------------------------------------
To remove duplicate  from a SQL

SELECT JOB, ENAME FROM
(SELECT DISTINCT ENAME,JOB, row_number () OVER (PARTITION BY ENAME ORDER BY ENAME) rn
FROM scott.emp )
WHERE rn = 1

Performance Tuning without Toad ie(In oracle)

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select golflink,course_name,x,y from (select b.golflink,b.course_name,( select distinct sdo_geom.sdo_min_mbr_ordinate(a.sdo_geometry,1) x
from username.golf_ground a where b.golflink=a.golflink and rownum=1 ) x
,( select distinct sdo_geom.sdo_min_mbr_ordinate(a.sdo_geometry,2) y
from username.golf_ground a where b.golflink=a.golflink and rownum=1 ) y
from username.golf_area b ) where x is not null and y is not null';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => null,--sql_binds(anydata.ConvertNumber(0)),
user_name => 'USER',
scope => 'COMPREHENSIVE',
time_limit => 10,
task_name => 'RuR',
description => 'Task to tune a query on a specified employee');
END;



SELECT * FROM DBA_ADVISOR_LOG where task_name='RuR'




BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'RuR' );
END;




SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'RuR'



SELECT * FROM V$ADVISOR_PROGRESS



SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'RuR') FROM DUAL;

By executing this query the details including explain-plan will be listed whether indexes to be added or not


BEGIN
DBMS_SQLTUNE.drop_tuning_task( task_name => 'RuR' );
END;

Rownum between

select rin from (select rownum as rin
from floor_address where rownum <= 5)
where rin between 2 and 4

Flashback Query

Flashback Query in 9i
---------------

/*In 9i flashback is for User level (ie : Schema level)*/

CONN SYS
GRANT EXECUTE ON dbms_flashback TO user;

CONN TO USER

EXEC DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP('31-JAN-2009:115427','DD-MON-YYYY:HH24MISS'))

SELECT * FROM ......

1)WITHOUT DISABLING DBMS_FLASHBACK WE CANT DO ANY TRANSACTIONS
2)U CANNOT GET DATA FROM A TRUNCATED TABLE

EXEC DBMS_FLASHBACK.DISABLE; --After this only DML transactions can be done

--------------------------------------------------------------------------------
Flashback Query in 10g
----------------

/*In 10g flashback is for Table level */

ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

FLASHBACK TABLE scott.emp
TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '05' minute);