SQL表连接、 order by 优化

最新推荐文章于 2024-07-07 16:52:52 发布
最新推荐文章于 2024-07-07 16:52:52 发布
阅读量554 收藏
点赞数
分类专栏： oracle SQL优化 文章标签： SQL表连接、 order by 优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ljl_name/article/details/88299281
版权

select *
from (select t2.*
from (select rownum r, t1.*
from (Select *
From (Select *
From crm_Vd健康档案
Where 1 = 1
And 有效状态b = '1') T
order by 显示顺序, 系统序号) t1
where rownum <= 100) t2
where t2.r > 0) T

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1128422515

-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 428K|| 114K (1)| 00:22:59 |
|* 1 | VIEW | | 100 | 428K|| 114K (1)| 00:22:59 |
|* 2 | COUNT STOPKEY | | | || | |
| 3 | VIEW | | 231K| 968M|| 114K (1)| 00:22:59 |
|* 4 | SORT ORDER BY STOPKEY | | 231K| 80M| 86M| 114K (1)| 00:22:59 |
| 5 | NESTED LOOPS OUTER | | 231K| 80M|| 52601 (1)| 00:10:32 |
| 6 | NESTED LOOPS OUTER | | 231K| 76M|| 45792 (1)| 00:09:10 |
|* 7 | HASH JOIN RIGHT OUTER | | 231K| 70M|| 45792 (1)| 00:09:10 |
| 8 | VIEW | index$_join$_033 | 992 | 10912 || 8 (0)| 00:00:01 |
|* 9 | HASH JOIN | | | || | |
| 10 | INDEX FAST FULL SCAN | PK_T员工档案 | 992 | 10912 || 4 (0)| 00:00:01 |
| 11 | BITMAP CONVERSION TO ROWIDS | | 992 | 10912 || 5 (0)| 00:00:01 |
| 12 | BITMAP INDEX FULL SCAN | DOC_T员工档案_名 | | || | |
|* 13 | HASH JOIN RIGHT OUTER | | 231K| 68M|| 45784 (1)| 00:09:10 |
| 14 | VIEW | index$_join$_023 | 992 | 10912 || 8 (0)| 00:00:01 |
|* 15 | HASH JOIN | | | || | |
| 16 | INDEX FAST FULL SCAN | PK_T员工档案 | 992 | 10912 || 4 (0)| 00:00:01 |
| 17 | BITMAP CONVERSION TO ROWIDS | | 992 | 10912 || 5 (0)| 00:00:01 |
| 18 | BITMAP INDEX FULL SCAN | DOC_T员工档案_名 | | || | |
| 19 | MERGE JOIN OUTER | | 231K| 65M|| 45775 (1)| 00:09:10 |
| 20 | SORT JOIN | | 231K| 61M| 129M| 44961 (1)| 00:09:00 |
|* 21 | HASH JOIN RIGHT OUTER | | 231K| 61M|| 1780 (1)| 00:00:22 |
| 22 | TABLE ACCESS FULL | DOC_T民族 | 57 | 570 || 3 (0)| 00:00:01 |
|* 23 | HASH JOIN RIGHT OUTER | | 231K| 59M|| 1777 (1)| 00:00:22 |
| 24 | TABLE ACCESS FULL | DOC_T职业 | 13 | 156 || 3 (0)| 00:00:01 |
|* 25 | HASH JOIN RIGHT OUTER | | 231K| 56M|| 1773 (1)| 00:00:22 |
| 26 | TABLE ACCESS FULL | DOC_T病人类型 | 11 | 143 || 3 (0)| 00:00:01 |
| 27 | NESTED LOOPS OUTER | | 231K| 53M|| 1769 (1)| 00:00:22 |
| 28 | NESTED LOOPS OUTER | | 231K| 50M|| 1769 (1)| 00:00:22 |
| 29 | NESTED LOOPS OUTER | | 231K| 48M|| 1766 (1)| 00:00:22 |
|* 30 | HASH JOIN RIGHT OUTER | | 231K| 46M|| 1763 (1)| 00:00:22 |
| 31 | TABLE ACCESS FULL | DOC_T婚姻状况 | 8 | 72 || 3 (0)| 00:00:01 |
|* 32 | HASH JOIN RIGHT OUTER | | 231K| 44M|| 1760 (1)| 00:00:22 |
| 33 | TABLE ACCESS FULL | DOC_T国籍 | 4 | 32 || 3 (0)| 00:00:01 |
| 34 | NESTED LOOPS OUTER | | 231K| 42M|| 1756 (1)| 00:00:22 |
|* 35 | HASH JOIN RIGHT OUTER | | 231K| 41M|| 1755 (1)| 00:00:22 |
| 36 | BITMAP CONVERSION TO ROWIDS | | 2 | 12 || 1 (0)| 00:00:01 |
| 37 | BITMAP INDEX FAST FULL SCAN| DOC_B性别_名称_系| | || | |
|* 38 | HASH JOIN | | 231K| 39M|| 1753 (1)| 00:00:22 |
| 39 | TABLE ACCESS FULL | DOC_T机构列表 | 2 | 22 || 3 (0)| 00:00:01 |
|* 40 | TABLE ACCESS FULL | CRM_D健康档案 | 231K| 37M|| 1750 (1)| 00:00:21 |
| 41 | TABLE ACCESS BY INDEX ROWID | DOC_T宗教信仰 | 1 | 8 || 1 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | PK_T宗教信仰 | 1 | || 0 (0)| 00:00:01 |
| 43 | TABLE ACCESS BY INDEX ROWID | DOC_B血型 | 1 | 9 || 1 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | PK_B血型 | 1 | || 0 (0)| 00:00:01 |
| 45 | TABLE ACCESS BY INDEX ROWID | DOC_T学历 | 1 | 9 || 1 (0)| 00:00:01 |
|* 46 | INDEX UNIQUE SCAN | PK_T学历 | 1 | || 0 (0)| 00:00:01 |
| 47 | TABLE ACCESS BY INDEX ROWID | DOC_T社会关系 | 1 | 15 || 0 (0)| 00:00:01 |
|* 48 | INDEX UNIQUE SCAN | PK_T社会关系 | 1 | || 0 (0)| 00:00:01 |
|* 49 | SORT JOIN | | 47079 | 873K| 2600K| 814 (1)| 00:00:10 |
| 50 | TABLE ACCESS FULL | DOC_T行政区划 | 47079 | 873K|| 240 (0)| 00:00:03 |
| 51 | TABLE ACCESS BY INDEX ROWID | DOC_T往来单位 | 1 | 25 || 0 (0)| 00:00:01 |
|* 52 | INDEX UNIQUE SCAN | FK_DOC_T往来单位 | 1 | || 0 (0)| 00:00:01 |
| 53 | TABLE ACCESS BY INDEX ROWID | DOC_T行政区划 | 1 | 19 || 1 (0)| 00:00:01 |
|* 54 | INDEX RANGE SCAN | IDX_T行政区划_编 | 1 | || 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T2"."R">0)
2 - filter(ROWNUM<=100)
4 - filter(ROWNUM<=100)
7 - access("JD"."系统序号"(+)="A"."建档人R")
9 - access(ROWID=ROWID)
13 - access("K"."系统序号"(+)="A"."操作人R")
15 - access(ROWID=ROWID)
21 - access("F"."系统序号"(+)="A"."民族I")
23 - access("I"."系统序号"(+)="A"."职业I")
25 - access("A"."人员类型I"="Q"."系统序号"(+))
30 - access("C"."系统序号"(+)="A"."婚姻状况I")
32 - access("G"."系统序号"(+)="A"."国籍I")
35 - access("N"."系统序号"(+)="A"."性别I")
38 - access("B"."系统序号"="A"."隶属机构I")
40 - filter("A"."有效状态B"=1)
42 - access("L"."系统序号"(+)="A"."宗教信仰I")
44 - access("E"."系统序号"(+)="A"."血型I")
46 - access("H"."系统序号"(+)="A"."学历I")
48 - access("M"."系统序号"(+)="A"."联系人关系I")
49 - access("P"."编码"(+)="A"."联系地址编码")
filter("P"."编码"(+)="A"."联系地址编码")
52 - access("J"."系统序号"(+)="A"."客户单位I")
54 - access("O"."编码"(+)="A"."籍贯编码")

