The table MyTable contains around 3,000,000 + records. I have the following
update statement which takes around 3 hours to execute. Could any body give
me some information on how to speed up the process? Oracle version 8.1.7.
Sort_area_size = 50MB. PK=fldy, index on fldx is also availble.


UPDATE MyTable t
SET (
t.fld1-1,
t.fld1-2,
t.fld1-3,
t.fld1-4,
t.fld1-5,
t.fld1-6
) =
(SELECT u.fld2-1
u.fld2-2,
u.fld2-3,
u.fld2-4,
u.fld2-5,
u.fld2-6
FROM MyTable u
WHERE t.fldx = u.fldy
);

thanks and regards,
DGK

DGK wrote:

> The table MyTable contains around 3,000,000 + records. I have the following
> update statement which takes around 3 hours to execute. Could any body give
> me some information on how to speed up the process? Oracle version 8.1.7.
> Sort_area_size = 50MB. PK=fldy, index on fldx is also availble.
>
>
> UPDATE MyTable t
> SET (
> t.fld1-1,
> t.fld1-2,
> t.fld1-3,
> t.fld1-4,
> t.fld1-5,
> t.fld1-6
> ) =
> (SELECT u.fld2-1
> u.fld2-2,
> u.fld2-3,
> u.fld2-4,
> u.fld2-5,
> u.fld2-6
> FROM MyTable u
> WHERE t.fldx = u.fldy
> );
>
> thanks and regards,
> DGK

Assuming it is patched to 8.1.7.4 and that you already know that it is
beginning desupport in just three months ...

1. Gather current statistics with DBMS_STATS
2. Run an explain plan
3. Create or modify indexes as indicated

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

"Daniel Morgan" <damorgan@x.washington.edu> wrote in message
news:1096089350.128632@yasure...
> DGK wrote:
>
> > The table MyTable contains around 3,000,000 + records. I have the
following
> > update statement which takes around 3 hours to execute. Could any body
give
> > me some information on how to speed up the process? Oracle version
8.1.7.
> > Sort_area_size = 50MB. PK=fldy, index on fldx is also availble.
> >
> >
> > UPDATE MyTable t
> > SET (
> > t.fld1-1,
> > t.fld1-2,
> > t.fld1-3,
> > t.fld1-4,
> > t.fld1-5,
> > t.fld1-6
> > ) =
> > (SELECT u.fld2-1
> > u.fld2-2,
> > u.fld2-3,
> > u.fld2-4,
> > u.fld2-5,
> > u.fld2-6
> > FROM MyTable u
> > WHERE t.fldx = u.fldy
> > );
> >
> > thanks and regards,
> > DGK
>
> Assuming it is patched to 8.1.7.4 and that you already know that it is
> beginning desupport in just three months ...
>
> 1. Gather current statistics with DBMS_STATS
> 2. Run an explain plan
> 3. Create or modify indexes as indicated
>
> --
> Daniel A. Morgan
> University of Washington
> damorgan@x.washington.edu
> (replace 'x' with 'u' to respond)
>

You make DEsupported sound like UNsupported. Sometimes it's just not
possible to keep up with the very latest versions. Sometimes what you have
works perfectly. We just finished upgrading to 8i from 7.3.4, and guess
what- we are still alive and working just fine. Do you buy a new car on
every model change just because the manufacturer says you really need a new
one, and they are not making any improvements to the current model? Sure,
eventually you need a new one- but that's when YOU decide you need one- and,
more importantly, can afford one.

Alan wrote:

> You make DEsupported sound like UNsupported. Sometimes it's just not
> possible to keep up with the very latest versions. Sometimes what you have
> works perfectly. We just finished upgrading to 8i from 7.3.4, and guess
> what- we are still alive and working just fine. Do you buy a new car on
> every model change just because the manufacturer says you really need a
> new one, and they are not making any improvements to the current model?
> Sure, eventually you need a new one- but that's when YOU decide you need
> one- and,
> more importantly, can afford one.

One minor glitch in the analogy:

Now let's say you use that car for business. If that translates to "it's in
for repairs ... no car, no income", your urgency to update to a newer model
might be somewhat greater.

And then there's the "I can support it myself" argument, which involves a
business crisis "what do I do ... earn money or fix the car?".

Apparently your organization has an interesting business model ... let's get
a newer 2000 model car (8.1.7) to replace the old one, even if the 2002
model (9.2.0) is: the same price; is known to be more fuel efficient; and
is less expensive to repair. Interesting!

Not arguing - just providing some alternate thought processes. I've
frequently been around businesses that think like yours. (And yes, there
are 'valid' reasons for sticking with 8.1.7, like the vendor hasn't
upgraded.)

/Hans

"Hans Forbrich" <news.hans@telus.net> wrote in message
news:6yV5d.121773$KU5.56721@edtnps89...
> Alan wrote:
>
> > You make DEsupported sound like UNsupported. Sometimes it's just not
> > possible to keep up with the very latest versions. Sometimes what you
have
> > works perfectly. We just finished upgrading to 8i from 7.3.4, and guess
> > what- we are still alive and working just fine. Do you buy a new car on
> > every model change just because the manufacturer says you really need a
> > new one, and they are not making any improvements to the current model?
> > Sure, eventually you need a new one- but that's when YOU decide you need
> > one- and,
> > more importantly, can afford one.
>
> One minor glitch in the analogy:
>
> Now let's say you use that car for business. If that translates to "it's
in
> for repairs ... no car, no income", your urgency to update to a newer
model
> might be somewhat greater.
>
> And then there's the "I can support it myself" argument, which involves a
> business crisis "what do I do ... earn money or fix the car?".
>
> Apparently your organization has an interesting business model ... let's
get
> a newer 2000 model car (8.1.7) to replace the old one, even if the 2002
> model (9.2.0) is: the same price; is known to be more fuel efficient; and
> is less expensive to repair. Interesting!

