OOUG Oracle Technology Day - July 20, 2005

set echo on
set linesize 121
drop table t;

clear screen
create table t ( str_date, date_date, number_date, data )
as
select to_char( dt+rownum,'yyyymmdd' ),
       dt+rownum,
       to_number( to_char( dt+rownum,'yyyymmdd' ) ),
	   rpad('*',45,'*')
  from (select to_date('01-jan-1995','dd-mon-yyyy') dt
          from all_objects)
/
create index t_str_date_idx on t(str_date);
create index t_date_date_idx on t(date_date);
create index t_number_date_idx on t(number_date);
pause

clear screen
begin
	dbms_stats.gather_table_stats
	( user, 'T', 
	  method_opt=> 'for all indexed columns', 
	  cascade=> true );
end;
/
pause
set autotrace on explain
clear screen
select * 
  from t
 where str_date between '20001231' and '20010101';
pause
clear screen
select * 
  from t
 where number_date between 20001231 and 20010101;
pause 
clear screen
select * 
  from t
 where date_date 
    between to_date('20001231','yyyymmdd') 
	    and to_date('20010101','yyyymmdd');
set autotrace traceonly explain
pause
/*
pause 
clear screen
select * 
  from t
 where str_date between '19951231' and '20190801';
pause 
clear screen
select * 
  from t
 where date_date between to_date('19951231','yyyymmdd') 
                     and to_date('20190801','yyyymmdd');
*/
clear screen
set autotrace off
	

drop table hash_lookup;
drop table heap_lookup;
drop cluster hash_cluster;
set echo on

clear screen
create cluster Hash_Cluster
( id number )
SINGLE TABLE
hashkeys 50000   -- number of distinct lookups we expect over time
size 125         -- size of data associated with key
/
pause


clear screen
create table heap_lookup
( OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID,
  DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME,
  TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, 
  primary key(object_id) 
)
as
select * from all_objects;

create table hash_lookup
( OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID,
  DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME,
  TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, 
  primary key(object_id)
)
cluster hash_cluster(object_id)
as
select * from heap_lookup;
pause
clear screen
begin
    dbms_stats.gather_table_stats
    ( user, 'HEAP_LOOKUP', cascade=>true );
    dbms_stats.gather_table_stats
    ( user, 'HASH_LOOKUP', cascade=>true );
end;
/
pause

clear screen
declare
    type array is table of number;
    l_object_ids array;
    l_rec        heap_lookup%rowtype;
begin
    select object_id bulk collect into l_object_ids
      from heap_lookup;

    execute immediate q'|
        alter session set events '10046 trace name context forever, level 12'
    |';

    for k in 1 .. l_object_ids.count 
    loop
        select * into l_rec 
          from heap_lookup 
         where object_id = l_object_ids(k);
        select * into l_rec 
          from hash_lookup 
         where object_id = l_object_ids(k);
    end loop;
end;
/
pause
@connect /
!tkprof `ls -t $ORACLE_HOME/admin/$ORACLE_SID/udump/*ora_*.trc | head -1` tk.prf sys=no
edit tk.prf
	

@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 /
	

@connect iot_heap/iot_heap
set linesize 121
set echo on

 
REM drop table stock_ticker_iot;
REM drop table stock_ticker_heap;
clear screen
/*
create table stock_ticker_iot
( sym varchar2(4),
  dt  date,
  val number,
  primary key(sym,dt)
)
organization index
compress 1
/
create table stock_ticker_heap
( sym varchar2(4),
  dt  date,
  val number,
  primary key(sym,dt)
)
organization heap 
/
*/
pause

clear screen
/*
insert into stock_ticker_heap
select distinct substr(object_name,1,4), trunc(sysdate-50), 1 
  from all_objects;
commit;

begin
    for i in 1 .. 50
    loop
        insert into stock_ticker_heap
        select sym, dt+i, val+i 
          from stock_ticker_heap
         where dt = trunc(sysdate-50);
        commit;
    end loop;
end;
/
*/
pause

clear screen
/*
insert into stock_ticker_iot
select * from stock_ticker_heap;
commit;
exec dbms_stats.gather_table_stats( user, 'stock_ticker_iot', cascade=> true );
exec dbms_stats.gather_table_stats( user, 'stock_ticker_heap', cascade=> true );
*/
pause
set termout off
select avg(val) val
  from stock_ticker_heap
 where sym = :s;
select avg(val) val
  from stock_ticker_iot
 where sym = :s;
select dt, 
       avg(val) over (order by dt rows 5 preceding) val,
       count(*) over ( order by dt rows 5 preceding) cnt
  from stock_ticker_heap
 where sym = :s
   and dt > sysdate-20;
select dt, 
       avg(val) over (order by dt rows 5 preceding) val,
       count(*) over ( order by dt rows 5 preceding) cnt
  from stock_ticker_iot
 where sym = :s
   and dt > sysdate-20;

set termout on

clear screen
select count(*), count(distinct sym), count(distinct dt) 
  from stock_ticker_iot
/
variable s varchar2(4);
begin
   select sym into :s from stock_ticker_heap where rownum = 1;
end;
/
print s
pause

