NATURAL JOIN Explained

A NATURAL JOIN, allows for tables to be joined on common column names
without explicitly naming the columns.


For example, the following two tables; share  the STATISTIC# columns
SQL> desc v$sesstat;
 Name                  
 --------------------
 SID                   
 STATISTIC#            
 VALUE                 

SQL>
SQL> desc v$statname
 Name                  
 --------------------
 STATISTIC#            
 NAME                  
 CLASS                 
 STAT_ID               


So when the following SQL is run, the tables are joined on STATISTIC#;

select max(value)
from v$sesstat natural join v$statname
where name = 'session cursor cache count';



The same applies to the following statement;

select Amount
      ,Session_Cached_Cursors*30+30 Cached_Cursors_Rounded
from
    (
     select trunc(value/30)  as Session_Cached_Cursors
           ,count(*)         as Amount
     from  
            v$sesstat natural join v$statname
     where  name = 'session cursor cache count'
     group by trunc(value/30) order by 1
    )
/

Validating a number as an integer

set serveroutput on
declare
   l_Char_Num varchar2 (40) := '&Enter_Number';
   i          number;
begin
  i := to_number (l_Char_Num);
  if ( i = trunc(i) ) then
    dbms_output.put_line (i ||' is an Integer');
  else
    dbms_output.put_line (i ||' is not an Integer');
  end if;
  exception
    when others then
      dbms_output.put_line ('Not an Integer');
end proc;
/
Enter value for Enter_Number: 40.6
old   2:    p_search varchar2 (40) := '&num';
new   2:    p_search varchar2 (40) := '40.6';

40.6 is not an Integer


However, when run with -40, outcome is;

Enter value for Enter_Number: -40
old   2:    p_search varchar2 (40) := '&num';
new   2:    p_search varchar2 (40) := '-40';

-40 is an Integer

Format large number to spaces and commas

/*
to convert this number;
-13237836,2
to
-13 237 836,20.

*/
select to_char(-13237836.2
              ,'999g999g999d90'
              ,'nls_numeric_characters='', ''') Format_Number
from dual
/
 

Compress CLOB

create table t1 (c clob);
alter table t1 move lob (c) 

store as securefile compressed_lob (compress high);

Analytic functions with Tabibitosan

/*
The requirement for this data;

    4   'pk'
    5,  null  
    6,  null  
    9,  'jk'   
    13, 'jk'   
    14, null

is that a Group identifier be associated with each id,
where the id's form a series.
For example, rows 4, 5 and 6 would form Group 1
whereas 9 would form a group on its own, since
no id precedes it or follows it in a series.
Finally, 13 and 14 would form the final group.

The technique is known as Tabibitosan and has
been well documented by Aketi Jyuuzou in the following thread
https://forums.oracle.com/forums/thread.jspa?threadID=1005478&start=0&tstart=0

*/


-- This is merely an example of the technique;

with t as
(
select 4 id, 'pk' lk from dual union all
select 5,  null  from dual union all
select 6,  null  from dual union all
select 9,  'jk'   from dual union all
select 13, 'jk'   from dual union all
select 14, null  from dual
)
select id, lk, 'LKG'|| dense_rank() over (order by sgroup) lkg_grp
from
    (
    select   id
            ,lk
            ,id - row_number () over (order by id) sgroup
    from t)
order by id;

 ID LK LKG_GRP
--- -- -------
  4 pk LKG1
  5    LKG1
  6    LKG1
  9 jk LKG2
 13 jk LKG3
 14    LKG3
 

Show todays date in Arabic

select  to_char(sysdate, 'DD-Mon-YYYY')                     Today
       ,to_char(sysdate,'dd mon rrrr'
                      ,'nls_calendar = ''english hijrah''') Arabic_Day
from dual
/
TODAY                ARABIC_DAY
-------------------- -------------------------
22-Sep-2011          23 Shawwal           1432

Compound trigger example

create table Emp (empno number, mngno number);

create table Mgr (mngno number, emp_count number);


insert into Mgr (mngno, emp_count) values (1, 0);

insert into Mgr (mngno, emp_count) values (2, 0);

commit;

create type ty_Emp as object (empno number, mngno number);
/

create type tb_Emp as table of ty_Emp;
/
create trigger tr_Set_Emp_Cnt
for insert on Emp compound trigger

  v_tb_Emp tb_Emp := tb_Emp();
  l_Cnt    pls_integer;

  ---- Row part of the compound trigger
  before each row is
  begin
    v_tb_Emp.extend(1);
    v_tb_Emp(v_tb_Emp.last) :=  ty_Emp(:new.EmpNo, :new.MngNo);

  end before each row;

  -- Statement part of the compound trigger
  after statement is
  begin

    update Mgr m
    set m.Emp_Count = m.Emp_Count + (select count(*)
                                    from   table(v_tb_Emp) e
                                    where  e.MngNo = m.MngNo)
    where  m.MngNo in (select distinct e.MngNo
                       from   table(v_tb_Emp) e);

  end after statement;

end tr_Set_Emp_Cnt;
/

insert into emp (empno, mngno)
(select 1,1 from dual union all
 select 2,1 from dual
);

insert into emp (empno, mngno)
(select 3,2 from dual union all
select 4,2  from dual
);

insert into emp (empno, mngno)
(select 5,1 from dual union all
 select 6,1 from dual
);

select * from mgr;

MNGNO  EMP_COUNT
----- ----------
    1          4
    2          2

Index rebuild after compress/uncompress

create table t1 (a number) compress
/
-- Either compress or uncompress
alter table t1 move nocompress nologging parallel 4;
alter table t1 move compress nologging parallel 4;


