Oracle 19C 聚簇表访问路径

CREATE CLUSTER employees_departments_cluster
   (department_id NUMBER(4)) SIZE 512;

CREATE INDEX idx_emp_dept_cluster
   ON CLUSTER employees_departments_cluster;

CREATE TABLE employees2
   CLUSTER employees_departments_cluster (department_id)
   AS SELECT * FROM employees;
 CREATE TABLE departments2
   CLUSTER employees_departments_cluster (department_id)
   AS SELECT * FROM departments;
复制

您可以30按以下方式查询部门中的员工：

SELECT * 
FROM   employees2 
WHERE  department_id = 30;
复制

为了执行扫描，Oracle数据库首先通过扫描集群索引来获取描述部门30的行的rowid（步骤2）。然后，Oracle数据库employees2使用此rowid 查找行中的行（步骤1）。

SQL_ID  b7xk1jzuwdc6t, child number 0
-------------------------------------
SELECT * FROM employees2 WHERE department_id = 30

Plan hash value: 49826199

--------------------------------------------------------------------------------
|Id| Operation            | Name               |Rows|Bytes|Cost(%CPU)| Time    |
--------------------------------------------------------------------------------
| 0| SELECT STATEMENT     |                    |    |     |   2 (100)|         |
| 1|  TABLE ACCESS CLUSTER| EMPLOYEES2         |  6 | 798 |   2   (0)| 00:00:01|
|*2|   INDEX UNIQUE SCAN  |IDX_EMP_DEPT_CLUSTER|  1 |     |   1   (0)| 00:00:01|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPARTMENT_ID"=30)
复制