Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Simple performance question regarding INSERT statement
"Russ Reynolds" <rrey2279_at_aol.com> wrote in message
news:2a73e567.0411120735.a80672_at_posting.google.com...
> Let's say you have a table:
>
> CREATE TABLE products
> (
> prod_id int(10),
> prod_name varchar(30),
> prod_desc varchar(100),
> prod_category varchar(10),
> active boolean
> )
>
> Is performance affected if the columns in your INSERT statements are
> not in-line with the CREATE TABLE declarations. For example,
>
> --Column list out of order
> INSERT INTO products (active, prod_id, prod_category, prod_desc,
> prod_name)
> VALUES (TRUE, 100001, 'Outdoor', 'Best Lawnmower Available',
> 'Lawnmower');
>
> Let's say you were going to do 100,000 INSERTS at one time. Will
> performance be degraded, and to what degree?
Russ,
The biggest impact by far will be if you do not use bind variables and you
do not use the array interface. Use bind variables and don't worry about
the columns in the table and the columns in the insert clause being in
different orders. The lack of bind variables will FAR outweigh any possible
performance difference from you example. Also you should submit a large
number of rows at a time via the array interface. (less network trips) And
commit at the end, not after each row.
Jim
Received on Fri Nov 12 2004 - 20:00:33 CST