已选择88行。

SQL>
-------------------------------------------------
1 分页语句调整
select * from
(
select * from
(
select a.*,rownum rn
from
(
Select * From crm_Vd健康档案 Where 1 = 1
And 有效状态b = '1' -- And substr(分级编码,1,3)='001'

) a order by 显示顺序, 系统序号
) where rownum<=100
) where rn>=0; 

2 
DOC_T员工档案 、DOC_B性别 存在bitmap索引 
消除bitmap索引
SQL> set linesize 200 pagesize 500
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

Plan hash value: 840188945

-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 428K| |113K (1)| 00:22:42 |
|* 1 | VIEW | | 100 | 428K| |113K (1)| 00:22:42 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 231K| 970M| |113K (1)| 00:22:42 |
|* 4 | SORT ORDER BY STOPKEY | | 231K| 80M| 86M|113K (1)| 00:22:42 |
| 5 | COUNT | | | | | | |
| 6 | NESTED LOOPS OUTER | | 231K| 80M| | 51113 (1)| 00:10:14 |
| 7 | NESTED LOOPS OUTER | | 231K| 76M| | 44305 (1)| 00:08:52 |
|* 8 | HASH JOIN RIGHT OUTER | | 231K| 70M| | 44304 (1)| 00:08:52 |
| 9 | TABLE ACCESS FULL | DOC_T员工档案T| 992 | 10912 | | 7 (0)| 00:00:01 |
|* 10 | HASH JOIN RIGHT OUTER | | 231K| 68M| | 44296 (1)| 00:08:52 |
| 11 | TABLE ACCESS FULL | DOC_T员工档案T| 992 | 10912 | | 7 (0)| 00:00:01 |
|* 12 | HASH JOIN RIGHT OUTER | | 231K| 65M| | 44288 (1)| 00:08:52 |
| 13 | TABLE ACCESS FULL | DOC_T民族 | 57 | 570 | | 3 (0)| 00:00:01 |
| 14 | MERGE JOIN OUTER | | 231K| 63M| | 44285 (1)| 00:08:52 |
| 15 | SORT JOIN | | 231K| 59M| 125M| 43471 (1)| 00:08:42 |
|* 16 | HASH JOIN RIGHT OUTER | | 231K| 59M| | 1779 (1)| 00:00:22 |
| 17 | TABLE ACCESS FULL | DOC_T职业 | 13 | 156 | | 3 (0)| 00:00:01 |
|* 18 | HASH JOIN RIGHT OUTER | | 231K| 56M| | 1775 (1)| 00:00:22 |
| 19 | TABLE ACCESS FULL | DOC_T病人类型 | 11 | 143 | | 3 (0)| 00:00:01 |
| 20 | NESTED LOOPS OUTER | | 231K| 53M| | 1772 (1)| 00:00:22 |
| 21 | NESTED LOOPS OUTER | | 231K| 50M| | 1772 (1)| 00:00:22 |
| 22 | NESTED LOOPS OUTER | | 231K| 48M| | 1769 (1)| 00:00:22 |
|* 23 | HASH JOIN RIGHT OUTER | | 231K| 46M| | 1766 (1)| 00:00:22 |
| 24 | TABLE ACCESS FULL | DOC_T婚姻状况 | 8 | 72 | | 3 (0)| 00:00:01 |
|* 25 | HASH JOIN RIGHT OUTER | | 231K| 44M| | 1762 (1)| 00:00:22 |
| 26 | TABLE ACCESS FULL | DOC_T国籍 | 4 | 32 | | 3 (0)| 00:00:01 |
| 27 | NESTED LOOPS OUTER | | 231K| 42M| | 1758 (1)| 00:00:22 |
|* 28 | HASH JOIN RIGHT OUTER | | 231K| 41M| | 1757 (1)| 00:00:22 |
| 29 | TABLE ACCESS FULL | DOC_B性别TEST | 2 | 12 | | 3 (0)| 00:00:01 |
|* 30 | HASH JOIN | | 231K| 39M| | 1753 (1)| 00:00:22 |
| 31 | TABLE ACCESS FULL | DOC_T机构列表 | 2 | 22 | | 3 (0)| 00:00:01 |
|* 32 | TABLE ACCESS FULL | CRM_D健康档案 | 231K| 37M| | 1750 (1)| 00:00:21 |
| 33 | TABLE ACCESS BY INDEX ROWID| DOC_T宗教信仰 | 1 | 8 | | 1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | PK_T宗教信仰 | 1 | | | 0 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | DOC_B血型 | 1 | 9 | | 1 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | PK_B血型 | 1 | | | 0 (0)| 00:00:01 |
| 37 | TABLE ACCESS BY INDEX ROWID | DOC_T学历 | 1 | 9 | | 1 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | PK_T学历 | 1 | | | 0 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | DOC_T社会关系 | 1 | 15 | | 0 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | PK_T社会关系 | 1 | | | 0 (0)| 00:00:01 |
|* 41 | SORT JOIN | | 47079 | 873K| 2600K|814 (1)| 00:00:10 |
| 42 | TABLE ACCESS FULL | DOC_T行政区划 | 47079 | 873K| |240 (0)| 00:00:03 |
| 43 | TABLE ACCESS BY INDEX ROWID | DOC_T往来单位 | 1 | 25 | | 1 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | FK_DOC_T往来单| 1 | | | 0 (0)| 00:00:01 |
| 45 | TABLE ACCESS BY INDEX ROWID | DOC_T行政区划 | 1 | 19 | | 1 (0)| 00:00:01 |
|* 46 | INDEX RANGE SCAN | IDX_T行政区划_| 1 | | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN">=0)
2 - filter(ROWNUM<=100)
4 - filter(ROWNUM<=100)
8 - access("JD"."系统序号"(+)="A"."建档人R")
10 - access("K"."系统序号"(+)="A"."操作人R")
12 - access("F"."系统序号"(+)="A"."民族I")
16 - access("I"."系统序号"(+)="A"."职业I")
18 - access("A"."人员类型I"="Q"."系统序号"(+))
23 - access("C"."系统序号"(+)="A"."婚姻状况I")
25 - access("G"."系统序号"(+)="A"."国籍I")
28 - access("N"."系统序号"(+)="A"."性别I")
30 - access("B"."系统序号"="A"."隶属机构I")
32 - filter("A"."有效状态B"=1)
34 - access("L"."系统序号"(+)="A"."宗教信仰I")
36 - access("E"."系统序号"(+)="A"."血型I")
38 - access("H"."系统序号"(+)="A"."学历I")
40 - access("M"."系统序号"(+)="A"."联系人关系I")
41 - access("P"."编码"(+)="A"."联系地址编码")
filter("P"."编码"(+)="A"."联系地址编码")
44 - access("J"."系统序号"(+)="A"."客户单位I")
46 - access("O"."编码"(+)="A"."籍贯编码")

