Nov 28, 2008

Overlap,Explain Plan

Overlap

If any of the condition in where clause of a query overlaps , with the parameter in overlap function query returns value else not

select 'a' from dual where (sysdate,sysdate+1) overlaps (sysdate,sysdate+1);

---------------------------------------------
SQL> set autotrace on explain

Then execute the SQL script that you want to get the explain plan




--------------------------------------------------------------------------------------------------------
EXPLAIN PLAN
------------------
EXPLAIN PLAN statement determine the execution plan Oracle Database follows to execute a specified
SQL statement.


TO INSERT INTO PLAN TABLE

EXPLAIN PLAN
SET STATEMENT_ID = 'Raise in Tokyo'
INTO plan_table FOR SELECT * FROM DUAL;

DISPLAYING  PLAN_TABLE OUTPUT

select * from plan_table t
----------------------------------------------

select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",object_name
"Object",position, BYTES,CARDINALITY,COST from plan_table start with id = 0
connect by prior id=parent_id;
---------------------------------------
select * from table(dbms_xplan.display);
-----------------------------------------

SELECT  *  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'BASIC'));

BASIC :   Display only the minimum set of information

TYPICAL: This is the default. Display most information  of the explain plan (operation id, name and option,#rows, #bytes and optimizer cost).

ALL: Maximum user level, like typical with additional informations (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).

Some words about PLAN_TABLE resides in sys user with name PLAN_TABLE$ ;which is a  ON COMMIT PRESERVE ROWS GLOBAL TEMPORARY TABLE
 
------------------------------------
How to Read Explain Plan
------------------------------------
select * from sys.v_$sql_workarea t
--------------------------------------
select * from sys.v_$sql_plan t
-------------------------------

Nov 24, 2008

Encryption

CREATE OR REPLACE FUNCTION fnc_encryption(pi_src IN VARCHAR2) RETURN CLOB
IS
v_enc_val raw(32767);
v_key1 VARCHAR2(16) := '1111111111111111';
v_key2 VARCHAR2(16) := '2222222222222222';
v_charterset VARCHAR2(8) := 'AL32UTF8';
v_mod number := DBMS_CRYPTO.ENCRYPT_AES128
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
v_enc_val := DBMS_CRYPTO.ENCRYPT(
src => UTL_I18N.STRING_TO_RAW(pi_src, v_charterset),
typ => v_mod,
key => UTL_I18N.STRING_TO_RAW(v_key1, v_charterset),
iv => UTL_I18N.STRING_TO_RAW(v_key2, v_charterset)
);
RETURN RAWTOHEX(v_enc_val);
END fnc_encryption;
/



Scenario 1: Use the function to encrypt all of the rows in the table and then repeat the operation - no error occurs:

SQL> update TEST
2 SET NAME=fnc_encryption(name);

3 rows updated.

SQL> update TEST
2 SET NAME=fnc_encryption(name);

3 rows updated.

Time ; Avoid duplicate

select DBMS_UTILITY.GET_TIME from dual;

select * from v$timer;

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

Avoid duplicate

To avoid duplicate rows there are three steps

1) Use DISTINCT Keyword
2) Use UNIQUE Keyword
3) Use GROUP BY Keyword

Examples

1) Select distinct vcname,empcode,deptcode,addr,age,phoneno from hr_emp_dup_mast;
2) Select unique vcname,empcode,deptcode,addr,age,phoneno from hr_emp_dup_mast;
3) Select vcname,empcode,deptcode,addr,age,phoneno from hr_emp_dup_mast
group by vcname,empcode,deptcode,addr,age,phoneno;

Nov 14, 2008

Level queries

To get the higher manager of a particular employee


select yy.empno,yy.mgr,yy.ename,rt.empno,rt.mgr,rt.ename from
(select * from emp t
connect by prior t.empno=t.mgr start with mgr is null) yy,
emp rt
where yy.empno=rt.mgr --and rt.empno=7782

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

To get repeating data

select deptno,dname,loc,ltrim(ename,' ,') ename,ltrim(hiredate,' ,') hiredate,ltrim(mgr,' ,') mgr
,ltrim(job,' ,') job
from (
select deptno,dname,loc,SYS_CONNECT_BY_PATH (ename,' ,') ename,SYS_CONNECT_BY_PATH (hiredate,' ,') hiredate,
SYS_CONNECT_BY_PATH (job,' ,') job,SYS_CONNECT_BY_PATH (mgr,' ,') mgr
from (
select r.deptno,r.dname,r.loc,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm ,
row_number() over (partition by r.deptno order by r.deptno) r,
row_number() over (partition by r.deptno order by r.deptno) -1 c
from scott.dept r,scott.emp e
where r.deptno=10 and e.deptno=r.deptno
) start with r = 1
connect by c = prior r
order by rownum desc)
where rownum = 1 ;


OR


select r.deptno,r.dname,r.loc,wmsys.wm_concat(e.ename),wmsys.wm_concat(e.job),wmsys.wm_concat(e.mgr),wmsys.wm_concat(e.hiredate),wmsys.wm_concat(e.sal),wmsys.wm_concat(e.comm)
from scott.dept r,scott.emp e
where r.deptno=10 and e.deptno=r.deptno
group by r.deptno,r.dname,r.loc