clear screen
set pagesize 999
set pause on
set autotrace on 
select avg(val) val
  from stock_ticker_heap
 where sym = :s;
pause

clear screen
select avg(val) val
  from stock_ticker_iot
 where sym = :s;
pause

clear screen
select dt, 
       avg(val) over (order by dt rows 5 preceding) val,
       count(*) over ( order by dt rows 5 preceding) cnt
  from stock_ticker_heap
 where sym = :s
   and dt > sysdate-20;
pause

clear screen
select dt, 
       avg(val) over (order by dt rows 5 preceding) val,
       count(*) over ( order by dt rows 5 preceding) cnt
  from stock_ticker_iot
 where sym = :s
   and dt > sysdate-20;
pause

clear screen
set pause off
set autotrace off
select segment_name, blocks from user_segments;

@connect /
	

connect /
drop table t;

set echo on
set linesize 121

clear screen
create table t as
select 'Y' processed_flag, a.* from all_objects a;
pause

clear screen
create or replace view v
as
select t.*,
       case when processed_flag = 'N' then 'N'
            else NULL
        end processed_flag_indexed
  from t;

create index t_idx on
t( case when processed_flag = 'N' then 'N'
        else NULL
    end );
pause

clear screen
analyze index t_idx validate structure;

select name, del_lf_rows, lf_rows, lf_blks
  from index_stats;
pause

clear screen
update t set processed_flag = 'N'
  where rownum <= 100;

analyze index t_idx validate structure;

select name, del_lf_rows, lf_rows, lf_blks
  from index_stats;
pause
set termout off
select rowid, object_name
  from v
 where processed_flag_indexed = 'N'
   and rownum = 1;
set termout on
clear screen
set autotrace on
set pause on
select rowid, object_name
  from v
 where processed_flag_indexed = 'N'
   and rownum = 1;
set autotrace off
set pause off
	

set tab off
drop table project;
set echo off

clear screen
prompt The goal is to make TEAMID, JOB unique across ACTIVE projects
prompt
prompt
prompt
set echo on
create table project
(project_ID number primary key,
 teamid number,
 job varchar2(100),
 status varchar2(20) check (status in ('ACTIVE', 'INACTIVE'))
);
pause

clear screen
create UNIQUE index
job_unique_in_teamid on project
( case when status = 'ACTIVE' then teamid else null end,
  case when status = 'ACTIVE' then job    else null end
)
/
pause

clear screen
insert into project 
(project_id, teamid,   job, status) values
(         1,    100, 'job', 'INACTIVE' );
insert into project 
(project_id, teamid,   job, status) values
(         2,    100, 'job', 'INACTIVE' );
pause
insert into project 
(project_id, teamid,   job, status) values
(         3,    100, 'job', 'ACTIVE' );
pause
insert into project 
(project_id, teamid,   job, status) values
(         4,    100, 'job', 'ACTIVE' );
	

drop table t1;
drop table idx_stats;
create or replace function is_number( p_str in varchar2 ) return number
as
	l_number number;
begin
	l_number := p_str;
	return 1;
exception
	when others then return 0;
end;
/
set echo on
clear screen
create table t1
as
select * from dba_objects;
insert /*+ append */ into t1 select * from t1;
commit;
insert /*+ append */ into t1 select * from t1;
commit;
pause

clear screen
create index uncompressed_idx
on t1( owner,object_type,object_name );
analyze index uncompressed_idx validate structure;
create table idx_stats as select * from index_stats;
pause

clear screen
drop index uncompressed_idx;
create index compressed_idx
on t1( owner,object_type,object_name )
COMPRESS 3;
analyze index compressed_idx validate structure;
insert into idx_stats select * from index_stats;
pause

clear screen
column val format a25
select * from idx_stats;
pause

clear screen
select * 
  from table(cols_as_rows('select * from idx_stats where rownum=1'))
 where rownum <= 10;
