OOUG Oracle Technology Day - July 20, 2005

@connect /
drop table iot;
drop table heap;
set echo on 
set linesize 121
clear screen
create table iot
( username         varchar2(30),
  document_name    varchar2(30),
  other_data       char(1000),
  constraint iot_pk primary key (username,document_name))
organization index
/
create table heap
( username         varchar2(30),
  document_name    varchar2(30),
  other_data       char(1000),
  constraint heap_pk primary key (username,document_name))
/
pause


clear screen
begin
    for i in 1 .. 100
    loop
        for x in ( select username from all_users )
        loop
            insert into heap
            (username,document_name,other_data)
            values
            ( x.username, x.username || '_' || i, 'x' );
 
            insert into iot
            (username,document_name,other_data)
            values
            ( x.username, x.username || '_' || i, 'x' );
        end loop;
    end loop;
    commit;
end;
/
exec dbms_stats.gather_table_stats( user, 'IOT', cascade=>true );
exec dbms_stats.gather_table_stats( user, 'HEAP', cascade=>true );
pause

clear screen
@trace
pause
clear screen
declare
    l_rec   heap%rowtype;
    cursor heap_cursor(p_username in varchar2) is 
    select * from heap single_row where username = p_username;
    cursor  iot_cursor(p_username in varchar2) is 
    select * from  iot single_row where username = p_username;
begin
for i in 1 .. 10
loop
    for x in (select username from all_users) loop
        open  iot_cursor(x.username);
        loop
            fetch  iot_cursor into l_rec;
            exit when  iot_cursor%notfound;
        end loop;
        close  iot_cursor;
        open heap_cursor(x.username);
        loop
            fetch heap_cursor into l_rec;
            exit when heap_cursor%notfound;
        end loop;
        close heap_cursor;
    end loop;
end loop;
end;
/
pause
clear screen
declare
    type array is table of iot%rowtype;
    l_data array;
begin
for i in 1 .. 10
loop
    for x in (select username from all_users)
    loop
        select * bulk collect into l_data
          from  iot bulk_collect
         where username = x.username;
        select * bulk collect into l_data
          from heap bulk_collect
         where username = x.username;
    end loop;
end loop;
end;
/
pause
disconnect

!tkprof `ls -t $ORACLE_HOME/admin/$ORACLE_SID/udump/*ora_*.trc | head -1` ./tk.prf sys=no 
edit tk.prf
@connect /
	

set echo on
alter session set sql_trace=true;

begin
    for x in ( select /* this is my very nice Comment */ * 
                 from big_table.big_table 
                where rownum <= 10000 ) 
    loop
        null;
    end loop;
end;
/
pause
connect /
!tk
drop table t;
create table t ( x int );
begin
    savepoint foo;

    insert into t 
    select 1 
      from all_objects 
     where rownum <= 1;

    for x in ( select * from t )
    loop
        rollback to savepoint foo;
    end loop;
end;
/
pause

begin
    savepoint foo;

    insert into t 
    select 1 
      from all_objects 
     where rownum <= 101;
	 
    for x in ( select * from t )
    loop
        rollback to savepoint foo;
    end loop;
end;
/
	

column val clear
set echo on
set linesize 121
drop table t;

clear screen
create table t ( dt date, val number );
insert into t 
select sysdate-100+rownum, decode(mod(rownum,4),1,user_id)
  from (select * from all_users order by user_id desc )
 where rownum <= 10;
pause


clear screen
select dt, val
  from t
 order by dt;
pause


clear screen
select dt, val,
       case when val is not null
	   then to_char(row_number() over (order by dt),'fm0000')||val
	   end max_val
  from t
 order by dt;
pause


clear screen
select dt, val, 
       to_number(substr(max(max_val) over (order by dt),5)) max_val
  from (
select dt, val,
       case when val is not null
	   then to_char(row_number() over (order by dt),'fm0000')||val
	   end max_val
  from t
       )
 order by dt
/
pause


clear screen
select dt, val,
       last_value(val ignore nulls) over (order by dt) val
  from t
 order by dt
/
pause
	