已选择78行。

SQL>
语句只有CRM_D健康档案 是大表 28w 其他表几十条到几百条不等
过滤列只能过滤两条数据

视图中crm_vd健康档案 改写表连接顺序 ，/*+ USE_NL(M,L,JD,Q,P) */
create or replace view crm_vd健康档案 as
Select /*+ USE_NL(M,L,JD,Q,P) */
A.系统序号
,NVL(A.同步序号,0) AS 同步序号
,A.隶属机构I,B.名称 As 隶属机构
,A.显示顺序
,a.拼音简码 As 编码
,A.健康ID
,A.健康卡号
,A.身份证号
,A.姓名
,A.姓名 As 名称
,a.拼音简码 As 拼音简码
,A.性别I
,N.名称 As 性别
,A.出生日期
,A.婚姻状况I,C.名称 As 婚姻状况
,A.血型I,E.名称 As 血型
,A.民族I,F.名称 As 民族
,A.国籍I,G.名称 As 国籍
,A.学历I,H.名称 As 学历
,A.职业I,I.名称 As 职业
,A.联系电话
,A.籍贯编码
,O.名称 As 籍贯编码名称
,A.籍贯
,A.联系地址编码
,P.名称 As 联系地址编码名称
,A.联系地址
,A.客户单位I,J.名称 As 客户单位
,A.账户余额
,A.账户余额校验
,A.正式档案
,A.建档方式
,A.操作人R,K.名称 As 操作员
,A.操作时间
,A.交易密码
,nvl(A.分级编码,'001') AS 分级编码
,A.有效状态B,DeCode(A.有效状态B,0,'禁用',1,'可用') As 有效状态
,A.人员类型I,Q.名称 人员类型
,A.人员类型I as 病员类型I,Q.名称 病员类型
,A.监护人
,A.有无过敏史B,DeCode(A.有无过敏史B,0,'无',1,'有') As 有无过敏史
,A.门诊次数
,A.过敏药物
,A.其他过敏
,Ltrim(Rtrim(a.过敏药物||';'||a.其他过敏,';'),';') As 过敏源
,A.推荐人
,A.建档人R,JD.名称 as 建档人
,A.建档时间
,case when A.客户单位I is not null then '【客户单位】'||J.名称 else null end as 收费扩展信息
,A.冻结余额
,A.宗教信仰I,L.名称 as 宗教信仰
,A.证件号
,A.联系人关系I,M.名称 as 联系人关系

