数据库(oracle)连接如何处理过滤条件

Question

当我们在Oracle中创建两个表之间的连接时，在一个或两个表上添加一些额外的过滤条件，Oracle是先连接表然后过滤还是先过滤条件然后连接?

或者简单地说，这两个哪个是更好的查询

假设我们有两个表Employee和Department，我想要employees所有Employee + dept detail，其中员工工资大于50000

查询1:

查询员工e、部门d中e.dept_id=d.id且e.salary>50000的e.name, d.name;

查询2:

Answer 1

通常它会先过滤尽可能多的内容。从explain计划中，您可以实际看到在哪里执行过滤，以及在哪里执行连接，例如，创建一些表和数据:

create table employees (id integer, dept_id integer, salary number);
create table dept (id integer, dept_name varchar2(10));
insert into dept values (1, 'IT');
insert into dept values (2, 'HR');
insert into employees
select level, mod(level, 2) + 1, level * 1000
from dual connect by level <= 100;
create index employee_uk1 on employees (id);
create index dept_uk1 on dept (id);
exec dbms_stats.gather_table_stats(user, 'DEPT');

现在，如果我解释你提供的两个查询，你会发现Oracle在幕后将每个查询转换为相同的计划(它并不总是执行你认为它做的事情- Oracle有"重写"查询的许可，并且它做了很多):

explain plan for
select e.*, d.*
from employees e, dept d
where e.dept_id = d.id
and e.salary > 5000;
select * from table(dbms_xplan.display());
    ------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |    96 |  1536 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |           |    96 |  1536 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT      |     2 |    12 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_UK1  |     2 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |           |    96 |   960 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMPLOYEES |    96 |   960 |     3   (0)| 00:00:01 |
4 - access("E"."DEPT_ID"="D"."ID")
    filter("E"."DEPT_ID"="D"."ID")
5 - filter("E"."SALARY">5000)

注意应用于查询的筛选操作。现在解释另一个查询:

explain plan for
select e.*, d.*
from (select * from employees where salary > 5000) e, dept d
where e.dept_id = d.id;
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |    96 |  1536 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |           |    96 |  1536 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT      |     2 |    12 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_UK1  |     2 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |           |    96 |   960 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMPLOYEES |    96 |   960 |     3   (0)| 00:00:01 |    
4 - access("EMPLOYEES"."DEPT_ID"="D"."ID")
    filter("EMPLOYEES"."DEPT_ID"="D"."ID")
5 - filter("SALARY">5000)

一旦你学会了如何获取解释计划以及如何阅读它们，你就可以大致了解Oracle在执行你的查询时正在做什么。