Search This Blog

Total Pageviews

Saturday 27 February 2010

Oracle Analytical Fuctions With Anuj

Taking example from Book Advance SQL Functions In Oracle 10g


create table employee( empno number(3),
ename varchar2(20),
hiredate date,
orig_salary number(6),
curr_salary number(6),
region varchar2(2))


insert into employee
values(106,'Chloe','19-jan-96',33000,44000,'W');

insert into employee
values(101,'John','02-dec-97',35000,44000,'W');

insert into employee
values(102,'Stephanie','22-sep-98',35000,44000,'W');

insert into employee
values(108,'David','08-jul-01',37000,39000,'E');

insert into employee
values(106,'Lindsey','22-may-97',30000,52000,'E');

insert into employee
values(106,'Christina','08-mar-98',43000,55000,'W');

insert into employee
values(111,'Katie','13-apr-00',45000,49000,'E');




fuction() over()

row_number
**********

SQL> select empno,ename,orig_salary,row_number() over(order by orig_salary desc )
toprank from employee;




EMPNO ENAME ORIG_SALARY TOPRANK
---------- -------------------- ----------- ----------
111 Katie 45000 1
106 Christina 43000 2
108 David 37000 3
101 John 35000 4 <<<<<----
102 Stephanie 35000 5 <<<<<----
106 Chloe 33000 6
106 Lindsey 30000 7

7 rows selected.




It will rank the same value salary .


rank
**********

SQL> select empno,ename,orig_salary,rank() over(order by orig_salary desc ) toprank
from employee;

EMPNO ENAME ORIG_SALARY TOPRANK
---------- -------------------- ----------- ----------
111 Katie 45000 1
106 Christina 43000 2
108 David 37000 3
101 John 35000 4 <<<<<----
102 Stephanie 35000 4 <<<<<----
106 Chloe 33000 6 *****----
106 Lindsey 30000 7

7 rows selected.

*****----
it will rank the ORIG_SALARY same, but will consume next sequence .


dense_rank
**********

SQL> select empno,ename,orig_salary,dense_rank() over(order by orig_salary desc )
toprank from employee;

EMPNO ENAME ORIG_SALARY TOPRANK
---------- -------------------- ----------- ----------
111 Katie 45000 1
106 Christina 43000 2
108 David 37000 3
101 John 35000 4
102 Stephanie 35000 4
106 Chloe 33000 5
106 Lindsey 30000 6

7 rows selected.

Delete Duplicate Rows with Oracle SQL and How to perform long (Million rows ) deletes

Creating duplicate data .....


SQL> create table emp_dup as select * from emp;

Table created.

SQL> insert into emp_dup select * from emp;

14 rows created.

SQL> /

14 rows created.

SQL> insert into emp_dup select * from emp_dup;

42 rows created.

SQL> commit;

Commit complete.



Total no of rows...

SQL> select count(*) from emp_dup;

COUNT(*)
----------
84




These are the unique row


SQL> select * from (SELECT rowid,empno,deptno, ename, hiredate, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) AS emp_id
FROM emp_dup )
where emp_id=1;


ROWID EMPNO DEPTNO ENAME HIREDATE EMP_ID
------------------ ---------- ---------- ---------- --------- ----------
AAAaL6AAEAAABDkAAA 7369 20 SMITH 17-DEC-80 1
AAAaL6AAEAAABDkAAB 7499 30 ALLEN 20-FEB-81 1
AAAaL6AAEAAABDkAAC 7521 30 WARD 22-FEB-81 1
AAAaL6AAEAAABDkAAD 7566 20 JONES 02-APR-81 1
AAAaL6AAEAAABDkAAE 7654 30 MARTIN 28-SEP-81 1
AAAaL6AAEAAABDkAAF 7698 30 BLAKE 01-MAY-81 1
AAAaL6AAEAAABDkAAG 7782 10 CLARK 09-JUN-81 1
AAAaL6AAEAAABDkAAH 7788 20 SCOTT 19-APR-87 1
AAAaL6AAEAAABDkAAI 7839 10 KING 17-NOV-81 1
AAAaL6AAEAAABDkAAJ 7844 30 TURNER 08-SEP-81 1
AAAaL6AAEAAABDkAAK 7876 20 ADAMS 23-MAY-87 1
AAAaL6AAEAAABDkAAL 7900 30 JAMES 03-DEC-81 1
AAAaL6AAEAAABDkAAM 7902 20 FORD 03-DEC-81 1
AAAaL6AAEAAABDkAAN 7934 10 MILLER 23-JAN-82 1

