nested loop,sort merge join,hash join

arthurtangel發表於2012-06-04
OOP
-- Create table
create table T_MPED_INFO
(
  MPED_ID             INTEGER not null,
  MPED_INDEX          NUMBER(6) not null,
  MPED_NAME           VARCHAR2(50),
  PARAM_FLAG          CHAR(1),
  COMM_PORT           NUMBER(5),
  MPED_TYPE           CHAR(1),
  USER_FLAG           CHAR(1),
  REMARK              VARCHAR2(50),
  METER_ID            INTEGER,
  TERMINAL_ID         INTEGER,
  COLL_ID             INTEGER,
  MP_MODE             VARCHAR2(8),
  MEASURE_SITE        VARCHAR2(8),
  DEVICE_NO           VARCHAR2(10),
  MEASURE_ADD         VARCHAR2(40),
  CUST_ID             INTEGER,
  IS_TOTALMETER       CHAR(1),
  IS_METERFLAG        CHAR(1),
  CREATE_DATE         VARCHAR2(20) default TO_CHAR(sysdate,'YYYY-MM-DD'),
  PARAM_FLAG_DATETIME VARCHAR2(20) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
  OPERATOR            VARCHAR2(20),
  OPERATETIME         VARCHAR2(20),
  LINE_ID             INTEGER
)
tablespace TS_BASE_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 

-- Create/Recreate primary, unique and foreign key constraints 
alter table T_MPED_INFO
  add constraint PK_T_MPED_INFO primary key (MPED_ID)
  using index 
  tablespace TS_BASE_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index FK1_T_MPED_INFO on T_MPED_INFO (METER_ID)
  tablespace TS_BASE_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create unique index FK2_T_MPED_INFO on T_MPED_INFO (TERMINAL_ID, MPED_INDEX)
  tablespace TS_BASE_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index FK3_T_MPED_INFO on T_MPED_INFO (CUST_ID)
  tablespace TS_BASE_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- Create table
create table T_METER_INFO
(
  METER_ID          INTEGER not null,
  METER_NAME        VARCHAR2(50),
  METER_NUMBER      VARCHAR2(20),
  DEVICENO          VARCHAR2(20),
  LINE_STYLE.        CHAR(1),
  PROD_FACTORY      VARCHAR2(50),
  METERTYPENO       VARCHAR2(20),
  METER_TYPE        CHAR(1),
  FACTORYNO         VARCHAR2(20),
  RETURN_ZERO_VALUE NUMBER(8),
  SHOW_WORD         VARCHAR2(20),
  IS_METER_FLAG     CHAR(1),
  ORIENTATION       CHAR(1),
  ADDRESS           VARCHAR2(50),
  LONGITUDE         NUMBER(8),
  LATITUDE          NUMBER(8),
  DOUBLERATE        NUMBER(8),
  ENERGYRATE1       NUMBER(11,4),
  ENERGYRATE2       NUMBER(11,4),
  ENERGYRATE3       NUMBER(11,4),
  ENERGYRATE4       NUMBER(11,4),
  PT                VARCHAR2(20),
  CT                VARCHAR2(20),
  CHECK_DATE        VARCHAR2(20),
  INTEGER_CNT       NUMBER(3),
  DECIMAL_CNT       NUMBER(3),
  RATE_CNT          NUMBER(3),
  I_SPEC            CHAR(1),
  U_SPEC            CHAR(1),
  IS_TOTALMETER     CHAR(1),
  CUST_ID           INTEGER,
  METER_STATUS      CHAR(1),
  REMOTE_STATUS     CHAR(1),
  REMARK            VARCHAR2(50),
  PRCE_GRADE        CHAR(1),
  TOTALENERGY       NUMBER(11,4),
  SET_NUMBER        VARCHAR2(20),
  MP_NAME           VARCHAR2(50),
  MP_NUMBER         VARCHAR2(20),
  MP_ADDR           VARCHAR2(50),
  READ_METER_NUM    NUMBER,
  PRP_ENERGYVALUE   NUMBER(11,4),
  CHARGE_SIGN       CHAR(1) default 0,
  RUN_TIME          DATE,
  OPERATOR          VARCHAR2(20),
  OPERATETIME       VARCHAR2(20),
  CITY_ID           NUMBER
)
tablespace TS_BASE_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 

-- Create/Recreate primary, unique and foreign key constraints 
alter table T_METER_INFO
  add constraint PK_METER_ID primary key (METER_ID)
  using index 
  tablespace TS_BASE_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index FK1_T_METER_INFO on T_METER_INFO (CUST_ID)
  tablespace TS_BASE_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index FK2_T_METER_INFO on T_METER_INFO (CITY_ID)
  tablespace TS_BASE_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