set echo on
set linesize 121
clear screen
create or replace function f(x in number) return number
as
begin
    dbms_application_info.set_client_info(userenv('client_info')+1);
    return 42;
end;
/
pause

clear screen
exec dbms_application_info.set_client_info(0);
set pause on
select emp.empno, f(dept.deptno)
  from scott.emp, scott.dept
 where emp.deptno = dept.deptno;
pause
set pause off
clear screen
select userenv('client_info') from dual;
pause


clear screen
exec dbms_application_info.set_client_info(0);
set pause on
select emp.empno, fdeptno, dname
  from scott.emp, 
       (select deptno, f(deptno) fdeptno, dname from scott.dept )dept
 where emp.deptno = dept.deptno;
pause
set pause off
clear screen
select userenv('client_info') from dual;
pause

clear screen
exec dbms_application_info.set_client_info(0);
set pause on
select emp.empno, fdeptno, dname
  from scott.emp, 
       (select deptno, f(deptno) fdeptno, dname, rownum r from scott.dept )dept
 where emp.deptno = dept.deptno;
pause
set pause off
clear screen
select userenv('client_info') from dual;
pause

alter session set optimizer_goal=first_rows;

clear screen
exec dbms_application_info.set_client_info(0);
set pause on
select emp.empno, fdeptno, dname
  from scott.emp, 
       (select /*+ NO_MERGE */ deptno, dname, f(deptno) fdeptno from scott.dept )dept
 where emp.deptno = dept.deptno;
pause
set pause off
clear screen
select userenv('client_info') from dual;
pause

clear screen
delete from plan_table;
explain plan for
select emp.empno, fdeptno, dname
  from scott.emp, 
       (select deptno, f(deptno) fdeptno, dname, rownum r from scott.dept )dept
 where emp.deptno = dept.deptno;
pause


clear screen
select * from table(dbms_xplan.display);
pause



clear screen
delete from plan_table;
explain plan for
select emp.empno, fdeptno, dname
  from scott.emp, 
       (select /*+ NO_MERGE */ deptno, dname, f(deptno) fdeptno from scott.dept )dept
 where emp.deptno = dept.deptno;
pause


clear screen
select * from table(dbms_xplan.display);
pause
	

set echo on
set linesize 121
clear screen
delete from plan_table;
explain plan for
select * from (select * from scott.emp order by sal desc) where rownum <= 10;
pause
clear screen
select * from table(dbms_xplan.display);
pause


clear screen
delete from plan_table;
explain plan for
select * from scott.emp order by sal desc;
pause

clear screen
select * from table(dbms_xplan.display);
pause


clear screeen
@trace
set autotrace traceonly statistics
select * from (select * from all_objects order by object_id) where rownum <= 10;
set autotrace off
pause
clear screen
declare
   cursor c is 
      select * from all_objects order by object_id;
   l_rec all_objects%rowtype;
begin
   open c;
   for i in 1 .. 10
   loop
       fetch c into l_rec;
   end loop;
   close c;
end;
/
pause
select * from dual;
disconnect 
connect /
!tkprof `ls -t $ORACLE_HOME/admin/$ORACLE_SID/udump/*ora_*.trc | head -1` ./tk.prf sys=no 
edit tk.prf
	

set echo on
set linesize 121
clear screen
@trace
set autotrace traceonly statistics
variable max number
variable min number
exec :min := 100; :max := 115;
pause
select * 
  from (select /*+ FIRST_ROWS */ a.*, rownum rnum
          from (select * from all_objects order by object_id) a
         where rownum <= :Max) 
 where rnum >= :min;
set autotrace off
pause



clear screen
declare
   cursor c is 
      select * from all_objects order by object_id;
   l_rec all_objects%rowtype;
begin
   open c;
   for i in 1 .. 115
   loop
       fetch c into l_rec;
       if ( i < 100 )
       then
           null;
       else
        null; -- process it
       end if;
   end loop;
   close c;
end;
/
pause
select * from dual;
disconnect 
connect /
!tkprof `ls -t $ORACLE_HOME/admin/$ORACLE_SID/udump/*ora_*.trc | head -1` ./tk.prf sys=no 
edit tk.prf	
	

