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; 


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行。

 

 

展开阅读全文

mysql order by/group by 优化

11-20

explain select im.item_id from property_value p left join item i on p.id = i.value left join rnitem_message im on i.item_id = im.item_id where ( p.value like '%餐饮%' or p.value like '%拉%' ) and im.class = 1 and im.status = 0 and p.`property_id` in (SELECT id FROM rn`property` WHERE `key_p` = 1) LIMIT 24 , 8rnrnrnid select_type table type possible_keys key key_len ref rows Extra rn1 PRIMARY im ref class,item_id class 4 const 9096 Using where rn1 PRIMARY i ref value,item_id item_id 4 fenlei.im.item_id 8 Using where rn1 PRIMARY p eq_ref PRIMARY PRIMARY 4 fenlei.i.value 1 Using where rn2 DEPENDENT SUBQUERY property unique_subquery PRIMARY PRIMARY 4 func 1 Using where rnrnrnexplain select count(im.item_id), im.item_id from property_value p left join item i on p.id = i.value left join rnitem_message im on i.item_id = im.item_id where ( p.value like '%餐饮%' or p.value like '%拉%' ) and im.class = 1 and im.status = 0 and p.`property_id` in (SELECT id FROM rn`property` WHERE `key_p` = 1) group by im.item_id LIMIT 20 , 8rnrnid select_type table type possible_keys key key_len ref rows Extra rn1 PRIMARY im index class,item_id item_id 4 NULL 3 Using where rn1 PRIMARY i ref value,item_id item_id 4 fenlei.im.item_id 8 Using where rn1 PRIMARY p eq_ref PRIMARY PRIMARY 4 fenlei.i.value 1 Using where rn2 DEPENDENT SUBQUERY property unique_subquery PRIMARY PRIMARY 4 func 1 Using where rnrnexplain select count(im.item_id), im.item_id from property_value p left join item i on p.id = i.value left join rnitem_message im on i.item_id = im.item_id where ( p.value like '%餐饮%' or p.value like '%拉%') and im.class = 1 and im.status = 0 and p.`property_id` in (SELECT id FROM rn`property` WHERE `key_p` = 1) group by im.item_id order by count(im.item_id) LIMIT 20 , 8rnrnid select_type table type possible_keys key key_len ref rows Extra rn1 PRIMARY im index class,item_id item_id 4 NULL 3 Using where; Using temporary; Using filesort rn1 PRIMARY i ref value,item_id item_id 4 fenlei.im.item_id 8 Using where rn1 PRIMARY p eq_ref PRIMARY PRIMARY 4 fenlei.i.value 1 Using where rn2 DEPENDENT SUBQUERY property unique_subquery PRIMARY PRIMARY 4 func 1 Using where rnrn我要用得第3条sql,我要order by的是count(im.item_id)这个数,请问如何优化,附索引,谢谢rnrnSHOW INDEX FROM item_message rnrnTable Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment rnitem_message 0 PRIMARY 1 id A 11994 NULL NULL BTREE rnitem_message 1 class 1 class A 3 NULL NULL BTREE rnitem_message 1 regional 1 regional A 3 NULL NULL YES BTREE rnitem_message 1 city 1 city A 11 NULL NULL YES BTREE rnitem_message 1 regional_city 1 regional A 3 NULL NULL YES BTREE rnitem_message 1 regional_city 2 city A 11 NULL NULL YES BTREE rnitem_message 1 create_date 1 create_date A 3 NULL NULL YES BTREE rnitem_message 1 item_id 1 item_id A 11994 NULL NULL BTREE rnrnSHOW INDEX FROM item rnrnTable Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment rnitem 0 PRIMARY 1 id A 1148679 NULL NULL BTREE rnitem 0 value 1 value A 1148679 NULL NULL BTREE rnitem 1 item_id 1 item_id A 143584 NULL NULL BTREE rnitem 1 class_id 1 class_id A 3 NULL NULL BTREE rnrnSHOW INDEX FROM property_value rnrnTable Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment rnproperty_value 0 PRIMARY 1 id A 1149757 NULL NULL BTREE rnproperty_value 1 property_id 1 property_id A 23 NULL NULL BTREE rnproperty_value 1 property_id 2 value A 6495 255 NULL BTREE rnproperty_value 1 value 1 value NULL 4892 NULL NULL FULLTEXT rnrnrn 论坛

没有更多推荐了,返回首页