Home » RDBMS Server » Performance Tuning » Updates
Updates [message #319497] Sun, 11 May 2008 22:51 Go to next message
Lucky A
Messages: 65
Registered: October 2007
Member


I am applying updates to a table based on partition key. I need to apply 50,000 updates. I need help with the most efficient way to apply the 50,000 updates to the partitioned table. The column to update has a constant value and two other columns values changes.

Lucky
Re: Updates [message #319501 is a reply to message #319497] Mon, 12 May 2008 00:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you want to execute 50,000 updates of a single row or do you have to update 50,000 rows?
You have to post more precisions.

Regards
Michel
Re: Updates [message #319543 is a reply to message #319501] Mon, 12 May 2008 02:30 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
UPDATE table
SET col1 = 'A'
,   col2 = col2 + 10
WHERE partcol = 'X'


Ross Leishman
Re: Updates [message #319671 is a reply to message #319501] Mon, 12 May 2008 09:48 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
I have to applied 50,000 updates to a single row based on information that is supplied in a text format.

Example is listed below:

CODE_ID is constant.
COUNTY_ID and MUNICI_CODE are not constant.

The update is applied to a partition using the PARTITION_KEY. Based on the additional information, how can I transfer the information from the notepad text to update the table?

UPDATE STATE_T
SET CODE_ID = '400'
WHERE
PARTITION_KEY = 400
AND COUNTY_ID = 0003012
AND MUNICI_CODE = 4508160402
AND CITY_PRIN = 5;

Lucky
Re: Updates [message #319678 is a reply to message #319497] Mon, 12 May 2008 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>how can I transfer the information from the notepad text to update the table?
By CUT & PASTE
Re: Updates [message #319679 is a reply to message #319671] Mon, 12 May 2008 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have 50,000 single row updates then there is nothing to tune but add an index on your criteria (see WHERE clause).

Regards
Michel
Re: Updates [message #319689 is a reply to message #319679] Mon, 12 May 2008 11:57 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
I don't want to cut and paste 50,000 times to apply an updates. I am asking for a more efficient way to apply the updates. I could use any suggestions.

Thanks.
Re: Updates [message #319690 is a reply to message #319689] Mon, 12 May 2008 11:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am asking for a more efficient way to apply the updates

Like what?

Put all the values in a file and use an external table to join and make the update.

Regards
Michel

[Updated on: Mon, 12 May 2008 12:00]

Report message to a moderator

Re: Updates [message #319699 is a reply to message #319690] Mon, 12 May 2008 13:43 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
Can you please elaborate with an example?

Thanks,
Lucky
Re: Updates [message #319701 is a reply to message #319497] Mon, 12 May 2008 13:53 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>Can you please elaborate with an example?

How about 1st you comply with posting guidelines as stated in URL below?

http://www.orafaq.com/forum/t/88153/0/
Re: Updates [message #319702 is a reply to message #319699] Mon, 12 May 2008 13:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you post an example of your tables and data (create table and insert statements) and the data you want to modify.

Regards
Michel
Re: Updates [message #319757 is a reply to message #319702] Mon, 12 May 2008 23:38 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
This is the create table:

CREATE TABLE LOAD_STAGE_T
(
PARTITION_KEY NUMBER(4) NOT NULL,
PROCESS_NAME VARCHAR2(20) NOT NULL,
TENANT_ID VARCHAR2(20),
DEPT_ID VARCHAR2(3),
CLIENT_NUM NUMBER(5),
BUSINESS_UNIT NUMBER(10),
SERVICE_DATE DATE
)
TABLESPACE SKY_BLUE_1
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
NOLOGGING
PARTITION BY RANGE (PARTITION_KEY, SERVICE_DATE)
SUBPARTITION BY HASH (TENANT_ID)


UPDATE LOAD_STAGE_T
SET DEPT_ID = '021'
WHERE
PARTITION_KEY = 300
AND BUSINESS_UNIT= 077787
AND PROCESS_NAME = 012345600765432
AND CLIENT_NUM = 14;

In the where clause, the value of the BUSINESS_UNIT and PROCESS_NAME is not constant. The above UPDATE is 50,000, provided in notepad text. How can I apply the 50,000 UPDATE?

Thanks,
Lucky
Re: Updates [message #319760 is a reply to message #319757] Tue, 13 May 2008 00:03 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
O.k I think this is what you want
update <table_A>
set col_a = <value
where (col_b,col_c) in (select col_b, col_c from external_table)
and partition_key = <value>

For how to create an external table check the following link.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/et_concepts.htm#g1017623

Regards

Raj
Re: Updates [message #319989 is a reply to message #319760] Tue, 13 May 2008 11:20 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
Raj, I like your suggestion and went along to implement it in testing before going to production. This is the error I am getting:

ORA-30657: operation not supported on external organized table


Thanks,
Lucky
Re: Updates [message #319992 is a reply to message #319989] Tue, 13 May 2008 11:28 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You cannot do any DML activities on an external table.

Regards

Raj
Re: Updates [message #319993 is a reply to message #319989] Tue, 13 May 2008 11:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to update permanent table not the external one.
You just select on the external table.

Regards
Michel
Re: Updates [message #319994 is a reply to message #319993] Tue, 13 May 2008 11:58 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
I got the above error while trying to create the external table.
However, any suggestions on how to get the data from my C:\ drive to a table in the database?

Thanks,
Lucky
Re: Updates [message #319996 is a reply to message #319497] Tue, 13 May 2008 12:03 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>any suggestions on how to get the data from my C:\ drive to a table in the database?
sqlldr - SQL LOADER
Re: Updates [message #319999 is a reply to message #319994] Tue, 13 May 2008 12:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I got the above error while trying to create the external table.

I don't how you could expect we can help us WITHOUT posting any information of what you did.
As always: copy and paste your session.

As I also previously requested:
Quote:
Can you post an example of your tables and data (create table and insert statements) and the data you want to modify.


If you had done this you'd already have a full solution.
You are too lazy to do it, you have to do with your errors.

Regards
Michel
Re: Updates [message #320010 is a reply to message #319999] Tue, 13 May 2008 12:45 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
Michel,

I did comply with your request to post an example of the table and data. The data is in the form of UPDATE query in notepad as posted below under heading: "DATA to apply for the update:"; 50,000 updates query which is saved on my C:\ drive. Please note that the BUSINESS_UNIT and PROCESS_NAME values are not constant. Here is the create table posting again:

This is the create table:

CREATE TABLE LOAD_STAGE_T
(
PARTITION_KEY NUMBER(4) NOT NULL,
PROCESS_NAME VARCHAR2(20) NOT NULL,
TENANT_ID VARCHAR2(20),
DEPT_ID VARCHAR2(3),
CLIENT_NUM NUMBER(5),
BUSINESS_UNIT NUMBER(10),
SERVICE_DATE DATE
)
TABLESPACE SKY_BLUE_1
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
NOLOGGING
PARTITION BY RANGE (PARTITION_KEY, SERVICE_DATE)
SUBPARTITION BY HASH (TENANT_ID)

DATA to apply for the update:

UPDATE LOAD_STAGE_T
SET DEPT_ID = '021'
WHERE
PARTITION_KEY = 300
AND BUSINESS_UNIT= 077787
AND PROCESS_NAME = 012345600765432
AND CLIENT_NUM = 14;
UPDATE LOAD_STAGE_T
SET DEPT_ID = '021'
WHERE
PARTITION_KEY = 300
AND BUSINESS_UNIT= 077792
AND PROCESS_NAME = 012345600850264
AND CLIENT_NUM = 14;

Thanks Again,
Lucky
Re: Updates [message #320013 is a reply to message #320010] Tue, 13 May 2008 12:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are BUSINESS_UNIT and PROCESS_NAME the only variable parts of the updates? Are the other ones constant?
Can't you have it in a csv form?
Like "DEPT_ID,PARTITION_KEY,BUSINESS_UNIT,PROCESS_NAME,CLIENT_NUM"
What is your OS?

Regards
Michel
Re: Updates [message #320017 is a reply to message #320013] Tue, 13 May 2008 13:05 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
To answer your question Michel:

BUSINESS_UNIT and PROCESS_NAME are the only variable parts of the updates. The others are constant.

The version of Oracle is 10.2.0.2 on Solaris. I can login using sqlplus from Windows as I have Oracle Client installed.

Thanks,
Lucky

Re: Updates [message #320018 is a reply to message #320017] Tue, 13 May 2008 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can't you have it in a csv form?
Like "DEPT_ID,PARTITION_KEY,BUSINESS_UNIT,PROCESS_NAME,CLIENT_NUM"

Regards
Michel
Re: Updates [message #320020 is a reply to message #320018] Tue, 13 May 2008 13:24 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
Yes I can have it in CSV form.

Lucky
Re: Updates [message #320024 is a reply to message #320020] Tue, 13 May 2008 13:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case it is much easier, something like:
create table ext_table (
  <DEPT_ID,PARTITION_KEY,BUSINESS_UNIT,PROCESS_NAME,CLIENT_NUM fields with their type>
  )
organization external (
  type oracle_loader
  default directory <an Oracle direcory created vie CREATE DIRECTORY>
  access parameters (
    records delimited by X'0A'
    nobadfile
    nologfile
    nodiscardfile
    fields terminated by ',' optionally enclosed by '"'
    missing field values are null
    (DEPT_ID,PARTITION_KEY,BUSINESS_UNIT,PROCESS_NAME,CLIENT_NUM)
  )
  location ('<your file.csv>)
)
reject limit unlimited
/

Then
merge into mytable a
using ext_table b
on (b.partition_key = a.partition_key
    and <... for all other fields>)
when matched then update set dept_id = b.dept_id
/

Regards
Michel
Re: Updates [message #320278 is a reply to message #320024] Wed, 14 May 2008 08:57 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
How do I solve the below error? I have created a directory and granted read and write on the directory to the user. I am getting this error message:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file C:\Loadfiles\PRODUPDATED0.csv in EXT_TAB_DIR not found
ORA-06512: at "SYS.ORACLE_LOADER", line 19

Thanks,
Lucky
Re: Updates [message #320280 is a reply to message #319497] Wed, 14 May 2008 08:59 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>I have created a directory and granted read and write on the directory to the user.
Which user would be "the user"?
Re: Updates [message #320292 is a reply to message #320280] Wed, 14 May 2008 10:01 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
The user, with DBA privilege who would be querying both the External Table and the Base Table.

Thanks,
Lucky
Re: Updates [message #320307 is a reply to message #320278] Wed, 14 May 2008 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How do I solve the below error?

Read
Quote:
file C:\Loadfiles\PRODUPDATED0.csv in EXT_TAB_DIR not found

This is the only information you gave us.
This is the only answer we can give you.

Regards
Michel

[Updated on: Wed, 14 May 2008 11:40]

Report message to a moderator

Re: Updates [message #320335 is a reply to message #320307] Wed, 14 May 2008 13:50 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
I created the directory ext_tab_dir AS '\C:\Loadfiles';(as SYS)
and granted read and write to ext_dir_tab to user LMADBA;

The data file PRODUPDATED0.csv is located in:
C:\Loadfiles.

This is the query I ran:

merge into UPDATE_STAGE_T a
using UPDATE_LOAD_T b
on (b.partitioning_ky = a.partitioning_ky
and b.B_SYS_ID = a.B_SYS_ID
and b.CLM_TCN = a.CLM_TCN and b.C_LI_NUM = A.C_LI_NUM)
when matched then update set PLAN_ID = b.PLAN_ID
/

I get the following error:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file C:\Sqlloader\PRODUPDATED0.csv in EXT_TAB_DIR not found
ORA-06512: at "SYS.ORACLE_LOADER", line 19

With the information provided, how best to solve this?

Thanks,
Lucky
Re: Updates [message #320336 is a reply to message #319497] Wed, 14 May 2008 13:55 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>I created the directory ext_tab_dir AS '\C:\Loadfiles';(as SYS)
and granted read and write to ext_dir_tab to user LMADBA;

The data file PRODUPDATED0.csv is located in:
>C:\Loadfiles.

>KUP-04040: file C:\Sqlloader\PRODUPDATED0.csv in EXT_TAB_DIR not found
The error message immediately above would indicate it is looking for the file in Sqlloader folder not where you claim the file resides in Loadfiles folder.
Re: Updates [message #320338 is a reply to message #320336] Wed, 14 May 2008 14:16 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
My mistake by stating C:\Loadfiles, but it is in the SQLLOADER directory.

Thanks,
Lucky.
Re: Updates [message #320340 is a reply to message #319497] Wed, 14 May 2008 14:22 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>My mistake by stating C:\Loadfiles, but it is in the SQLLOADER directory.

You're On Your Own (YOYO)!
Re: Updates [message #320343 is a reply to message #320340] Wed, 14 May 2008 14:34 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
I created and replaced the Directory with both C:\Sqlloader and C:\Loadfiles and I am still getting the same error.

Be patient anacedent. If I didn't need help, I wouldn't be posting on the forum. I just need some guidance as I've gotten in the past from the forum in many ways of which I commend you all and I am appreciative.

Thanks,
Lucky.
Re: Updates [message #320345 is a reply to message #320343] Wed, 14 May 2008 14:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Be patient anacedent.

I don't know for Ana but myself I gave up.
You have been asked to copy and paste your session and not to badly and wrongly explain what you did.
Use SQL*Plus and DO IT!

Forgot: AND FORMAT IT!

Regards
Michel

[Updated on: Wed, 14 May 2008 14:41]

Report message to a moderator

Re: Updates [message #320348 is a reply to message #320343] Wed, 14 May 2008 14:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
Is your C: drive on your server or your client? In order to use external tables, the directory must be on your server, not your client. If on your client, then you either need to move/copy the files to your server first or use SQL*Loader instead. When you create an Oracle directory object, it accepts any string for the path and does not attempt to validate it until you attempt to use it.

[Updated on: Wed, 14 May 2008 14:49]

Report message to a moderator

Re: Updates [message #320355 is a reply to message #320348] Wed, 14 May 2008 16:12 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
Barbara,

Thanks for pointing out my unintended error. Michel, Anacedent and the rest, thanks also. Apparently the C:\ drive is on my client. I have copied the data to the server and recreated the directory to point to the directory on the server. I am no longer getting any error message. But the merge command on one of my tests comes with no rows merged. Is my query in order?

SQL> merge into LOAD_STAGE_T a
2 using UPDATE_LOAD_T b
3 on (b.partition_key = a.partition_key
4 and b.BUSINESS_UNIT = a.BUSINESS_UNIT
5 and b.PROCESS_NAME = a.PROCESS_NAME and b.CLIENT_NUM = A.CLIENT_NUM)
6 when matched then update set DEPT_ID = b.DEPT_ID
7 /

0 rows merged.

Thanks,
Lucky
Re: Updates [message #320356 is a reply to message #320355] Wed, 14 May 2008 16:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
What do you get from?:

SELECT COUNT(*) FROM load_stage_t;

SELECT COUNT(*) FROM update_load_t;

SELECT COUNT(*)
FROM load_stage_t a, update_load_t b
WHERE b.partition_key = a.partition_key
and b.BUSINESS_UNIT = a.BUSINESS_UNIT
and b.PROCESS_NAME = a.PROCESS_NAME
and b.CLIENT_NUM = A.CLIENT_NUM;


Re: Updates [message #320385 is a reply to message #320355] Wed, 14 May 2008 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

AND FORMAT YOUR POST

Read OraFAQ Forum Guide, especially "How to format your post?" section.
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.

Regards
Michel
Re: Updates [message #320391 is a reply to message #319497] Wed, 14 May 2008 23:45 Go to previous messageGo to previous message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
> don't know for Ana but myself I gave up.
Then why continue to feed the those who refuse to learn?
Previous Topic: Moving table into same tablespace
Next Topic: MView Complete Refresh Performance Issue
Goto Forum:
  


Current Time: Tue Dec 06 12:04:46 CST 2016

Total time taken to generate the page: 0.21316 seconds