DB2中如何生成主键(SQL优化) [英] How to generate the primary key in DB2 (SQL Optimization)

问题描述

我使用以下语句来生成我的主键.table2 保留当前的主键.但它太慢了.我该如何优化这个过程.

I use the following statement to generate my primary key. table2 keeps the curent primary key. But it is too slow. How can i optimize this proces.

update table1 t set ID =ROW_NUMBER() OVER ();

update table1 t 
set ID = (Select NUMGEN.currentvalue + ID from  table1 data, table2 NUMGEN 
where NUMGEN.tablename = 'table1' and t.Id = data.ID  );

推荐答案

你为什么要这样做主键.为什么需要动态生成/修改自己的 id 值?

Why are you doing primary keys this way. Why would you need to dynamically generate/modify your own id values?

如果您需要为表生成唯一的 id，请创建一个序列并在表中使用该序列值.例如:

If you need a unique generated id for a table, create a sequence and use that sequence value in your table. For example:

CREATE SEQUENCE <schema>.SEQ_SAMPLE_TABLE DATA TYPE BIGINT INCREMENT BY 1 NO ORDER NO CYCLE MINVALUE 100 MAXVALUE 9223372036854775807 CACHE 20;

获取唯一的序列以用作您的主键.

to get a unique sequence to use as your primary key.

然后创建一个以ID为主键的表:

Then create a table with an ID as the primary key:

CREATE TABLE <schema>.SAMPLE_TABLE ( 
    STATUS_ID BIGINT DEFAULT NULL , 
    STATUS_DESC VARCHAR(80) DEFAULT NULL , 
    CONSTRAINT <schema>.XPK_STATUS PRIMARY KEY( STATUS_ID ) );

然后插入一行，你这样做:

and then to insert a row, you do:

 insert into SAMPLE_TABLE (nextval for SEQ_SAMPLE_TABLE, 'This is working');

其中大部分可以来自 db2 手册.

Most of this can come from the db2 manuals.

例如:创建表:http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000927.htm

创建序列:http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0004201.htm

我应该补充一下——以上是我们在我当前的客户中的做法.另一种方式(也许更好)是:

I should add -- the above is how we're doing it at my current client. Another way (perhaps better) is:

CREATE TABLE WIDGET_INVENT 
    ( ROW_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY 
                     (START WITH 1, INCREMENT BY 1, NO CACHE), 
      WIDGET_NO CHAR(6), 
      INV_COUNT INT WITH DEFAULT 0 
    ); 

您没有在外部指定序列，而只是将它们构建到表定义中.在这种情况下，在进行插入时，根本不要引用 row_id 列.例如，请参见此处:http://www.ibm.com/developerworks/data/library/techarticle/0205pilaka/0205pilaka2.html

where you don't specify the sequences externally, but just build them into the table definition. In this case, when doing inserts, just don't reference the row_id column at all. For example, see here: http://www.ibm.com/developerworks/data/library/techarticle/0205pilaka/0205pilaka2.html

第一种方式，即您指定自己的序列，部分地以这种方式完成，因为它们如何将一些表从一个环境迁移到另一个环境.序列从具有不同起始值的不同来源映射出来，从而可以轻松合并数据.YMMV

The first way, where you specify your own sequences, was partially done that way because of how they migrate some tables from one environment to the next. Sequences get mapped out from different sources with different start values, allowing easy merging of data. YMMV

这篇关于DB2中如何生成主键(SQL优化)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！