Update Problem

[Database Administrator]

Hi

I have this query to update some records in my table. It is working
good but my problem is this query very slow. Is any way to do this job
faster.

UPDATE SWKPIFA Y
SET Y.SWKPIFA_TRAN_CODE = 'G'
WHERE SWKPIFA_START_TERM =
( SELECT MIN(X.SWKPIFA_START_TERM)
FROM SWKPIFA X
WHERE Y.SWKPIFA_PIDM = x.SWKPIFA_PIDM
AND Y.SWKPIFA_MAJR_CODE = X.SWKPIFA_MAJR_CODE)
AND SWKPIFA_END_TERM =
( SELECT MAX(X.SWKPIFA_END_TERM)
FROM SWKPIFA X
WHERE Y.SWKPIFA_PIDM = x.SWKPIFA_PIDM
AND Y.SWKPIFA_MAJR_CODE = X.SWKPIFA_MAJR_CODE)
/

Thanks
Hakim

[Database Administrator]

"Mohammad" a écrit dans le message news:
cc59b627.0312120749.2e5984d4@posting.google.com...
> Hi
>
> I have this query to update some records in my table. It is working
> good but my problem is this query very slow. Is any way to do this job
> faster.
>
> UPDATE SWKPIFA Y
> SET Y.SWKPIFA_TRAN_CODE = 'G'
> WHERE SWKPIFA_START_TERM =
> ( SELECT MIN(X.SWKPIFA_START_TERM)
> FROM SWKPIFA X
> WHERE Y.SWKPIFA_PIDM = x.SWKPIFA_PIDM
> AND Y.SWKPIFA_MAJR_CODE = X.SWKPIFA_MAJR_CODE)
> AND SWKPIFA_END_TERM =
> ( SELECT MAX(X.SWKPIFA_END_TERM)
> FROM SWKPIFA X
> WHERE Y.SWKPIFA_PIDM = x.SWKPIFA_PIDM
> AND Y.SWKPIFA_MAJR_CODE = X.SWKPIFA_MAJR_CODE)
> /
>
> Thanks
> Hakim

Hello,
First of all, it could be a bit useful if you could tell the ng the version
of Oracle,
the OS and its version.
Second, nobody but you has even an idea of the DB you are running, what kind
of app it is,
how big are your tables, what are the indices and so on.
Plus, you could have posted the EXPLAIN PLAN along with your query.

Without any of these infos, one can't tell you anything except the fact that
running queries
with where clauses containing num_field=(select max(num_field) from
blahblah)
can be very long especially when main query and its sub queries are joined
together.

--
Alkos

[Database Administrator]

"Mohammad" a écrit dans le message news:
cc59b627.0312120749.2e5984d4@posting.google.com...
> Hi
>
> I have this query to update some records in my table. It is working
> good but my problem is this query very slow. Is any way to do this job
> faster.
>
> UPDATE SWKPIFA Y
> SET Y.SWKPIFA_TRAN_CODE = 'G'
> WHERE SWKPIFA_START_TERM =
> ( SELECT MIN(X.SWKPIFA_START_TERM)
> FROM SWKPIFA X
> WHERE Y.SWKPIFA_PIDM = x.SWKPIFA_PIDM
> AND Y.SWKPIFA_MAJR_CODE = X.SWKPIFA_MAJR_CODE)
> AND SWKPIFA_END_TERM =
> ( SELECT MAX(X.SWKPIFA_END_TERM)
> FROM SWKPIFA X
> WHERE Y.SWKPIFA_PIDM = x.SWKPIFA_PIDM
> AND Y.SWKPIFA_MAJR_CODE = X.SWKPIFA_MAJR_CODE)
> /
>
> Thanks
> Hakim

Hello,
First of all, it could be a bit useful if you could tell the ng the version
of Oracle,
the OS and its version.
Second, nobody but you has even an idea of the DB you are running, what kind
of app it is,
how big are your tables, what are the indices and so on.
Plus, you could have posted the EXPLAIN PLAN along with your query.

Without any of these infos, one can't tell you anything except the fact that
running queries
with where clauses containing num_field=(select max(num_field) from
blahblah)
can be very long especially when main query and its sub queries are joined
together.

--
Alkos

[Database Administrator]

"Mohammad" a écrit dans le message news:
cc59b627.0312120749.2e5984d4@posting.google.com...
> Hi
>
> I have this query to update some records in my table. It is working
> good but my problem is this query very slow. Is any way to do this job
> faster.
>
> UPDATE SWKPIFA Y
> SET Y.SWKPIFA_TRAN_CODE = 'G'
> WHERE SWKPIFA_START_TERM =
> ( SELECT MIN(X.SWKPIFA_START_TERM)
> FROM SWKPIFA X
> WHERE Y.SWKPIFA_PIDM = x.SWKPIFA_PIDM
> AND Y.SWKPIFA_MAJR_CODE = X.SWKPIFA_MAJR_CODE)
> AND SWKPIFA_END_TERM =
> ( SELECT MAX(X.SWKPIFA_END_TERM)
> FROM SWKPIFA X
> WHERE Y.SWKPIFA_PIDM = x.SWKPIFA_PIDM
> AND Y.SWKPIFA_MAJR_CODE = X.SWKPIFA_MAJR_CODE)
> /
>
> Thanks
> Hakim

