INFORMIX SQL技巧

原创 2004年08月30日 20:52:00
如何加快sql的执行速度?

1.select 语句中使用sort,或join

如果你有排序和连接操作,你可以先select数据到一个临时表中,然后再对临时表进行处理。因为临时表是建立在内存中,所以比建立在磁盘上表操作要快的多。

如:

SELECT time_records.*, case_name 

FROM time_records, OUTER cases 

WHERE time_records.client = "AA1000" 

AND time_records.case_no = cases.case_no 

ORDER BY time_records.case_no 


这个语句返回34个经过排序的记录,花费了5分钟42秒。而:

SELECT time_records.*, case_name 

FROM time_records, OUTER cases 

WHERE time_records.client = "AA1000" 

AND time_records.case_no = cases.case_no 

INTO temp foo; 

SELECT * from foo ORDER BY case_no 

返回34条记录,只花费了59秒。


2.使用not in 或者not exists 语句

下面的语句看上去没有任何问题,但是可能执行的非常慢:

SELECT code FROM table1 

WHERE code NOT IN ( SELECT code FROM table2 icon_wink.gif 


如果使用下面的方法:

SELECT code, 0 flag 

FROM table1 

INTO TEMP tflag; 

然后:

UPDATE tflag SET flag = 1

WHERE code IN ( SELECT code 

FROM table2 

WHERE tflag.code = table2.code icon_wink.gif

然后:

SELECT * FROM 

tflag 

WHERE flag = 0; 

看上去也许要花费更长的时间,但是你会发现不是这样。

事实上这种方式效率更快。有可能第一种方法也会很快,那是在对相关的每个字段都建立了索引的情况下,但是那显然不是一个好的注意。


3.避免使用过多的“or"

如果有可能的话,尽量避免过多地使用or:

WHERE a = "B" OR a = "C" 

要比 

WHERE a IN ("B","C") 

慢。

有时甚至UNION会比OR要快。


4.使用索引。

在所有的join和order by 的字段上建立索引。 
在where中的大多数字段建立索引。 
WHERE datecol >= "this/date" AND datecol <= "that/date" 
要比 
WHERE datecol BETWEEN "this/date" AND "that/date" 慢 


如何在shell脚本中使用一个sql查询的结果?

以下的是一个运行在sh/ksh下面的脚本。在online中,如果你想要更新一个有许多表的数据库的统计信息。这个脚本不太好。因为这个脚本只能单个处理数据库中的表,而不能同时处理大量的表。

例子:

# update_em 

# Run UPDATE STATISTICS on a table by table basis 



DATABASE=$1 

if [ -z "$DATABASE" ] 

then 

echo "usage: update_em dbname" >&2 

exit 1 

fi 

isql $DATABASE - < dev/null | isql $DATABASE - 

output to pipe "cat" without headings 

select "update statistics for table ", tabname, ";" 

from systables where tabid >= 100 order by tabname; 

EOF

exit 0 


也许你已经注意到exit的返回值对不同的isql不是都相同,因此这样作不是很可靠,代替通过$?来检查返回值的更好的主意是将标准错误重定向到一个文件中,然后在这个文件中grep “error"。例如:

# Generate the data 

isql -qr <<!>stage.rep 2>$stage.err 

database $database; 

select ... 



# Check for errors 

if grep -i "error" $stage.err >/dev/null 

then

...error_handler... 

fi


为什么不能对一个计算产生的字段创建视图?

问题:为什么我不能创建视图:

CREATE VIEW tst AS 

SELECT ship_charge - totval cout 

FROM orders WHERE ship_charge > 0; 

回答:你应该这样写:

CREATE VIEW tst (cout) AS 

SELECT ship_charge - totval 

FROM orders WHERE ship_charge > 0;


如何只select 出数据库中的部分数据(例如10%)。

问题:如果你想要得到一个select 语句正常返回的数据的一部分,例如:

SELECT firstname, lastname, city, state 

FROM bigdatabase 

WHERE state = "TX"

回答:

有一个方法可以返回一个近似值,只需要在where后加上:

AND rowid=(trunc(rowid/x)*x) 

其中的x代表你想要返回的总的记录的1/x。需要说明的是,这种方法只能返回一个近似的值,并且表中的数据在物理上分布的连续性。


如何创建一个表结构和永久表完全一致的临时表。 例如:CREATE TEMP TABLE mytemp (prodno LIKE product.prodno 

desc LIKE product.desc) 

你可以使用如下的语句:

SELECT prodno, desc FROM product 

WHERE ROWID = -1 

INSERT INTO TEMP mytemp

如何更改serial类型下一次插入操作产生的值?

我们知道serial类型的字段是系统自动增加的整数字段,那么怎样能控制下一个serial类型字段的值。

想要下一个插入的serial类型的值比默认值大,可以用:

ALTER TABLE tabname MODIFY( ser_col_name SERIAL([new_start_number])

想要下一个插入的serial类型的值比默认的值要小,首先需要将serial类型重新置为1:

INSERT INTO table (serial_column) VALUES (2147483647); 

INSERT INTO table (serial_column) VALUES (0); -- 重新从1开始! 

....然后执行ALTER TABLE(就像上面的做法一样)。

 

如何在发生错误的时候终止sql脚本的执行?

如果你创建了一个sql脚本,并且在UNIX命令行中使用以下的方式来执行这个脚本:

$ dbaccess <database> <脚本文件名>

这时,脚本中的所有的sql语句都会被执行,即使其中的一个sql语句发生了错误。例如,如果你脚本中为如下的语句:

BEGIN WORK; 

INSERT INTO history 

SELECT * 

FROM current 

WHERE month = 11; 

DELETE FROM current 

WHERE month = 11; 

COMMIT WORK; 

如果INSERT语句失败了,DELETE语句仍旧会继续执行。直到commit work。这样的后果可能会很严重。你可以通过设置一个环境变量来防止这种情况的发生。

DBACCNOIGN=1

 

如何设置decimal字段运算结果的精度?

假定你使用dbaccess或者isql,设置环境变量DBFLTMASK=6 就可以设置为小数点后面6位,比如:

CREATE TEMP TABLE t 

( col_a DECIMAL(8,4) NOT NULL, 

col_b DECIMAL(8,4) NOT NULL, 

col_c DECIMAL(8,4) NOT NULL 

); 

INSERT INTO t VALUES(1.2345, 3.4567, 5.6789); 

SELECT (col_a + col_b) / col_c AS value FROM t; 

value 0.826075 

如果DBFLTMASK=7

value 0.8260755

 

为什么我们有时会遇到sysprocplan表被锁的提示?

sysprocplan表是sysmaster库中的一个表,其中记录存储过程经过优化的查询计划。每当查询树中的数据库对象有任何结构上的变化,这个查询计划就会自动更新。如果对查询树中存在的任何表有update statistics操作,也会自动更新查询计划。在查询计划更新的时候,会对sysporcplan表中的相关记录加锁。

注意:每次你对一个表更新统计的时候,也同时会更新于这个表相关的存储过程,即UPDATE STATISTICS FOR PROCEDURE 。

你可以作的另外一件事情就是:在存储过程中使用SET OPTIMIZATION LOW,这会让优化器在存储过程运行的时候不会试图去重新优化它。否则存储过程通常都会被重新优化一次。


 

如何删除掉表中重复的记录?

假设“keycol”字段的值唯一,而且没有对表进行分片,并且没有其它的人正在删除"sometable"中的记录,你可以执行如下的SQL:

delete from sometable as a 

where rowid <> (select min(rowid) from sometable where keycol = a.keycol)

如果这个表使用表分片,rowid不存在,你还可以用如下的方法:

BEGIN WORK; 

SELECT DISTINCT * FROM Table INTO TEMP Temp1; 

DELETE FROM Table WHERE 1 = 1; 

INSERT INTO Table SELECT * FROM Temp1; 

COMMIT WORK; 

对于规模较小或中等的表,并且你有足够的存储空间来存储整个的临时表的时候,这种方法通常十分有效。

 

如何加快SELECT COUNT(DISTINCT)的速度。

通常“SELECT COUNT(DISTINCT)”这样的操作要花费比较长的时间,如果你这样作:

SELECT UNIQUE xxx INTO TEMP XXX " 然后再"SELECT COUNT(*) FROM TEMP XXX" 

这样通常可以提高几倍的效率。

Informix SQL 语句性能调优最佳实践

SQL 执行计划 关于执行计划,我们可以把它简单的理解成 SQL 语句执行时访问数据的方式。执行计划的优劣是影响 SQL 执行效率的重要因素。它包括:查询优化器认为最优的数据访问路径,返回记录数的估...
  • ltt4172406
  • ltt4172406
  • 2014年07月24日 17:08
  • 2286

informix的常用SQL语句

informix的常用SQL语句
  • educast
  • educast
  • 2016年05月26日 14:47
  • 2423

informix数据库中批量导入部分表的数据到另外一个相同的数据库中

一般在开发的时候,都有多个相同的数据库(比如测试环境可能有好几个(比如有开发人员使用的库,测试人员使用的数据库,还有正式环境的数据库等),但是使用的数据库是一样的,)。 现在有这样一个需求:在某个系统...
  • zhengqiqiqinqin
  • zhengqiqiqinqin
  • 2014年02月11日 17:50
  • 3683

informix数据库导入和导出

所谓中间库,就是说把从各个业务系统卸载下来的数据(通常是文本),装载到这个中间库Informix中,然后再然过ETL过程操作,最后装载到数据仓库中。        之所以要采用一个中间库,主要是为...
  • lzyzuixin
  • lzyzuixin
  • 2014年06月11日 17:55
  • 2148

Informix常用操作方法命令

1. 相关文件 l  informix配置文件: informix配置文件定义数据库的各种参数设置,通过环境变量$ONCONFIG指定,存放在informix用户的etc目录下,如$ONCONFI...
  • konglongaa
  • konglongaa
  • 2016年07月09日 16:53
  • 2253

50条SQL查询技巧

Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) ...
  • emoven
  • emoven
  • 2015年05月28日 10:47
  • 7023

Informix存储过程 详解

一、存储过程概述 存储过程是一个用户定义的函数,由存储过程语句(SPL) 和一组SQL语句组成,以可以执行代码形式存储在数据库中,和表、视图、索引等一样 ,是数据库的一种对象。 ...
  • baoguaalalei
  • baoguaalalei
  • 2016年12月26日 13:57
  • 701

8个SQL语句常用优化技巧

要提高SQL语句的执行效率,最常见的方法就是建立索引,以及尽量避免全表扫描。在本章MySQL教程中,UncleToo给大家整理一些常见的SQL优化技巧,避免全表扫描。一个简单的优化,也许能让你的SQL...
  • csdnstudent
  • csdnstudent
  • 2014年10月23日 15:31
  • 11036

informix的字符串类型详解

informix的字符串类型详解 lvarchar在9.4版本之后才可以用带指定长度的方式,如lvarchar(3000),不带长度则默认是2048字节,最大长度是32,739字节。所以对于...
  • zhengqiqiqinqin
  • zhengqiqiqinqin
  • 2013年10月14日 16:45
  • 5621

Informix数据库临时表的一些总结

在Informix数据库中,创建临时表来处理应用中的临时数据,并且临时表数据存储在临时表空间中,Informix系统不记录临时表的字典信息 从数据库系统表中查询不到临时表的任何相关信息,可以在临时表...
  • qq_24063701
  • qq_24063701
  • 2017年12月12日 16:35
  • 122
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:INFORMIX SQL技巧
举报原因:
原因补充:

(最多只允许输入30个字)