Re: Maximun number of unions in a single query

From: <BigBoote66_at_hotmail.com>
Date: 10 Jun 2005 08:10:57 -0700
Message-ID: <1118416257.688832.181170@g49g2000cwa.googlegroups.com>

> My results differ. Can you post your test case?

> Also 5000 inserts taking 16 seconds seem to be excessively slow...
> unless this is a very busy of very old Sun platform? Or am I spoiled
> with HP and Sun/AMD hardware? :-)

We prefer the term "mature" :-).

Actually, the machine I test on is just one of our development servers - an Enterprise 420 (4 450Mhz Sparc processors), 4GB RAM, with all our Oracle datafiles on a single RAID5 volume. Not using raw devices, or even direct IO, so it's not exactly built for speed.

As for the test case, I used this table:

create table smb (col1 number, col2 varchar2(20), col3 number, col4 number);

(no indexes)

and these two files:

many.sql:

set feedback off

insert into smb values ( 1 , 'abc1',  10 , 15 );
insert into smb values ( 2 , 'abc2',  20 , 30 );
insert into smb values ( 3 , 'abc3',  30 , 45 );
insert into smb values ( 4 , 'abc4',  40 , 60 );

[...]

insert into smb values ( 4998 , 'abc4998',  49980 , 74970 );
insert into smb values ( 4999 , 'abc4999',  49990 , 74985 );
insert into smb values ( 5000 , 'abc5000',  50000 , 75000 );

commit;
truncate table smb;
exit;

and few.sql:

set feedback off
insert into smb

select  1 , 'abc1',  10 , 15 from dual union all
select  2 , 'abc2',  20 , 30 from dual union all
select  3 , 'abc3',  30 , 45 from dual union all
select  4 , 'abc4',  40 , 60 from dual union all

[...]

select  4998 , 'abc4998',  49980 , 74970 from dual union all
select  4999 , 'abc4999',  49990 , 74985 from dual union all
select  5000 , 'abc5000',  50000 , 75000 from dual;

commit;
truncate table smb;
exit;

and I ran them through sqlplus the normal way:

time sqlplus -s smb_asmldtm_250/smb_asmldtm_250_at_paradox.world @few

time sqlplus -s smb_asmldtm_250/smb_asmldtm_250_at_paradox.world @many

and did it several times in order to eliminate any issues regarding caches & SGA. The PC I used was just a Dell with a 700Mhz Pentium III.

few.sql (the union one) was the slowpoke, taking 44 seconds, many.sql (just plain inserts) was the faster one (16 seconds). As you can see, no bind variables, so lots of parsing.

However, I'd think that all of these factors that make our box slow (no binds, slow physical i/o, pokey processors) would, if anything, make the fast case seem slower. On faster hardware, I would expect the difference between the two cases to be even greater.

-Steve Received on Fri Jun 10 2005 - 10:10:57 CDT