pause
column cname format a25
column t1 format a20
column t2 format a20
clear screen
set pause on
select a.cname, 
       decode( is_number(a.val),0,a.val,round(a.val,2)) t1, 
       decode( is_number(b.val),0,b.val,round(b.val,2)) t2,
	   case when is_number(a.val) = 1 and is_number(b.val) = 1
	        then to_char( decode(a.val,'0',null,round(b.val/a.val*100,2) ), '9,999.00' )
		 end pct
  from table( cols_as_rows( q'|select * 
                                 from idx_stats 
                                where name = 'UNCOMPRESSED_IDX' |' ) ) a,
       table( cols_as_rows( q'|select * 
                                 from idx_stats 
                                where name = 'COMPRESSED_IDX' |' ) ) b
     where a.cname = b.cname
/
set pause off
	

@connect /

set echo off
drop table compressed
/
drop table uncompressed
/
drop table all_objects_unload
/
!msu -u ora10gr1 rm /tmp/allobjects.dat
set echo on

clear screen
create or replace directory tmp as '/tmp'
/
pause
clear screen
!ls -l /tmp/allobjects.dat
create table all_objects_unload
organization external
( type oracle_datapump
  default directory TMP
  location( 'allobjects.dat' )
)
as
select * from dba_objects
/
!ls -l /tmp/allobjects.dat
pause

clear screen
create table uncompressed
pctfree 0
as
select *
  from all_objects_unload
 order by owner, object_type, object_name;

exec dbms_stats.gather_table_stats( user, 'UNCOMPRESSED' );
pause

clear screen
create table compressed
COMPRESS
as
select *
  from all_objects_unload
 order by owner, object_type, object_name;

exec dbms_stats.gather_table_stats( user, 'COMPRESSED' );
pause

clear screen
select cblks comp_blks, uncblks uncomp_blks,
       round(cblks/uncblks*100,2) pct
  from (
select max(decode(table_name,'COMPRESSED',blocks,null)) cblks,
  max(decode(table_name,'UNCOMPRESSED',blocks,null)) uncblks
  from user_tables
 where table_name in ( 'COMPRESSED', 'UNCOMPRESSED' )
       )
/
pause

clear screen
drop table compressed;
create table compressed
COMPRESS
as
select *
  from all_objects_unload
 order by dbms_random.random;

exec dbms_stats.gather_table_stats( user, 'COMPRESSED' );
pause

clear screen
select cblks comp_blks, uncblks uncomp_blks,
       round(cblks/uncblks*100,2) pct
  from (
select max(decode(table_name,'COMPRESSED',blocks,null)) cblks,
  max(decode(table_name,'UNCOMPRESSED',blocks,null)) uncblks
  from user_tables
 where table_name in ( 'COMPRESSED', 'UNCOMPRESSED' )
       )
/
pause

clear screen
column val format a30
select * from table( cols_as_rows( q'|
select
count(OWNER) OWNER_cnt,
count(distinct OWNER) OWNER_dcnt,
max(vsize(OWNER)) OWNER_sz,
count(OBJECT_NAME) OBJECT_NAME_cnt,
count(distinct OBJECT_NAME) OBJECT_NAME_dcnt,
max(vsize(OBJECT_NAME)) OBJECT_NAME_sz,
count(SUBOBJECT_NAME) SUBOBJECT_NAME_cnt,
count(distinct SUBOBJECT_NAME) SUBOBJECT_NAME_dcnt,
max(vsize(SUBOBJECT_NAME)) SUBOBJECT_NAME_sz,
count(OBJECT_ID) OBJECT_ID_cnt,
count(distinct OBJECT_ID) OBJECT_ID_dcnt,
max(vsize(OBJECT_ID)) OBJECT_ID_sz,
count(DATA_OBJECT_ID) DATA_OBJECT_ID_cnt,
count(distinct DATA_OBJECT_ID) DATA_OBJECT_ID_dcnt,
max(vsize(DATA_OBJECT_ID)) DATA_OBJECT_ID_sz,
count(OBJECT_TYPE) OBJECT_TYPE_cnt,
count(distinct OBJECT_TYPE) OBJECT_TYPE_dcnt,
max(vsize(OBJECT_TYPE)) OBJECT_TYPE_sz,
count(CREATED) CREATED_cnt,
count(distinct CREATED) CREATED_dcnt,
max(vsize(CREATED)) CREATED_sz,
count(LAST_DDL_TIME) LAST_DDL_TIME_cnt,
count(distinct LAST_DDL_TIME) LAST_DDL_TIME_dcnt,
max(vsize(LAST_DDL_TIME)) LAST_DDL_TIME_sz,
count(TIMESTAMP) TIMESTAMP_cnt,
count(distinct TIMESTAMP) TIMESTAMP_dcnt,
max(vsize(TIMESTAMP)) TIMESTAMP_sz,
count(STATUS) STATUS_cnt,
count(distinct STATUS) STATUS_dcnt,
max(vsize(STATUS)) STATUS_sz,
count(TEMPORARY) TEMPORARY_cnt,
count(distinct TEMPORARY) TEMPORARY_dcnt,
max(vsize(TEMPORARY)) TEMPORARY_sz,
count(GENERATED) GENERATED_cnt,
count(distinct GENERATED) GENERATED_dcnt,
max(vsize(GENERATED)) GENERATED_sz,
count(SECONDARY) SECONDARY_cnt,
count(distinct SECONDARY) SECONDARY_dcnt,
max(vsize(SECONDARY)) SECONDARY_sz
from all_objects_unload
|' ) );
pause


clear screen
drop table compressed;
create table compressed
COMPRESS
as
select *
  from all_objects_unload
 order by timestamp, owner, object_type, object_name;

exec dbms_stats.gather_table_stats( user, 'COMPRESSED' );
pause

clear screen
select cblks comp_blks, uncblks uncomp_blks,
       round(cblks/uncblks*100,2) pct
  from (
select max(decode(table_name,'COMPRESSED',blocks,null)) cblks,
  max(decode(table_name,'UNCOMPRESSED',blocks,null)) uncblks
  from user_tables
 where table_name in ( 'COMPRESSED', 'UNCOMPRESSED' )
       )
/
	
 

	    

			

				

					
Please
         				visit our sponsors!

         				They help OOUG bring quality speakers and
         				resources to you.
         		
         		

					
Platinum
					Sponsors