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 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 INDEX emp_dept_index
ON CLUSTER emp_dept
TABLESPACE MYTBS
STORAGE (INITIAL 50 K
NEXT 50 K
MINEXTENTS 2
MAXEXTENTS 10
PCTINCREASE 33);
向非聚簇的表和聚簇的表插入数据
下载并执行 sql 文件，插入数据。
简单查询比较
非聚簇的执行计划
SQL> explain plan SET statement_id = 'ex_plan1' for
2 select *
3 from employees t1, deptments t2
4 where t1.deptno = t2.deptno
5 and t1.deptno = 10;
Explained
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(null,'ex_plan1','TYPICAL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4145658673
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 174 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 174 | 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 | 114 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."DEPTNO"=10) 4 - filter("T1"."DEPTNO"=10) 17 rows selected
SQL>
聚簇的执行计划
SQL> explain plan SET statement_id = 'ex_plan2' for
2 select *
3 from emp t1, dept t2
4 where t1.deptno = t2.deptno
5 and t1.deptno = 10;
Explained
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(null,'ex_plan2','TYPICAL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3957749479
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 174 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 174 | 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 | 114 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."DEPTNO"=10) 4 - filter("T1"."DEPTNO"=10) 17 rows selected
SQL>
区别：
- 执行计划输出中 Operation 列最后一行。前者的访问路径是全表扫描，而后则是聚簇。这个区别决定了之后的差异。
- 执行计划输出中 Rows 和 Bytes 列相同。因为两个语句的 WHERE 子句相同。
- 但是，Cost 列，CPU 利用率就有差异了。
TAG:
