Oracle 用最小和最大函数调优SQL

create table test_dept
(dept_id number,
dept_name varchar2(50),
dept_region number);

alter table test_dept
add constraint pk primary key (dept_id);

create table test_emp
(emp_id number,
sal number,
dept_id number ,
constraint fk foreign key (dept_id) references test_dept(dept_id));


insert into test_dept values (10,'IT', 1);
insert into test_dept values (20,'ADMIN', 1);
insert into test_dept values (30,'SUPPORT', 2);
insert into test_dept values (40,'SERVICES', 3);

COMMIT;

SELECT * FROM test_dept;



insert into test_emp values (101,1000, 10);
insert into test_emp values (102,6000, 30);
insert into test_emp values (103,8000, 10);
insert into test_emp values (104,5000, 20);
insert into test_emp values (105,1000, 10);
insert into test_emp values (106,6000, 30);
insert into test_emp values (107,8000, 10);
insert into test_emp values (108,5000, 10);
insert into test_emp values (109,1000, 10);
insert into test_emp values (110,6000, 30);
insert into test_emp values (111,8000, 20);
insert into test_emp values (112,5000, 10);
insert into test_emp values (113,1000, 10);
insert into test_emp values (114,6000, 20);
insert into test_emp values (115,8000, 10);


SELECT * FROM test_emp;


SELECT dept_id, min(sal), max(sal)
from test_emp e
where exists (select 1 from test_dept d where e.dept_id = d.dept_id and dept_region in (1,2))
group by dept_id;

 Plan Hash Value  : 605905731 

----------------------------------------------------------------------------------
| Id  | Operation                       | Name      | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |           |      |       |      |      |
|   1 |   SORT GROUP BY                 |           |      |       |      |      |
|   2 |    NESTED LOOPS                 |           |      |       |      |      |
|   3 |     NESTED LOOPS                |           |      |       |      |      |
|   4 |      TABLE ACCESS FULL          | TEST_EMP  |      |       |      |      |
| * 5 |      INDEX UNIQUE SCAN          | PK        |      |       |      |      |
| * 6 |     TABLE ACCESS BY INDEX ROWID | TEST_DEPT |      |       |      |      |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 5 - access("E"."DEPT_ID"="D"."DEPT_ID")
* 6 - filter("DEPT_REGION"=1 OR "DEPT_REGION"=2)




Now consider that the test_dept has 414 records for the given dept_region and test_emp has 3 million records, in that case the explain plan gives something like 



--------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                    | Rows      | Bytes      | Cost   | Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                         |      5124 |      97356 | 329559 | 01:16:54 |
|   1 |   SORT GROUP BY NOSORT  |                         |      5124 |      97356 | 329559 | 01:16:54 |
|   2 |    NESTED LOOPS         |                         | 154913245 | 2943351655 | 321286 | 01:14:59 |
|   3 |     SORT UNIQUE         |                         |       414 |       2898 |     35 | 00:00:01 |
| * 4 |      TABLE ACCESS FULL  | TEST_DEPT               |       414 |       2898 |     35 | 00:00:01 |
|   5 |     PARTITION RANGE ALL |                         |    374639 |    4495668 |   1552 | 00:00:22 |
| * 6 |      INDEX RANGE SCAN   | PK                      |    374639 |    4495668 |   1552 | 00:00:22 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 4 - filter("D"."DEPT_REGION"=1 OR "D"."DEPT_REGION"=2)
* 6 - access("D"."DEPT_ID"="E"."DEPT_ID")



The execution of the query takes around 12 mnins.

can you help me on reducing this time??
复制

好的，这是我的副本 (可能与您的真实版本不同)，但希望仍然会有所帮助

SQL> 创建表test_dept
  2  (dept_id number,
3页名称变量2(50)，
4 dept_region number);

创建的表。

SQL>
SQL>
SQL> 创建表test_emp
2 (emp_id号，
3 sal数，
  4  dept_id number);