set echo on
set linesize 121

clear screen
set autotrace on statistics
set pause on
select a.username, count(b.owner)
  from all_users a left join all_objects b
    on (a.username = b.owner)
 where a.username in ( user, 'SYSTEM' )
 group by a.username;
pause


clear screen
select a.username, (select count(*)
                      from all_objects b
                     where b.owner = a.username ) cnt
  from all_users a
 where a.username in ( user, 'SYSTEM' );
pause


clear screen
select a.username, 
(select count(*) from all_objects b where b.owner = a.username ) cnt,
(select min(created) from all_objects b where b.owner = a.username ) min_created,
(select max(created) from all_objects b where b.owner = a.username ) max_created
  from all_users a
 where a.username in ( user, 'SYSTEM' );
pause



clear screen
select username,
       to_number(substr(data,1,10)) cnt,
       to_date(substr(data,11,14),'yyyymmddhh24miss') min_created,
       to_date(substr(data,25),'yyyymmddhh24miss') max_created
  from (
select a.username, (select to_char( count(*), 'fm0000000000') ||
                           to_char( min(created),'yyyymmddhh24miss') || 
                           to_char( max(created),'yyyymmddhh24miss') 
                      from all_objects b where b.owner = a.username ) data
  from all_users a
 where a.username in ( user, 'SYSTEM' )
       );
pause


clear screen
create or replace type myType as object 
( cnt number, min_created date, max_created date )
/
select username, a.data.cnt, a.data.min_created, a.data.max_created
  from (
select a.username, (select myType( count(*), min(created), max(created) )
                      from all_objects b where b.owner = a.username ) data
  from all_users a
 where a.username in ( user, 'SYSTEM' )
       ) a;
pause

set autotrace off
set pause off
	

set echo on
set linesize 121

clear screen
create or replace function f( x in varchar2 ) return number
as
begin	
	dbms_application_info.set_client_info(userenv('client_info')+1 );
	return length(x);
end;
/
pause

clear screen
exec dbms_application_info.set_client_info(0);
set autotrace traceonly statistics;
select owner, f(owner) from all_objects;
set autotrace off
select userenv('client_info') from dual;
pause



clear screen
exec dbms_application_info.set_client_info(0);
set autotrace traceonly statistics;
select owner, (select f(owner) from dual) f from all_objects;
set autotrace off
select userenv('client_info') from dual;
pause


clear screen
exec dbms_application_info.set_client_info(0);
set autotrace traceonly statistics;
select owner, (select f(owner) from dual) f 
  from (select owner from all_objects order by owner);
set autotrace off
select userenv('client_info') from dual;
pause
	

set echo on
set linesize 121

clear screen
variable x varchar2(20)
variable n number
exec :x := '01-jan-2005'
exec :n := 5
set autotrace on
set pause on
select to_date(:x,'dd-mon-yyyy')+level-1
  from dual
connect by level <= :n
/
set autotrace off
set pause off
	

@connect scott/tiger

set echo on
break on deptno skip 1
set pause on
clear screen
Select deptno, ename, sal, 
       sum(sal) over (partition by deptno order by sal) running_total1,
       sum(sal) over (partition by deptno order by sal, rowid) running_total2
  from emp order by deptno, sal;
set pause off
	

@connect scott/tiger

break on deptno skip 1
set pause on
clear screen
select deptno, ename, sal, 
       to_char( round( 
	     ratio_to_report(sal) over (partition by deptno)
		 *100, 2 ), '990.00' )||'%' rtr
  from emp
/
set pause off
	

@connect scott/tiger

set echo on
break on deptno skip 1
set pause on
clear screen
select deptno, ename, sal, rn
  from (
Select deptno, ename, sal, 
       row_number() over (partition by deptno order by sal desc) rn
  from emp 
       )
 where rn <= 3
/
pause
clear screen

select deptno, ename, sal, rank
  from (
Select deptno, ename, sal, 
       rank() over (partition by deptno order by sal desc) rank
  from emp 
       )
 where rank <= 3
/
pause
clear screen