t_mped_info,t_meter_info大概有300W記錄數。


--nested loops,可以用於（非等值，等值）連線,當過濾後的記錄數比較小時才會nested loops

select * from t_mped_info a,t_meter_info b 
where a.meter_id=b.meter_id and a.mped_id=10000;

SELECT STATEMENT, GOAL = ALL_ROWS 5 1 124 5 ALL_ROWS 40217
 NESTED LOOPS 5 1 124 5 40217
  TABLE ACCESS BY INDEX ROWID PTEST T_MPED_INFO 3 1 76 3 ANALYZED 23004
   INDEX UNIQUE SCAN PTEST PK_T_MPED_INFO 2 1 2 ANALYZED 15293
  TABLE ACCESS BY INDEX ROWID PTEST T_METER_INFO 2 3181500 152712000 2 ANALYZED 17213
   INDEX UNIQUE SCAN PTEST PK_METER_ID 1 1 1 ANALYZED 9021

select * from t_mped_info a,t_meter_info b 
where a.meter_id>b.meter_id and a.mped_id=10000;

SELECT STATEMENT, GOAL = ALL_ROWS 9903 1590750 197253000 9646 ALL_ROWS 3657729904
 NESTED LOOPS 9903 1590750 197253000 9646 3657729904
  TABLE ACCESS BY INDEX ROWID PTEST T_MPED_INFO 3 1 76 3 ANALYZED 23004
   INDEX UNIQUE SCAN PTEST PK_T_MPED_INFO 2 1 2 ANALYZED 15293
  TABLE ACCESS FULL PTEST T_METER_INFO 9900 1590750 76356000 9643 ANALYZED 3657706900
  
--nested loops,where過濾後的記錄數小的表為driving table  
select * from t_mped_info a,t_meter_info b 
where a.meter_id=b.meter_id and a.meter_id=90;

SELECT STATEMENT, GOAL = ALL_ROWS 6 1 124 6 ALL_ROWS 47279
 NESTED LOOPS 6 1 124 6 47279
  TABLE ACCESS BY INDEX ROWID PTEST T_METER_INFO 3 1 48 3 ANALYZED 23484
   INDEX UNIQUE SCAN PTEST PK_METER_ID 2 1 2 ANALYZED 15293
  TABLE ACCESS BY INDEX ROWID PTEST T_MPED_INFO 3 1 76 3 ANALYZED 23794
   INDEX RANGE SCAN PTEST FK1_T_MPED_INFO 2 1 2 ANALYZED 15493
  


--走sort merge join,可以用於（非等值，等值）連線
select * from t_mped_info a,t_meter_info b 
where a.meter_id>b.meter_id and a.mped_id>90;

SELECT STATEMENT, GOAL = ALL_ROWS 35722232 5060827957500 627542666730000 99009 ALL_ROWS 506092942099254
 MERGE JOIN 35722232 5060827957500 627542666730000 99009 506092942099254
  SORT JOIN 51751 3181411 241787236 51533 3091937408
   TABLE ACCESS BY INDEX ROWID PTEST T_MPED_INFO 51751 3181411 241787236 51533 ANALYZED 3091937408
    INDEX FULL SCAN PTEST FK1_T_MPED_INFO 6815 3243986 6766 ANALYZED 696980863
  SORT JOIN 47995 3181500 152712000 47476 7372561847
   TABLE ACCESS FULL PTEST T_METER_INFO 9900 3181500 152712000 9643 ANALYZED 3657706900


select emp.ename,dept.dname
from emp,dept
where emp.deptno=dept.deptno;

| 0 | SELECT STATEMENT | | 12 | 264 | 8 (25)| 00:00:01 |
| 1 | MERGE JOIN | | 12 | 264 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 4 | 52 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 12 | 108 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| EMP | 12 | 108 | 3 (0)| 00:00:01 |


--走hash join,不可以用於非等值連線，只能用於等值連線，當where後的記錄數較大時會選擇hash join
select * from t_mped_info a,t_meter_info b 
where a.meter_id=b.meter_id and a.mped_id>90;

SELECT STATEMENT, GOAL = ALL_ROWS 42881 3181411 394494964 42365 ALL_ROWS 7325536675
 HASH JOIN 42881 3181411 394494964 42365 7325536675
  TABLE ACCESS FULL PTEST T_METER_INFO 9900 3181500 152712000 9643 ANALYZED 3657706900
  TABLE ACCESS FULL PTEST T_MPED_INFO 10634 3181411 241787236 10471 ANALYZED 2311414346


select * from t_mped_info a,t_meter_info b 
where a.meter_id=b.meter_id;
 
