I have to update a partitioned table with about 80 million records.
We have added a new column which has about 1250 different possible values.
The value is derived by matching a substring of another field to a table. The substring can be either 1,2,3 or 4 characters in length so I have created a function to find the value. The function works great when adding to the table but is too slow for the update.
I decided to create 4 temporary global function based indexes and update each value on the table 1250 times. I can see that the index is being used but this is also taking too long.
Should I have created the indexes locally? I would think since I am updating across partitions it should be global.
Perhaps I should have done one partition at a time but that seems repetitive.
Maybe create a loop that updates by rowid? But then I would think that 50 updates would be slower than 1250 updates on an indexed column.
It almost seems it would be faster to rename the table and re-insert the data using the function.
Any suggestions?
Additional Info:
The table has 38 columns
The column I am updated has no indexes on it
The table has no constraints on it
The table has 6 partitions by year.
The smallest partition has 500,000 rows
The largest partition has 15,000,000 rows
What would you do next?
Thank in advance! I have also pasted the function code below for reference.
We have added a new column which has about 1250 different possible values.
The value is derived by matching a substring of another field to a table. The substring can be either 1,2,3 or 4 characters in length so I have created a function to find the value. The function works great when adding to the table but is too slow for the update.
I decided to create 4 temporary global function based indexes and update each value on the table 1250 times. I can see that the index is being used but this is also taking too long.
Should I have created the indexes locally? I would think since I am updating across partitions it should be global.
Perhaps I should have done one partition at a time but that seems repetitive.
Maybe create a loop that updates by rowid? But then I would think that 50 updates would be slower than 1250 updates on an indexed column.
It almost seems it would be faster to rename the table and re-insert the data using the function.
Any suggestions?
Additional Info:
The table has 38 columns
The column I am updated has no indexes on it
The table has no constraints on it
The table has 6 partitions by year.
The smallest partition has 500,000 rows
The largest partition has 15,000,000 rows
What would you do next?
Thank in advance! I have also pasted the function code below for reference.
Code:
CREATE OR REPLACE FUNCTION DW.f_e164 (v_call varchar)
RETURN NUMBER IS
v_COUNT NUMBER;
v_1 varchar2(1);
v_2 varchar2(2);
v_3 varchar2(3);
v_4 varchar2(4);
v_e164code varchar2(10);
v_short_code varchar2(30);
CURSOR c_short IS
SELECT longnumber
FROM dw.all_short_codes
WHERE shortcode = v_call;
CURSOR c_164 IS
SELECT e164cd
FROM dw.te164mapping
WHERE e164cd IN (v_1, v_2, v_3, v_4);
--===========================================================
-- This function finds the E164 codes for called numbers ==
-- There are 4 different ways to return a value ==
-- 1) Match the first X digits to the E164 LU table ==
-- 2) 2STG calls will default to '111111' ==
-- 3) Match short code calls to the short code table and ==
-- and replace the called number from the short code ==
-- table and lookup the E164 code based on the long ==
-- number ==
-- 4) Everything else is defaulted to 0 which is a misdial==
--===========================================================
BEGIN
--===========================================================
-- Determine if it is a 2stg call ==
--===========================================================
IF v_call = '2STGMTC'
THEN RETURN ('111111');
ELSE
--===========================================================
-- Determine if a short code was used ==
--===========================================================
OPEN c_short;
FETCH c_short INTO v_short_code;
--===========================================================
-- If no short code was used, look up the E164 on the long ==
-- number. Otherwise, use the called number to find code ==
--===========================================================
IF c_short%NOTFOUND
THEN
v_1 := substr(v_call,1, 1);
v_2 := substr(v_call,1, 2);
v_3 := substr(v_call,1, 3);
v_4 := substr(v_call,1, 4);
ELSE
v_1 := substr(v_short_code,1, 1);
v_2 := substr(v_short_code,1, 2);
v_3 := substr(v_short_code,1, 3);
v_4 := substr(v_short_code,1, 4);
END IF;
CLOSE c_short;
--===========================================================
-- Look up the E164 Code, if no code is found return '0' ==
--===========================================================
OPEN c_164;
FETCH c_164 INTO v_e164code ;
IF c_164%NOTFOUND
THEN RETURN ('0');
ELSE
RETURN (v_e164code);
END IF;
END IF;
END;