Chinaunix

标题: INFORMIX SQL技巧 [打印本页]

作者: 蓝色键盘 时间: 2003-05-29 18:10
标题: INFORMIX SQL技巧
如何加快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 )

如果使用下面的方法：

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 );

然后：

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"

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

作者: tyffly 时间: 2003-05-29 18:28
提示: 作者被禁止或删除内容自动屏蔽

作者: carrierye 时间: 2003-05-29 19:25
标题: INFORMIX SQL技巧
up
不过提一句这里的session时间设的太少了吧。

作者: 蓝色键盘 时间: 2003-05-29 20:28
标题: INFORMIX SQL技巧
我会建议坛主的，以后把session时间设置的长一点。

作者: 大梦 时间: 2003-05-29 22:10
标题: INFORMIX SQL技巧
好东西！

作者: cruelsun 时间: 2003-05-31 15:14
标题: INFORMIX SQL技巧
[quote]原帖由 "蓝色键盘"]因为临时表是建立在内存中 ..........[/quote 发表：

我想请教，临时表不也是建立在dbspace中的吗？

作者: gxm 时间: 2003-06-02 17:04
标题: INFORMIX SQL技巧
太好了，对我的帮助太大了，非常感谢！

作者: cruelsun 时间: 2003-06-05 22:58
标题: INFORMIX SQL技巧
再问一句，为什么临时表就能加快速度？

作者: zoukunhong 时间: 2003-06-06 10:51
标题: INFORMIX SQL技巧
1。数据量少。
2。避免多次排序。

作者: bryanheqi 时间: 2003-06-11 18:04
标题: INFORMIX SQL技巧
好就一个字！

作者: Monicawyc 时间: 2003-07-06 13:01
标题: INFORMIX SQL技巧
受启发！

作者: 大梦 时间: 2003-07-07 08:32
标题: INFORMIX SQL技巧
嗯！
我也如此！

作者: wjj9912130 时间: 2003-07-07 20:41
标题: INFORMIX SQL技巧
從來沒有想過sql語句還可以這麼寫!實在是太好了!

作者: xiaodongc 时间: 2003-07-14 02:39
标题: INFORMIX SQL技巧
good

作者: old tea 时间: 2003-07-15 16:10
标题: INFORMIX SQL技巧
试一试这也许对我非常管用

作者: Jack.D 时间: 2003-08-14 15:52
标题: INFORMIX SQL技巧
up

作者: li2002 时间: 2003-08-15 08:40
标题: INFORMIX SQL技巧
学了不少，good！

作者: 大梦 时间: 2003-08-15 08:43
标题: INFORMIX SQL技巧
很实用的~~~~

作者: YeLLoW 时间: 2003-08-18 13:42
标题: INFORMIX SQL技巧
xiexie!!!

作者: stroustrup 时间: 2003-08-18 21:27
标题: INFORMIX SQL技巧
谢谢，学习ing...

作者: chinalif 时间: 2003-09-29 15:06
标题: INFORMIX SQL技巧
好贴，谢楼主

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)