SELECT STATEMENT, GOAL = ALL_ROWS 42881 3181411 394494964 42365 ALL_ROWS 7325536675
 HASH JOIN 42881 3181411 394494964 42365 7325536675
  TABLE ACCESS FULL PTEST T_METER_INFO 9900 3181500 152712000 9643 ANALYZED 3657706900
  TABLE ACCESS FULL PTEST T_MPED_INFO 10634 3181411 241787236 10471 ANALYZED 2311414346
  
 
 
--nested loops,結果集相對小
巢狀迴圈連線的工作方式是這樣的： 
1、 Oracle首先選擇一張表作為連線的驅動表，這張表也稱為外部表（Outer Table）。由驅動表進行驅動連線的表或資料來源稱為內部表（Inner Table）。 
2、 提取驅動表中符合條件的記錄，與被驅動表的連線列進行關聯查詢符合條件的記錄。在這個過程中，Oracle首先提取驅動表中符合條件的第一條記錄，再與內部表的連線列進行關聯查詢相應的記錄行。在關聯查詢的過程中，Oracle會持續提取驅動表中其他符合條件的記錄與內部表關聯查詢。這兩個過程是並行進行的，因此巢狀迴圈連線返回前幾條記錄的速度是非常快的。在這裡需要說明的是，由於Oracle最小的IO單位為單個資料塊，因此在這個過程中Oracle會首先提取驅動表中符合條件的單個資料塊中的所有行，再與內部表進行關聯連線查詢的，然後提取下一個資料塊中的記錄持續地迴圈連線下去。當然，如果單行記錄跨越多個資料塊的話，就是一次單條記錄進行關聯查詢的。 
3、NESTED LOOP
driving table
可以用於（非等值，等值）連線,當where過濾後的記錄數比較小時才會nested loops，where過濾後的記錄數小的表為driving table。

通常要求驅動表的記錄（符合條件的記錄，通常通過高效的索引訪問）較少，且被驅動表連線列有唯一索引或者選擇性強的非唯一索引時，巢狀迴圈連線的效率是比較高的。

巢狀迴圈連線返回前幾行的記錄是非常快的，這是因為使用了巢狀迴圈後，不需要等到全部迴圈結束再返回結果集，而是不斷地將查詢出來的結果集返回。在這種情況下，終端使用者將會快速地得到返回的首批記錄，且同時等待Oracle內部處理其他記錄並返回。如果查詢的驅動表的記錄數非常多，或者被驅動表的連線列上無索引或索引不是高度可選的情況，巢狀迴圈連線的效率是非常低的。

--sort merge join，結果集相對較大
在排序合併連線中是沒有驅動表的概念的，兩個互相連線的表按連線列的值先排序，排序完後形成的結果集再互相進行合併連線提取符合條件的記錄。

可以用於（非等值，等值）連線。較大資料行，且為非等值連線時，用sort merge join，已經逐漸被hash join取代。

由於Oracle中排序操作的開銷是非常消耗資源的，當結果集很大時排序合併連線的效能很差。
--hash join，結果集相對大
雜湊連線分為兩個階段，如下。 
1、 構建階段：優化器首先選擇一張小表做為驅動表，運用雜湊函式對連線列進行計算產生一張雜湊表。通常這個步驟是在記憶體（hash_area_size）裡面進行的，因此運算很快。 
2、 探測階段：優化器對被驅動表的連線列運用同樣的雜湊函式計算得到的結果與前面形成的雜湊表進行探測返回符合條件的記錄。這個階段中如果被驅動表的連線列的值沒有與驅動表連線列的值相等的話，那麼這些記錄將會被丟棄而不進行探測。關於雜湊連線更深層次的原理可以參考Itpub上網友logzgh發表的“hash join演算法原理”帖子（http://www.itpub.net/showthread.php?threadid=315494）。 

雜湊連線比較適用於返回大資料量結果集的連線。使用雜湊連線必須是在CBO模式下，引數hash_join_enabled設定為true，且只適用於等值連線。

Hash join不需要在驅動表上存在索引。

從Oracle9i開始，雜湊連線由於其良好的效能漸漸取代了原來的排序合併連線。

--跟表連線有關的幾個HINT 
（1、）use_nl（t1,t2）：表示對錶t1、t2關聯時採用巢狀迴圈連線。 
（2、）use_merge（t1,t2）：表示對錶t1、t2關聯時採用排序合併連線。 
（3、）use_hash（t1,t2）：表示對錶t1、t2關聯時採用雜湊連線。 
（4、）leading(t)：表示在進行表連線時，選擇t為驅動表。 
（5、）ordred：要求優化器按from列出的表順序進行連線。 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11990065/viewspace-731822/，如需轉載，請註明出處，否則將追究法律責任。

相關文章