Re: Maximun number of unions in a single query

From: Andreas Sheriff <spamcontrol_at_iion.com>
Date: Thu, 9 Jun 2005 07:23:59 -0700
Message-ID: <aiYpe.9541$tr.4141@fed1read03>

Yeah, you were right, but I just wanted to know the upper limit. + I'm still concerned with the network overhead of sending multiple insert statements (ColdFusion and binding arrays is a bust).

See results of EXPLAIN PLAN FOR below.

SQL> explain plan for
2 insert into test(col1, col2, col3)

  3  select 1, 2, 3 from dual union all
  4  select 4, 5, 6 from dual union all
  5  select 7, 8, 9 from dual union all

6 select 10, 11, 12 from dual;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

| 0 | INSERT STATEMENT | | 4 | | 8 |
| 1 | UNION-ALL | | | | |
| 2 | TABLE ACCESS FULL | DUAL | 1 | | 2 |
| 3 | TABLE ACCESS FULL | DUAL | 1 | | 2 |
| 4 | TABLE ACCESS FULL | DUAL | 1 | | 2 |
| 5 | TABLE ACCESS FULL | DUAL | 1 | | 2 |

SQL> explain plan for
2 insert into test(col1, col2, col3)
3 values(1,2,3);

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

| 0 | INSERT STATEMENT | | 1 | 39 | 2 |

-- 

Andreas Sheriff (Oracle Certified DBA and PL/SQL Developer)
----
"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding, if you don't eat your meat?"

If this is the first email to me,
please place NOSPAM somewhere
in the subject.

"Billy" <vslabs_at_onwe.co.za> wrote in message
news:1118299161.221485.216970_at_g49g2000cwa.googlegroups.com...
> Andreas Sheriff wrote:
> > As for mendicant, yes I did mean the lexical definition.  To call
someone an
> > a$$h0le does not abide by the geek's book of insults.
>
> Yeah, but that book does not count on Usenet as you should well know.
> Or have you forgotten about Godwin's Law? Sheez!
>
> > I'm working with an application that does multiple inserts into a table.
> <snipped>
> > Here's what I want to accomplish:
> >
> > insert into mytable(col1, col2, col3, col4)
> > select  :1,  :2,   :3,    :4 from dual union all
> > select  :5,  :6,    :7,   :8 from dual union all
> > select  :9, :10, :11, :12 from dual union all
> > select :13, :14,:15, :16 from dual union all
> > etc...
> >
> > It seems to me that this method of inserting data into a table using one
> > insert statement
> > would be a hell-of-a-lot better than doing multiple insert statement
between
> > <cfquery></cfquery>.
>
> Nope. I cannot see that as being any faster. How much parsing is that,
> compare to a single hard parse of:
> insert into mytable( :col1, :col2, :col3, :col4)
>
> Followed by a soft parse (assuming no statement handle re-use on the
> client side) for every following INSERT statement.
>
> Better yet would be a single parse and passing the bind variable data
> as an array (for bulk binding on the SQL engine side).
>
> Also not forgetting the execution path. The INSERT is still
> called/execxuted x times irrespective. Only with the UNIONs, there is
> also a SELECT that is called x times. Why the SELECT overhead when you
> can simply execute the INSERT x times?
>
> Please say you agree or you will force me to spend time setting upo a
> test case to show you are wrong.
>
> > Now you know the reasoning behind my question and now you *all* can stop
the
> > flaming, or have I poured kerosene on the whole situation?
>
> Flaming!? This is not even the size of small birthday candle flame.
>
> --
> Billy
>

Received on Thu Jun 09 2005 - 09:23:59 CDT