14 rows selected.



SQL> select rowid from (SELECT rowid,empno,deptno, ename, hiredate, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) AS emp_id
FROM emp_dup )
where emp_id=1;


ROWID
------------------
AAAaL6AAEAAABDkAAA
AAAaL6AAEAAABDkAAB
AAAaL6AAEAAABDkAAC
AAAaL6AAEAAABDkAAD
AAAaL6AAEAAABDkAAE
AAAaL6AAEAAABDkAAF
AAAaL6AAEAAABDkAAG
AAAaL6AAEAAABDkAAH
AAAaL6AAEAAABDkAAI
AAAaL6AAEAAABDkAAJ
AAAaL6AAEAAABDkAAK
AAAaL6AAEAAABDkAAL
AAAaL6AAEAAABDkAAM
AAAaL6AAEAAABDkAAN

14 rows selected.



SQL> delete from emp_dup
where rowid in (select rowid from (SELECT rowid,empno,deptno, ename, hiredate, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) AS emp_id
FROM emp_dup )
where emp_id!=1)
/

70 rows deleted.



SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> select * from emp_dup;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7900 JAMES CLERK 7698 03-DEC-81 950 30

14 rows selected.



=======================================
>>>>>>>>>>>>>>>>>>>>>>>>>>> Important
=======================================


How to perform long deletes ( commiting every n number of records)

When performing a DELETE from a table, Oracle will generate rollback. If the particular table you are trying to delete from contains a large number of records, it is possible for the transaction to fail because of the rollback segment not being large enough



Solution would be to use the PL/SQL block below.



set serveroutout on


DECLARE
cnt NUMBER := 0;
total NUMBER := 0;
BEGIN

FOR rec IN (select rowid from (SELECT rowid,empno,deptno, ename, hiredate,
ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) AS emp_id
FROM emp_dup )
where emp_id!=1)

LOOP
DELETE FROM emp_dup
WHERE rowid = rec.rowid;
total := total + 1;
cnt := cnt + 1;

IF (cnt >= 5000) THEN
COMMIT;
cnt := 0;
END IF;
END LOOP;

COMMIT;

DBMS_OUTPUT.PUT_LINE('Deleted ' || total || ' records from .');

END;

Oracle plsql loop examples

declare
lev number;
nspaces number;
begin
lev := &level;

for i in 1..lev
loop
nspaces := round((lev - i) / 2) ;
for j in 1..nspaces
loop
dbms_output.put('*');
end loop;

for j in 1..i
loop
dbms_output.put('9');
end loop;
for j in 1..nspaces
loop
dbms_output.put('*');
end loop;
dbms_output.put_line('');
end loop;
end;
/

fina@ORA7.WORLD> @pr
Enter value for level: 9
old 5: lev := &level;
new 5: lev := 9;
****9****
****99****
***999***
***9999***
**99999**
**999999**
*9999999*
*99999999*
999999999





DECLARE
numero NUMBER := 1;
longueur NUMBER;
BEGIN
DBMS_OUTPUT.enable(1000000);
FOR i IN 1..38 LOOP
DBMS_OUTPUT.put_line( numero);
numero := 10*numero+1;
END LOOP;
END;

08:58:20 SCOTT: ORACLE9I> /