It's more like "We bought the 2000 model brand new, and it's still running,
you know how to fix it if it breaks, and you are cheaper than buying a new
one."

>
> Not arguing - just providing some alternate thought processes. I've
> frequently been around businesses that think like yours. (And yes, there
> are 'valid' reasons for sticking with 8.1.7, like the vendor hasn't
> upgraded.)

That is part of the reason also.

>
> /Hans

Alan wrote:

> It's more like "We bought the 2000 model brand new, and it's still
> running, you know how to fix it if it breaks, and you are cheaper than
> buying a new one."
>

LOL

Can I assume you have an Oracle support contract? IF so, then back to the
analogy: "you can trade up at any time, no additional charge. Just spend
some time learning how to take advantage of the new fuel efficiencies."

(You _would_ spend some time understanding the different features of a newer
car, wouldn't you?)

/Hans

"Hans Forbrich" <news.hans@telus.net> wrote in message
news:gTW5d.165322$XP3.88606@edtnps84...
> Alan wrote:
>
> > It's more like "We bought the 2000 model brand new, and it's still
> > running, you know how to fix it if it breaks, and you are cheaper than
> > buying a new one."
> >
>
> LOL
>
> Can I assume you have an Oracle support contract?

Oh, yes!

IF so, then back to the
> analogy: "you can trade up at any time, no additional charge. Just spend
> some time learning how to take advantage of the new fuel efficiencies."
>

But I would also need to re-program my radio settings. This could take
hours, and I would still be listening to the same music when I was done. I
guess it might sound a little better, or at least I would certainly feel
better listening to it in my brand new car.

> (You _would_ spend some time understanding the different features of a
newer
> car, wouldn't you?)

I would love to.

>
> /Hans

Alan wrote:

>
> But I would also need to re-program my radio settings. This could take
> hours, and I would still be listening to the same music when I was done. I
> guess it might sound a little better, or at least I would certainly feel
> better listening to it in my brand new car.
>

Guess radio setting are more important than the improved handling, the
ability to stick corners at higher speeds, the onboard engine monitor, the
reduced cost related to better mileage, etc. To each his own <g>

Note that I DO understand the/your situation. A lot of people (like
lurkers) don't and just tend to do the mythological Ostrich thing - I like
to bring the myth aspect to their attention.

"Hans Forbrich" <news.hans@telus.net> wrote in message
news:dhY5d.122024$KU5.81009@edtnps89...
> Alan wrote:
>
> >
> > But I would also need to re-program my radio settings. This could take
> > hours, and I would still be listening to the same music when I was done.
I
> > guess it might sound a little better, or at least I would certainly feel
> > better listening to it in my brand new car.
> >
>
> Guess radio setting are more important than the improved handling, the
> ability to stick corners at higher speeds, the onboard engine monitor, the
> reduced cost related to better mileage, etc. To each his own <g>

I guess it's important to me as a driver, but in any event, I can't convince
the car's owner that
the new car is worth the expense, as the owner never drives the car. The
owner asks me to run to the grocery store and pick up some milk. I get back
in a reasonable amount of time, and the milk is still pretty cold, so he
doesn't see the need.

>
> Note that I DO understand the/your situation. A lot of people (like
> lurkers) don't and just tend to do the mythological Ostrich thing - I like
> to bring the myth aspect to their attention.
>
>

I'm sure my situation is not unique.

Alan wrote:

>
> "Hans Forbrich" <news.hans@telus.net> wrote in message
> news:dhY5d.122024$KU5.81009@edtnps89...
>> Alan wrote:
>>
>> >
>> > But I would also need to re-program my radio settings. This could take
>> > hours, and I would still be listening to the same music when I was
>> > done.
> I
>> > guess it might sound a little better, or at least I would certainly
>> > feel better listening to it in my brand new car.
>> >
>>
>> Guess radio setting are more important than the improved handling, the
>> ability to stick corners at higher speeds, the onboard engine monitor,
>> the
>> reduced cost related to better mileage, etc. To each his own <g>
>
> I guess it's important to me as a driver, but in any event, I can't
> convince the car's owner that
> the new car is worth the expense, as the owner never drives the car. The
> owner asks me to run to the grocery store and pick up some milk. I get
> back in a reasonable amount of time, and the milk is still pretty cold, so
> he doesn't see the need.
>

As I said - because of the support agreement, the owner gets to upgrade the
car at any time with no additional direct expense. And that means the
driver makes a lot of the decisions - but needs to have the savvy (and
desire) to help the owner come to the right conclusion.

For any non-professional drivers, lack of technical skill or knowledge keeps
them from that. For some professional drivers, lack of communication
skill, lack of enthusiasm or lack of political acumen, does the same.

Admittedly some owners will not listen - at which time many professionals
will look elsewhere. But most owners WILL listen when told that the cost
of fetching the milk is several times the cost of the milk itself - and
then told there is a way to cut that cost.

The above does not imply your situation. I have, however, seen that several
times in the computing industry.

>>
>> Note that I DO understand the/your situation. A lot of people (like
>> lurkers) don't and just tend to do the mythological Ostrich thing - I
>> like to bring the myth aspect to their attention.
>>
>
> I'm sure my situation is not unique.

It's not. Quite typical - especially since companies have been led to
believe that computing, and computing profesionals, are commodities.

'tis an article of faith by most that Oracle is expensive, and Oracle
upgrades are just more expense with little or no benefit. And it's a bad
thing to tamper with a person's religion, isn't it? <g>

'nuff said?

/Hans