查询索引 [英] Query regarding Index
问题描述
假设我有一个表格，其中包含数以千计的记录，其中
以下结构
CREATE TABLE" test。T_CNTRY" （
" CNTRY_CDE" CHAR（2）NOT NULL，
" CNTRY_NAME" VARCHAR（50））
我创造了一个索引如下::
CREATE UNIQUE INDEX" testI"。" Uindex1" ONtest。T_CNTRY
（CNTRY_CDEASC）;
1.）我是否必须创建这样的索引才能进行查询这使得运行更快的使用这个运行更快
CREATE INDEX" testI"。" Iindex1" ONtest。T_CNTRY （CNTRY_CDE
ASC）;
或
2.）创建唯一索引/主要关键指数将达到目的
让查询运行得更快
提前感谢
rAinDeEr写道：假设我有一个表格，其中包含以下结构的数千条记录
创建表格 test。" T_CNTRY" （
" CNTRY_CDE" CHAR（2）NOT NULL，
CNTRY_NAME" VARCHAR（50））
我已经创建了如下索引::
CREATE UNIQUE INDEX" testI"。" Uindex1" ONtest。T_CNTRY
（CNTRY_CDEASC）;
1.）我是否必须创建一个像这样的索引来进行查询使用此运行更快
CREATE INDEX" testI"。" Iindex1" ONtest。T_CNTRY （CNTRY_CDE
ASC）;
2.）创建唯一索引/主键索引将有助于制作目的查询运行得更快
UNIQUE索引是一个INDEX + UNIQUEness。
DB2知道并会告诉你：
db2 = > create table t（c1 int not null）;
DB20000I SQL命令成功完成。
db2 =>在t（c1）上创建唯一索引i1;
DB20000I SQL命令成功完成。
db2 =>在t（c1）上创建索引i2;
SQL0605W因为索引SRIELAU.I1而没有创建索引。已经存在
匹配定义。 SQLSTATE = 01550
如您所见，DB2认为不需要第二个索引。
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
hhmmmm ...
1.）这意味着当我已经定义了
时我不需要创建主键UNIQUE + INDEX就像我之前测试过的那样。
CREATE UNIQUE INDEX" test。IAWD_TYP01" ONDB2GRA。T_TYP
（" AWD_TYP_CDE" ASC）CLUSTER
DB20000I SQL命令成功完成。
ALTER TABLE" test"" T__TYP"添加约束PK_AWD_TYP PRIMARY KEY
（" AWD_TYP_CDE"）
SQL0598W现有索引DB2GRA.IAWD_TYP01用作
主键或唯一键的索引。 SQLSTATE = 01550
2.）这是否意味着我可以要求Data Modeler不要使用主键制作唯一的
索引
coz cerating独特的索引服务于两者的目的。
~感谢您的信息
主键是键上的唯一索引+ NOT NULL约束
列...即，唯一键列可以具有空值但是
主键列不能...
当您想要定义一个refrential
约束时，还需要主键...
从数据模型角度来看，定义主键是一个很好的
练习...其他唯一键被定义为备用键...
因此，为什么不定义主键并避免在PK列上定义唯一索引
？
回到原来的问题，请确保你在RUNSTATS上做
表和索引...否则，优化器可能不会使用索引
HTH
Sathyaram
Suppose i have a table which holds thousands of records with the
following structure
CREATE TABLE "test "."T_CNTRY" (
"CNTRY_CDE" CHAR(2) NOT NULL ,
"CNTRY_NAME" VARCHAR(50) )
and i have Created an index like below ::
CREATE UNIQUE INDEX "testI "."Uindex1" ON "test "."T_CNTRY"
("CNTRY_CDE" ASC);
1.) Do i have to create an Index like this to make queries which make
use of this run faster
CREATE INDEX "testI "."Iindex1" ON "test "."T_CNTRY" ("CNTRY_CDE"
ASC);
OR
2.) Creating the unique index/primary key index will serve the purpose
of making queries run faster
thanks in advance
rAinDeEr wrote:Suppose i have a table which holds thousands of records with the
following structure
CREATE TABLE "test "."T_CNTRY" (
"CNTRY_CDE" CHAR(2) NOT NULL ,
"CNTRY_NAME" VARCHAR(50) )
and i have Created an index like below ::
CREATE UNIQUE INDEX "testI "."Uindex1" ON "test "."T_CNTRY"
("CNTRY_CDE" ASC);
1.) Do i have to create an Index like this to make queries which make
use of this run faster
CREATE INDEX "testI "."Iindex1" ON "test "."T_CNTRY" ("CNTRY_CDE"
ASC);
OR
2.) Creating the unique index/primary key index will serve the purpose
of making queries run faster
A UNIQUE index is an INDEX + UNIQUEness.
DB2 knows that and will tell you:
db2 => create table t(c1 int not null);
DB20000I The SQL command completed successfully.
db2 => create unique index i1 on t(c1);
DB20000I The SQL command completed successfully.
db2 => create index i2 on t(c1);
SQL0605W The index was not created because an index "SRIELAU.I1" with a
matching definition already exists. SQLSTATE=01550
As you see, DB2 figured that the second index is not needed.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
hhmmmm...
1.)this means i need not create a primary key when I have already
defined a UNIQUE + INDEX like what i have tested before.
CREATE UNIQUE INDEX "test"."IAWD_TYP01" ON "DB2GRA "."T_TYP"
("AWD_TYP_CDE" ASC) CLUSTER
DB20000I The SQL command completed successfully.
ALTER TABLE "test"."T__TYP" ADD CONSTRAINT "PK_AWD_TYP" PRIMARY KEY
("AWD_TYP_CDE")
SQL0598W Existing index "DB2GRA.IAWD_TYP01" is used as the index for
the
primary key or a unique key. SQLSTATE=01550
2.) Does that mean that I can ask the Data Modeler not to make unique
index togther with a primary key
coz cerating unique indexes serves the purpose of both.
~ Thanks for the information
A primary key is a unique index + NOT NULL constraints on the key
column(s) ... ie , a unique key columns can have null values but
primary key columns cannot...
The primary key is also needed when you want to define a refrential
constraint ...
From the data model perspecitive, defining a primary key is a good
practise ... Other unique keys are defined as alternate keys ...
therefor, why not define a primary key and avoid defining Unique index
on the PK columns ?
Going back to your original question, make sure you do RUNSTATS on the
tables and indexes ... Otherwise, the optimizer might not use the index
HTH
Sathyaram
这篇关于查询索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