创建的表。

SQL>
SQL> 插入到test_dept
2选择rownum * 10，“dept' | |(rownum * 10)，mod(rownum，50)
3从双连接级别 <= 50*400;

创建20000行。

SQL>
SQL> 在test_emp中插入/* 追加 */
2选择rownum，rownum，(1 mod(rownum，18000))* 10
3从 (从dual connect中选择1，by level <1000)，
4 (从dual connect中按级别 <3000选择1);

创建2996001行。

SQL>
SQL> alter table test_dept
  2  add constraint pk primary key (dept_id);

Table altered.

SQL>
SQL> alter table test_emp
  2  add constraint fk foreign key (dept_id) references test_dept(dept_id);

Table altered.

SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats(''，'test_emp');

PL/SQL过程成功完成。

SQL> exec dbms_stats.gather_table_stats(''，'测试 _ 部门');

PL/SQL过程成功完成。
复制

所以我有20,000个部门，50个地区，3m员工。您的查询在逻辑上等同于:

SQL> 选择e.dept_id，min(sal)，max(sal)
2来自test_emp e，
3测试 _ 部门d
4其中e.dept_id = d.dept_id
5和d.dept_region in (1,2)
6按e.dept_id分组;

最小 (SAL) 最大 (SAL)
----------
2010 200 2988200
3510 350 2988350
4020 401 2988401
...

选择720行。


执行计划
-
Plan hash value: 379542677

------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------
|   0 | SELECT STATEMENT    |           | 18130 |   336K|  2486   (3)| 00:00:01 |
|   1 |  HASH GROUP BY      |           | 18130 |   336K|  2486   (3)| 00:00:01 |
|*  2 |   HASH JOIN         |           |   132K|  2452K|  2480   (2)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| TEST_DEPT |   800 |  6400 |    19   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST_EMP  |  2996K|    31M|  2447   (2)| 00:00:01 |
------------------------

谓词信息 (由操作id标识):
---------------------------------------------------

   2 - access("E"."DEPT_ID"="D"."DEPT_ID")
   3 - filter("D"."DEPT_REGION"=1 OR "D"."DEPT_REGION"=2)


统计
-
         54  recursive calls
         10  db block gets
       8920  consistent gets
0物理读取
          0  redo size
21334字节通过SQL * Net发送到客户端
通过SQL * Net从客户端接收到的1125字节
49 SQL * 往返客户端的净往返
          6  sorts (memory)
0排序 (磁盘)
已处理720行
        
<代码>

So already, 9000 consistent gets in the worst case (lets say all physical multiblock reads IO at 10ms) is only a couple of seconds.  I could look at indexing the relevant columns to avoid TEST_EMP table access, eg

<代码>
SQL> create index test_emp_ix on test_emp ( dept_id, sal );

Index created.

SQL> 选择e.dept_id，min(sal)，max(sal)
2来自test_emp e，
3测试 _ 部门d
4其中e.dept_id = d.dept_id
5和d.dept_region in (1,2)
6按e.dept_id分组;

最小 (SAL) 最大 (SAL)
----------
2010 200 2988200
3510 350 2988350
4020 401 2988401
...

选择720行。


执行计划
-
Plan hash value: 496026648

--------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------
|   0 | SELECT STATEMENT    |             | 18130 |   336K|  1627   (1)| 00:00:01 |
|   1 |  HASH GROUP BY      |             | 18130 |   336K|  1627   (1)| 00:00:01 |
|   2 |   NESTED LOOPS      |             |   132K|  2452K|  1621   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| TEST_DEPT   |   800 |  6400 |    19   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | TEST_EMP_IX |   165 |  1815 |     2   (0)| 00:00:01 |
--------------------------

谓词信息 (由操作id标识):
---------------------------------------------------

   3 - filter("D"."DEPT_REGION"=1 OR "D"."DEPT_REGION"=2)
   4 - access("E"."DEPT_ID"="D"."DEPT_ID")


