如何在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屋！