首页 > 数据库 > mysql教程 > 如何访问索引最大最小值min/max scan

如何访问索引最大最小值min/max scan

WBOY
发布: 2016-06-07 16:37:48
原创
1627 人浏览过

?? 访问索引的最大和最小值,ORACLE做了哪些优化呢?不同的版本是否有限制呢?索引是有序存储的,因此,获取索引的最大值,只需要扫描最右(最左desc索引)叶子块,最小值,只需要扫描最左(最右desc索引)叶子块即可,在RBO中就有这种优化访问路径了。见下

?? 访问索引的最大和最小值,ORACLE做了哪些优化呢?不同的版本是否有限制呢?索引是有序存储的,因此,获取索引的最大值,只需要扫描最右(最左desc索引)叶子块,最小值,只需要扫描最左(最右desc索引)叶子块即可,在RBO中就有这种优化访问路径了。见下图(摘自ORACLE 11G CONCEPTS):

如下例:
单独访问MAX,MIN,可以走INDEX FULL SCAN MIN/MAX访问路径

dingjun123@ORADB> show rel
release 1102000100

DROP TABLE t;
CREATE TABLE t AS SELECT * FROM dba_objects;
CREATE INDEX idx_t ON t(object_id);

dingjun123@ORADB> SELECT MAX(object_id) FROM t;
1 row selected.

Execution Plan
———————————————————-
Plan hash value: 3689784082
————————————————————————————
| 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)| IDX_T | ? ? 1 | ? ?13 | ? ? 2 ? (0)| 00:00:01 |
————————————————————————————
Note
—–
? ?- dynamic sampling used for this statement (level=2)

统计
————————————————————-
? ? ? ? ? 0 ?递归调用
? ? ? ? ? 0 ?db 块获取
? ? ? ? ? 2 ?一致的获取
? ? ? ? ? 0 ?物理读取
? ? ? ? ? 0 ?重做大小
? ? ? ? 430 ?通过 SQL*Net 发送到客户端的字节
? ? ? ? 415 ?通过 SQL*Net 从客户端接收的字节
? ? ? ? ? 2 ?SQL*Net 往返于客户端
? ? ? ? ? 0 ?排序(记忆)
? ? ? ? ? 0 ?排序(磁盘)
? ? ? ? ?已处理 1 行

dingjun123@ORADB> SELECT MIN(object_id) FROM t;
已选择 1 行。

执行计划
————————————————————-
计划哈希值:3689784082
——————————————————————————————
|身份证号?|手术 ? ? ? ? ? ? ? ? ?|姓名?|行?|字节 |成本(%CPU)|时间 ? ? |
——————————————————————————————
| ? 0 |选择语句? ? ? ? ? | ? ? ? | ? ? 1 | ? ?13 | ? ? 2 ? (0)| 00:00:01 |
| ? 1 | ?对聚合进行排序? ? ? ? ? ?| ? ? ? | ? ? 1 | ? ?13 | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 2 | ??索引全扫描(最小/最大)| IDX_T | ? ? 1 | ? ?13 | ? ? 2 ? (0)| 00:00:01 |
——————————————————————————————

注意
——–
? ?- 用于此语句的动态采样 (level=2)

统计
————————————————————-
? ? ? ? ? 0 ?递归调用
? ? ? ? ? 0 ?db 块获取
? ? ? ? ? 2 ?一致的获取
? ? ? ? ? 0 ?物理读取
? ? ? ? ? 0 ?重做大小
? ? ? ? 428 ?通过 SQL*Net 发送到客户端的字节
? ? ? ? 415 ?通过 SQL*Net 从客户端接收的字节
? ? ? ? ? 2 ?SQL*Net 往返于客户端
? ? ? ? ? 0 ?排序(记忆)
? ? ? ? ? 0 ?排序(磁盘)
? ? ? ? ?已处理 1 行


通过这种高效的访问路径,只需要扫描最左或最右边的一个叶子块即可,只找1行数据,上面统计信息显示只需要2个IO就搞定了。如果要一条SQL同时获得MIN/MAX呢?

–先收集统计信息
dingjun123@ORADB> EXEC dbms_stats.gather_table_stats(ownname => USER,tabname => ‘t’,estimate_percent => 100,cascade => TRUE);
PL/SQL procedure successfully completed.

?

dingjun123@ORADB> SELECT MAX(object_id) max_obj,MIN(OBJECT_id) min_obj FROM t;
1 row selected.

