| UPDATE ISSUE [message #345083] |
Tue, 02 September 2008 04:54  |
someswar1
Messages: 53
Registered: January 2008
|
Member |
|
|
Can any one tell me if source table is consist 155321890m rows. I want to update the one column AND RUN THE FOLOWWING UPDATE QUERY
UPDATE /*+ index(dim_tan_subs_service_hlr DIM_SUBS_SERVICE_HLR_SEVMSIS) */ dim_tan_subs_service_hlr
SET status_key = '3'
WHERE served_msisdn IN (
SELECT /*+ First_Rows(100) index(dim_tan_subs_service_hlr DIM_SUBS_SERVICE_HLR_SEVMSIS,DIM_SUBS_SERVICE_HLR_SERVID,INDX_DIM_SERVICE_VALUE) */ served_msisdn
FROM dim_tan_subs_service_hlr
WHERE hlr_service_id_key IN ('15', '19', '105')
AND service_id_value = '1'
AND served_msisdn IN (SELECT /*+ First_Rows(100) index(dim_tan_subs_service_hlr DIM_SUBS_SERVICE_HLR_SEVMSIS) */ served_msisdn
FROM dim_tan_subs_service_hlr));
cAN ANY ONE TELL ME WHICH HINTS R USE FULL TO BETTER ACCESS
|
|
|
|
| Re: UPDATE ISSUE [message #345087 is a reply to message #345083] |
Tue, 02 September 2008 05:03  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Rather than use hints, make sure the statistics are up to date and let the optimiser do it's job.
Indexes on Served_MsIsdn and possibly on Hlr_service_Id_Key, Service_Id_Value,Served_Msisdn are worth connsidering too.
Also, I think your query can stand to loose the last two lines (the innermost sub-query) as that's just checking that the value of SERVED_MSISDN in the current Dim_Tan_Subs_Service record exists as a value of SERVED_MSISDN in the table Dim_Tan_Subs_Service.
This can be replaced with a simply not null ckeck.
UPDATE dim_tan_subs_service_hlr
SET status_key = '3'
WHERE served_msisdn IN (SELECT served_msisdn
FROM dim_tan_subs_service_hlr
WHERE hlr_service_id_key IN ('15', '19', '105')
AND service_id_value = '1'
AND served_msisdn IS NOT NULL);
If you want any more help, post the explain plan for the query with and without hints, and tell us what indexes you've got on the table.
Also, let us know what percentage of the table rows you are expecting to be updated.
In a table of 155,000,000 rows, the best optimised query is going to take some timee
|
|
|
|
| Re: UPDATE ISSUE [message #345152 is a reply to message #345087] |
Tue, 02 September 2008 07:28 |
someswar1
Messages: 53
Registered: January 2008
|
Member |
|
|
I am analyzed table
analyze table dim_tan_subs_service_hlr estimate statistics
and then run
UPDATE dim_tan_subs_service_hlr
SET status_key = '3'
WHERE served_msisdn IN (SELECT served_msisdn
FROM dim_tan_subs_service_hlr
WHERE hlr_service_id_key IN ('15', '19', '105')
AND service_id_value = '1'
AND served_msisdn IS NOT NULL);
UPDATE /*+ DML_UPDATE INDEX(DIM_SL_SUBSCRIBER_HLR DIM_SUBS_HLR_TMP_SERVED_IMSI) */ DIM_SL_SUBSCRIBER_HLR
SET STATUS_KEY = NVL (GET_SUBSCRIBER_STATUS_KEY (SERVED_IMSI), '-99')
WHERE SERVED_IMSI IN (SELECT /*+ FIRST_ROWS(100) INDEX(DIM_SL_SUBSCRIBER_HLR DIM_SUBS_HLR_TMP_SERVED_IMSI) */ SERVED_IMSI
FROM DIM_SL_SUBSCRIBER_HLR);
but it takes more time. which hints r usefull. please help
|
|
|
|
|
|
| Re: UPDATE ISSUE [message #345164 is a reply to message #345152] |
Tue, 02 September 2008 08:11 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Try BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => '<schema owner>'
,tabname => 'DIM_TAN_SUBS_SERVICE_HLR'
,method_opt => 'FOR ALL INDEXED COLUMNS'
,cascade => true);
END;
Can you post Explain Plans for your version of the query and the one I suggested?
You need to forget the idea that hints are the key to tuning SQL.
Hints are what you use when everything else has failed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
