分布式數(shù)據(jù)庫系統(tǒng)原理中的查詢過程與通過Oracle執(zhí)行計(jì)劃推測(cè)其優(yōu)化策略

1、分布式數(shù)據(jù)庫系統(tǒng)原理中的查詢過程一、定義與說明1 優(yōu)化為了減少優(yōu)化消耗,查詢過程的優(yōu)化的目標(biāo)為得到一個(gè)較優(yōu)解,而不是最優(yōu)解.其中解為一個(gè)消耗較小的關(guān)系運(yùn)算序列或操作序列.2片段片段是指被分散并保存在不同位置的(全局)關(guān)系的子集(局部關(guān)系集合)二、查詢優(yōu)化過程1 查詢語句分解1.1 解析查詢語句1.2 判斷查詢語句語義正確性1.3 消除重復(fù)出現(xiàn)的謂詞1.4 將查詢語句重構(gòu)為較優(yōu)的關(guān)系運(yùn)算序列2 數(shù)據(jù)定位2.1 將分布式(全局)查詢映射為片段查詢2.2 簡(jiǎn)化和重構(gòu)碎片上的查詢，優(yōu)化片段查詢3 全局查詢優(yōu)化3.1 結(jié)合片段上的信息(包括所查詢的屬性元素?cái)?shù)等)進(jìn)行優(yōu)化,得到較優(yōu)片段查詢操作與通訊操作的