Execution Plan
———————————————————-
Plan hash value: 2966233522
—————————————————————————
| Id ?| Operation ? ? ? ? ?| Name | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
—————————————————————————
| ? 0 | SELECT STATEMENT ? | ? ? ?| ? ? 1 | ? ?13 | ? 299 ? (1)| 00:00:04 |
| ? 1 | ?SORT AGGREGATE ? ?| ? ? ?| ? ? 1 | ? ?13 | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 2 | ??TABLE ACCESS FULL| T ? ?| 75885 | ? 963K| ? 299 ? (1)| 00:00:04 |
—————————————————————————

–先收集统计信息 dingjun123@ORADB> EXEC dbms_stats.gather_table_stats(ownname => USER,tabname => ‘t’,estimate_percent => 100,cascade => TRUE); PL/SQL procedure successfully completed. ? dingjun123@ORADB> SELECT MAX(object_id) max_obj,MIN(OBJECT_id) min_obj FROM t; 1 row selected. Execution Plan ———————————————————- Plan hash value: 2966233522 ————————————————————————— | Id ?| Operation ? ? ? ? ?| Name | Rows ?| Bytes | Cost (%CPU)| Time ? ? | ————————————————————————— | ? 0 | SELECT STATEMENT ? | ? ? ?| ? ? 1 | ? ?13 | ? 299 ? (1)| 00:00:04 | | ? 1 | ?SORT AGGREGATE ? ?| ? ? ?| ? ? 1 | ? ?13 | ? ? ? ? ? ?| ? ? ? ? ?| | ? 2 | ??TABLE ACCESS FULL| T ? ?| 75885 | ? 963K| ? 299 ? (1)| 00:00:04 | —————————————————————————

? ? 竟然走的全表扫描,很显然,我这里的索引,是比表小很多,应该只访问索引就可以了,虽然object_id无NOT NULL约束,但是max/min(列)运算已经告之ORACLE,这里肯定不包含NULL,但是:

dingjun123@ORADB> SELECT MAX(object_id) max_obj,MIN(OBJECT_id) min_obj FROM t?WHERE object_id IS NOT NULL;
1 row selected.

Execution Plan
———————————————————-
Plan hash value: 2371838348
——————————————————————————-
| Id ?| Operation ? ? ? ? ? ? | Name ?| Rows ?| Bytes | Cost (%CPU)| Time ? ? |
——————————————————————————-
| ? 0 | SELECT STATEMENT ? ? ?| ? ? ? | ? ? 1 | ? ? 5 | ? ?48 ? (3)| 00:00:01 |
| ? 1 | ?SORT AGGREGATE ? ? ? | ? ? ? | ? ? 1 | ? ? 5 | ? ? ? ? ? ?| ? ? ? ? ?|
|* ?2 | ??INDEX FAST FULL SCAN| IDX_T | 75263 | ? 367K| ? ?48 ? (3)| 00:00:01 |
——————————————————————————-
Predicate Information (identified by operation id):
—————————————————
? ?2 – filter(“OBJECT_ID” IS NOT NULL)

dingjun123@ORADB> SELECT MAX(object_id) max_obj,MIN(OBJECT_id) min_obj FROM t?WHERE object_id IS NOT NULL;
1 row selected. Execution Plan
———————————————————-
Plan hash value: 2371838348
——————————————————————————-
| Id ?| Operation ? ? ? ? ? ? | Name ?| Rows ?| Bytes | Cost (%CPU)| Time ? ? |
——————————————————————————-
| ? 0 | SELECT STATEMENT ? ? ?| ? ? ? | ? ? 1 | ? ? 5 | ? ?48 ? (3)| 00:00:01 |
| ? 1 | ?SORT AGGREGATE ? ? ? | ? ? ? | ? ? 1 | ? ? 5 | ? ? ? ? ? ?| ? ? ? ? ?|
|* ?2 | ??INDEX FAST FULL SCAN| IDX_T | 75263 | ? 367K| ? ?48 ? (3)| 00:00:01 |
——————————————————————————-
Predicate Information (identified by operation id):
—————————————————
? ?2 – filter(“OBJECT_ID” IS NOT NULL)

? ? 显式加WHERE OBJECT_ID IS NOT NULL告诉ORACLE,那么计划改变,走INDEX FAST FULL SCAN,这是正确的。这可以认定是此版本下ORACLE优化器的一个限制。通过实验发现,单个组函数对索引运算,可以走索引,但是2个或以上的就不行了。

