循序漸進講解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 (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"

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

循序漸進講解SQL查詢語句的高級應用技巧
一、 簡單查詢 簡單的Transact-SQL查詢只包括選擇列表、FROM子句和WHERE子句。它們分別說明所查詢列、查詢的 表或視圖、以及搜索條件等。 例如,下面的語句查詢testtable表中姓名爲“張三”的nickname字段和email字段...查看完整版>>循序漸進講解SQL查詢語句的高級應用技巧
 
講解Informix數據庫的安全性及安全審計
Informix動態服務器提供兩個級別的訪問特權,來保證數據庫的安全性。數據庫特權控制對數據庫的訪問和在數據庫上創建表和索索引的權力。表的特權指定用戶在特定的表上所能進行的操作。 Informix動態服務器支持表級的修...查看完整版>>講解Informix數據庫的安全性及安全審計
 
用一個實例講解Oracle數據庫的PL/SQL語句塊
PL/SQL程序塊以顯示所給出雇員編號的雇員的詳細信息 1 SQL> Declare 2 v_empno emp.empno%Type; /*聲明變量v_empno,%type:使該變量的類型與emp表中的empno類型相同*/ 3 v_emprecord emp%Rowtype;/*聲明變量v_empr...查看完整版>>用一個實例講解Oracle數據庫的PL/SQL語句塊
 
實例講解Informix日期邊界處理的缺陷問題
這篇論壇文章(賽迪網技術社區)著重介紹了Informix日期邊界處理的缺陷問題及解決方法,更多內容請參考下文: Informix數據庫由于其良好的動態擴展性,在各個行業得到了廣泛的使用和認可,但Informix數據庫自身也存在...查看完整版>>實例講解Informix日期邊界處理的缺陷問題
 
用一個案例講解SQL Server數據庫恢複
案例:客戶的一台服務器出現Raid故障,經過我們的努力,數據基本恢複成功,但是其中的一個SQL Server日志文件(擴展名LDF)損壞嚴重,基本不能使用,但是數據文件損壞並不嚴重,我們通過下面的操作使數據全部恢複。 ...查看完整版>>用一個案例講解SQL Server數據庫恢複
 
Informix SQL函數的詳細用法
Informix SQL函數的詳細用法一、內部函數   1、內部合計函數     1)COUNT(*)          返回行數     2)COUNT(DISTINCT COLNAME)   返回指定列中唯一值的個數     3)SUM(COLNAME/EXPRE...查看完整版>>Informix SQL函數的詳細用法
 
講解IBM DB2數據庫的三十一個使用技巧
1. 查看本地節點目錄 命令窗口中輸入:db2 list node directory 2. 編目一個TCP/IP節點 命令窗口:db2 catalog tcpip node remote server ostype 3. 取消節點編目 db2 uncatalog node 4. 查看系統數據庫目錄 db2 lis...查看完整版>>講解IBM DB2數據庫的三十一個使用技巧
 
循序漸進講解DB2數據庫的內置數據類型
DB2數據庫的內置數據類型主要分成數值型(numeric)、字符串型(character string)、圖形字符串(graphic string)、二進制字符串型(binary string)或日期時間型(datetime)。還有一種叫做 DATALINK 的特殊數據類...查看完整版>>循序漸進講解DB2數據庫的內置數據類型
 
循序漸進講解MySQL數據庫的性能調整
MySQL的性能調整: INSERT查詢的速度: 插入一個記錄的時間由下列組成: 連接:(3) 發送查詢給服務器:(2) 分析查詢:(2) 插入記錄:(1 x 記錄大小) 插入索引:(1 x 索引) 關閉:(1) 這裏的數字有點與總體時間成正...查看完整版>>循序漸進講解MySQL數據庫的性能調整
 
 
回到王朝網路移動版首頁