当前位置:Gxlcms > mysql > 如何访问索引最大最小值min/maxscan

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

时间:2021-07-01 10:21:17 帮助过:5人阅读

?? 访问索引的最大和最小值,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)

Statistics
———————————————————-
? ? ? ? ? 0 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ? ? 2 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ? 430 ?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

dingjun123@ORADB> SELECT MIN(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)

Statistics
———————————————————-
? ? ? ? ? 0 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ? ? 2 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ? 428 ?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


通过这种高效的访问路径,只需要扫描最左或最右边的一个叶子块即可,只找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 |
—————————————————————————

? ? 竟然走的全表扫描,很显然,我这里的索引,是比表小很多,应该只访问索引就可以了,虽然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)

? ? 显式加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 ;
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 |
—————————————————————————

?

dingjun123@ORADB> SELECT SUM(object_id) ?,count(object_id) FROM t?WHERE object_id IS NOT NULL;
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)

–多个组函数实验,省略,当然多个组函数

? ? ??那么这种情况下,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

? ?下面举一个实例说明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.

Execution Plan
———————————————————-
Plan hash value: 233755475
—————————————————————————————
| Id ?| Operation ? ? ? ? ? ? ? ? ? ? | Name ?| Rows ?| Bytes | Cost (%CPU)| Time ? ? |
—————————————————————————————
| ? 0 | SELECT STATEMENT ? ? ? ? ? ? ?| ? ? ? | 75264 | ? ?15M| ?1478 ? (1)| 00:00:18 |
|* ?1 | ?VIEW ? ? ? ? ? ? ? ? ? ? ? ? | ? ? ? | 75264 | ? ?15M| ?1478 ? (1)| 00:00:18 |
|* ?2 | ??WINDOW NOSORT STOPKEY?? ? ? | ? ? ? | 75264 | ?7129K| ?1478 ? (1)| 00:00:18 |
| ? 3 | ? ?TABLE ACCESS BY INDEX ROWID| T ? ? | 75264 | ?7129K| ?1478 ? (1)| 00:00:18 |
| ? 4 | ? ? INDEX FULL SCAN ? ? ? ? ? | IDX_T | 75264 | ? ? ? | ? 169 ? (1)| 00:00:03 |
—————————————————————————————
Predicate Information (identified by operation id):
—————————————————
? ?1 – filter(“RN”=1)
? ?2 – filter(ROW_NUMBER() OVER ( ORDER BY “OBJECT_ID”)<=1)
Statistics
———————————————————-
? ? ? ? ? 0 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ? ??5 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ?1451 ?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

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

Execution Plan
———————————————————-
Plan hash value: 3436459561
—————————————————————————————
| Id ?| Operation ? ? ? ? ? ? ? ? ? ? | Name ?| Rows ?| Bytes | Cost (%CPU)| Time ? ? |
—————————————————————————————
| ? 0 | SELECT STATEMENT ? ? ? ? ? ? ?| ? ? ? | ? ? 1 | ? 207 | ? ? 3 ? (0)| 00:00:01 |
|* ?1 | ?COUNT STOPKEY ? ? ? ? ? ? ? ?| ? ? ? | ? ? ? | ? ? ? | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 2 | ? VIEW ? ? ? ? ? ? ? ? ? ? ? ?| ? ? ? | ? ? 1 | ? 207 | ? ? 3 ? (0)| 00:00:01 |
| ? 3 | ? ?TABLE ACCESS BY INDEX ROWID| T ? ? | 75264 | ?7129K| ? ? 3 ? (0)| 00:00:01 |
| ? 4 | ? ? INDEX FULL SCAN ? ? ? ? ? | IDX_T | ? ? 1 | ? ? ? | ? ? 2 ? (0)| 00:00:01 |
—————————————————————————————
Predicate Information (identified by operation id):
—————————————————
? ?1 – filter(ROWNUM=1)

Statistics
———————————————————-
? ? ? ? ? 0 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ? ??3 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ?1392 ?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

–3.子查询实现,子查询走高效的FULL INDEX SCAN?MIN MAX,外部查询条件又是OBJECT_ID,走INDEX RANGE SCAN,6个IO,效率比上面的差,但是不算太差
dingjun123@ORADB> SELECT * FROM t
? 2 ?WHERE t.object_id=
? 3 ?(SELECT MIN(object_id) FROM t);
1 row selected.

Execution Plan
———————————————————-
Plan hash value: 72615852
—————————————————————————————
| Id ?| Operation ? ? ? ? ? ? ? ? ? ?| Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
—————————————————————————————
| ? 0 | SELECT STATEMENT ? ? ? ? ? ? | ? ? ? ?| ? ? 1 | ? ?97 | ? ? 4 ? (0)| 00:00:01 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID | T ? ? ?| ? ? 1 | ? ?97 | ? ? 2 ? (0)| 00:00:01 |
|* ?2 | ? INDEX RANGE SCAN ? ? ? ? ? | IDX_T ?| ? ? 1 | ? ? ? | ? ? 1 ? (0)| 00:00:01 |
| ? 3 | ? ?SORT AGGREGATE ? ? ? ? ? ?| ? ? ? ?| ? ? 1 | ? ? 5 | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 4 | ? ? INDEX FULL SCAN (MIN/MAX)| IDX1_T | ? ? 1 | ? ? 5 | ? ? 2 ? (0)| 00:00:01 |
—————————————————————————————
Predicate Information (identified by operation id):
————————————————–

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