select deptno, ename, sal, dr
  from (
Select deptno, ename, sal, 
       dense_rank() over (partition by deptno order by sal desc) dr
  from emp 
       )
 where dr <= 3
/
set pause off
	

@connect scott/tiger
column last_5 format a30
set echo on
set pause on
clear screen
Select ename, sal, 
       round( 
	     avg(sal) over (order by sal 
                             rows 5 preceding)
		 ) avg_sal,
       rtrim(
       lag(sal) over (order by sal) || ',' ||
       lag(sal,2) over (order by sal) || ',' ||
       lag(sal,3) over (order by sal) || ',' ||
       lag(sal,4) over (order by sal) || ',' ||
       lag(sal,5) over (order by sal),',') last_5
  from emp 
 order by sal;
set pause off
pause
select round( (3000+3000+2975+2850+2450+5000)/6 ) from dual
/
	

@connect scott/tiger

set echo on
break on deptno skip 1
set pause on
clear screen
Select deptno, ename, sal, 
       rank() over ( partition by deptno order by sal desc ) r,
       dense_rank() over ( partition by deptno order by sal desc ) dr,
       row_number() over ( partition by deptno order by sal desc ) rn
  from emp 
 order by deptno, sal desc;
set pause off
	

@connect /
set echo on
clear screen

drop table emp;
create table emp as select * from scott.emp;
pause
clear screen
set pause on

select deptno,
       count(*),
       percentile_cont(0.5) within group (order by sal) med
  from emp
 group by deptno
/
pause
clear screen

select deptno,
       sal,
       cnt,
       rn,
       case when
           ((mod(cnt,2) = 1 and rn = ceil(cnt/2) )
            or (mod(cnt,2) = 0 and rn in ( cnt/2, cnt/2+1 ) )) then '<<===='
        end
  from (
select deptno,
       sal,
       count(*) over (partition by deptno) cnt,
       row_number() over(partition by deptno order by sal) rn
  from emp
       )
/
pause
clear screen
select deptno,
       sal,
       cnt,
       rn
  from (
select deptno,
       sal,
       count(*) over (partition by deptno) cnt,
       row_number() over(partition by deptno order by sal) rn
  from emp
       )
 where (mod(cnt,2) = 1 and rn = ceil(cnt/2) )
    or (mod(cnt,2) = 0 and rn in ( cnt/2, cnt/2+1 ) )
/
pause
clear screen

select deptno,
       avg(sal),
       cnt
  from (
select deptno,
       sal,
       count(*) over (partition by deptno) cnt,
       row_number() over(partition by deptno order by sal) rn
  from emp
       )
 where (mod(cnt,2) = 1 and rn = ceil(cnt/2) )
    or (mod(cnt,2) = 0 and rn in ( cnt/2, cnt/2+1 ) )
 group by deptno, cnt
/
pause
clear screen

SELECT deptno, AVG(DISTINCT sal)
  FROM  (SELECT cp1.deptno, CP1.sal
           FROM emp CP1, emp CP2
          where cp1.deptno = cp2.deptno
          GROUP BY cp1.deptno, CP1.sal
         HAVING SUM(DECODE(CP1.sal, CP2.sal, 1, 0)) >=
                        ABS(SUM(SIGN(CP1.sal - CP2.sal))))
 group by deptno
/

pause
clear screen

insert into emp select * from emp;
insert into emp select * from emp;
insert into emp select * from emp;
insert into emp select * from emp;
insert into emp select * from emp;
insert into emp select * from emp;
insert into emp select * from emp;
insert into emp select * from emp;
commit;
exec dbms_stats.gather_table_stats(user,'EMP');
select count(*) from emp;
pause
clear screen
set pause off

@trace
select deptno,
       count(*),
       percentile_cont(0.5) within group (order by sal) med
  from emp
 group by deptno
/
pause
clear screen

select deptno,
       avg(sal),
       cnt
  from (
select deptno,
       sal,
       count(*) over (partition by deptno) cnt,
       row_number() over(partition by deptno order by sal) rn
  from emp
       )
 where (mod(cnt,2) = 1 and rn = ceil(cnt/2) )
    or (mod(cnt,2) = 0 and rn in ( cnt/2, cnt/2+1 ) )
 group by deptno, cnt
