Home » RDBMS Server » Performance Tuning » UPDATE ISSUE (Oracle 10g)
UPDATE ISSUE [message #345083] Tue, 02 September 2008 04:54 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #345159 is a reply to message #345083] Tue, 02 September 2008 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topics:
Michel Cadot wrote on Fri, 23 May 2008 08:39
please read OraFAQ Forum Guide, especially "How to format your post?" section, use code tags.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

What did you try to fix it?

Regards
Michel


Michel Cadot wrote on Tue, 03 June 2008 13:45
Read and FOLLOW OraFAQ Forum Guide.

Regards
Michel



Do it!
Re: UPDATE ISSUE [message #345164 is a reply to message #345152] Tue, 02 September 2008 08:11 Go to previous message
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;
to generate the stats.

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.
Previous Topic: Alternative way to avoid full scan?
Next Topic: Performance tuning
Goto Forum:
  


Current Time: Mon Dec 05 12:57:01 CST 2016

Total time taken to generate the page: 0.10007 seconds