欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle10g数据库max、min的写法

程序员文章站 2022-07-09 22:47:02
一、有主键情况 1、单查最大的主键object_id(cost:2、consistent gets:100) select max(object_id) from t; 2、...

一、有主键情况

1、单查最大的主键object_id(cost:2、consistent gets:100)

select max(object_id) from t;

2、单查最小的主键object_id(cost:2、consistent gets:69)

select min(object_id) from t;

3、一起查max(object_id),min(object_id) (cost:47、consistent gets 224)

select max(object_id),min(object_id) from t;

4、最优一起查询(cost:4、consistent gets:138)

select max,min from (select max(object_id) max from t)a,(select min(object_id) min from t)b;

结论:

在字段为主键有索引的情况下,单查单个max、min效率最快

在字段为主键有索引的情况下,优化方式查询最快

在字段为主键有索引的情况下,直接max、min的方式最慢

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

二、在t表中object_id不是主键没有唯一索引 并且有null值的情况

SQL> select max(object_id) from t;

MAX(OBJECT_ID)

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

178100

执行计划

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

Plan hash value: 2966233522

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

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

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

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

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

| 2 | TABLE ACCESS FULL| T | 69433 | 881K| 288 (1)| 00:00:04 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

统计信息

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

48 recursive calls

0 db block gets

1099 consistent gets

1024 physical reads

0 redo size

343 bytes sent via SQL*Net to client

350 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select min(object_id) from t;

MIN(OBJECT_ID)

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

2

执行计划

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

Plan hash value: 2966233522

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

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

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

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

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

| 2 | TABLE ACCESS FULL| T | 69433 | 881K| 288 (1)| 00:00:04 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

统计信息

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

4 recursive calls

0 db block gets

1095 consistent gets

0 physical reads

0 redo size

342 bytes sent via SQL*Net to client

350 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

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

MAX(OBJECT_ID) MIN(OBJECT_ID)

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

178100 2

执行计划

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

Plan hash value: 2966233522

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

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

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

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

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

| 2 | TABLE ACCESS FULL| T | 69433 | 881K| 288 (1)| 00:00:04 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

统计信息

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

4 recursive calls

0 db block gets

1095 consistent gets

0 physical reads

0 redo size

406 bytes sent via SQL*Net to client

350 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select max,min from (select max(object_id) max from t) a,(select min(object_id) min from t) b;

MAX MIN

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

178100 2

执行计划

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

Plan hash value: 1937292215

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

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

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

| 0 | SELECT STATEMENT | | 1 | 26 | 575 (1)| 00:00:07 |

| 1 | NESTED LOOPS | | 1 | 26 | 575 (1)| 00:00:07 |

| 2 | VIEW | | 1 | 13 | 288 (1)| 00:00:04 |

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

| 4 | TABLE ACCESS FULL| T | 69433 | 881K| 288 (1)| 00:00:04 |

| 5 | VIEW | | 1 | 13 | 288 (1)| 00:00:04 |

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

| 7 | TABLE ACCESS FULL| T | 69433 | 881K| 288 (1)| 00:00:04 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

统计信息

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

7 recursive calls

0 db block gets

2190 consistent gets

0 physical reads

0 redo size

384 bytes sent via SQL*Net to client

350 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

结论:

优化的方式查询还没有一起查询快,一起查询及单个查询花费的cpu几乎一样多。