2、序列。其中很重要的一方面則是聯(lián)結(jié)操作的執(zhí)行順序。很多現(xiàn)代分布式數(shù)據(jù)庫都使用半聯(lián)結(jié)的方式進(jìn)行優(yōu)化。3.2 半聯(lián)結(jié)：只將表中那些將會(huì)實(shí)際參與聯(lián)結(jié)的元組進(jìn)行傳輸，并在站點(diǎn)上對(duì)這些元組和相關(guān)表進(jìn)行聯(lián)結(jié)操作。4 局部查詢優(yōu)化每個(gè)站點(diǎn)上針對(duì)本地查詢進(jìn)行優(yōu)化。通過Oracle執(zhí)行計(jì)劃推測(cè)優(yōu)化策略一、測(cè)試前提：1 如何查看Oracle執(zhí)行計(jì)劃第一步：登入sql/plus 執(zhí)行命令（無先后順序） set time on; (說明：打開時(shí)間顯示） set autotrace traceonly; (說明：打開自動(dòng)分析統(tǒng)計(jì)，不顯示SQL語句的運(yùn)行結(jié)果)。第二步：輸入你要查看的sql 執(zhí)行第三步：查看結(jié)果2 用戶、庫

3、表說明2.1 用戶1用戶名：XUQIUORG004密碼：XUQIU2.2 用戶2用戶名：需求分析定制網(wǎng)站 ORACLEDB_15密碼：XUQIU2.3 用戶3用戶名：PADISTESTORAGS7_密碼：apple2.3 庫表說明用戶1、用戶2所對(duì)應(yīng)的庫都是需求庫且具有相同的庫表結(jié)構(gòu)，且每個(gè)對(duì)應(yīng)的表中具有相同的數(shù)據(jù);用戶3所對(duì)應(yīng)的庫為仿真庫。二、測(cè)試過程1 登錄SQL/PLUS、開啟執(zhí)行計(jì)劃并創(chuàng)建DBLINK1.1 SQL/PLUS記錄Microsoft Windows XP 版本 5.1.2600(C) 版權(quán)所有 1985-2001 Microso

4、ft Corp.C:Documents and SettingsAdministrator>sqlplusSQL*Plus: Release .0 - Production on 星期二 1月 18 14:45:04 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.請(qǐng)輸入用戶名: xuqiuorg004輸入口令:連接到:Oracle9i Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP and Orac

5、le Data Mining optionsJServer Release .0 - ProductionSQL> set time on;14:45:35 SQL> set autotrace traceonly;14:45:46 SQL> create database link mopishv0link connect to "需求分析定制網(wǎng)站" identified by "XUQIU" using'ORACLEDB_15'數(shù)據(jù)庫鏈接已創(chuàng)建。14:47:52 SQL> s

6、elect * from WORK_INFOmopishv0link;已選擇1161行。執(zhí)行計(jì)劃- 0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'WORK_INFO' ORACLEDB .US.ORAC LE.COM統(tǒng)計(jì)信息- 0 recursive calls 1 db block gets 0 consistent gets 0 physical reads 212 redo size 172850 bytes sent via SQL*Net to client 1219

7、bytes received via SQL*Net from client 158 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1161 rows processed15:35:37 SQL> create database link mopishv1link connect to "PADISTEST" identified by "apple" using'ORAGS7_'數(shù)據(jù)庫鏈接已創(chuàng)建。16:44:09 SQL&g

8、t; select * from ACTIVITYmopishv1link;已選擇452行。執(zhí)行計(jì)劃- 0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'ACTIVITY' ORAGS7.U S.ORACLE .COM2 有條件查詢本地庫表2.1 SQL/PLUS記錄14:48:18 SQL> select work_id from WORK_RULE_INFO where RULE_LEV='操作級(jí)'執(zhí)行計(jì)劃- 0 SELECT STATEMENT Optim

9、izer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'WORK_RULE_INFO'統(tǒng)計(jì)信息- 0 recursive calls 0 db block gets 32 consistent gets 0 physical reads 0 redo size 378 bytes sent via SQL*Net to client 372 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1

10、 rows processed14:49:43 SQL>2.2 記錄分析由TABLE ACCESS (FULL) OF 'WORK_RULE_INFO'我們可以看到在對(duì)本地查詢的時(shí)候雖然有條件限制，但也沒有做過多的優(yōu)化而是直接訪問整個(gè)WORK_RULE_INFO表。3 無條件查詢遠(yuǎn)程庫表3.1 SQL/PLUS記錄14:49:43 SQL> select work_id from WORK_RULE_INFOmopishv0link;已選擇1161行。執(zhí)行計(jì)劃- 0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE 1 0 TAB

11、LE ACCESS (FULL) OF 'WORK_RULE_INFO' ORACLEDB .US.ORAC LE.COM統(tǒng)計(jì)信息- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 18183 bytes sent via SQL*Net to client 1219 bytes received via SQL*Net from client 158 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sort

12、s (disk) 1161 rows processed14:53:57 SQL>3.2 記錄分析與3.1比較可以發(fā)現(xiàn)，訪問遠(yuǎn)程庫表時(shí)，會(huì)在操作后加入遠(yuǎn)程數(shù)據(jù)庫的全局?jǐn)?shù)據(jù)庫名(Global Database Name)表示操作發(fā)生在遠(yuǎn)程數(shù)據(jù)庫服務(wù)上。4 不同庫中的不同表的表間查詢4.1 SQL/PLUS記錄115:02:39 SQL> select WORK_RULE_INFO.OP_AV_BE_TIME1 from WORK_RULE_INFO,WORK_INFOmopishv0link where WORK_INFO.WORK_ID=WORK_RULE_INFO.WORK_ID

13、 and RULE_LEV='操作級(jí)'執(zhí)行計(jì)劃- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 REMOTE* MOPISHV0 LINK.US. ORACLE.C OM 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'WORK_RULE_INFO' 4 3 INDEX (UNIQUE SCAN) OF 'WORK_RULE_INFO_PK' (UNIQUE) 2 SERIAL_FROM_REMOTE SELECT "WORK_ID"

14、; FROM "WORK_INFO" "WORK_INFO "統(tǒng)計(jì)信息- 0 recursive calls 0 db block gets 3118 consistent gets 0 physical reads 0 redo size 390 bytes sent via SQL*Net to client 372 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk)1 rows proce

15、ssed4.2 SQL/PLUS記錄215:09:21 SQL> select WORK_RULE_INFO.OP_AV_BE_TIME1 from WORK_RULE_INFO,WORK_INFOmopishv0link where RULE_LEV='操作級(jí)' and WORK_INFO.WORK_ID=WORK_RULE_INFO.WORK_ID;執(zhí)行計(jì)劃- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 REMOTE* MOPISHV0 LINK.US. ORACLE.C OM 3 1 TABLE

