Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0314· OpenAIAi2 WildChat-4.8M (AI2)

oracle 举例1个回表的例子,并列出执行计划,并提出优化过程

  1. User

    oracle 举例1个回表的例子,并列出执行计划,并提出优化过程

  2. 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)|
    -----------------------------------------------------------------------------------
    

    优化过程:

    1. 为 departments 表创建索引以加快查询速度:
    CREATE INDEX dept_dept_id_idx ON departments(id);
    
    1. 为 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)|
    -----------------------------------------------------------------------------------
    

    此时,查询已优化。使用了索引而非全表扫描。