1
11
111
1111
11111
111111
1111111
11111111
111111111
1111111111
11111111111
111111111111
1111111111111
11111111111111
111111111111111
1111111111111111
11111111111111111
111111111111111111
1111111111111111111
11111111111111111111
111111111111111111111
1111111111111111111111
11111111111111111111111
111111111111111111111111
1111111111111111111111111
11111111111111111111111111
111111111111111111111111111
1111111111111111111111111111
11111111111111111111111111111
111111111111111111111111111111
1111111111111111111111111111111
11111111111111111111111111111111
111111111111111111111111111111111
1111111111111111111111111111111111
11111111111111111111111111111111111
111111111111111111111111111111111111
1111111111111111111111111111111111111
11111111111111111111111111111111111111


DECLARE
numero NUMBER := 2;
longueur NUMBER;
BEGIN
DBMS_OUTPUT.enable(1000000);
FOR i IN 1..38 LOOP
DBMS_OUTPUT.put_line( numero);
numero := 10*numero+2;
END LOOP;
END;

08:58:44 SCOTT: ORACLE9I> /

2
22
222
2222
22222
222222
2222222
22222222
222222222
2222222222
22222222222
222222222222
2222222222222
22222222222222
222222222222222
2222222222222222
22222222222222222
222222222222222222
2222222222222222222
22222222222222222222
222222222222222222222
2222222222222222222222
22222222222222222222222
222222222222222222222222
2222222222222222222222222
22222222222222222222222222
222222222222222222222222222
2222222222222222222222222222
22222222222222222222222222222
222222222222222222222222222222
2222222222222222222222222222222
22222222222222222222222222222222
222222222222222222222222222222222
2222222222222222222222222222222222
22222222222222222222222222222222222
222222222222222222222222222222222222
2222222222222222222222222222222222222
22222222222222222222222222222222222222

PL/SQL procedure successfully completed.




DECLARE
numero NUMBER := 9;
longueur NUMBER;
BEGIN
DBMS_OUTPUT.enable(1000000);
FOR i IN 1..38 LOOP
SELECT VSIZE (numero) INTO longueur FROM dual;
DBMS_OUTPUT.put_line( numero);
numero := 10*numero+9;
END LOOP;
END;

08:52:14 SCOTT: ORACLE9I> /

9
99
999
9999
99999
999999
9999999
99999999
999999999
9999999999
99999999999
999999999999
9999999999999
99999999999999
999999999999999
9999999999999999
99999999999999999
999999999999999999
9999999999999999999
99999999999999999999
999999999999999999999
9999999999999999999999
99999999999999999999999
999999999999999999999999
9999999999999999999999999
99999999999999999999999999
999999999999999999999999999
9999999999999999999999999999
99999999999999999999999999999
999999999999999999999999999999
9999999999999999999999999999999
99999999999999999999999999999999
999999999999999999999999999999999
9999999999999999999999999999999999
99999999999999999999999999999999999
999999999999999999999999999999999999
9999999999999999999999999999999999999
99999999999999999999999999999999999999

PL/SQL procedure successfully completed.





1 declare
2 x number:=2;
3 y number;
4 begin
5 for i in 1..30
6 loop
7 --dbms_output.put_line(x);
8 x := x*10 + 2;
9 y := x;
10 end loop;
11 for i in 1..31
12 loop
13 dbms_output.put_line(y);
14 y := round(y/10);
15 end loop;
16* end;
fina@ORA7.WORLD> /
2222222222222222222222222222222
222222222222222222222222222222
22222222222222222222222222222
2222222222222222222222222222
222222222222222222222222222
22222222222222222222222222
2222222222222222222222222
222222222222222222222222
22222222222222222222222
2222222222222222222222
222222222222222222222
22222222222222222222
2222222222222222222
222222222222222222
22222222222222222
2222222222222222
222222222222222
22222222222222
2222222222222
222222222222
22222222222
2222222222
222222222
22222222
2222222
222222
22222
2222
222
22
2

PL/SQL procedure successfully completed.

Oracle DBA

anuj blog Archive