From crm_D健康档案 A
Inner Join DOC_T机构列表 B On B.系统序号 = A.隶属机构I
Left Join DOC_T婚姻状况 C On C.系统序号 = A.婚姻状况I
Left Join DOC_B血型 E On E.系统序号 = A.血型I
Left Join DOC_T民族 F On F.系统序号 = A.民族I
Left Join DOC_T国籍 G On G.系统序号 = A.国籍I
Left Join DOC_T学历 H On H.系统序号 = A.学历I
Left Join DOC_T职业 I On I.系统序号 = A.职业I
Left Join DOC_T往来单位 J On J.系统序号 = A.客户单位I
Left Join DOC_T员工档案test K On K.系统序号 = A.操作人R
Left Join DOC_B性别test N On N.系统序号=A.性别I
Left Join DOC_T行政区划 O On O.编码=a.籍贯编码
Left Join DOC_T行政区划 P On P.编码=a.联系地址编码
Left Join DOC_T病人类型 Q on A.人员类型I=Q.系统序号
Left Join DOC_T员工档案test JD On JD.系统序号 = A.建档人R
Left Join DOC_T宗教信仰 L On L.系统序号=A.宗教信仰I
Left Join DOC_T社会关系 M On M.系统序号=A.联系人关系I;

加入hint后执行计划
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

