oracle sql select max,select max(id),min(id) from table优化

1、查看数据库版本

SQL> select * from v$version where rownum<2;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

2、创建表和索引

create table t_a as select * from dba_objects;

create index t_a_ind on t_a(object_id);

3、查询最大值

SQL> select max(object_id) from t_a;

执行计划

———————————————————-

Plan hash value: 3226265922

————————————————————————————–

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

————————————————————————————–

| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

| 2 | INDEX FULL SCAN (MIN/MAX)| T_A_IND | 1 | 13 | 2 (0)| 00:00:01 |

————————————————————————————–

Note—— dynamic sampling used for this statement (level=2)

统计信息

———————————————————-

0 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

431 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

4、查询最小值

SQL> select min(object_id) from t_a;

执行计划

———————————————————-

Plan hash value: 3226265922

————————————————————————————–

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

————————————————————————————–

| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | |

|

| 2 | INDEX FULL SCAN (MIN/MAX)| T_A_IND | 1 | 13 | 2 (0)| 00:00:01 |

————————————————————————————–

Note—— dynamic sampling used for this statement (level=2)

统计信息

———————————————————-

0 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

429 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

5、查询最大值和最小值

SQL> select max(object_id),min(object_id) from t_a;

执行计划

———————————————————-

Plan hash value: 2127980459

—————————————————————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

—————————————————————————

| 0 | SELECT STATEMENT | | 1 | 13 | 293 (1)| 00:00:04 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

| 2 | TABLE ACCESS FULL| T_A | 78093 | 991K| 293 (1)| 00:00:04 |

—————————————————————————

Note—— dynamic sampling used for this statement (level=2)

统计信息

———————————————————-

4 recursive calls

0 db block gets

1119 consistent gets

1044 physical reads

0 redo size

502 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

6、查询最大值和最小值(排除null)

SQL> select max(object_id),min(object_id) from t_a where object_id is not null;

执行计划

———————————————————-

Plan hash value: 1214261695

———————————————————————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

|

———————————————————————————

| 0 | SELECT STATEMENT | | 1 | 13 | 50 (2)| 00:00:01

|

| 1 | SORT AGGREGATE | | 1 | 13 | |

|

|* 2 | INDEX FAST FULL SCAN| T_A_IND | 78093 | 991K| 50 (2)| 00:00:01

|

———————————————————————————

Predicate Information (identified by operation id):

—————————————————

2 – filter(“OBJECT_ID” IS NOT NULL)

Note—— dynamic sampling used for this statement (level=2)

统计信息

———————————————————-

4 recursive calls

0 db block gets

242 consistent gets

0 physical reads

0 redo size

502 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

7、分别查询最大值和最小值

SQL> select (select max(object_id) from t_a) max,(select min(object_id) from t_a) min from dual;

执行计划

———————————————————-

Plan hash value: 312201770

————————————————————————————–

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

————————————————————————————–

| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | |

| 2 | INDEX FULL SCAN (MIN/MAX)| T_A_IND | 1 | 13 | 2 (0)| 00:00:01 |

| 3 | SORT AGGREGATE | | 1 | 13 | |

| 4 | INDEX FULL SCAN (MIN/MAX)| T_A_IND | 1 | 13 | 2 (0)| 00:00:01 |

| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |

————————————————————————————–

Note—— dynamic sampling used for this statement (level=2)

统计信息

———————————————————-

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

480 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

说明:

1、单查询最大值或者最小值,sql会自动走index

2、如果同时查询最大值和最小值,sql会使用全表扫描,而不是我们想象的索引快速扫描

3、加上where 排除掉null的情况,sql使用索引快速扫描,原因是:在不能确定索引列不为null(或者没有排除掉null)的情况下,不会使用索引快速扫描,而sql为了保证正确而采用了全表扫描

4、INDEX FULL SCAN (MIN/MAX)扫描效率很搞,所以把最大值,最小值分开查询,提高执行效率

5、其他写法

SQL> select (select /*+ index_asc(t_a t_a_ind) */ object_id from t_a where rownu

m=1) min ,(select /*+ index_desc(t_a t_a_ind) */ object_id from t_a where rownum=1)

max from dual;

执行计划

———————————————————-

Plan hash value: 674626822

—————————————————————————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

—————————————————————————————

| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:

00:01 |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | INDEX FULL SCAN | T_A_IND | 78093 | 991K| 2 (0)| 00:

00:01 |

|* 3 | COUNT STOPKEY | | | | |

| 4 | INDEX FULL SCAN DESCENDING| T_A_IND | 78093 | 991K| 2 (0)| 00:

00:01 |

| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |

—————————————————————————————

Predicate Information (identified by operation id):

—————————————————

1 – filter(ROWNUM=1)

3 – filter(ROWNUM=1)

Note—— dynamic sampling used for this statement (level=2)

统计信息

———————————————————-

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

480 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

注意:

1)这个要正确执行,需要一个前提条件object_id这列要为not null限制条件,不然会hint提示无效

2)从执行计划的统计信息上看,这个和INDEX FULL SCAN (MIN/MAX)方式的执行效率一样

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值