统计
-
         20  recursive calls
          0  db block gets
       1931  consistent gets
0物理读取
          0  redo size
21334字节通过SQL * Net发送到客户端
通过SQL * Net从客户端接收到的1125字节
49 SQL * 往返客户端的净往返
          6  sorts (memory)
0排序 (磁盘)
已处理720行     
复制

这让我感到沮丧，1900年得到，并注意到我们从未碰过表，因为我们将SAL放入了索引中。(显然，您需要将其与应用程序的其余部分进行权衡)。

有趣的是，您的一个执行计划引用了一个分区模式，所以也许您没有告诉我所有内容，但是让我们继续前进-我们可以查看引用分区，例如

<代码>
SQL> 创建表test_dept
2 (dept_id号主键，
3页名称变量2(50)，
4 dept_region number)
5按哈希划分 (dept_region)
6分区16;

创建的表。

SQL>
SQL>
SQL> 创建表test_emp
2 (emp_id号，
3 sal数，
4 dept_id号不为空，
5删除级联上的约束fk外键 (dept_id) 引用test_dept(dept_id))
6引用分区 (fk);

创建的表。

SQL>
SQL> 插入到test_dept
2选择rownum * 10，“dept' | |(rownum * 10)，mod(rownum，50)
3从双连接级别 <= 50*400;

创建20000行。

SQL>
SQL> 在test_emp中插入/* 追加 */
2选择rownum，rownum，(1 mod(rownum，18000))* 10
3从 (从dual connect中选择1，by level <1000)，
4 (从dual connect中按级别 <3000选择1);

创建2996001行。

SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats(''，'test_emp');

PL/SQL过程成功完成。

SQL> exec dbms_stats.gather_table_stats(''，'测试 _ 部门');

PL/SQL过程成功完成。


SQL> 选择e.dept_id，min(sal)，max(sal)
2来自test_emp e，
3测试 _ 部门d
4其中e.dept_id = d.dept_id
5和d.dept_region in (1,2)
6按e.dept_id分组;

最小 (SAL) 最大 (SAL)
----------
2010 200 2988200
3510 350 2988350
4020 401 2988401
...

选择720行。

执行计划
-
计划哈希值: 2769476757

-------------------------------------------
| Id | 操作 | 名称 | 行 | 字节 | 成本 (% CPU)| 时间 | Pstart | Pstop |
-------------------------------------------
| 0 | SELECT语句 | | 18130 | 336K | 3057 (2)| 00:00:01 |
| 1 | 哈希组 | | 18130 | 336K | 3057 (2)| 00:00:01 |
| 2 | 分区哈希列表 | | 132K | 2452K | 3051 (2)| 00:00:01 | 键 (I) |
| * 3 | 哈希连接 | | 132K | 2452K | 3051 (2)| 00:00:01 |
| * 4 | 表访问完全 | TEST_DEPT | 800 | 6400 | 548 (1)| 00:00:01 | 键 (I) |
| 5 | 表访问完全 | TEST_EMP | 2996K | 31M | 2489 (2)| 00:00:01 | 键 (I) |
-------------------------------------------

谓词信息 (由操作id标识):
---------------------------------------------------

3-访问 (“E”。“DEPT_ID” = “D”。“DEPT_ID”)
4-过滤器 (“D”。“DEPT_REGION” = 1或 “D”。“DEPT_REGION” = 2)


统计
-
15个递归调用
5 db块获取
1719一致获取
0物理读取
132重做大小
21334字节通过SQL * Net发送到客户端
通过SQL * Net从客户端接收到的1125字节
49 SQL * 往返客户端的净往返
2种 (内存)
0排序 (磁盘)
已处理720行



现在我在1700得到没有任何额外的索引。再一次，很明显，你需要权衡这与你的应用程序的其余部分。

所以有很多选择可以探索。