Oracle 11g Release 1 (11.1)——聚簇和非聚簇的简单查询比较
本文内容
- 创建非聚簇的相关表
- 创建聚簇
- 简单查询比较
本文简单比较建立聚簇后，对查询的影响。虽然就几条数据，但多少也能说明点问题。有机会的话，再试下大数据量的比较。
创建非聚簇的相关模式对象
创建 EMPLOYEES 和 DEPTMENTS 表。
-- Create table
create table EMPLOYEES
(
EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(3)
)
tablespace MYTBS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table EMPLOYEES
add constraint PK_EMPLOYEES_EMPNO primary key (EMPNO)
using index
tablespace MYTBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create table
create table DEPTMENTS
(
DEPTNO NUMBER(3) not null,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
)
tablespace MYTBS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table DEPTMENTS
add constraint PK_DEPTMENTS_DEPTNO primary key (DEPTNO)
using index
tablespace MYTBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
创建聚簇的相关模式对象
创建聚簇 emp_dept、聚簇的表 emp 和 dept，以及聚簇索引 emp_dept_index。
-- Create Cluster
CREATE CLUSTER emp_dept (deptno NUMBER(3))
SIZE 600
TABLESPACE mytbs
STORAGE (INITIAL 200K
NEXT 300K
MINEXTENTS 2
PCTINCREASE 33);
-- Create table
create table EMP
(
EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(3)
)
cluster EMP_DEPT (DEPTNO);
-- Create/Recreate primary, unique and foreign key constraints
alter table EMP
add constraint PK_EMP_EMPNO primary key (EMPNO)
using index
tablespace MYTBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create table
create table DEPT
(
DEPTNO NUMBER(3) not null,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
)
cluster EMP_DEPT (DEPTNO);
-- Create/Recreate primary, unique and foreign key constraints
alter table DEPT
add constraint PK_DEPT_DEPTNO primary key (DEPTNO)
using index
tablespace MYTBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create Cluster index
CREATE INDEX emp_dept_index
ON CLUSTER emp_dept
TABLESPACE MYTBS
STORAGE (INITIAL 50 K
NEXT 50 K
MINEXTENTS 2
MAXEXTENTS 10
PCTINCREASE 33);
向非聚簇的表和聚簇的表插入数据
下载并执行 .sql 文件，插入数据。
简单查询比较
示例 1：比较等值连接
SQL>; EXPLAIN PLAN
2 SET STATEMENT_ID = 'ex_plan1'
3 FOR
4 SELECT *
5 FROM employees t1, deptments t2
6 WHERE t1.deptno = t2.deptno;
Explained
SQL>; SELECT plan_table_output
2 FROM TABLE (DBMS_XPLAN.display (NULL, 'ex_plan1', 'TYPICAL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3396288718
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPTMENTS | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPTMENTS_DEPTNO | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."DEPTNO"="T2"."DEPTNO")
filter("T1"."DEPTNO"="T2"."DEPTNO")
18 rows selected
SQL>; EXPLAIN PLAN
2 SET STATEMENT_ID = 'ex_plan2'
3 FOR
4 SELECT *
5 FROM emp t1, dept t2
6 WHERE t1.deptno = t2.deptno;
Explained
SQL>; SELECT plan_table_output
2 FROM TABLE (DBMS_XPLAN.display (NULL, 'ex_plan2', 'TYPICAL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2705476012
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS CLUSTER| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | EMP_DEPT_INDEX | 1 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."DEPTNO"="T2"."DEPTNO")
filter("T1"."DEPTNO"="T2"."DEPTNO")
18 rows selected
SQL>
示例 2：比较左外连接
SQL>; EXPLAIN PLAN
2 SET STATEMENT_ID = 'ex_plan1'
3 FOR
4 SELECT *
5 FROM employees t1 LEFT JOIN deptments t2 ON (t1.deptno = t2.deptno);
Explained
SQL>; SELECT plan_table_output
2 FROM TABLE (DBMS_XPLAN.display (NULL, 'ex_plan1', 'TYPICAL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 47896472
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 812 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 14 | 532 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPTMENTS | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."DEPTNO"="T2"."DEPTNO"(+))
15 rows selected
SQL>; EXPLAIN PLAN
2 SET STATEMENT_ID = 'ex_plan2'
3 FOR
4 SELECT *
5 FROM emp t1 LEFT JOIN dept t2 ON (t1.deptno = t2.deptno);
Explained
SQL>; SELECT plan_table_output
2 FROM TABLE (DBMS_XPLAN.display (NULL, 'ex_plan2', 'TYPICAL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3577968021
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 17 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 14 | 812 | 17 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS CLUSTER| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."DEPTNO"="T2"."DEPTNO"(+))
15 rows selected
SQL>
示例 3：比较选择部门编号为 10 的所有员工
SQL>; EXPLAIN PLAN
2 SET STATEMENT_ID = 'ex_plan1'
3 FOR
4 SELECT t1.dname,
5 t1.loc,
6 t2.empno,
7 t2.ename,
8 t2.job,
9 t2.mgr,
10 t2.hiredate,
11 t2.sal
12 FROM deptments t1 LEFT JOIN employees t2 ON (t1.deptno = t2.deptno)
13 WHERE t1.deptno = 10;
Explained
SQL>; SELECT plan_table_output
2 FROM TABLE (DBMS_XPLAN.display (NULL, 'ex_plan1', 'TYPICAL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2928143533
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 171 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 3 | 171 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPTMENTS | 1 | 20 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPTMENTS_DEPTNO | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMPLOYEES | 3 | 111 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."DEPTNO"=10)
4 - filter("T2"."DEPTNO"(+)=10)
17 rows selected
SQL>; EXPLAIN PLAN
2 SET STATEMENT_ID = 'ex_plan2'
3 FOR
4 SELECT t1.dname,
5 t1.loc,
6 t2.empno,
7 t2.ename,
8 t2.job,
9 t2.mgr,
10 t2.hiredate,
11 t2.sal
12 FROM dept t1 LEFT JOIN emp t2 ON (t1.deptno = t2.deptno)
13 WHERE t1.deptno = 10;
Explained
SQL>; SELECT plan_table_output
2 FROM TABLE (DBMS_XPLAN.display (NULL, 'ex_plan2', 'TYPICAL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4275711305
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 171 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 3 | 171 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT_DEPTNO | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS CLUSTER | EMP | 3 | 111 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."DEPTNO"=10)
4 - filter("T2"."DEPTNO"(+)=10)
17 rows selected
SQL>
备注：
- 执行计划输出中 Operation 列最后一行。前者的访问路径是全表扫描，而后则是聚簇。这个区别决定了之后的差异。
- 执行计划输出中 Rows 和 Bytes 列相同。因为两个语句的 WHERE 子句相同。
- 但是，Cost 列（CPU 利用率）就有差异了。
