gpt-4-0314· OpenAI
WildChat-4.8M (AI2)
oracle 举例1个回表的例子,并列出执行计划,并提出优化过程
- User
oracle 举例1个回表的例子,并列出执行计划,并提出优化过程
- Assistant
假设有两个表:
employees表:CREATE TABLE employees ( id NUMBER(10) PRIMARY KEY, name VARCHAR(50), department_id NUMBER(10) );departments表:CREATE TABLE departments ( id NUMBER(10) PRIMARY KEY, name VARCHAR(50) );现在需要查询所有员工及其所属部门的名称。这需要用到回表操作。先写一个基本的查询:
SELECT e.id, e.name, d.name AS department_name FROM employees e JOIN departments d ON e.department_id = d.id;接下来看查询的执行计划:
EXPLAIN PLAN FOR SELECT e.id, e.name, d.name AS department_name FROM employees e JOIN departments d ON e.department_id = d.id;这可能会产生如下的执行计划:
----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000 | 30000 | 5 (20)| | 1 | MERGE JOIN | | 1000 | 30000 | 5 (20)| | 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1000 | 20000 | 2 (0)| | 3 | INDEX FULL SCAN | DEPT_INDEX | 1000 | | 1 (0)| | 4 | SORT JOIN | | 100 | 5000 | 3 (34)| | 5 | TABLE ACCESS FULL | DEPARTMENTS | 100 | 5000 | 2 (0)| -----------------------------------------------------------------------------------优化过程:
- 为
departments表创建索引以加快查询速度:
CREATE INDEX dept_dept_id_idx ON departments(id);- 为
employees表的department_id列创建索引:
CREATE INDEX emp_dept_id_idx ON employees(department_id);执行计划可能如下:
----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000 | 30000 | 4 (0)| | 1 | NESTED LOOPS | | 1000 | 30000 | 4 (0)| | 2 | NESTED LOOPS | | 1000 | 30000 | 4 (0)| | 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1000 | 20000 | 2 (0)| | 4 | INDEX FULL SCAN | DEPT_INDEX | 1000 | | 1 (0)| | 5 | INDEX UNIQUE SCAN | DEPT_DEPT_ID_IDX| 1 | | 0 (0)| | 6 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 50 | 1 (0)| -----------------------------------------------------------------------------------此时,查询已优化。使用了索引而非全表扫描。
- 为