Home » SQL & PL/SQL » SQL & PL/SQL » SQL UPDATE Statement Problem
SQL UPDATE Statement Problem [message #208234] Fri, 08 December 2006 14:19 Go to next message
salanalani
Messages: 7
Registered: December 2005
Junior Member
Below is the plan:


KPIR table (the target table which I want to update next, specifically updatng the 'CA' field):

RNC_ID (str), PST ('YYYYMMDD_HH24'), CA (Numner)
1, 20061105_11, 0
1, 20061105_12, 0
1, 20061105_13, 0
2, 20061105_11, 0
2, 20061105_12, 0
2, 20061105_13, 0
3, 20061105_11, 0
3, 20061105_12, 0
3, 20061105_13, 0
4, 20061105_11, 0
4, 20061105_12, 0
4, 20061105_13, 0





PV_WCEL_SERVICE_LEVEL table (the source table):

GLOBAL_ID (str), STARTTIME ('YYYYMMDD_HH24'), RRC_CONN_STP_ATT (Number)
A1, 20061101_00, 9
A1, 20061101_01, 4
...
...
...
A1, 20061101_23, 3
...
...
...
A1, 20061130_23, 4
A2, 20061101_00, 3
A2, 20061101_01, 4
...
...
...
A2, 20061101_23, 1
...
...
...
A2, 20061130_23, 5
...
...
...





UTP_MO table (this table is the OBJECT heirarechy; which determine each GLOBAL_ID is under which RNC_ID):

RNC_ID (str), GLOBAL_ID (str), OBJECT_INSTANCE (str)
1, A1, <A1_NAME>
2, A2, <B1_NAME>
3, XY, <XY_NAME>
4, ZW, <ZW_NAME>
1, WE, <WE_NAME>
...
...
...



Now, I want to update the value of CA in table KPIR:

For instance, for RNC_ID='1' and at PST='20061105_11', the CA should equals to the sum of value RRC_CONN_STP_ATT for all GLOBAL_IDs under RNC_ID='1' and at STARTTIME '20061105_11'.
So, is this SQL UPDATE statement will do it?

update KPIR kr
set ( CA ) = (
select sum(sl.RRC_CONN_STP_ATT) CA
from PV_WCEL_SERVICE_LEVEL sl, UTP_MO um
where sl.GLOBAL_ID = um.GLOBAL_ID and kr.RNC_ID = um.RNC_ID and kr.PST = sl.STARTTIME
group by sl.STARTTIME, um.OBJECT_INSTANCE
)

And if so, why it is taking around 3 hours to be implemented? This issue is happened after upgrading from Oracle 8 to 9!


Really appreciate your help and thanks in advance.
Re: SQL UPDATE Statement Problem [message #208249 is a reply to message #208234] Fri, 08 December 2006 16:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
generate an EXPLAIN_PLAN & post it back here (nicely formated)!
You provided NO facts (quantifiable metrics) to based any answer upon & I refuse to speculate.

How many rows in each table?
When was the last time the tables had STATISTICS generated for them?
Re: SQL UPDATE Statement Problem [message #208252 is a reply to message #208234] Fri, 08 December 2006 17:12 Go to previous messageGo to next message
salanalani
Messages: 7
Registered: December 2005
Junior Member
Well sorry but the thing that I don't have the database in my hand. I wrote everything so I could post here in the forum to see some feedback.

Anyway, regarding the number of rows for the tables:
The UTP_MO table has around 1000 rows

The PV_WCEL_SERVICE_LEVEL table has a data for a complete month in hourly basis for each GLOBAL_ID; meaning that for eaxmple for the GLOBAL_ID 'X' there is 30x24 (720) value. So this table has around 720000 rows (30x24x1000)

The KPIR table, just take as what I post (12 rows).


First, I just want to know if the UPDATE SQL statement is correct or not?

Thanks..
Re: SQL UPDATE Statement Problem [message #208297 is a reply to message #208234] Sat, 09 December 2006 03:22 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
So you have not even tried to run this update. You should, as it shall end with error, as the GROUP BY columns are not represented in the SELECT clause. Just try
select kr.rnc_id, kr.pst, sum(sl.RRC_CONN_STP_ATT) CA
from KPIR kr, PV_WCEL_SERVICE_LEVEL sl, UTP_MO um
where sl.GLOBAL_ID = um.GLOBAL_ID and kr.RNC_ID = um.RNC_ID and kr.PST = sl.STARTTIME
group by kr.rnc_id, kr.pst

Seems to me (not tested as I am lazy to create the testcase) it should write the desired result. To use it for update, use your previously posted update with the GROUP BY clause removed.

I do not know, why developers implemented it 3 hours (as you re-wrote it here faster).
It is also not clear to me what is the issue that was affected by Oracle upgrade.
Re: SQL UPDATE Statement Problem [message #208375 is a reply to message #208234] Sun, 10 December 2006 02:35 Go to previous messageGo to next message
salanalani
Messages: 7
Registered: December 2005
Junior Member
Sorry for late but the thing that the database is not under my hand.

Anyway, I deeply investigated the problem and concluded the following issue:

Below is the query which causes the delay:

select
sum(sl.RRC_CONN_STP_ATT) ca,
from
PV_WCEL_SERVICE_LEVEL sl, UTP_MO um
where
sl.global_id = um.co_gid
and
'2006120713' = to_char(sl.starttime,'yyyymmddHH24')
and
'1' = nvl(substr(um.co_object_instance, 5,1),0)
;

The above statement outputs only one row but it is taking around 6 minutes to be implemented (the output is correct and there is no errors).

I have around 166,000 rows in "PV_WCEL_SERVICE_LEVEL" and around 28,000 rows in "UTP_MO" table.

I've noticed that if I remove the last condition in the "where" clause which is:
'1' = nvl(substr(um.co_object_instance, 5,1),0)
The query will take around only 6 seconds to be implemented and outputs also one row but of course the result will not be correct.

Anyone knows why?
Is there a way that I could optimize this queury? The problem is that I need this kind of query too many times and this causes a very big delay in my reports.

Thanks in advance...
Re: SQL UPDATE Statement Problem [message #208379 is a reply to message #208375] Sun, 10 December 2006 03:28 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd rather rewrite the following line (so that (possible) index on 'starttime' column night be used):

old: and '2006120713' = to_char(sl.starttime,'yyyymmddHH24')

new: and sl.starttime = to_date('2006120713', 'yyyymmddhh24')

Also, if there's an index on 'co_object_instance' column, SUBSTR function will prevent its use. In that case, you might try to create a function-based index and see how it works.
Re: SQL UPDATE Statement Problem [message #208383 is a reply to message #208234] Sun, 10 December 2006 03:48 Go to previous messageGo to next message
salanalani
Messages: 7
Registered: December 2005
Junior Member
Thanks Littlefoot,

I've changed the "sl.starttime" condition as you suggested then the query is more faster now. I did not know that I removed the indexing in "starttime" column when I do the condition in my way.

However, the "co_object_instance" is not indexed. Would you tell me if I index that column, does the table's data will be affected? And how could I substitute my condition which is:
'1' = nvl(substr(um.co_object_instance, 5,1),0)
How could I do a fuction-based index as you said?

Thanks again
Re: SQL UPDATE Statement Problem [message #208391 is a reply to message #208383] Sun, 10 December 2006 04:49 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Creating an index will not affect data. It *might* fail, though, if you intend to create a unique index (but this/these column(s) contain data which is/are not unique).

Search the Internet to find out how to create a function-based index.

Previous Topic: length & replace
Next Topic: soundex
Goto Forum:
  


Current Time: Thu Dec 08 22:06:28 CST 2016

Total time taken to generate the page: 0.14113 seconds