search

Home >Database >Mysql Tutorial >如何访问索引最大最小值min/max scan

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

WBOY
WBOYOriginal
2016-06-07 16:37:481634browse

?? 访问索引的最大和最小值，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”)
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”)
? ?6 – filter(“RN”=1)
? ?7 – filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION(“OBJECT_ID”) DESC
? ? ? ? ? ? ? )
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

Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：【Oracle ASM】PST Partnership Status Table介绍Next article：【Oracle开发】关于11.2上WMSYS.WM_CONCAT的workaround

Related articles

See more