/*
Note, indexes have to be rebuilt after either dynamically decompressing or compressing
because the blocks are changed (located elsewhere)

For example, for each index on T1, rebuild
*/

alter session force parallel query;

alter system set parallel_max_servers = 8;

alter session enable parallel ddl;

alter index indexname rebuild parallel 4 nologging;

alter session disable parallel ddl;

Calculating Age and grouping into Age groups

-- Grouping people in a specific age group
with t as
(
select 'john' name,to_date('01.01.1990','dd.mm.yyyy') birth_date from dual union
select 'sue',      to_date('02.05.2000','dd.mm.yyyy')  from dual union
select 'abraham',  to_date('04.07.1998','dd.mm.yyyy')  from dual union
select 'Tim',      to_date('15.09.1998','dd.mm.yyyy')  from dual union
select 'Rob',      to_date('16.09.1998','dd.mm.yyyy')  from dual union
select 'Mary',     to_date('14.09.1990','dd.mm.yyyy')  from dual union
select 'Larry',    to_date('16.09.1995','dd.mm.yyyy')  from dual union
select 'Sally',    to_date('16.09.1965','dd.mm.yyyy')  from dual
)
--
select age_group , count(*)
from
(
  select
    case
      when trunc( months_between(sysdate, birth_date) / 12 ) <= 17 then
        '0-17'
      when trunc( months_between(sysdate, birth_date) / 12 ) <= 24 then
        '18-24'
      when trunc( months_between(sysdate, birth_date) / 12 ) <= 34 then
        '25-34'
      when trunc( months_between(sysdate, birth_date) / 12 ) <= 44 then
        '35-44'
      when trunc( months_between(sysdate, birth_date) / 12 ) <= 54 then
        '45-54'
      else 'n/a'
    end age_group
  from t
)
group by age_group
order by age_group;

Using NULLIF with Analytic functions

-- No zeros wanted in output, instead, must always get the previously non-zero value
with t as
(
select 'a' col1, 1 col2 from dual union all
select 'b', 5 from dual union all
select 'c', 0 from dual union all
select 'd', 0 from dual union all
select 'e', 3 from dual union all
select 'f', 8 from dual union all
select 'g', 0 from dual
)
--
select col1
      ,last_value(nullif(col2,0) ignore nulls)  over (order by col1) col2
from t
order by col1
/

C       COL2
- ----------
a          1
b          5
c          5
d          5
e          3
f          8
g          8 

Parallel primary key index rebuild

-- Trick to build a primary key in parallel
create table t1 (a number, b number);

alter table t1 nologging;

alter session enable parallel ddl;
create unique index t1_idx on t1 (a,b) parallel 12 nologging;
alter session disable parallel ddl;

-- Once index has completed rebuilding, assign to primary key
alter table t1 add constraint t1_pk primary key (a,b) using index t1_idx;

alter table t1 logging;

Deferred constraints

DEFERRED forces the check to occur on COMMIT, whereas IMMEDIATE forces the check to occur on INSERT.

The following is an example;

SQL> create table t1 ( a number, b number);

Table created.

SQL> alter table t1 add constraint t1_pk primary key (a);

Table altered.

SQL> create table t2 (a number);

Table created.

SQL> alter table t2 add constraint t2_fk
  2  foreign key (a) references t1 (a)
  3  deferrable initially immediate;
 
Table altered.

SQL> insert into t1 values (1,1);

1 row created.

SQL> insert into t2 values (2);
insert into t2 values (2)
*
ERROR at line 1:
ORA-02291: integrity constraint (XDB_DEV3.T2_FK) violated - parent key not found

SQL> alter session set constraints = deferred;

Session altered.

SQL> insert into t2 values (2);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (XDB_DEV3.T2_FK) violated - parent key not found

Foreign Keys

create table contact (id number
                     ,referred_by number
                     ,user_refer_type varchar2(1)
                     ,prospect_refer_type varchar2(1));

create table users(id number, refer_type varchar2(1) default 'U');

create table prospect(id number,refer_type varchar2(1) default 'P');

alter table users    add constraint u_pk primary key(id,refer_type);
alter table prospect add constraint p_pk primary key(id,refer_type);

alter table contact add constraint c1_fk foreign key
(referred_by, user_refer_type) references users (id, refer_type);

alter table contact
add constraint c2_fk foreign key (referred_by, prospect_refer_type)
references prospect (id, refer_type);

insert into users (id) values (1);

insert into prospect (id) values (2);

insert into contact (id, referred_by, user_refer_type)
values (1, 1, 'U');

insert into contact (id, referred_by, prospect_refer_type)
values  (2, 2, 'P');

insert into contact (id) values  (3);

Mandatory/optional property of FK is related to mandatory/optional property of FK columns:

Mandatory FK - FK columns will be "not null"

Optional  FK - FK columns will allow nulls

Creating a reverse index primary key

-- Creating a reverse index primary key

create table t1 (a number, b number);

create unique index t1_idx on t1 (a,b) reverse;

alter table t1 add constraint t1_pk primary key (a,b) using index t1_idx;

/*
Creating a descending index is different to a reverse index.
Descending indexes are function-based using SYS_OP_DESCEND.
Function based indexes cannot be applied to primary keys
*/

SQL> create table t1 (a number, b number);

Table created.

SQL> create unique index t1_idx on t1 (a desc, b desc);

Index created.

SQL> alter table t1 add constraint t1_pk primary key (a,b) using index t1_idx;
alter table t1 add constraint t1_pk primary key (a,b) using index t1_idx
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.