16、ACCESS (BY INDEX ROWID) OF 'WORK_RULE_INFO' 4 3 INDEX (UNIQUE SCAN) OF 'WORK_RULE_INFO_PK' (UNIQUE) 2 SERIAL_FROM_REMOTE SELECT "WORK_ID" FROM "WORK_INFO" "WORK_INFO "統(tǒng)計(jì)信息- 0 recursive calls 0 db block gets 3118 consistent gets 0 physical reads 0 redo si

17、ze 390 bytes sent via SQL*Net to client 372 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk)1 rows processed4.2 SQL/PLUS記錄315:10:12 SQL> select WORK_RULE_INFO.OP_AV_BE_TIME1 from WORK_RULE_INFO,WORK_INFOmopishv0link where RULE_LEV='操作級(jí)

18、' and WORK_INFO.WORK_ID=WORK_RULE_INFO.WORK_ID and RULE_LEV='操作級(jí)'執(zhí)行計(jì)劃- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 REMOTE* MOPISHV0 LINK.US. ORACLE.C OM 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'WORK_RULE_INFO' 4 3 INDEX (UNIQUE SCAN) OF 'WORK_RULE_INFO_PK' (UNIQ

19、UE) 2 SERIAL_FROM_REMOTE SELECT "WORK_ID" FROM "WORK_INFO" "WORK_INFO "統(tǒng)計(jì)信息- 0 recursive calls 0 db block gets 3118 consistent gets 0 physical reads 0 redo size 390 bytes sent via SQL*Net to client 372 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from

20、client 0 sorts (memory) 0 sorts (disk) 1 rows processed15:24:25 SQL>4.4 記錄分析 1通過對(duì)比4.1、4.2與4.3我們可以發(fā)現(xiàn)條件的顛倒與重復(fù)都對(duì)執(zhí)行計(jì)劃沒有影響，這說明在聯(lián)結(jié)之前Oracle會(huì)對(duì)查詢進(jìn)行優(yōu)化。這點(diǎn)符合分布式數(shù)據(jù)庫系統(tǒng)原理中所描述的查詢語句分解的特點(diǎn)。4.5 SQL/PLUS記錄415:24:25 SQL> select WORK_INFO.WORK_NAME from WORK_RULE_INFO,WORK_INFOmopishv0link where WORK_INFO.WORK_ID=WO

21、RK_RULE_INFO.WORK_ID;已選擇1161行。執(zhí)行計(jì)劃- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 REMOTE* MOPISHV0 LINK.US. ORACLE.C OM 3 1 INDEX (UNIQUE SCAN) OF 'WORK_RULE_INFO_PK' (UNIQUE) 2 SERIAL_FROM_REMOTE SELECT "WORK_ID","WORK_NAME" FROM "WORK_INFO " "W

22、ORK_INFO"統(tǒng)計(jì)信息- 0 recursive calls 0 db block gets 1240 consistent gets 0 physical reads 0 redo size 32377 bytes sent via SQL*Net to client 1219 bytes received via SQL*Net from client 158 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1161 rows processed15:35:37 SQL>4.6 記錄分析

23、 2通過對(duì)比4.1、4.2、4.3與4.5可以發(fā)現(xiàn)，Oracle只從遠(yuǎn)程庫表中查詢參與聯(lián)結(jié)的元組，而不是查詢所有列。這與分布式數(shù)據(jù)庫系統(tǒng)原理中所描述的半聯(lián)結(jié)相符。4.7 SQL/PLUS記錄516:57:00 SQL> select WORK_INFO.WORK_NAME,FLOWNAMES2.FLOW_NAME from WORK_RULE_INFO,WORK_INFOmopishv0link,FLOWNAMES2mopishv1link where WORK_INFO.WORK_ID=WORK_RULE_INFO.WORK_ID and FLOWNAMES2.WORK_ID=WORK

24、_RULE_INFO.WORK_ID and trim(COMPANY_CODE)='高法'已選擇327行。執(zhí)行計(jì)劃- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN 2 1 SORT (JOIN) 3 2 NESTED LOOPS 4 3 REMOTE* MOPISHV1 LINK.US. ORACLE.C OM 5 3 INDEX (UNIQUE SCAN) OF 'WORK_RULE_INFO_PK' (UNIQUE) 6 1 SORT (JOIN) 7 6 REMOTE* MOPISHV0 LINK.

