Home » SQL & PL/SQL » SQL & PL/SQL » SQL UPDATE Statement Problem
| SQL UPDATE Statement Problem [message #208234] |
Fri, 08 December 2006 14:19  |
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 #208252 is a reply to message #208234] |
Fri, 08 December 2006 17:12  |
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 |
flyboy
Messages: 1903
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 |
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 #208383 is a reply to message #208234] |
Sun, 10 December 2006 03:48 |
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 |
 |
Littlefoot
Messages: 21823
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.
|
|
|
|
Goto Forum:
Current Time: Fri Dec 06 13:17:02 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