Plan hash value: 1524041796

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 428K| | 533K (1)| 01:46:37 |
|* 1 | VIEW | | 100 | 428K| | 533K (1)| 01:46:37 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 231K| 968M| | 533K (1)| 01:46:37 |
|* 4 | SORT ORDER BY STOPKEY | | 231K| 80M| 86M| 533K (1)| 01:46:37 |
| 5 | NESTED LOOPS OUTER | | 231K| 80M| | 470K (1)| 01:34:09 |
| 6 | NESTED LOOPS OUTER | | 231K| 76M| | 240K (1)| 00:48:06 |
| 7 | NESTED LOOPS OUTER | | 231K| 72M| | 233K (1)| 00:46:44 |
| 8 | NESTED LOOPS OUTER | | 231K| 66M| | 233K (1)| 00:46:44 |
|* 9 | HASH JOIN RIGHT OUTER | | 231K| 64M| | 231K (1)| 00:46:24 |
| 10 | TABLE ACCESS FULL | DOC_T员工档案T| 992 | 10912 | |7 (0)| 00:00:01 |
|* 11 | HASH JOIN RIGHT OUTER | | 231K| 61M| | 231K (1)| 00:46:23 |
| 12 | TABLE ACCESS FULL | DOC_T民族 | 57 | 570 | |3 (0)| 00:00:01 |
|* 13 | HASH JOIN RIGHT OUTER | | 231K| 59M| | 231K (1)| 00:46:23 |
| 14 | TABLE ACCESS FULL | DOC_T职业 | 13 | 156 | |3 (0)| 00:00:01 |
| 15 | NESTED LOOPS OUTER | | 231K| 56M| | 231K (1)| 00:46:23 |
| 16 | NESTED LOOPS OUTER | | 231K| 53M| | 1771 (1)| 00:00:22 |
| 17 | NESTED LOOPS OUTER | | 231K| 50M| | 1771 (1)| 00:00:22 |
| 18 | NESTED LOOPS OUTER | | 231K| 48M| | 1768 (1)| 00:00:22 |
|* 19 | HASH JOIN RIGHT OUTER | | 231K| 46M| | 1765 (1)| 00:00:22 |
| 20 | TABLE ACCESS FULL | DOC_T婚姻状况 | 8 | 72 | |3 (0)| 00:00:01 |
|* 21 | HASH JOIN RIGHT OUTER | | 231K| 44M| | 1761 (1)| 00:00:22 |
| 22 | TABLE ACCESS FULL | DOC_T国籍 | 4 | 32 | |3 (0)| 00:00:01 |
| 23 | NESTED LOOPS OUTER | | 231K| 42M| | 1757 (1)| 00:00:22 |
|* 24 | HASH JOIN RIGHT OUTER | | 231K| 41M| | 1757 (1)| 00:00:22 |
| 25 | TABLE ACCESS FULL | DOC_B性别TEST | 2 | 12 | |3 (0)| 00:00:01 |
|* 26 | HASH JOIN | | 231K| 39M| | 1753 (1)| 00:00:22 |
| 27 | TABLE ACCESS FULL | DOC_T机构列表 | 2 | 22 | |3 (0)| 00:00:01 |
|* 28 | TABLE ACCESS FULL | CRM_D健康档案 | 231K| 37M| | 1750 (1)| 00:00:21 |
| 29 | TABLE ACCESS BY INDEX ROWID| DOC_T宗教信仰 | 1 | 8 | |0 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | PK_T宗教信仰 | 1 | | |0 (0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | DOC_B血型 | 1 | 9 | |1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | PK_B血型 | 1 | | |0 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | DOC_T学历 | 1 | 9 | |1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | PK_T学历 | 1 | | |0 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | DOC_T社会关系 | 1 | 15 | |0 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | PK_T社会关系 | 1 | | |0 (0)| 00:00:01 |
| 37 | TABLE ACCESS BY INDEX ROWID | DOC_T病人类型 | 1 | 13 | |1 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | PK_T病人类型 | 1 | | |0 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | DOC_T员工档案T| 1 | 11 | |1 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | PK_T员工档案TE| 1 | | |0 (0)| 00:00:01 |
| 41 | TABLE ACCESS BY INDEX ROWID | DOC_T往来单位 | 1 | 25 | |0 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | FK_DOC_T往来单| 1 | | |0 (0)| 00:00:01 |
| 43 | TABLE ACCESS BY INDEX ROWID | DOC_T行政区划 | 1 | 19 | |1 (0)| 00:00:01 |
|* 44 | INDEX RANGE SCAN | IDX_T行政区划_| 1 | | |0 (0)| 00:00:01 |
| 45 | TABLE ACCESS BY INDEX ROWID | DOC_T行政区划 | 1 | 19 | |1 (0)| 00:00:01 |
|* 46 | INDEX RANGE SCAN | IDX_T行政区划_| 1 | | |0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
-------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T2"."R">0)
2 - filter(ROWNUM<=100)
4 - filter(ROWNUM<=100)
9 - access("K"."系统序号"(+)="A"."操作人R")
11 - access("F"."系统序号"(+)="A"."民族I")
13 - access("I"."系统序号"(+)="A"."职业I")
19 - access("C"."系统序号"(+)="A"."婚姻状况I")
21 - access("G"."系统序号"(+)="A"."国籍I")
24 - access("N"."系统序号"(+)="A"."性别I")
26 - access("B"."系统序号"="A"."隶属机构I")
28 - filter("A"."有效状态B"=1)
30 - access("L"."系统序号"(+)="A"."宗教信仰I")
32 - access("E"."系统序号"(+)="A"."血型I")
34 - access("H"."系统序号"(+)="A"."学历I")
36 - access("M"."系统序号"(+)="A"."联系人关系I")
38 - access("A"."人员类型I"="Q"."系统序号"(+))
40 - access("JD"."系统序号"(+)="A"."建档人R")
42 - access("J"."系统序号"(+)="A"."客户单位I")
44 - access("O"."编码"(+)="A"."籍贯编码")
46 - access("P"."编码"(+)="A"."联系地址编码")

已选择77行。

 

SQL>
要执行11s多 显然 在页面上等待客户无法接受，进一步优化

 

进一步优化 order by

等价改写 使用with 后 排序秒出结果  


with T as ( select *
from (select t2.*
from (select rownum r, t1.*
from (Select *
From (Select /*+materialize*/ * 
From crm_Vd健康档案test
Where 1 = 1
And 有效状态b = '1' )T
) t1
where rownum <= 100) t2
where t2.r > 0) T)
select * from T order by 系统序号,显示顺序; 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------


Plan hash value: 2314858628

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 428K| 233 (1)|00:00:03 |
| 1 | SORT ORDER BY | | 100 | 428K| 233 (1)|00:00:03 |
|* 2 | VIEW | | 100 | 428K| 232 (0)|00:00:03 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | NESTED LOOPS OUTER | | 102 | 125K| 232 (0)|00:00:03 |
| 5 | NESTED LOOPS OUTER | | 102 | 123K| 131 (0)|00:00:02 |
| 6 | NESTED LOOPS OUTER | | 102 | 121K| 128 (0)|00:00:02 |
| 7 | NESTED LOOPS OUTER | | 102 | 119K| 128 (0)|00:00:02 |
|* 8 | HASH JOIN RIGHT OUTER | | 102 | 118K| 127 (0)|00:00:02 |
| 9 | TABLE ACCESS FULL | DOC_T员工档案T| 992 | 10912 | 7 (0)|00:00:01 |
|* 10 | HASH JOIN RIGHT OUTER | | 102 | 91494 | 120 (0)|00:00:02 |
| 11 | TABLE ACCESS FULL | DOC_T民族 | 57 | 570 | 3 (0)|00:00:01 |
| 12 | NESTED LOOPS OUTER | | 102 | 63036 | 117 (0)|00:00:02 |
| 13 | NESTED LOOPS OUTER | | 102 | 61812 | 116 (0)|00:00:02 |
| 14 | NESTED LOOPS OUTER | | 102 | 60486 | 15 (0)|00:00:01 |
| 15 | NESTED LOOPS OUTER | | 102 | 58956 | 15 (0)|00:00:01 |
| 16 | NESTED LOOPS OUTER | | 102 | 58038 | 14 (0)|00:00:01 |
| 17 | NESTED LOOPS OUTER | | 102 | 57120 | 13 (0)|00:00:01 |
| 18 | NESTED LOOPS OUTER | | 102 | 56202 | 10 (0)|00:00:01 |
| 19 | NESTED LOOPS OUTER | | 102 | 55386 | 9 (0)|00:00:01 |
|* 20 | HASH JOIN RIGHT OUTER | | 102 | 54570 | 9 (0)|00:00:01 |
| 21 | TABLE ACCESS FULL | DOC_B性别TEST | 2 | 12 | 3 (0)|00:00:01 |
|* 22 | HASH JOIN | | 102 | 35598 | 6 (0)|00:00:01 |
| 23 | TABLE ACCESS FULL | DOC_T机构列表 | 2 | 22 | 3 (0)|00:00:01 |
|* 24 | TABLE ACCESS FULL | CRM_D健康档案 | 102 | 17238 | 3 (0)|00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID| DOC_T宗教信仰 | 1 | 8 | 0 (0)|00:00:01 |
|* 26 | INDEX UNIQUE SCAN | PK_T宗教信仰 | 1 | | 0 (0)|00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | DOC_T国籍 | 1 | 8 | 1 (0)|00:00:01 |
|* 28 | INDEX UNIQUE SCAN | PK_T国籍 | 1 | | 0 (0)|00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | DOC_T婚姻状况 | 1 | 9 | 1 (0)|00:00:01 |
|* 30 | INDEX UNIQUE SCAN | PK_T婚姻状况 | 1 | | 0 (0)|00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | DOC_B血型 | 1 | 9 | 1 (0)|00:00:01 |
|* 32 | INDEX UNIQUE SCAN | PK_B血型 | 1 | | 0 (0)|00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | DOC_T学历 | 1 | 9 | 1 (0)|00:00:01 |
|* 34 | INDEX UNIQUE SCAN | PK_T学历 | 1 | | 0 (0)|00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | DOC_T社会关系 | 1 | 15 | 0 (0)|00:00:01 |
|* 36 | INDEX UNIQUE SCAN | PK_T社会关系 | 1 | | 0 (0)|00:00:01 |
| 37 | TABLE ACCESS BY INDEX ROWID | DOC_T病人类型 | 1 | 13 | 1 (0)|00:00:01 |
|* 38 | INDEX UNIQUE SCAN | PK_T病人类型 | 1 | | 0 (0)|00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | DOC_T职业 | 1 | 12 | 1 (0)|00:00:01 |
|* 40 | INDEX UNIQUE SCAN | PK_T职业 | 1 | | 0 (0)|00:00:01 |
| 41 | TABLE ACCESS BY INDEX ROWID | DOC_T员工档案T| 1 | 11 | 1 (0)|00:00:01 |
|* 42 | INDEX UNIQUE SCAN | PK_T员工档案TE| 1 | | 0 (0)|00:00:01 |
| 43 | TABLE ACCESS BY INDEX ROWID | DOC_T往来单位 | 1 | 25 | 0 (0)|00:00:01 |
|* 44 | INDEX UNIQUE SCAN | FK_DOC_T往来单| 1 | | 0 (0)|00:00:01 |
| 45 | TABLE ACCESS BY INDEX ROWID | DOC_T行政区划 | 1 | 19 | 1 (0)|00:00:01 |
|* 46 | INDEX RANGE SCAN | IDX_T行政区划_| 1 | | 0 (0)|00:00:01 |
| 47 | TABLE ACCESS BY INDEX ROWID | DOC_T行政区划 | 1 | 19 | 1 (0)|00:00:01 |
|* 48 | INDEX RANGE SCAN | IDX_T行政区划_| 1 | | 0 (0)|00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("T2"."R">0)
3 - filter(ROWNUM<=100)
8 - access("K"."系统序号"(+)="A"."操作人R")
10 - access("F"."系统序号"(+)="A"."民族I")
20 - access("N"."系统序号"(+)="A"."性别I")
22 - access("B"."系统序号"="A"."隶属机构I")
24 - filter("A"."有效状态B"=1)
26 - access("L"."系统序号"(+)="A"."宗教信仰I")
28 - access("G"."系统序号"(+)="A"."国籍I")
30 - access("C"."系统序号"(+)="A"."婚姻状况I")
32 - access("E"."系统序号"(+)="A"."血型I")
34 - access("H"."系统序号"(+)="A"."学历I")
36 - access("M"."系统序号"(+)="A"."联系人关系I")
38 - access("A"."人员类型I"="Q"."系统序号"(+))
40 - access("I"."系统序号"(+)="A"."职业I")
42 - access("JD"."系统序号"(+)="A"."建档人R")
44 - access("J"."系统序号"(+)="A"."客户单位I")
46 - access("O"."编码"(+)="A"."籍贯编码")
48 - access("P"."编码"(+)="A"."联系地址编码")

已选择78行。

 

 

ljl_name
关注
专栏目录
SQL优化—— 优化order by语句 || Filesort 的优化
qq_39368007的博客
03-13 576
两种排序方式 1). 第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。 2). 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高 多字段排序 了解了MySQL的排序方式， 优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。 where 条件和Order by 使用相同的索引，并且Order B...
数据库sql之order by优化
Yangyg_0818的博客
02-12 668
建立复合索引（age,birth），使用order by排序产生filesort情况 情况1：Select *from user where age>20 order by age;没有filesort,用到了index索引,原因是age是索引头 情况2：Select *from user where age>20 order by age, birth;没有filesort且用到...
34.进阶-SQL优化-order by优化
最新发布
m0_63464979的博客
07-07 860
也出现 Using index， 但是此时Extra中出现了 Backward index scan，这个代表反向扫描索引，因为在MySQL中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询排序时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan。因为创建索引时，如果未指定顺序，默认都是按照升序排序的，而查询时，一个升序，一个降序，此时就会出现Using filesort。F. 根据phone，age进行升序排序，phone在前，age在后。
SQL高级之order by优化
youhebuke225的博客
06-13 2613
假设 KEY a_b_c(a,b,c)
sql优化之oder by
Mr.Debug的博客
01-25 436
文章目录一、 一、 首先要注意： MySQL一次查询只能使用一个索引，如果要对多个字段使用索引，建立复合索引。 通过索引优化来实现MySQL的ORDER BY语句优化： 1、ORDER BY的索引优化。如果一个SQL语句形如： 复制代码代码如下: SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort]; 在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。 2、WHERE + ORDER BY的索引优化，形如：
sql-order by
05-12 80
经常需要能够将抓出的资料做一个有系统的显示。这可能是由小往大 (ascending) 或是由大往小 (descending)。在这种情况下，我们就可以运用ORDER BY这个指令来达到我们的目的。 ORDER BY的语法如下: SELECT "栏位名" FROM "表格名" [WHERE "条件"] ORDER BY "栏位名" [ASC, DESC]; ...
MySQL查询优化:连接查询排序limit(join、order by、limit语句)介绍
09-10
在MySQL查询优化中，连接查询（join）与排序（order by）和限制返回结果的数量（limit）是常见的操作，但当它们结合在一起时，可能会导致性能下降。这个问题在标题和描述中已经阐述得很清楚，主要涉及到如何高效地...
sql优化之order by，group by，limit，join
bugNoneNull的博客
08-03 1049
一、前提准备 创建一个employees表，一个主键索引，一个联合索引，表结构如下： -- 创建表 CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', `position` varchar(20) NOT N
【mysql知识点整理】— order by 、group by 出现Using filesort原因详解
12-14
MySQL中的`ORDER BY`和`GROUP BY`是SQL查询中两个关键的子句，它们用于对查询结果进行排序和分组。然而，在某些情况下，MySQL可能会使用`Using filesort`来完成这些操作，这通常会导致性能下降。本文将深入探讨`...
【SQL】查询优化|排序优化|group by优化|分页查询优化|覆盖索引
null
11-26 1028
这是一个常见又头疼的问题，有很庞大的一堆数据，要查看第200万条数据后的10条数据，此时需要MySQL排序前2000010条数据，从中返回第2000000~2000010的记录，其他记录丢弃，查询排序的代价非常大。子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是个常用的优化手段。
SQL server 2008 数据库优化常用脚本
12-15
`SELECT TOP 10 * from sys.dm_os_wait_stats ORDER BY wait_time_ms DESC` `sys.dm_os_wait_stats`动态管理视图提供等待统计信息，按照等待时间降序排列，可识别系统中主要的等待类别。 3. **筛选特定等待类型**...
MySQL进阶-----order by优化和group by优化
m0_73633088的博客
04-13 2037
本期我们继续学习sql优化，其中分为order by 排序优化和group by分组优化。数据准备：可以查阅此链接（MySQL 进阶-----索引使用规则-CSDN博客。
第48讲：SQL优化之ORDER BY排序查询的优化
江晓龙的博客
02-17 1068
针对3.5中排序后的现象，两个字段不同的排序规则，索引默认是升序排序，nl字段的升序排序没有任何效率问题，但是lxfs字段当下拿到的是nl字段升序后的结果集，还想要对lxfs字段降序排序，只能拿到排序缓冲区中进行额外的排序，因为在nl字段升序后的结果集里，lxfs字段也是升序的，但是我们要的是降序的效果，所以只能额外排序了。为不同的字段设置了不同的排序规则后，在索引的叶子节点里就已经排序好了，上面图的所有字段排序规则都是升序，下面图中lxfs字段是降序，可以看到每个索引元素中手机号都是降序排序了。
Sql优化(六):如何优化order by?
nzbing的博客
09-09 732
order by
mysql order by 优化
weixin_34301307的博客
07-14 126
version : 5.7, from 8.2.1.14 ORDER BY Optimization 本节描述MySQL何时可以使用索引来满足ORDER BY子句，当不能使用索引时使用filesort，以及优化器中有关ORDER BY的执行计划信息。 一个order by语句对于有没有使用limit可能存在执行差异。详细内容查看8.2.1.17 LIMIT Query Optimization...
MySQL的ORDER BY排序优化
weixin_43073775的博客
11-12 1325
索引设计时，尽量满足既能满足排序，又能查找行。 ORDER BY优化时尽量使用索引排序。 只有当索引列的顺序和ORDER BY的字段顺序一致，排序方向也一致，并且索引包含要查找的行，满足最左前缀原则，才能使用索引来对结果排序。 例如给student表建立联合索引NAME, AGE, CLASS CREATE TABLE `student` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `NAME` varchar(30) NOT NULL, `SEX` char
一次sql的order by优化
cpt22069的博客
12-04 214
同事发过来sql,说加order by与不加order by 差距很大,不加的返回数据时间为0.156,加后速度为3s ### 1 #sql语句 点击(此处)折叠或打开 ...
MySQL数据库SQL优化之order by 语句
taojin12的博客
08-20 2383
优化ORDER BY 语句之前，首先来了解一下MySQL中排序方式。先看customer 表上的索引情况 mysql&gt; show index from customer; +----------+------------+-------------------+--------------+-------------+-----------+-------------+---------...
Oracle SQL优化：用表连接替换EXISTS提升效率
其中，着重强调了用表连接替换EXISTS子句作为一项重要的优化策略。 通常情况下，使用表连接的方式比EXISTS子句更为高效。例如，以下两个查询： 1. 原始查询（使用EXISTS）： ```sql SELECT ENAME FROM EMP E WHERE...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值