nested loop,sort merge join,hash join

最新推荐文章于 2022-04-07 15:08:56 发布

cqu63737

最新推荐文章于 2022-04-07 15:08:56 发布

阅读量155

点赞数

文章标签：数据库 php 大数据

-- 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11990065/viewspace-731822/