Oracle distinct/group如何实现分页消除排序stopkey裁剪
##distinct/group by的分页，要先消除order by排序，然后还要消除distinct/group by排序才能count stop key,用hash group by不行，必须
##sort group by nosort才可以
### oracle单表使用组合索引，对于单表distinct,group by包括查询聚合函数的，可以stop key，用的是SORT GROUP BY NOSORT
### 注意组合索引，需要消除order by和group by的排序
要把排序前导列放在前面，（排序列，其它分组列）组合索引
如果有where条件，等值条件的放排序键前面,当然where等值要有一定过滤性,（等值where列，排序列，其它分组列）组合索引
如果没有where等值条件，注意组合不能not null
########多表JOIN，必须能够有约束消除distinct的，也即关联键都是1-1的才可以，且select查询了驱动表关联列，不能出现sort unique之类的操作
###必须是1-1关联的才可以，要么有约束自动去掉distinct,要么是业务上1-1关联，rownum移到里面才等价
##oracle多表distinct/group by,不是1-1关系关联的基本不行，有的TOP N对也是正好前几行无重复值，rownum前移到order by一侧数据不对，
这种是先查rownum，然后剔重的，除非能够消除distinct,那就要求关联关系是1-1关联，约束或业务上
===理解下rownum和order by写一起的问题，必须1-1关联且查询的是无重复的才可以用索引优化
## oracle缺乏增量排序算法，pg和MySQL可以
create index idx123_t1 on t1(owner,object_type,status);
--没有条件，加not null约束测试，也可以SQL里使用xx is not null
alter table t1 modify owner not null;
select owner,object_type,status,cnt
from (
select distinct owner,object_type,status,count(*) cnt
from t1
group by object_type,owner,status
order by owner)
where rownum<=5;
-- SORT GROUP BY NOSORT 可以count stop key避免查询全部数据，比如下面的数据有73749，实际只要查92条
Plan hash value: 4072252330
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.01 | 4 |
|* 1 | COUNT STOPKEY | | 1 | | 5 |00:00:00.01 | 4 |
| 2 | VIEW | | 1 | 5 | 5 |00:00:00.01 | 4 |
| 3 | SORT GROUP BY NOSORT| | 1 | 5 | 5 |00:00:00.01 | 4 |
| 4 | INDEX FULL SCAN | IDX123_T1 | 1 | 73749 | 92 |00:00:00.01 | 4 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
##多表JOIN，如果只查order by表单表数据，也可以
select owner,object_type,status,cnt
from (
select distinct t1.owner,t1.object_type,t1.status,count(*) cnt
from t1,t2
where t1.object_id=t2.object_id
group by t1.object_type,t1.owner,t1.status
order by t1.owner)
where rownum<=5;
Plan hash value: 2068096088
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.01 | 101 | 10 |
|* 1 | COUNT STOPKEY | | 1 | | 5 |00:00:00.01 | 101 | 10 |
| 2 | VIEW | | 1 | 5 | 5 |00:00:00.01 | 101 | 10 |
| 3 | SORT GROUP BY NOSORT | | 1 | 5 | 5 |00:00:00.01 | 101 | 10 |
| 4 | NESTED LOOPS | | 1 | 5 | 92 |00:00:00.01 | 101 | 10 |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 73749 | 92 |00:00:00.01 | 26 | 2 |
| 6 | INDEX FULL SCAN | IDX123_T1 | 1 | 5 | 92 |00:00:00.01 | 4 | 2 |
|* 7 | INDEX RANGE SCAN | IDX111_T2 | 92 | 1 | 92 |00:00:00.01 | 75 | 8 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
###############查询多表数据，distinct/group by的分页
alter table t1 modify object_id not null;
create index idx1_t1 on t1(object_id,object_name);
##传统写法，无法消除group by的排序
select * from ( select/*+leading(t1) index(t1 idx1_t1) use_nl(t2) use_nl(t3)*/
distinct t1.object_id,t1.object_name,t2.data_object_id,t3.created
from t1,t2,t3
where t1.object_id=t2.object_id and
t2.object_name=t3.object_name
order by t1.object_id) where rownum<=50;
Plan hash value: 1580461461
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 50 |00:00:00.58 | 135K| 514 | | | |
|* 1 | COUNT STOPKEY | | 1 | | 50 |00:00:00.58 | 135K| 514 | | | |
| 2 | VIEW | | 1 | 88685 | 50 |00:00:00.58 | 135K| 514 | | | |
|* 3 | SORT UNIQUE STOPKEY | | 1 | 88685 | 50 |00:00:00.58 | 135K| 514 | 4096 | 4096 | 4096 (0)|
| 4 | NESTED LOOPS | | 1 | 88685 | 145K|00:00:00.55 | 135K| 514 | | | |
| 5 | NESTED LOOPS | | 1 | 88685 | 145K|00:00:00.42 | 104K| 514 | | | |
| 6 | NESTED LOOPS | | 1 | 73749 | 73749 |00:00:00.21 | 3336 | 514 | | | |
| 7 | INDEX FULL SCAN | IDX1_T1 | 1 | 73749 | 73749 |00:00:00.08 | 515 | 514 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 73749 | 1 | 73749 |00:00:00.11 | 2821 | 0 | | | |
|* 9 | INDEX RANGE SCAN | IDX111_T2 | 73749 | 1 | 73749 |00:00:00.07 | 1148 | 0 | | | |
|* 10 | INDEX RANGE SCAN | IDX_T3 | 73749 | 1 | 145K|00:00:00.18 | 100K| 0 | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | T3 | 145K| 1 | 145K|00:00:00.09 | 31290 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=50)
3 - filter(ROWNUM<=50)
9 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
10 - access("T2"."OBJECT_NAME"="T3"."OBJECT_NAME")
34 rows selected.
## 还有SORT UNIQUE STOPKEY，没有消除distinct排序，虽然消除了order by排序，也不能count stop key
## oracle缺乏增量排序算法，pg和MySQL可以
select * from ( select/*+leading(t1) index(t1) use_nl(t2) use_nl(t3)*/
distinct t1.owner,t1.object_name,t2.data_object_id,t3.created
from t1,t2,t3
where t1.object_id=t2.object_id and
t2.object_name=t3.object_name
order by t1.owner) where rownum<5;
OWNER OBJECT_NAME DATA_OBJECT_ID CREATED
-------------------- -------------------- -------------- ------------
APPQOSSYS DBMS_WLM 30-MAY-19
APPQOSSYS DBMS_WLM 30-MAY-19
APPQOSSYS DBMS_WLM 30-MAY-19
APPQOSSYS WLM_CLASSIFIER_PLAN 20567 30-MAY-19
Plan hash value: 3508113489
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.54 | 137K| | | |
|* 1 | COUNT STOPKEY | | 1 | | 4 |00:00:00.54 | 137K| | | |
| 2 | VIEW | | 1 | 88685 | 4 |00:00:00.54 | 137K| | | |
|* 3 | SORT UNIQUE STOPKEY | | 1 | 88685 | 4 |00:00:00.54 | 137K| 4096 | 4096 | 4096 (0)|
| 4 | NESTED LOOPS | | 1 | 88685 | 145K|00:00:00.51 | 137K| | | |
| 5 | NESTED LOOPS | | 1 | 88685 | 145K|00:00:00.38 | 105K| | | |
| 6 | NESTED LOOPS | | 1 | 73749 | 73749 |00:00:00.17 | 5002 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 73749 | 73749 |00:00:00.04 | 2186 | | | |
| 8 | INDEX FULL SCAN | IDX1_T1 | 1 | 73749 | 73749 |00:00:00.01 | 515 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 73749 | 1 | 73749 |00:00:00.12 | 2816 | | | |
|* 10 | INDEX RANGE SCAN | IDX111_T2 | 73749 | 1 | 73749 |00:00:00.07 | 1144 | | | |
|* 11 | INDEX RANGE SCAN | IDX_T3 | 73749 | 1 | 145K|00:00:00.18 | 100K| | | |
| 12 | TABLE ACCESS BY INDEX ROWID | T3 | 145K| 1 | 145K|00:00:00.09 | 31290 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<5)
3 - filter(ROWNUM<5)
10 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
11 - access("T2"."OBJECT_NAME"="T3"."OBJECT_NAME")
#####rownum移到里面不等价，这个是先执行rownum再执行的排序，只有distinct消除了才等价，也即必须排序列唯一且走索引消除排序
select * from ( select/*+leading(t1) index(t1) use_nl(t2) use_nl(t3)*/
distinct t1.owner,t1.object_name,t2.data_object_id,t3.created
from t1,t2,t3
where t1.object_id=t2.object_id and
t2.object_name=t3.object_name
and rownum<5
order by t1.owner);
OWNER OBJECT_NAME DATA_OBJECT_ID CREATED
-------------------- -------------------- -------------- ------------
SYS CLU$ 2 30-MAY-19
SYS C_OBJ# 2 30-MAY-19
SYS I_OBJ# 3 30-MAY-19
SYS TAB$ 2 30-MAY-19
Plan hash value: 1188258364
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 21 | | | |
| 1 | VIEW | | 1 | 4 | 4 |00:00:00.01 | 21 | | | |
| 2 | SORT UNIQUE | | 1 | 4 | 4 |00:00:00.01 | 21 | 2048 | 2048 | 2048 (0)|
|* 3 | COUNT STOPKEY | | 1 | | 4 |00:00:00.01 | 21 | | | |
| 4 | NESTED LOOPS | | 1 | 88685 | 4 |00:00:00.01 | 21 | | | |
| 5 | NESTED LOOPS | | 1 | 88685 | 4 |00:00:00.01 | 20 | | | |
| 6 | NESTED LOOPS | | 1 | 73749 | 4 |00:00:00.01 | 10 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 73749 | 4 |00:00:00.01 | 3 | | | |
| 8 | INDEX FULL SCAN | IDX123_T1 | 1 | 73749 | 4 |00:00:00.01 | 2 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 4 | 1 | 4 |00:00:00.01 | 7 | | | |
|* 10 | INDEX RANGE SCAN | IDX111_T2 | 4 | 1 | 4 |00:00:00.01 | 6 | | | |
|* 11 | INDEX RANGE SCAN | IDX_T3 | 4 | 1 | 4 |00:00:00.01 | 10 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | T3 | 4 | 1 | 4 |00:00:00.01 | 1 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<5)
10 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
11 - access("T2"."OBJECT_NAME"="T3"."OBJECT_NAME")
34 rows selected.
###必须是1-1关联的才可以，要么有约束自动去掉distinct,要么是业务上1-1关联，rownum移到里面才等价
###rownum移到order by一侧，且distinct查询order by列，则等价，变成先count stop key再做distinct
select * from ( select/*+leading(t1) index(t1) use_nl(t2) use_nl(t3)*/
distinct t1.object_id,t1.owner,t1.object_name,t2.data_object_id,t3.created
from t1,t2,t3
where t1.object_id=t2.object_id and
t2.object_name=t3.object_name
and rownum<5
order by t1.object_id);
OBJECT_ID OWNER OBJECT_NAME DATA_OBJECT_ID CREATED
---------- -------------------- -------------------- -------------- ------------
2 SYS C_OBJ# 2 30-MAY-19
3 SYS I_OBJ# 3 30-MAY-19
4 SYS TAB$ 2 30-MAY-19
5 SYS CLU$ 2 30-MAY-19
4 rows selected.
Plan hash value: 1188258364
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 21 | | | |
| 1 | VIEW | | 1 | 4 | 4 |00:00:00.01 | 21 | | | |
| 2 | SORT UNIQUE | | 1 | 4 | 4 |00:00:00.01 | 21 | 2048 | 2048 | 2048 (0)|
|* 3 | COUNT STOPKEY | | 1 | | 4 |00:00:00.01 | 21 | | | |
| 4 | NESTED LOOPS | | 1 | 88685 | 4 |00:00:00.01 | 21 | | | |
| 5 | NESTED LOOPS | | 1 | 88685 | 4 |00:00:00.01 | 20 | | | |
| 6 | NESTED LOOPS | | 1 | 73749 | 4 |00:00:00.01 | 10 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 73749 | 4 |00:00:00.01 | 3 | | | |
| 8 | INDEX FULL SCAN | IDX123_T1 | 1 | 73749 | 4 |00:00:00.01 | 2 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 4 | 1 | 4 |00:00:00.01 | 7 | | | |
|* 10 | INDEX RANGE SCAN | IDX111_T2 | 4 | 1 | 4 |00:00:00.01 | 6 | | | |
|* 11 | INDEX RANGE SCAN | IDX_T3 | 4 | 1 | 4 |00:00:00.01 | 10 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | T3 | 4 | 1 | 4 |00:00:00.01 | 1 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<5)
10 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
11 - access("T2"."OBJECT_NAME"="T3"."OBJECT_NAME")
select * from ( select /*+leading(t1) index(t1) use_nl(t2) use_nl(t3)*/
distinct t1.object_id,t1.owner,t1.object_name,t2.data_object_id,t3.created
from t1,t2,t3
where t1.object_id=t2.object_id and
t2.object_name=t3.object_name
and rownum<5
order by t1.object_id);
OBJECT_ID OWNER OBJECT_NAME DATA_OBJECT_ID CREATED
---------- -------------------- -------------------- -------------- ------------
2 SYS C_OBJ# 2 30-MAY-19
3 SYS I_OBJ# 3 30-MAY-19
4 SYS TAB$ 2 30-MAY-19
5 SYS CLU$ 2 30-MAY-19
4 rows selected.
Plan hash value: 1188258364
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 21 | | | |
| 1 | VIEW | | 1 | 4 | 4 |00:00:00.01 | 21 | | | |
| 2 | SORT UNIQUE | | 1 | 4 | 4 |00:00:00.01 | 21 | 2048 | 2048 | 2048 (0)|
|* 3 | COUNT STOPKEY | | 1 | | 4 |00:00:00.01 | 21 | | | |
| 4 | NESTED LOOPS | | 1 | 88685 | 4 |00:00:00.01 | 21 | | | |
| 5 | NESTED LOOPS | | 1 | 88685 | 4 |00:00:00.01 | 20 | | | |
| 6 | NESTED LOOPS | | 1 | 73749 | 4 |00:00:00.01 | 10 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 73749 | 4 |00:00:00.01 | 3 | | | |
| 8 | INDEX FULL SCAN | IDX123_T1 | 1 | 73749 | 4 |00:00:00.01 | 2 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 4 | 1 | 4 |00:00:00.01 | 7 | | | |
|* 10 | INDEX RANGE SCAN | IDX111_T2 | 4 | 1 | 4 |00:00:00.01 | 6 | | | |
|* 11 | INDEX RANGE SCAN | IDX_T3 | 4 | 1 | 4 |00:00:00.01 | 10 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | T3 | 4 | 1 | 4 |00:00:00.01 | 1 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<5)
10 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
11 - access("T2"."OBJECT_NAME"="T3"."OBJECT_NAME")
34 rows selected.
===理解下rownum和order by写一起的问题，必须1-1关联且查询的是无重复的才可以用索引优化
select * from ( select/*+leading(t1) index(t1) use_nl(t2) use_nl(t3)*/
distinct t1.object_id,t1.owner,t1.object_name,t2.data_object_id,t3.created
from t1,t2,t3
where t1.object_id=t2.object_id and
t2.object_name=t3.object_name
and rownum<5
order by t1.object_id);
drop table a;
drop table b;
drop table c;
create table a(aid number,aid1 number,aid2 number);
create table b(bid number,bid1 number,bid2 number);
create table c(cid number,cid1 number,cid2 number);
insert into a values(1,1,1);
insert into a values(2,2,2);
insert into a values(3,2,2);
insert into a values(4,2,2);
insert into a values(5,2,2);
insert into a values(6,3,3);
insert into a values(7,4,4);
insert into a values(8,4,4);
insert into a values(9,4,5);
insert into a values(10,5,6);
commit;
insert into b select * from a;
insert into c select * from a;
select * from (
select distinct a.aid1,a.aid2,b.bid1,b.bid2,c.cid1
from a,b,c
where a.aid1=b.bid1
and b.bid2=c.cid2
order by a.aid1
) where rownum<=5;
AID1 AID2 BID1 BID2 CID1
---------- ---------- ---------- ---------- ----------
1 1 1 1 1
2 2 2 2 2
3 3 3 3 3
4 4 4 4 4
4 4 4 5 4
##先查询的rownum <=5然后distinct,前五行有4行重复的，所以最终返回2行
select distinct a.aid1,a.aid2,b.bid1,b.bid2,c.cid1
from a,b,c
where a.aid1=b.bid1
and b.bid2=c.cid2
and rownum <=5
order by a.aid1;
AID1 AID2 BID1 BID2 CID1
---------- ---------- ---------- ---------- ----------
1 1 1 1 1
2 2 2 2 2
create index idx_a on a(aid1);
create index idx_b on b(bid1);
create index idx_c on c(cid2);
select/*+leading(a) index(a) use_nl(b) use_nl(c) index(b) index(c)*/ distinct a.aid1,a.aid2,b.bid1,b.bid2,c.cid1
from a,b,c
where a.aid1=b.bid1
and b.bid2=c.cid2
and rownum <=5
order by a.aid1;
AID1 AID2 BID1 BID2 CID1
---------- ---------- ---------- ---------- ----------
1 1 1 1 1
2 2 2 2 2
2 rows selected.
=============== 多表join
drop table a;
drop table b;
drop table c;
create table a(aid number,aid1 number,aid2 number);
create table b(bid number,bid1 number,bid2 number);
create table c(cid number,cid1 number,cid2 number);
insert into a values(1,1,1);
insert into a values(2,2,2);
insert into a values(3,2,2);
insert into a values(4,2,2);
insert into a values(5,2,2);
insert into a values(6,3,3);
insert into a values(7,4,4);
insert into a values(8,4,4);
insert into a values(9,4,5);
insert into a values(10,5,6);
commit;
insert into b select * from a;
insert into c select * from a;
commit;
select * from (
select distinct a.aid,a.aid1,a.aid2,b.bid1,b.bid2,c.cid1
from a,b,c
where a.aid=b.bid1
and b.bid2=c.cid2
order by a.aid
) where rownum<=5;
select distinct a.aid,a.aid1,a.aid2,b.bid1,b.bid2,c.cid1
from a,b,c
where a.aid=b.bid1
and b.bid2=c.cid2
and rownum <= 5
order by a.aid;
AID AID1 AID2 BID1 BID2 CID1
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 1 1
2 2 2 2 2 2
create index idx_a on a(aid);
create index idx_b on b(bid1);
create index idx_c on c(cid2);
select distinct a.aid,a.aid1,a.aid2
from a,b,c
where a.aid=b.bid1
and b.bid2=c.cid2
and rownum <= 5
order by a.aid;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7655508/viewspace-3117893/ ，如需转载，请注明出处，否则将追究法律责任。