–单个组函数对索引运算,走索引
dingjun123@ORADB> SELECT SUM(object_id) ?FROM t;
Execution Plan
———————————————————-
Plan hash value: 2371838348
——————————————————————————-
| Id ?| Operation ? ? ? ? ? ? | Name ?| Rows ?| Bytes | Cost (%CPU)| Time ? ? |
——————————————————————————-
| ? 0 | SELECT STATEMENT ? ? ?| ? ? ? | ? ? 1 | ? ? 5 | ? ?48 ? (3)| 00:00:01 |
| ? 1 | ?SORT AGGREGATE ? ? ? | ? ? ? | ? ? 1 | ? ? 5 | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 2 | ??INDEX FAST FULL SCAN| IDX_T | 75264 | ? 367K| ? ?48 ? (3)| 00:00:01 |
——————————————————————————-

–多个组函数对索引运算,不走索引,需要NOT NULL约束或手动加IS NOT NULL条件
dingjun123@ORADB> SELECT SUM(object_id) ?,count(object_id) FROM t ;
Execution Plan
———————————————————-
Plan hash value: 2966233522
—————————————————————————
| Id ?| Operation ? ? ? ? ?| Name | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
—————————————————————————
| ? 0 | SELECT STATEMENT ? | ? ? ?| ? ? 1 | ? ? 5 | ? 299 ? (1)| 00:00:04 |
| ? 1 | ?SORT AGGREGATE ? ?| ? ? ?| ? ? 1 | ? ? 5 | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 2 | ??TABLE ACCESS FULL| T ? ?| 75264 | ? 367K| ? 299 ? (1)| 00:00:04 |
—————————————————————————

–无NOT NULL,使用HINT也是失效的
dingjun123@ORADB> SELECT/* index(t)*/ SUM(object_id) ?,count(object_id) FROM t ;
执行计划
————————————————————-
计划哈希值:2966233522
————————————————————————
|身份证号?|手术 ? ? ? ? ?|名称 |行?|字节 |成本(%CPU)|时间 ? ? |
————————————————————————
| ? 0 |选择语句? | ? ? ?| ? ? 1 | ? ? 5 | ? 299? (1)| 00:00:04 |
| ? 1 | ?对聚合进行排序? ?| ? ? ?| ? ? 1 | ? ? 5 | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 2 | ??表访问已满| T? ?| 75264 | 75264 ? 367K| ? 299? (1)| 00:00:04 |
————————————————————————

dingjun123@ORADB> SELECT SUM(object_id) ?,count(object_id) FROM t?WHERE object_id IS NOT NULL;
执行计划
————————————————————-
计划哈希值:2371838348
———————————————————————————-
|身份证号?|手术 ? ? ? ? ? ? |姓名?|行?|字节 |成本(%CPU)|时间 ? ? |
———————————————————————————-
| ? 0 |选择语句? ? ?| ? ? ? | ? ? 1 | ? ? 5 | ? ?48 ? (3)| 00:00:01 |
| ? 1 | ?对聚合进行排序? ? ? | ? ? ? | ? ? 1 | ? ? 5 | ? ? ? ? ? ?| ? ? ? ? ?|
|* ?2 | ??索引快速全扫描| IDX_T | 75263 | 75263 ? 367K| ? ?48 ? (3)| 00:00:01 |
———————————————————————————-
谓词信息(通过操作id标识):
——————————————————
? ?2 – 过滤器(“OBJECT_ID”不为空)

–多个组函数实验,快捷,当然组多个函数

? ? ??那么这种情况下,ORACLE为什么不走INDEX FULL?MIN/MAX呢,很显然SELECT MIN,MAX… FROM 是不行的。可以转换一下思路:既然单个组函数操作,可以走索引,特别是MIN,MAX的操作,可以高效走INDEX FULL MIN,MAX,那么就可以使用2条SQL,然后合并即可:

–使用UNION ALL,缺点,不能直接知道谁大谁小,还得进一步运算
–访问2次索引,使用的都是FULL MIN/MAX路径,因此IO增加1倍,4个IO

dingjun123@ORADB> SELECT MIN(object_id) FROM t
? 2 ?UNION ALL
? 3 ?SELECT MAX(object_id) FROM t;

2 rows selected.