25、US. ORACLE.C OM 4 SERIAL_FROM_REMOTE SELECT "COMPANY_CODE","FLOW_NAME","WORK_ID" FROM "FLOWNAMES2" "FLOWNAMES2" WHERE 7 SERIAL_FROM_REMOTE SELECT "WORK_ID","WORK_NAME" FROM "WORK_INFO " "WORK_INFO"統(tǒng)計(jì)信息- 0 recur

26、sive calls 0 db block gets 329 consistent gets 0 physical reads 0 redo size 27050 bytes sent via SQL*Net to client 603 bytes received via SQL*Net from client 46 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 327 rows processed17:30:08 SQL>4.8 SQL/PLUS記錄617:31:33 SQL> select

27、WORK_INFO.WORK_NAME,FLOWNAMES2.FLOW_NAME from WORK_RULE_INFO,WORK_INFOmopishv0link,FLOWNAMES2mopishv1link where WORK_INFO.WORK_ID=WORK_RULE_INFO.WORK_ID and FLOWNAMES2.WORK_ID=WORK_RULE_INFO.WORK_ID and WORK_INFO.SUBSYS_ID='55259'執(zhí)行計(jì)劃- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN 2 1 S

28、ORT (JOIN) 3 2 NESTED LOOPS 4 3 REMOTE* MOPISHV1 LINK.US. ORACLE.C OM 5 3 INDEX (UNIQUE SCAN) OF 'WORK_RULE_INFO_PK' (UNIQUE) 6 1 SORT (JOIN) 7 6 REMOTE* MOPISHV0 LINK.US. ORACLE.C OM 4 SERIAL_FROM_REMOTE SELECT "FLOW_NAME","WORK_ID" FROM "FLOWNAMES 2" "FLO

29、WNAMES2" 7 SERIAL_FROM_REMOTE SELECT "WORK_ID","WORK_NAME","SUBSYS_ID" FRO M "WORK_INFO" "WORK_INFO" WHERE "SUB統(tǒng)計(jì)信息- 0 recursive calls 0 db block gets 329 consistent gets 0 physical reads 0 redo size 814 bytes sent via SQL*Net to client 372

30、 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 5 rows processed17:32:12 SQL>4.9 記錄分析 3通過對(duì)比4.7與4.8可以發(fā)現(xiàn)：Oracle并沒有優(yōu)先執(zhí)行可以減少數(shù)據(jù)傳輸與聯(lián)結(jié)記錄數(shù)的查詢。例如4.8中并沒有優(yōu)先執(zhí)行WORK_INFO上的查詢。而從WHERE字句可以發(fā)現(xiàn)，Oracle并沒有利用之前查詢所得到的結(jié)果。這有兩種可能：1 優(yōu)化得到的是較優(yōu)的結(jié)果而不是最優(yōu)的，因此沒有利用之前查詢所得的

31、結(jié)果。2 Oracle的優(yōu)化不會(huì)動(dòng)態(tài)利用查詢所得到的結(jié)果。4.10 SQL/PLUS記錄717:30:08 SQL> select WORK_INFO.WORK_NAME,FLOWNAMES2.FLOW_NAME from WORK_RULE_INFOmopishv0link,WORK_INFOmopishv0link,FLOWNAMES2mopishv1link where WORK_INFO.WORK_ID=WORK_RULE_INFO.WORK_ID and FLOWNAMES2.WORK_ID=WORK_RULE_INFO.WORK_ID and trim(COMPANY_COD

32、E)='高法'已選擇327行。執(zhí)行計(jì)劃- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN 2 1 MERGE JOIN 3 2 SORT (JOIN) 4 3 REMOTE* MOPISHV1 LINK.US. ORACLE.C OM 5 2 SORT (JOIN) 6 5 REMOTE* MOPISHV0 LINK.US. ORACLE.C OM 7 1 SORT (JOIN) 8 7 REMOTE* MOPISHV0 LINK.US. ORACLE.C OM 4 SERIAL_FROM_REMOTE SELECT "COMPANY_CODE","FLOW_NAME","WORK_ID" FROM "FLOWNAMES2" "FLOWN