really -- do you have hard numbers to quantify this "performance difference"?

Funny thing is that all numbers are -- well -- numbers in Oracle.

A number(38) is not stored any differently then a number(12).

A number(38) is not "converted" to a number(12) for comparision. A number(12) is not "converted" to a number(38) for comparision.

It is not like comparing a C int to a C long or a float to a double...

Consider this test -- p1(number(12)) -> c1(number)
p2(number(12)) -> c2(number(12))

we'll compare:

o bulk insert
o singleton inserts
o bulk delete from parent (with on delete cascade)
o singleton delete from parent (with on delete cascade)

The script is:

create table p1 ( x number(12) primary key);
create table c1 ( x number references p1 on delete cascade);
create index c1_idx on c1(x);

create table p2 ( x number(12) primary key);
create table c2 ( x number(12) references p2 on delete cascade );
create index c2_idx on c2(x);


insert into p1 select rownum from all_objects;
insert into p2 select rownum from all_objects;
commit;

alter session set sql_trace=true;
insert into c1 select * from p1;
insert into c2 select * from p2;

begin
for x in ( select * from p1 )
loop
for i in 1 .. 5
loop
insert into c1 values ( x.x );
end loop;
commit;
end loop;
end;
/
begin
for x in ( select * from p2 )
loop
for i in 1 .. 5
loop
insert into c2 values ( x.x );
end loop;
commit;
end loop;
end;
/
delete from p1 where mod(x,2) = 0;
commit;

begin
for x in ( select * from p1 )
loop
delete from p1 where x = x.x;
end loop;
end;
/
commit;



delete from c2 where x < 12000;
commit;

delete from p2 where mod(x,2) = 0;
commit;

begin
for x in ( select * from p2 )
loop
delete from p2 where x = x.x;
end loop;
end;
/
commit;


And tkprof says.....



insert into c1 select * from p1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 1.64 1.74 0 242 49429 23939
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.65 1.75 0 242 49429 23939

insert into c2 select * from p2


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.72 1.78 0 239 49427 23939
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.72 1.78 0 239 49427 23939


well, for the bulk insert, C1 and C2 are for all intents and purposes - the same! a difference of 0.07 cpu seconds is not meaningful here (run a couple of times anc c2 would best c1 sometimes, c1 best c2 the others). Not only that -- but C1 is the NUMBER type and C2 is the NUMBER(12)...

Now, onto the single row inserts:


INSERT INTO C1 VALUES ( :b1 )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 119695 36.61 38.81 0 1205 657138 119695
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 119696 36.61 38.81 0 1205 657138 119695

********************************************************************************
INSERT INTO C2 VALUES ( :b1 )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 119695 37.66 38.90 0 1210 657146 119695
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 119696 37.66 38.90 0 1210 657146 119695


Once again, no measurable difference (although C1 again bests C2 -- maybe there is in fact a slight edge to being unconstrained??)

Looking at the deletes, bulk delete first:


delete from p1 where mod(x,2) = 0

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 3.29 4.41 61 40 66705 11969
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3.29 4.41 61 40 66705 11969


delete from p2 where mod(x,2) = 0

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 3.55 5.04 276 40 66699 11969
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3.55 5.04 276 40 66699 11969

well, no smoking gun there... looking at single row deletes:

DELETE FROM P1 WHERE X = :b1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 11970 8.24 8.08 0 23956 818445 11970
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11971 8.24 8.08 0 23956 818445 11970

********************************************************************************
DELETE FROM P2 WHERE X = :b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 11970 8.09 8.65 0 23956 818471 11970
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11971 8.10 8.65 0 23956 818471 11970

Finally -- C2 bested C1 -- but not by much and it is totally arbitrary


So, my suggestions:

a) benchmark these things, get hard and fast numbers before you go about doing long painful changes

b) stop doing your conversion -- it is quite simply "not worth it"