Execution Plan
———————————————————-
Plan hash value: 2039144771
————————————————————————————-
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ?| Rows ?| Bytes | Cost (%CPU)| Time ? ? |
————————————————————————————-
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? | ? ? 2 | ? ?10 | ? ? 4 ?(50)| 00:00:01 |
| ? 1 |??UNION-ALL?? ? ? ? ? ? ? ? ?| ? ? ? | ? ? ? | ? ? ? | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 2 | ? SORT AGGREGATE ? ? ? ? ? ?| ? ? ? | ? ? 1 | ? ? 5 | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 3 | ? ?INDEX FULL SCAN (MIN/MAX)| IDX_T | ? ? 1 | ? ? 5 | ? ? 2 ? (0)| 00:00:01 |
| ? 4 | ? SORT AGGREGATE ? ? ? ? ? ?| ? ? ? | ? ? 1 | ? ? 5 | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 5 | ? ?INDEX FULL SCAN (MIN/MAX)| IDX_T | ? ? 1 | ? ? 5 | ? ? 2 ? (0)| 00:00:01 |
————————————————————————————-

Statistics
———————————————————-
? ? ? ? ? 0 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ? ? 4 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ? 468 ?bytes sent via SQL*Net to client
? ? ? ? 415 ?bytes received via SQL*Net from client
? ? ? ? ? 2 ?SQL*Net roundtrips to/from client
? ? ? ? ? 0 ?sorts (memory)
? ? ? ? ? 0 ?sorts (disk)
? ? ? ? ? 2 ?rows processed

–使用标量子查询,好处,最大哪个列是最大值,哪个列是最小值
dingjun123@ORADB> SELECT (SELECT MIN(object_id) from t) min_obj,
? 2 ?(SELECT MAX(object_id) FROM t) max_obj
? 3 ?FROM dual;
1 row selected.

Execution Plan
———————————————————-
Plan hash value: 3635878085
————————————————————————————
| Id ?| Operation ? ? ? ? ? ? ? ? ?| Name ?| Rows ?| Bytes | Cost (%CPU)| Time ? ? |
————————————————————————————
| ? 0 | SELECT STATEMENT ? ? ? ? ? | ? ? ? | ? ? 1 | ? ? ? | ? ? 2 ? (0)| 00:00:01 |
| ? 1 | ?SORT AGGREGATE ? ? ? ? ? ?| ? ? ? | ? ? 1 | ? ? 5 | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 2 | ? INDEX FULL SCAN (MIN/MAX)| IDX_T | ? ? 1 | ? ? 5 | ? ? 2 ? (0)| 00:00:01 |
| ? 3 | ?SORT AGGREGATE ? ? ? ? ? ?| ? ? ? | ? ? 1 | ? ? 5 | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 4 | ? INDEX FULL SCAN (MIN/MAX)| IDX_T | ? ? 1 | ? ? 5 | ? ? 2 ? (0)| 00:00:01 |
| ? 5 | ?FAST DUAL ? ? ? ? ? ? ? ? | ? ? ? | ? ? 1 | ? ? ? | ? ? 2 ? (0)| 00:00:01 |
————————————————————————————

Statistics
———————————————————-
? ? ? ? ? 1 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ? ? 4 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ? 487 ?bytes sent via SQL*Net to client
? ? ? ? 415 ?bytes received via SQL*Net from client
? ? ? ? ? 2 ?SQL*Net roundtrips to/from client
? ? ? ? ? 0 ?sorts (memory)
? ? ? ? ? 0 ?sorts (disk)
? ? ? ? ? 1 ?rows processed

? ? 第一种UNION ALL方式可以行列转换一下:

dingjun123@ORADB> SELECT MAX(decode(rn,1,val)) min_obj, MAX(decode(rn,2,val)) max_obj
? 2 ?FROM (
? 3 ?SELECT MIN(object_id) val,1 rn FROM t
? 4 ?UNION ALL
? 5 ?SELECT MAX(object_id),2 rn FROM t
? 6 ?);

? ?MIN_OBJ ? ?MAX_OBJ
———- ———-
? ? ? ? ?2 ? ? 108164

dingjun123@ORADB> SELECT MAX(decode(rn,1,val)) min_obj, MAX(decode(rn,2,val)) max_obj
? 2 ?FROM (
? 3 ?SELECT MIN(object_id) val,1 rn FROM t
? 4 ?UNION ALL
? 5 ?SELECT MAX(object_id),2 rn FROM t
? 6 ?); ? ?MIN_OBJ ? ?MAX_OBJ
———- ———-
? ? ? ? ?2 ? ? 108164

? ?下面举一个实例说明INDEX FULL SCAN MIN/MAX的使用:
需求:查询出最小OBJECT_ID的所有信息。用多种方法实现:

