统计INFORMIX库中表索引INDEX数量

最新推荐文章于 2023-04-27 21:05:01 发布

lwei_998

最新推荐文章于 2023-04-27 21:05:01 发布

阅读量2.7k

点赞数 1

文章标签： informix 数据库 table schema null c

本文链接：https://blog.csdn.net/lwei_998/article/details/5990640

版权

索引在informix中，如果索引设置不当可能会导致锁表。因此数据库中的索引显得格外重要。

我们可以在对数据库做改变前对数据库中表索引的情况做个简单的备份。对比改变前后的结果

可以知道库中索引的变化情况。

1.统计表中索引的个数

select b.tabname ,count(*)

from sysindexes a ,systables b

where b.tabid=a.tabid

group by b.tabname;

2.统计表中那些字段上有索引

select a.tabid, c.tabname,b.idxname , a.colname,a.colno

from syscolumns a , sysindexes b ,systables c

where a.colno=b.part1

and a.tabid =b.tabid

and a.tabid = c.tabid ;

3.查看表结构

/home/testcar$dbschema -d test -t riskitem

test为库名，riskitem 为表名

/home/testcar$dbschema -d test-t riskitem

DBSCHEMA Schema Utility       INFORMIX-SQL Version 9.40.FC1
Copyright (C) Informix Software, Inc., 1984-1997
Software Serial Number AAA#B000000
{ TABLE "informix".ciendorriskitem row size = 238 number of columns = 23 index size
              = 141 }
create table "informix".riskitem
(
    demandno varchar(50) not null ,
    proposalno char(22),
    policyno char(22),
    applyno varchar(25),
    claimadjustvalue decimal(3,2),
    claimadjustreason varchar(8),
    loyaltyadjustvalue decimal(3,2),
    loyaltyadjustreason varchar(8),
    mileageadjustvalue decimal(3,2),
    mileageadjustreason varchar(8),
    peccancyadjustvalue decimal(3,2),
    peccancyadjustreason varchar(8),
    traveladjustvalue decimal(3,2),
    traveladjustreason varchar(8),
    driveradjustvalue decimal(3,2),
    driveradjustreason varchar(8),
    lossadjustvalue decimal(3,2),
    lossadjustreason varchar(8),
    theftadjustvalue decimal(3,2),
    theftadjustreason varchar(8),
    fleetadjustvalue decimal(3,2),
    adjustvaluereason varchar(8),
    iscontinuouspolicy varchar(8),
    primary key (demandno) constraint "testcar".pk_riskitem
);
revoke all on "informix".riskitem from "public";

create index "informix".idx_riskitem_applyno on "informix"
    .riskitem (applyno) using btree ;
create index "informix".idx_riskitem_policyno on "informix"
    .riskitem (policyno) using btree ;
create index "informix".idx_riskitem_proposalno on "informix"
    .riskitem (proposalno) using btree ;