INFORMIX SQL语句笔记（九）

INFORMIX SQL语句笔记（九）系统表应用

2008-10-21

以下INFORMIX SQL语句均经过测试，如非特别注明，测试版本为IDS11.5开发版。每一期结合一个具体应用详细介绍。
利用sysadmin、sysmaster、sysuser、sysutils编写一个针对 Informix 系统表的 SQL 查询，并使用这个查询的输出生成另一个 SQL 脚本。如果您要操作包含许多表的大型数据库，这个技巧可以节省大量时间（并减少错过某个表的可能性）。本期结合系统表进行批量处理简化操作（如果数据库有上百个表，那么需要考虑一下）
一、取出库中表名
取出用户创建表的表名
SELECT tabname, tabid FROM systables WHERE tabid > 99 order by tabname
取出系统目录表
SELECT tabname, tabid FROM systables WHERE tabid < 100 order by tabname

二、批量改变页锁为行锁
在最初创建表时，要定义一个锁模式。页锁（当锁定一行时，会锁定一个页面中的所有行）和行锁（只锁定正在使用的一行）。由于性能的原因，页锁是默认模式。但是，为了使并发度最大化，DBA 常常需要将页锁改为行锁。
1.将所有表由页锁改为行锁
生成改锁sql文件
output to lockmod.sql without headings select "alter table ",trim(tabname)," lock mode (row);" from systables where tabid > 99 and tabtype = "T" and locklevel = "P"
order by tabname
再运行改锁sql文件lockmod.sql

三、批量取消“public”特权
在创建表时，“public” 被默认授予对这个表的选择、更新、删除和插入特权。换句话说，能够访问这个数据库的任何用户都能够修改这个表中的所有数据。
生成取消特权sql文件(USER为建表用户)
output to "revokepub.sql" without headings select "revoke all on ",trim(tabname) ,
"from public;" from systables,systabauth where systables.tabid = systabauth.tabid
and grantee = "public" and grantor = USER
and systables.tabid > 99
再运行取消特权sql文件revokepub.sql

四、启用或禁用所有外键约束
output to " disable_fk_constraints.sql" without headings
select "set constraints " || trim(constrname) || "disabled ;"
from sysconstraints
where tabid > 99
and c;
output to " enable_fk_constraints.sql" without headings
select "set constraints " || trim(constrname) || "enabled ;"
from sysconstraints
where tabid > 99
and c;

五、跟踪SQL语句
1.使用新的DBA函数临时启用跟踪
execute function task('set sql tracing on',100,'2k','high','global');

六、何时Update statistics
通过执行update statistics命令可以更新系统的统计信息，使得优化器得到当前最新的统计信息。当修改或删除一个表的相关数据时，系统的统计信息并不自动更新。比如：如果使用delete命令删除一个数据库表内的一条记录，删除完成后查找systables内关于该表的记录信息时，将会发现nrows（数据库表的记录行数目）并没有改变。而通过执行update statistics命令，就可以使系统表systables、sysdistrib、syscolumns、sysindexes等表内的信息得到更新。在运行完update statistics后，这时就会发现systables内的nrows字段已得到更新。如果执行update statistics
medium（high），在sysdistrib表内还可以得到更新的数据分布信息。所以，当大量地修改数据库表后最好执行一下update statistics操作。另外，update statistics将强迫存储过程的优化（对sysprocpplan更新）。以下是与update statistics 相关的系统表：
1、syscolumns：
描述了数据库内的每个字段，其中的colmin、colmax存储了数据库各表字段的次小及次大值，这些值只有在该字段是索引且运行了Update statistics之后才生效。如对于字段值1、2、3、4、5，则4为次大值，2为次小值。
2、sysdistrib：
存储了数据分布信息。该表内提供了详细的表字段的信息用于提供给优化器优化SQL
Select语句的执行。当执行update statistics
medium（high）之后将往此表存入信息。
执行“dbschema -hd”可以得到指定表或字段的分布信息
3、sysindexes：
描述了数据库内的索引信息。对于数据库内的每个索引对应一条记录。修改索引之后只有执行Update statistics才能使其改变在该表内得到反映。同时也更新clust的数值，在该表的数据页数目及数据库记录条数之间
4、systables：
通过执行Update statistics可以更新nrows数据

update statistics有以下三种级别：
1、LOW：
缺省为LOW，此时搜集了关于column的最少量信息。只有systables、syscolumns、sysindexes内的内容改变，不影响sysdistrib。为了提高效率，一般对非索引字段执行LOW操作
2、HIGH：
此时构建的分布信息是准确的，而不是统计意义上的。
因为耗费时间和占用CPU 资源，可以只对表或字段执行HIGH操作。对于非常大的表，数据库服务器将扫描一次每个字段的所有数据。可以配置DBUPSPACE环境变量来决定可以利用的最大的系统磁盘空间
3、MEDIUM：
抽样选取数据分布信息，故所需时间比HIGH要少

什么时候应该执行update ststistics ？
建议在以下情况，执行update statistics 操作：
对数据做了大量修改，大量是针对数据的分布而言，若数据分布没有明显的改变则可以不做
改变的数据库表有与之相关的存储过程，避免在运行时存储过程重新优化
数据库升级之后完成对索引的转变
update ststistics 的方法
考虑到速度性能因素，执行update statistics的推荐方法：
对表执行：update statistics medium for table xxx distributions only
对每个索引的首字段执行：update statistics high
对复合索引执行：update statistics low
必要时对非索引字段但在条件中使用到的字段执行Update statistics high操作