–先将object_id变为NOT NULL约束
DELETE FROM t WHERE object_id IS NULL;
ALTER TABLE t MODIFY object_id NOT NULL;

–1.分析函数,因为OBJECT_ID有索引,11G的分页函数也可以谓词推进,如果走索引可以WINDOW NOSORT STOPKEY,效率不错,5个IO
dingjun123@ORADB> SELECT *
? 2 ?FROM (
? 3 ?SELECT t.*,row_number() over(ORDER BY object_id) rn
? 4 ?FROM t
? 5 ?) WHERE rn=1;
1 row selected.

执行计划
————————————————————-
计划哈希值:233755475
——————————————————————————————
|身份证号?|手术 ? ? ? ? ? ? ? ? ? ? |姓名?|行?|字节 |成本(%CPU)|时间 ? ? |
——————————————————————————————
| ? 0 |选择语句? ? ? ? ? ? ?| ? ? ? | 75264 | 75264 ? ?15M| ?1478? (1)| 00:00:18 |
|* ?1 | ?看法 ? ? ? ? ? ? ? ? ? ? ? ? | ? ? ? | 75264 | 75264 ? ?15M| ?1478? (1)| 00:00:18 |
|* ?2 | ??窗口无排序停止键?? ? ? | ? ? ? | 75264 | 75264 ?7129K| ?1478? (1)| 00:00:18 |
| ? 3 | ? ?按索引 ROWID 访问表| T? ? | 75264 | 75264 ?7129K| ?1478? (1)| 00:00:18 |
| ? 4 | ? ?索引全扫描? ? ? ? ? | IDX_T | 75264 | 75264 ? ? ? | ? 169? (1)| 00:00:03 |
——————————————————————————————
谓词信息(通过操作id标识):
——————————————————
? ?1 – 过滤器(“RN”=1)
? ?2 – 过滤器(ROW_NUMBER() OVER ( ORDER BY “OBJECT_ID”)
统计
————————————————————-
? ? ? ? ? 0 ?递归调用
? ? ? ? ? 0 ?db 块获取
? ? ? ? ??5 ?一致获取
? ? ? ? ? 0 ?物理读取
? ? ? ? ? 0 ?重做大小
? ? ? ?1451 ?通过 SQL*Net 发送到客户端的字节
? ? ? ? 415 ?通过 SQL*Net 从客户端接收的字节
? ? ? ? ? 2 ?SQL*Net 往返于客户端
? ? ? ? ? 0 ?排序(记忆)
? ? ? ? ? 0 ?排序(磁盘)
? ? ? ? ?已处理 1 行

–2.使用传统分页ROWNUM,走STOPKEY,3个IO,效率最好
dingjun123@ORADB>选择 x.*
? 2 ?来自 (
? 3 ?SELECT * FROM t ORDER BY object_id
? 4 ?) x WHERE ROWNUM=1;
已选择 1 行。

执行计划
————————————————————-
计划哈希值:3436459561
——————————————————————————————
|身份证号?|手术 ? ? ? ? ? ? ? ? ? ? |姓名?|行?|字节 |成本(%CPU)|时间 ? ? |
——————————————————————————————
| ? 0 |选择语句? ? ? ? ? ? ?| ? ? ? | ? ? 1 | ? 207 | 207 ? ? 3? (0)| 00:00:01 |
|* ?1 | ?计数停止键? ? ? ? ? ? ? ?| ? ? ? | ? ? ? | ? ? ? | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 2 | ?看法 ? ? ? ? ? ? ? ? ? ? ? ?| ? ? ? | ? ? 1 | ? 207 | 207 ? ? 3? (0)| 00:00:01 |
| ? 3 | ? ?按索引 ROWID 访问表| T? ? | 75264 | 75264 ?7129K| ? ? 3? (0)| 00:00:01 |
| ? 4 | ? ?索引全扫描? ? ? ? ? | IDX_T | ? ? 1 | ? ? ? | ? ? 2 ? (0)| 00:00:01 |
——————————————————————————————
谓词信息(通过操作id标识):
——————————————————
? ?1 – 过滤器(ROWNUM=1)

统计
————————————————————-
? ? ? ? ? 0 ?递归调用
? ? ? ? ? 0 ?db 块获取
? ? ? ? ??3 ?一致获取
? ? ? ? ? 0 ?物理读取
? ? ? ? ? 0 ?重做大小
? ? ? ?1392 ?通过 SQL*Net 发送到客户端的字节
? ? ? ? 415 ?通过 SQL*Net 从客户端接收的字节
? ? ? ? ? 2 ?SQL*Net 往返于客户端
? ? ? ? ? 0 ?排序(记忆)
? ? ? ? ? 0 ?排序(磁盘)
? ? ? ? ?已处理 1 行

–3.子查询实现高效,子查询走的FULL INDEX SCAN?MIN MAX,外部查询条件又是OBJECT_ID,走INDEX RANGE SCAN,6个IO,效率比上面的差,但是不算太差
dingjun123@ORADB>从 t
中选择 * ? 2 ?哪里 t.object_id=
? 3 ?(从 t 中选择 MIN(object_id));
已选择 1 行。

执行计划
————————————————————-
计划哈希值:72615852
——————————————————————————————
|身份证号?|手术 ? ? ? ? ? ? ? ? ? ?|姓名 ? |行?|字节 |成本(%CPU)|时间 ? ? |
——————————————————————————————
| ? 0 |选择语句? ? ? ? ? ? | ? ? ? ?| ? ? 1 | ? ?97 | ? ? 4 ? (0)| 00:00:01 |
| ? 1 | ?按索引 ROWID 访问表 | T? ? ?| ? ? 1 | ? ?97 | ? ? 2 ? (0)| 00:00:01 |
|* ?2 | ?索引范围扫描? ? ? ? ? | IDX_T?| ? ? 1 | ? ? ? | ? ? 1 ? (0)| 00:00:01 |
| ? 3 | ? ?对聚合进行排序? ? ? ? ? ?| ? ? ? ?| ? ? 1 | ? ? 5 | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 4 | ? ?索引全扫描(最小/最大)| IDX1_T | ? ? 1 | ? ? 5 | ? ? 2 ? (0)| 00:00:01 |
——————————————————————————————
谓词信息(通过操作id标识):
—————————————————–

? ?2 – access(“T”.”OBJECT_ID”= (SELECT MIN(“OBJECT_ID”) FROM “T” “T”))

统计
————————————————————-
? ? ? ? ? 0 ?递归调用
? ? ? ? ? 0 ?db 块获取
? ? ? ? ??6 ?一致获取
? ? ? ? ? 0 ?物理读取
? ? ? ? ? 0 ?重做大小
? ? ? ?1392 ?通过 SQL*Net 发送到客户端的字节
? ? ? ? 415 ?通过 SQL*Net 从客户端接收的字节
? ? ? ? ? 2 ?SQL*Net 往返于客户端
? ? ? ? ? 0 ?排序(记忆)
? ? ? ? ? 0 ?排序(磁盘)
? ? ? ? ?已处理 1 行

? ? 当然,如果要同时找最大最小值索引的全部信息

–不能图方便,不是简单的SQL就是好的SQL,无法走STOP KEY和降序索引扫描,逻辑读1474
dingjun123@ORADB> SELECT *
? 2 ?FROM (
? 3 ?SELECT t.*,row_number() over(ORDER BY object_id) rn1,
? 4 ?row_number() over(ORDER BY object_id DESC) rn2
? 5 ?FROM t
? 6 ?) WHERE rn1=1 OR rn2=1;
2 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1020799068
————————————————————————————————
| Id ?| Operation ? ? ? ? ? ? ? ? ? ? ?| Name ?| Rows ?| Bytes |TempSpc| Cost (%CPU)| Time ? ? |
————————————————————————————————
| ? 0 | SELECT STATEMENT ? ? ? ? ? ? ? | ? ? ? | 75264 | ? ?16M| ? ? ? | ?3149 ? (1)| 00:00:38 |
|* ?1 | ?VIEW ? ? ? ? ? ? ? ? ? ? ? ? ?| ? ? ? | 75264 | ? ?16M| ? ? ? | ?3149 ? (1)| 00:00:38 |
| ? 2 | ? WINDOW SORT ? ? ? ? ? ? ? ? ?| ? ? ? | 75264 | ?7129K| ?9880K| ?3149 ? (1)| 00:00:38 |
| ? 3 | ? ?WINDOW NOSORT ? ? ? ? ? ? ? | ? ? ? | 75264 | ?7129K| ? ? ? | ?3149 ? (1)| 00:00:38 |
| ? 4 | ? ? TABLE ACCESS BY INDEX ROWID| T ? ? | 75264 | ?7129K| ? ? ? | ?1478 ? (1)| 00:00:18 |
| ? 5 | ? ? ?INDEX FULL SCAN ? ? ? ? ? | IDX_T | 75264 | ? ? ? | ? ? ? | ? 169 ? (1)| 00:00:03 |
————————————————————————————————

Predicate Information (identified by operation id):
—————————————————
? ?1 – filter(“RN1″=1 OR “RN2″=1)

Statistics
———————————————————-
? ? ? ? ? 0 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ???1474 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ?1639 ?bytes sent via SQL*Net to client
? ? ? ? 415 ?bytes received via SQL*Net from client
? ? ? ? ? 2 ?SQL*Net roundtrips to/from client
? ? ? ? ? 1 ?sorts (memory)
? ? ? ? ? 0 ?sorts (disk)
? ? ? ? ? 2 ?rows processed

–使用UNION ALL,两条语句都很高效,后面的可以走DESC扫描并STOP KEY,逻辑读8
dingjun123@ORADB> SELECT *
? 2 ?FROM (
? 3 ?SELECT t.*,row_number() over(ORDER BY object_id) rn
? 4 ?FROM t
? 5 ?) WHERE rn=1
? 6 ?UNION ALL
? 7 ?SELECT *
? 8 ?FROM (
? 9 ?SELECT t.*,row_number() over(ORDER BY object_id DESC) rn
?10 ?FROM t
?11 ?) WHERE rn=1;
2 rows selected.

执行计划
————————————————————-
计划哈希值:4213848416
——————————————————————————————-
|身份证号?|手术 ? ? ? ? ? ? ? ? ? ? ?|姓名?|行?|字节 |成本(%CPU)|时间 ? ? |
——————————————————————————————-
| ? 0 |选择语句? ? ? ? ? ? ? | ? ? ? | ? 15万| ? ?31M| ?2955 ?(51)| 00:00:36 |
| ? 1 | ?联合所有? ? ? ? ? ? ? ? ? ? | ? ? ? | ? ? ? | ? ? ? | ? ? ? ? ? ?| ? ? ? ? ?|
|* ?2 | ?看法 ? ? ? ? ? ? ? ? ? ? ? ? | ? ? ? | 75264 | 75264 ? ?15M| ?1478? (1)| 00:00:18 |
|* ?3 | ? ?窗口NO排序停止键? ? ? | ? ? ? | 75264 | 75264 ?7129K| ?1478? (1)| 00:00:18 |
| ? 4 | ? ?按索引 ROWID 访问表| T? ? | 75264 | 75264 ?7129K| ?1478? (1)| 00:00:18 |
| ? 5 | ? ? ?索引全扫描? ? ? ? ? | IDX_T | 75264 | 75264 ? ? ? | ? 169? (1)| 00:00:03 |
|* ?6 | ?看法 ? ? ? ? ? ? ? ? ? ? ? ? | ? ? ? | 75264 | 75264 ? ?15M| ?1478? (1)| 00:00:18 |
|* ?7 | ? ?窗口NO排序停止键? ? ? | ? ? ? | 75264 | 75264 ?7129K| ?1478? (1)| 00:00:18 |
| ? 8 | ? ?按索引 ROWID 访问表| T? ? | 75264 | 75264 ?7129K| ?1478? (1)| 00:00:18 |
| ? 9 | ? ? ?索引完整扫描降序| IDX_T | 75264 | 75264 ? ? ? | ? 169? (1)| 00:00:03 |
——————————————————————————————-
谓词信息(通过操作id标识):
——————————————————
? ?2 – 过滤器(“RN”=1)
? ?3 – 过滤器(ROW_NUMBER() OVER ( ORDER BY “OBJECT_ID”)
? ?6 – 过滤器(“RN”=1)
? ?7 – 过滤器(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION(“OBJECT_ID”) DESC
? ? ? ? ? ? ? )
统计
————————————————————-
? ? ? ? ? 0 ?递归调用
? ? ? ? ? 0 ?db 块获取
? ? ? ??? 8 ?一致的获取
? ? ? ? ? 0 ?物理读取
? ? ? ? ? 0 ?重做大小
? ? ? ?1571 ?通过 SQL*Net 发送到客户端的字节
? ? ? ? 415 ?通过 SQL*Net 从客户端接收的字节
? ? ? ? ? 2 ?SQL*Net 往返于客户端
? ? ? ? ? 0 ?排序(记忆)
? ? ? ? ? 0 ?排序(磁盘)
? ? ? ? ?已处理 2 行

–传统ROWNUM分页UNION ALL,效率最好,逻辑读6
dingjun123@ORADB>选择 x.*
? 2 ?来自 (
? 3 ?SELECT * FROM t ORDER BY object_id
? 4 ?) x WHERE ROWNUM=1
? 5 ?联合所有
? 6 ?选择 x.*
? 7 ?来自 (
? 8 ?SELECT * FROM t ORDER BY object_id DESC
? 9 ?) x WHERE ROWNUM=1;
已选择 2 行。

执行计划
————————————————————-
计划哈希值:1989929593
——————————————————————————————-
|身份证号?|手术 ? ? ? ? ? ? ? ? ? ? ?|姓名?|行?|字节 |成本(%CPU)|时间 ? ? |
——————————————————————————————-
| ? 0 |选择语句? ? ? ? ? ? ? | ? ? ? | ? ? 2 | ? 414 | 414 ?2955 ?(51)| 00:00:36 |
| ? 1 | ?联合所有? ? ? ? ? ? ? ? ? ? | ? ? ? | ? ? ? | ? ? ? | ? ? ? ? ? ?| ? ? ? ? ?|
|* ?2 | ?计数停止键? ? ? ? ? ? ? ?| ? ? ? | ? ? ? | ? ? ? | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 3 | ? ?看法 ? ? ? ? ? ? ? ? ? ? ? ?| ? ? ? | 75264 | 75264 ? ?14M| ?1478? (1)| 00:00:18 |
| ? 4 | ? ?按索引 ROWID 访问表| T? ? | 75264 | 75264 ?7129K| ?1478? (1)| 00:00:18 |
| ? 5 | ? ? ?索引全扫描? ? ? ? ? | IDX_T | 75264 | 75264 ? ? ? | ? 169? (1)| 00:00:03 |
|* ?6 | ?计数停止键? ? ? ? ? ? ? ?| ? ? ? | ? ? ? | ? ? ? | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 7 | ? ?看法 ? ? ? ? ? ? ? ? ? ? ? ?| ? ? ? | 75264 | 75264 ? ?14M| ?1478? (1)| 00:00:18 |
| ? 8 | ? ?按索引 ROWID 访问表| T? ? | 75264 | 75264 ?7129K| ?1478? (1)| 00:00:18 |
| ? 9 | ? ? ?索引完整扫描降序| IDX_T | 75264 | 75264 ? ? ? | ? 169? (1)| 00:00:03 |
——————————————————————————————-

Predicate Information (identified by operation id):
—————————————————
? ?2 – filter(ROWNUM=1)
? ?6 – filter(ROWNUM=1)
Statistics
———————————————————-
? ? ? ? ? 0 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ??? ? 6 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ?1509 ?bytes sent via SQL*Net to client
? ? ? ? 415 ?bytes received via SQL*Net from client
? ? ? ? ? 2 ?SQL*Net roundtrips to/from client
? ? ? ? ? 0 ?sorts (memory)
? ? ? ? ? 0 ?sorts (disk)
? ? ? ? ? 2 ?rows processed

–使用MIN,MAX扫描,并且子查询走索引,逻辑读11,虽然不及前2个,但是还不错,并且SQL简单
dingjun123@ORADB> SELECT * FROM t
? 2 ?WHERE t.object_id IN
? 3 ?(SELECT MIN(object_id) FROM t UNION ALL
? 4 ? SELECT MAX(object_id) FROM t);
2 rows selected.

Execution Plan
———————————————————-
Plan hash value: 4243345848
——————————————————————————————–
| Id ?| Operation ? ? ? ? ? ? ? ? ? ? ? | Name ? ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
——————————————————————————————–
| ? 0 | SELECT STATEMENT ? ? ? ? ? ? ? ?| ? ? ? ? ?| ? ? 2 | ? 220 | ? ? 8 ? (0)| 00:00:01 |
| ? 1 | ?NESTED LOOPS ? ? ? ? ? ? ? ? ? | ? ? ? ? ?| ? ? ? | ? ? ? | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 2 | ? NESTED LOOPS ? ? ? ? ? ? ? ? ?| ? ? ? ? ?| ? ? 2 | ? 220 | ? ? 8 ? (0)| 00:00:01 |
| ? 3 | ? ?VIEW ? ? ? ? ? ? ? ? ? ? ? ? | VW_NSO_1 | ? ? 2 | ? ?26 | ? ? 4 ? (0)| 00:00:01 |
| ? 4 | ? ? HASH UNIQUE ? ? ? ? ? ? ? ? | ? ? ? ? ?| ? ? 2 | ? ?10 | ? ? 4 ?(50)| 00:00:01

来源:php.cn
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板