Re: Maximun number of unions in a single query

Andreas Sheriff wrote:
> Look, man,
> We shall not mention he who should not be mentioned.

Er.. who were we talking about again? ;-)

> If you want to mix up a test case, be my guest. We would all like to see
> your results.

Yep, and the results are interesting. I am wrong. I missed the fact that the UNION ALL method will be a *single* hard parse. Now that is always faster than a another hard parse (even with a much smaller statement), followed by a bunch of soft parses.

Here's the results:
==
SQL> @test-insert

Table dropped.

Table created.

timing for: UNION INSERT
Elapsed: 00:00:00.81

timing for: PLAIN INSERT
Elapsed: 00:00:01.24
==

I've turned on cursor forced sharing to do "soft parsing" - but there's still an overhead as Oracle needs to turn the SQL statement into a proper bind var statement. Even so, this will at least make both methods use shareable SQL and not trash the shared pool.

A 1000 rows were inserted in both cases. Table truncated before each test to reset the HWM.

Here's the script.
==
drop table my_table;
create table my_table ( col1 number, col2 varchar2(20), col3 varchar2(100) );

set heading off
set linesize 100
set trimspool on
set pages 0
set feedback off;
set echo off
set termout off

spool test-union-insert.sql
select 'INSERT INTO my_table ' as SQL_STATEMENT from dual union all
select

        'SELECT
'||NVL(object_id,0)||','''||object_type||''','''||object_name||''' FROM dual UNION ALL'
from user_objects
where rownum < 1000
union all
select

        'SELECT
'||NVL(object_id,0)||','''||object_type||''','''||object_name||''' FROM dual;'
from user_objects
where rownum = 1;
spool off;

spool test-plain-insert.sql
select

        'INSERT INTO my_table VALUES(
'||NVL(object_id,0)||','''||object_type||''','''||object_name||''' );' as SQL_STATEMENT
from user_objects
where rownum < 1000;
spool off;

set termout on
alter session set cursor_sharing=FORCE;

truncate table my_table;

TIMING START "UNION INSERT"
@test-union-insert.sql
TIMING SHOW truncate table my_table;

TIMING START "PLAIN INSERT"
@test-plain-insert.sql
TIMING SHOW
==

Okay, so my argument is legless. Which mean you will have to saunter over to the barcounter and buy the next round? ;-)

--
Billy