Update

[Lucky A]

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

[PAVB]

50K updates is not such a big deal.
Do you know how many updates do you have to apply in each partition?... Depending on conditions you may want to set a transaction per partition.

[Lucky A]

"Depending on conditions you may want to set a transaction per partition."

Please elaborate a little bit more on your above statement. I am updating one partition key among several. The update information to be applied is in a text format.

[PAVB]

"Depending on conditions you may want to set a transaction per partition."

Please elaborate a little bit more on your above statement. I am updating one partition key among several. The update information to be applied is in a text format.

If your process is bound to hit just one partition then your process does not meet the conditions

Are you planning to tell us how many rows do you have in your table?
... in your affected partition?
... where you are extending the size of the affected columns?
... how dense are your data blocks?

[hrishy]

Hi

50K updates are not a big deal if your update statement has the partitioning key in it.

Without knowing too much and assuming your update statement is simple then i would do the update in a single sql statement.

Having the partitoning column in the update statement is the key here.

regards
Hrishy

[Lucky A]

Please give me an example on how I could do the update for 50,000 records.

Thanks,
Lucky

[PAVB]

Please give me an example on how I could do the update for 50,000 records.

Are you serious?
Just look at your business requirements and write your darn update statement.

[Lucky A]

I do the update query. I am trying to transfer the data from my C:\ drive to the database. I tried creating a an external table and got this error:
ORA-30657: operation not supported on external organized table.
This is the update query and data to use. 50,000 of these to apply. Please note that the BUSINESS_UNIT and PROCESS_NAME values are not constant:

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;

How can I get the data from c:\ to the table in the database? Please give suggestion with example please.

Thanks,
Lucky

[PAVB]

Is LOAD_STAGE_T your external table?
--You cannot write on a external table.

Are you planning to write 50K individual update statements?
--Please tell me NO!

What's the table you have to update?
--Please post desc of your table.

[Lucky A]

I was not writing to an External table. I was creating it in order to select from it. The data, in the form of an update query is provided. 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,
Lucky