Statistics
———————————————————-
? ? ? ? ? 0 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ? ??6 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ?1392 ?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

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

–不能图方便,不是简单的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.

Execution Plan
———————————————————-
Plan hash value: 4213848416
—————————————————————————————-
| Id ?| Operation ? ? ? ? ? ? ? ? ? ? ?| Name ?| Rows ?| Bytes | Cost (%CPU)| Time ? ? |
—————————————————————————————-
| ? 0 | SELECT STATEMENT ? ? ? ? ? ? ? | ? ? ? | ? 150K| ? ?31M| ?2955 ?(51)| 00:00:36 |
| ? 1 | ?UNION-ALL ? ? ? ? ? ? ? ? ? ? | ? ? ? | ? ? ? | ? ? ? | ? ? ? ? ? ?| ? ? ? ? ?|
|* ?2 | ? VIEW ? ? ? ? ? ? ? ? ? ? ? ? | ? ? ? | 75264 | ? ?15M| ?1478 ? (1)| 00:00:18 |
|* ?3 | ? ?WINDOW NOSORT STOPKEY ? ? ? | ? ? ? | 75264 | ?7129K| ?1478 ? (1)| 00:00:18 |
| ? 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 |
|* ?6 | ? VIEW ? ? ? ? ? ? ? ? ? ? ? ? | ? ? ? | 75264 | ? ?15M| ?1478 ? (1)| 00:00:18 |
|* ?7 | ? ?WINDOW NOSORT STOPKEY ? ? ? | ? ? ? | 75264 | ?7129K| ?1478 ? (1)| 00:00:18 |
| ? 8 | ? ? TABLE ACCESS BY INDEX ROWID| T ? ? | 75264 | ?7129K| ?1478 ? (1)| 00:00:18 |
| ? 9 | ? ? ?INDEX FULL SCAN DESCENDING| IDX_T | 75264 | ? ? ? | ? 169 ? (1)| 00:00:03 |
—————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
? ?2 – filter(“RN”=1)
? ?3 – filter(ROW_NUMBER() OVER ( ORDER BY “OBJECT_ID”)<=1)
? ?6 – filter(“RN”=1)
? ?7 – filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION(“OBJECT_ID”) DESC
? ? ? ? ? ? ? )<=1)
Statistics
———————————————————-
? ? ? ? ? 0 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ??? 8 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ?1571 ?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

–传统ROWNUM分页UNION ALL,效率最好,逻辑读6
dingjun123@ORADB> SELECT x.*
? 2 ?FROM (
? 3 ?SELECT * FROM t ORDER BY object_id
? 4 ?) x WHERE ROWNUM=1
? 5 ?UNION ALL
? 6 ?SELECT x.*
? 7 ?FROM (
? 8 ?SELECT * FROM t ORDER BY object_id DESC
? 9 ?) x WHERE ROWNUM=1;
2 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1989929593
—————————————————————————————-
| Id ?| Operation ? ? ? ? ? ? ? ? ? ? ?| Name ?| Rows ?| Bytes | Cost (%CPU)| Time ? ? |
—————————————————————————————-
| ? 0 | SELECT STATEMENT ? ? ? ? ? ? ? | ? ? ? | ? ? 2 | ? 414 | ?2955 ?(51)| 00:00:36 |
| ? 1 | ?UNION-ALL ? ? ? ? ? ? ? ? ? ? | ? ? ? | ? ? ? | ? ? ? | ? ? ? ? ? ?| ? ? ? ? ?|
|* ?2 | ? COUNT STOPKEY ? ? ? ? ? ? ? ?| ? ? ? | ? ? ? | ? ? ? | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 3 | ? ?VIEW ? ? ? ? ? ? ? ? ? ? ? ?| ? ? ? | 75264 | ? ?14M| ?1478 ? (1)| 00:00:18 |
| ? 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 |
|* ?6 | ? COUNT STOPKEY ? ? ? ? ? ? ? ?| ? ? ? | ? ? ? | ? ? ? | ? ? ? ? ? ?| ? ? ? ? ?|
| ? 7 | ? ?VIEW ? ? ? ? ? ? ? ? ? ? ? ?| ? ? ? | 75264 | ? ?14M| ?1478 ? (1)| 00:00:18 |
| ? 8 | ? ? TABLE ACCESS BY INDEX ROWID| T ? ? | 75264 | ?7129K| ?1478 ? (1)| 00:00:18 |
| ? 9 | ? ? ?INDEX FULL SCAN DESCENDING| IDX_T | 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