Hi !

A quick question from a danish oracle newbie.

I have 2 tables, and i want to update a field in table t1 with the sum
of a field in table t2.

The tables are BIG, so therefore i want the statement to be a
effective as posible.

I have made 2 statements, shown below. Which one should i use ?

Statement 1:
UPDATE table1 t1 SET t1.TotalQty = (Select sum(t2.Qty)FROM table2 t2
WHERE t1.itemnumber=t2.itemnumber)

or

Statement 2:
UPDATE table1 t1 SET t1.TotalQty = (Select sum(t2.Qty)FROM table2 t2
WHERE t1.itemnumber=t2.itemnumber) WHERE t1.TotalQty != (Select
sum(t2.Qty)FROM table2 t2 WHERE t1.itemnumber=t2.itemnumber)


Will oracle make unnessesary updates if i use statement 1, or will it
actually jus update the rows, were t1.TotalQty != (Select
sum(t2.Qty)FROM table2 t2 WHERE t1.itemnumber=t2.itemnumber)

Thanks in advance, for any help given to me.


Regards, Ole


By the way - if anyone nows some good webpages, with sql-tutorials,
please let me know...

Statement 1 works just fine. Table t1 should have a primary key or
other index on the itemNumber column for best performance.

Jim

On 16 May 2005 12:04:22 -0700, ohahaahr (AT) hotmail (DOT) com (Ole Hansen)
wrote:

Oracle isn't smart enough to skip redundant updates.
However, your additional where clause in statement 2 to avoid those,
add so much inefficiency to the statement, that statement 2 would use
way more resources than statement 1, so your attempt to play smart on
behalf of Oracle will hit you back in the face.


--
Sybrand Bakker, Senior Oracle DBA

Ole Hansen wrote:
I would want that if the tables each contained only a single row.
Quite simply the one that after creating all appropriate indexes,
and running dbms_stats, is most efficient of time and resources.

Run EXPLAIN PLAN and/or AUTOTRACE. A DBA where you work will no
doubt smile when you ask for help doing this as most DBAs despise
developers that don't do this on every SQL statement they write.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Hi,

Firstly, Statement 2 will save you lots of rollback/undo space & redo which
may or may not be important, so you could try statement 3 to have the same
effect of only updating records that need updating withouth the cost of a
second correlated subquery.

Statement 3:
UPDATE table1 t1 SET t1.TotalQty = (Select sum(t2.Qty)FROM table2 t2
WHERE t1.itemnumber=t2.itemnumber
having sum(t2.Qty) != t1.TotalQty )

But equally important may be to eliminate rows where there is no
correlation. I.e. Statements 1, 2 and (my) statement 3 will update rows in
table1 setting TotalQty to null if there is no corresponding itemnumber in
table2. This may be what you are after, but if not, you should add a WHERE
clause to the update (as opposed to the subquery) for example WHERE
t1.item_number in (select itemnumber from table2).

Lastly, if this table can simply be rebuilt, it may be less expensive (but
you will have to decide how appropriate), especially on rollback space and
elapsed time, to:
truncate table table1;
insert into table1 (itemnumber, TotalQty) select itemnumber,sum(TotalQty)
from table2;

Perhaps you could even consider an Append hint for the insert and a
parallel(table2,x) hint for the the select clause (where x is an appropriate
degree of paralleism for the number of CPU's and the IO thoughput), and even
consider creating table1 NOLOGGING.


Statement 1 will update all rows of table t1. If there is no correlated row
in table t2
"Ole Hansen" <ohahaahr (AT) hotmail (DOT) com> wrote

DA Morgan <damorgan (AT) psoug (DOT) org> wrote

Hi !

Unfortunately we don't have an internal DBA in our company. We use an
external oracle-consultant. And everytime i have to call him, i have
to pay :-)

Our oracle database is quite small (20 Gb), and the application using
it is Microsoft Business solutions XAL (Formerly known as Navision XAL
or Concorde XAL)

XAL has its own query language (eXtended Application Language - XAL),
so therefore it's not nessesary to have an internal DBA or
SQL-programmer.

BUT - sometimes the XAL query language is to slow for our queries, and
then we are forced to use SQL. There are also some other limitations
in the XAL-language.

I have been an XAL-programmer for 10 years, but i am quite new to SQL,
so therefore i sometime posts theese newbie-questions.

(This should also clear the misunderstanding of me beeing a student,
who don't want to do his homework himself. I graduated many years ago
:-))

I am not shure how to make an explain plan. I have recently purchased
TOAD, but i am not sure, if i can make an explain plan there.

Perhaps someone can help ?

Regards,

Ole

Comments embedded.

On 19 May 2005 22:06:10 -0700, ohahaahr (AT) hotmail (DOT) com (Ole Hansen)
wrote:

You contradict yourself. On the one hand you state 'unfortunately' you
don't have a DBA, on the other hand you state you don't need him/her.
Obviously, the decision not to hire a DBA is pennywise poundfoolish,
especially as you are using a third party product to query the
database. And what if your database crashes?


Just hit ctrl-e, and you will get an explain plan window for the
current sql statement.
You may need to set appropiate options for the location of the
plan_table, you can do this by hitting View Options

--
Sybrand Bakker, Senior Oracle DBA

Ole Hansen wrote:
Everything Sybrand said about being pennywise ...

About Explain Plan ...
http://tahiti.oracle.com
or
http://www/psoug.org
click on Morgan's Library
click on Explain Plan
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Sybrand Bakker <postbus (AT) sybrandb (DOT) demon.nl> wrote


crash, and i hope i newer will. If our database crashes, we have a
4-hours on-site-support agreement. Hopefully a DBA will arrive within
4 hours, and solve the problem.

This means, that for 4 hours, our ERP-program will not run, which
means no invoicing, no orderprocessing, no pick jobs in the warehouse
etc. It will probaly cost us several thousand euros in lost workhours,
since we are 500 employees in the company. But our management thinks,
that this is an OK solution.

So that's the way it is.


Regards,

Ole

Ole Hansen wrote:
Your assumption that "NEED DBA" equates with "CRASH" is woefully
incorrect. If crash recovery is the only thing anyone needed a DBA
for ... there wouldn't be more than a handful of them on the planet:
You folks should rethink this assumption before reality stomps on
you with both feet.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)