Hello,
First of all, it could be a bit useful if you could tell the ng the version
of Oracle,
the OS and its version.
Second, nobody but you has even an idea of the DB you are running, what kind
of app it is,
how big are your tables, what are the indices and so on.
Plus, you could have posted the EXPLAIN PLAN along with your query.

Without any of these infos, one can't tell you anything except the fact that
running queries
with where clauses containing num_field=(select max(num_field) from
blahblah)
can be very long especially when main query and its sub queries are joined
together.

--
Alkos

[Database Administrator]

Mohammad wrote:

> Hi
>
> I have this query to update some records in my table. It is working
> good but my problem is this query very slow. Is any way to do this job
> faster.
>
> UPDATE SWKPIFA Y
> SET Y.SWKPIFA_TRAN_CODE = 'G'
> WHERE SWKPIFA_START_TERM =
> ( SELECT MIN(X.SWKPIFA_START_TERM)
> FROM SWKPIFA X
> WHERE Y.SWKPIFA_PIDM = x.SWKPIFA_PIDM
> AND Y.SWKPIFA_MAJR_CODE = X.SWKPIFA_MAJR_CODE)
> AND SWKPIFA_END_TERM =
> ( SELECT MAX(X.SWKPIFA_END_TERM)
> FROM SWKPIFA X
> WHERE Y.SWKPIFA_PIDM = x.SWKPIFA_PIDM
> AND Y.SWKPIFA_MAJR_CODE = X.SWKPIFA_MAJR_CODE)
> /

Depending on lots of information you haven't given us, you might try ...

UPDATE SWKPIFA
SET Y.SWKPIFA_TRAN_CODE = 'G'
WHERE (SWKPIFA_PIDM,
SWKPIFA_MAJR_CODE,
SWKPIFA_START_TERM,
SWKPIFA_END_TERM) IN (
SELECT SWKPIFA_PIDM,
SWKPIFA_MAJR_CODE,
MIN(X.SWKPIFA_START_TERM),
MAX(X.SWKPIFA_END_TERM)
FROM SWKPIFA X
GROUP BY SWKPIFA_PIDM,
SWKPIFA_MAJR_CODE
)
/

--
Richard Kuhler

[Database Administrator]

Mohammad wrote:

> Hi
>
> I have this query to update some records in my table. It is working
> good but my problem is this query very slow. Is any way to do this job
> faster.
>
> UPDATE SWKPIFA Y
> SET Y.SWKPIFA_TRAN_CODE = 'G'
> WHERE SWKPIFA_START_TERM =
> ( SELECT MIN(X.SWKPIFA_START_TERM)
> FROM SWKPIFA X
> WHERE Y.SWKPIFA_PIDM = x.SWKPIFA_PIDM
> AND Y.SWKPIFA_MAJR_CODE = X.SWKPIFA_MAJR_CODE)
> AND SWKPIFA_END_TERM =
> ( SELECT MAX(X.SWKPIFA_END_TERM)
> FROM SWKPIFA X
> WHERE Y.SWKPIFA_PIDM = x.SWKPIFA_PIDM
> AND Y.SWKPIFA_MAJR_CODE = X.SWKPIFA_MAJR_CODE)
> /

Depending on lots of information you haven't given us, you might try ...

UPDATE SWKPIFA
SET Y.SWKPIFA_TRAN_CODE = 'G'
WHERE (SWKPIFA_PIDM,
SWKPIFA_MAJR_CODE,
SWKPIFA_START_TERM,
SWKPIFA_END_TERM) IN (
SELECT SWKPIFA_PIDM,
SWKPIFA_MAJR_CODE,
MIN(X.SWKPIFA_START_TERM),
MAX(X.SWKPIFA_END_TERM)
FROM SWKPIFA X
GROUP BY SWKPIFA_PIDM,
SWKPIFA_MAJR_CODE
)
/

--
Richard Kuhler

[Database Administrator]

Mohammad wrote:

> Hi
>
> I have this query to update some records in my table. It is working
> good but my problem is this query very slow. Is any way to do this job
> faster.
>
> UPDATE SWKPIFA Y
> SET Y.SWKPIFA_TRAN_CODE = 'G'
> WHERE SWKPIFA_START_TERM =
> ( SELECT MIN(X.SWKPIFA_START_TERM)
> FROM SWKPIFA X
> WHERE Y.SWKPIFA_PIDM = x.SWKPIFA_PIDM
> AND Y.SWKPIFA_MAJR_CODE = X.SWKPIFA_MAJR_CODE)
> AND SWKPIFA_END_TERM =
> ( SELECT MAX(X.SWKPIFA_END_TERM)
> FROM SWKPIFA X
> WHERE Y.SWKPIFA_PIDM = x.SWKPIFA_PIDM
> AND Y.SWKPIFA_MAJR_CODE = X.SWKPIFA_MAJR_CODE)
> /

Depending on lots of information you haven't given us, you might try ...

UPDATE SWKPIFA
SET Y.SWKPIFA_TRAN_CODE = 'G'
WHERE (SWKPIFA_PIDM,
SWKPIFA_MAJR_CODE,
SWKPIFA_START_TERM,
SWKPIFA_END_TERM) IN (
SELECT SWKPIFA_PIDM,
SWKPIFA_MAJR_CODE,
MIN(X.SWKPIFA_START_TERM),
MAX(X.SWKPIFA_END_TERM)
FROM SWKPIFA X
GROUP BY SWKPIFA_PIDM,
SWKPIFA_MAJR_CODE
)
/

--
Richard Kuhler