统计INFORMIX库中表索引INDEX数量

索引在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 ;

 

 

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

 

过程没办法直接提供，不过可以搞定SQL语句：
select   'drop   index   '+object_name(a.id)+ '. '+a.name  
from   sysindexes   a,sysobjects   b
where   a.id   =   b.id  
and   b.type   = 'U '
and   indid>   0