updating fact table

[bobby]

hi,
i am trying to update a dimensional table from dim table(star flake)
i have (user define query for sql in my datastgae job in oracle 8)i was trying to update only on 1 column
my surrogated from dim table(based on business key), its taking too long
1)i did it my making new job
2) and (writing same sql query after button in datastage in in same job)
plz advice
Thanks
Bobby

[vmcburney]

Could you have another go at asking your question as several of your sentences do not make any sense.

I think you are having problems with a slow update of a table using user defined SQL. My advice would be to take the user defined SQL out and redesign your job to use generated SQL. This should be more robust and may fix up your performance problems.

[bobby]

hi,
i am trying to update on a single column
UPDATE DWADMIN.DIM_SSWAT_ORDER SET NI_WORK_KEY=:1
this table has about 500,000 rows its taking for ever
plz guide.
Thanks
Bobby

[kduke]

Bobby

You need to add another column for the key. This will update the whole table. An update will not update the key. It will use it in the where clause.

[bobby]

Hi,
I have a hash file omc_order_id and i am joining it with the order_id
of my table which has to be updated i make omc_order_id as key
joined hash file omc_order_id with order_id and then just updating ni_work_key

like this
IF DSLink4.ORDER_ID=hashfile.OMC_ORDER_ID then hashfile.NI_WORK_KEY
else '-99'

update on ni_work_key

so are u suggesting
do
UPDATE DWADMIN.DIM_SSWAT_ORDER SET NI_WORK_KEY=:1 where
order_id=2;
did same way it taking forever
plz advice
thanks
bobby

[kduke]

Bobby

You are changing the primary key. It has to do a table scan the way your update is written. Either delete the original record and insert the new one or create an index on order_id.

[bobby]

Hi Duke,
so index on table in oracle,
Thanks
Bobby