oracle: creating unique ID in a table
Hi there,
I have a huge table (hundreds of millions rows) with this structure: call_id, date, cust_id. The PK is cust_id + call_id, so call_id is unique for each cust_id.
I need to update call_id to be unique in whole table and sequential when table is ordered by cust_id and date. Values can come from sequences or generated by code.
Of course, performance is very important.
Any ideas?
Thanks,
Radu
Software/Hardware used:
Software/Hardware used:
ASKED:
February 26, 2009 7:10 PM
UPDATED:
March 12, 2009 4:41 PM
Answer Wiki:
As I see it you've basically got two options here.
1. Add a new column to the table. Create a sequence and use a cursor to update each row within the table with its new value.
2. Create a sequence and use a cursor update each row within the table changing the call_id value to the new value. After finishing the cursor operation change the primary key to be only the call_id column.
Neither of these options will be very quick based on the number of rows you have to deal with.
The order of the numbers that you are putting into the table will all depend on the order that you process the rows within the cursor.
------------------------------------------------
In this case, it will probably be better to create a new table, insert the data (using collections and bulk operations), and rename the new table, instead of perform the updates.
Something like this:
<pre>
create table Your_New_Table ( ... );
execute Your_Insert_Procedure(5000);
drop table Your_Table;
rename Your_New_Table to Your_Table;</pre>
where Your_Insert_Procedure is a procedure like this (I tested something similar in 10g, but in 8i it won't compile because the use of a table in the bulk fetch):
<pre>create or replace procedure Your_Insert_Procedure (p_table_size in number default 2000)
is
cursor Your_Cursor is select rownum,date,cust_id from
(select * from Your_Table order by cust_id,date);
type Your_Table_Type is table of Your_Cursor%rowtype index by binary_integer;
Your_Table Your_Table_Type;
BEGIN
open Your_Cursor;
loop
fetch Your_Cursor bulk collect into Your_Table limit p_table_size;
forall j in Your_Table.first..Your_Table.last
insert into Your_New_Table values Your_Table(j);
exit when Your_Cursor%notfound;
end loop;
close Your_Cursor;
commit;
END;</pre>
Note that you will need enough disk space to temporarily duplicate the table.
You can try using different values for the parameter p_table_size to improve performance.
Bulk and forall operations perform many times faster than single/normal ones.
-Carlos DL
To see all answers submitted to the Answer Wiki: View Answer History.
Concur with Carlosdl – best way is to construct a new table using the example code he shows.
A couple of additional comments/suggestions:
1. Create the new table WITHOUT any constraints – i.e., no Primary Key or other indexes. This will allow the inserts to go much faster. Note that this means your query must work correctly – if you accidentally generate duplicate keys, you won’t find it out until you try to add the primary key constraint later.
2. Put the table into NOLOGGING mode to avoid the overhead of the rollback segments.
Alter table <mytable> NOLOGGING;
3. Consider increasing the table’s blocksize to increase the number of rows that will fit into each block.
4. Add the “/* + append */” hint to the insert statement, so Oracle doesn’t attempt to “fit” new rows into holes (which shouldn’t exist), but instead grabs new blocks and raises the high water mark.
5. If you are running with NOLOGGING and APPEND, you can probably set the “p_table_size” parameter to a large number, like 100000. If you do not turn off logging, I would probably try no more than 10000 to avoid creating excessive rollback segments and causing problems with other transactions.
6. Once the table has been loaded, alter it to add the primary key constraint and any other indexes or constraints you need.
7. Use a Sequence to generate additional keys after the copy. Obviously, you need to set the starting value based on the last value added into the new table. Also, if you are concerned about performance AND you don’t care if all the sequence values are truly sequential, you can use the CACHE option to cause blocks of sequence numbers to be pre-allocated, which speeds operations. You can also specify NOORDER, which allows the sequence to issue sequence values out of order, which again is a bit faster when you may have multiple process threads accessing the sequence. Example:
create sequence myschema.mysequence start with <a_big_number> increment by 1 nomaxvalue nocycle cache 100 noorder
The “downside” of the cache and noorder is that you cannot use the generated sequence values to order the records in the table later in time sequential order, and there may be gaps in the generated values (usually only if you stop/restart the database instance, but there are no guarantees).
Let us know the results, please…