/
pause
clear screen

SELECT deptno, AVG(DISTINCT sal)
  FROM  (SELECT cp1.deptno, CP1.sal
           FROM emp CP1, emp CP2
          where cp1.deptno = cp2.deptno
          GROUP BY cp1.deptno, CP1.sal
         HAVING SUM(DECODE(CP1.sal, CP2.sal, 1, 0)) >=
                        ABS(SUM(SIGN(CP1.sal - CP2.sal))))
 group by deptno
/

@connect scott/tiger
!tk
	

@connect /
set echo on
clear screen

drop table t;

create table t ( x date, y int );
pause
clear screen
 
declare
        l_date date := to_date( '12:22:03', 'hh24:mi:ss' );
begin
        for i in 1 .. 10
        loop
             insert into t values ( l_date, dbms_random.value( 0, 100 ) );
             l_date := l_date + 1/24/60/60;
        end loop;
        l_date := l_date + 5/24/60/60;
        for i in 1 .. 3
        loop
             insert into t values ( l_date, dbms_random.value( 0, 100 ) );
             l_date := l_date + 1/24/60/60;
        end loop;
        l_date := l_date + 15/24/60/60;
        for i in 1 .. 12
        loop
             insert into t values ( l_date, dbms_random.value( 0, 100 ) );
             l_date := l_date + 1/24/60/60;
        end loop;
end;
/
pause
clear screen
alter session set nls_date_format = 'hh24:mi:ss';

set pause on

select x, y, 
       lag(x) over (order by x),
       case 
          when abs(lag(x) over (order by x) - x) > 3/24/60/60 
               then row_number() over (order by x)
       end rn
  from t
/
pause 
clear screen
select x, y, 
       max(rn) over (order by x) max_rn
  from (
select x, y, 
       lag(x) over (order by x),
       case 
          when abs(lag(x) over (order by x) - x) > 3/24/60/60 
               then row_number() over (order by x)
       end rn
  from t
       )
/
pause
clear screen

select min(x), max(x), sum(y) 
  from (
select x, y, 
       max(rn) over (order by x) max_rn
  from (
select x, y, 
       lag(x) over (order by x),
       case 
          when abs(lag(x) over (order by x) - x) > 3/24/60/60 
               then row_number() over (order by x)
       end rn
  from t
       )
       )
 group by max_rn
 order by 1
/
set pause off
	

@connect /

set linesize 121
set echo on
drop table emp;

clear screen
create or replace type str2tblType as table of varchar2(30);
/
create or replace 
function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) 
return str2tblType
PIPELINED
as
    l_str      long default p_str || p_delim;
    l_n        number;
begin
    loop
        l_n := instr( l_str, p_delim );
        exit when (nvl(l_n,0) = 0);
		pipe row ( ltrim(rtrim(substr(l_str,1,l_n-1))) );
        l_str := substr( l_str, l_n+1 );
    end loop;
    return;
end;
/
pause

clear screen
variable str varchar2(50);
exec :str := '1,2,3,4';
select * from table( cast( str2tbl(:str) as str2tblType ) );
pause

clear screen
create table emp
as
select * 
  from (
select object_name ename, max(object_id) empno, 
       max(object_type) ot, max(created) created
  from all_objects
 group by object_name
       )
 where rownum <= 5000;
alter table emp add constraint emp_pk primary key(empno);
create index ename_idx on emp(ename);
exec dbms_stats.gather_table_stats( user, 'EMP', cascade=>true );
pause

clear screen
variable in_list varchar2(255)
exec :in_list := 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE';
set autotrace traceonly explain
select * 
  from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t;
pause
clear screen
select /*+ cardinality( 10 ) */ *
  from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t;
set autotrace traceonly 
pause

clear screen
set autotrace traceonly explain
with T
as
( select * 
    from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t where rownum > 0
)
select *
  from emp
 where ename in ( select * from t )
/
pause

clear screen
with T
as
( select * 
    from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t where rownum > 0
)
select *
  from emp
 where ename in ( select /*+ cardinality(10) */ * from t )
/
set autotrace off