對于Oracle的一些基本知識要加強學習，我從網上抄了三個人對這個題目的見解下來，供已參考。
NESTED LOOP:
對于被連接的數據子集較小的情況，嵌套循環連接是個較好的選擇。在嵌套循環中，內
表被外表驅動，外表返回的每一行都要在內表中檢索找到與它匹配的行，因此整個查詢返回
的結果集不能太大（大于1 萬不適合），要把返回子集較小表的作為外表（CBO 默認外表是
驅動表），而且在內表的連接字段上一定要有索引。當然也可以用ORDERED 提示來改變CBO
默認的驅動表，使用USE_NL(table_name1 table_name2)可是強制CBO 執行嵌套循環連接。
HASH JOIN :
散列連接是CBO 做大數據集連接時的常用方式，優化器使用兩個表中較小的表（或數據
源）利用連接鍵在內存中建立散列表，然后掃描較大的表并探測散列表，找出與散列表匹配
的行。
這種方式適用于較小的表完全可以放于內存中的情況，這樣總成本就是訪問兩個表的成
本之和。但是在表很大的情況下并不能完全放入內存，這時優化器會將它分割成若干不同的
分區，不能放入內存的部分就把該分區寫入磁盤的臨時段，此時要有較大的臨時段從而盡量
提高I/O 的性能。
也可以用USE_HASH(table_name1 table_name2)提示來強制使用散列連接。如果使用散
列連接HASH_AREA_SIZE 初始化參數必須足夠的大，如果是9i，Oracle建議使用SQL工作區
自動管理，設置WORKAREA_SIZE_POLICY 為AUTO，然后調整PGA_AGGREGATE_TARGET 即可。
排序合并連接
通常情況下散列連接的效果都比排序合并連接要好，然而如果行源已經被排過序，在執
行排序合并連接時不需要再排序了，這時排序合并連接的性能會優于散列連接。可以使用
USE_MERGE(table_name1 table_name2)來強制使用排序合并連接
Nested loop join:
步驟：確定一個驅動表(outer table)，另一個表為inner table，驅動表中的每一行與inner表中的相應記錄JOIN。類似一個嵌套的循環。適用于驅動表的記錄集比較小（<10000）而且inner表需要有有效的訪問方法（Index）。需要注意的是：JOIN的順序很重要，驅動表的記錄集一定要小，返回結果集的響應時間是最快的。
cost = outer access cost + (inner access cost * outer cardinality)
| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)|
| 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|
| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|
| 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |
EMPLOYEES為outer table, JOBS為inner table.
Hash join
步驟：將兩個表中較小的一個在內存中構造一個HASH表（對JOIN KEY），掃描另一個表，同樣對JOIN KEY進行HASH后探測是否可以JOIN。適用于記錄集比較大的情況。需要注意的是：如果HASH表太大，無法一次構造在內存中，則分成若干個partition，寫入磁盤的temporary segment，則會多一個寫的代價，會降低效率。
cost = (outer access cost * # of hash partitions) + inner access cost
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 665 | 13300 | 8 (25)|
| 1 | HASH JOIN | | 665 | 13300 | 8 (25)|
| 2 | TABLE ACCESS FULL | ORDERS | 105 | 840 | 4 (25)|
| 3 | TABLE ACCESS FULL | ORDER_ITEMS | 665 | 7980 | 4 (25)|
--------------------------------------------------------------------------
ORDERS為HASH TABLE，ORDER_ITEMS掃描
Sort merge join
步驟：將兩個表排序，然后將兩個表合并。通常情況下，只有在以下情況發生時，才會使用此種JOIN方式：
1.RBO模式
2.不等價關聯(>,<,>=,<=,<>)
3.HASH_JOIN_ENABLED=false
4.數據源已排序
cost = (outer access cost * # of hash partitions) + inner access cost
轉載biti的一段話:
舉例，表連接返回一條記錄
存在兩個表，一個 10條記錄 ，一個1000萬條記錄
若2表都存在連接字段索引，若以小表為驅動表，則
代價：
10* (通過索引在大表查詢一條記錄的代價)
若以大表為驅動表：
1000萬 * (通過索引在小表中查詢一條記錄的代價)
通過索引獲取一條記錄，10rows的表，代價通常在 3 blocks
索引2塊，表一塊
而如果是1000萬的表，索引可能達到4塊表一塊
這樣一來參考上面的計算，你說哪個更好？很顯然！
小表查詢參考
SQL> create table test as select * from all_objects where rownum < 11;
Table created.
SQL> create index test_index on test(object_id);
Index created.
SQL> select object_id from test;
OBJECT_ID
----------
18159
7781
4841
19891
22549
17099
17712
4287
10107
19135
10 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
6 consistent gets
0 physical reads
0 redo size
736 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select * from test where object_id = 4287;
OWNER OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
SYS /1033c8a_SqlTypeWithMethods
4287 JAVA CLASS
14-NOV-00 03-JUL-03 2003-07-03:11:18:19 INVALID N N N
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'TEST_INDEX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1157 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
|
|
| TAG 表聯接 oracle join |
posted on 2010-02-01 15:04
何克勤 閱讀(698)
評論(0) 編輯 收藏 所屬分類:
數據庫和SQL