分享
 
 
 

循序渐进讲解Informix SQL的十一个技巧

王朝mssql·作者佚名  2008-06-04
窄屏简体版  字體: |||超大  

一、加快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 (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 <脚本文件名>

这时,脚本中的所有的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"

此例一般可以提高几倍的效率。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有