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