SQL 求助，如下sql怎么改写可以快起来，Oracle 12C
/*tabs表数据量5000w，会持续增长；
time列有索引；
end_time 非空时两行数有联系,end_time d列数值比 start_time 多 1；
where 条件只能基于end_time判断*/
SELECT *
FROM (SELECT a,
b,
c,
t.time start_time,
LEAD (t.time, 1, SYSDATE)
OVER (PARTITION BY a, b ORDER BY time) end_time
FROM tabs)
WHERE end_time > = SYSDATE - 8 / 24
ORDER BY 1, 2;
1、从SQL逻辑上看，全表描是必须的。从避免全表扫的角度看，可以将SQL中出现的列建一个组合索上，变全表扫为对索引的快速全扫。
2、如果从业务上，我们可以确定，满足条件的记录的TIME列上的值，一定是某个时间之后的，且施加这个条件后，满足条件的记录不多，走TIME列上的索引会比较高效的话，那么可以在内层查询中，加入对TIME列的过滤条件。即这个条件的加入，不会导致结果不同。
评论
有用 0create table tab (a varchar2(10),b varchar2(10),c varchar2(10),time date);
insert into tab values ('南京','玄武区','ZHANG',sysdate -2/3);
insert into tab values ('南京','玄武区','LI',sysdate -0.5);
insert into tab values ('南京','玄武区','ZHAO',sysdate -0.25);
insert into tab values ('南京','玄武区','SHEN',sysdate -0.21);
insert into tab values ('南京','玄武区','GU',sysdate -0.2);
insert into tab values ('武汉','江汉区','WANG',sysdate -0.48);
insert into tab values ('武汉','江汉区','GUAN',sysdate -0.24);
commit;
业务逻辑到底是什么？能否详述？
如果只存在一条a='xx',b='yy'的记录，且这条记录的time是5年前比如2018-03-01 00:00:00，那么这条SQL会查出这条记录，其start_time为
2018-03-01 00:00:00，end_time为sysdate即SQL发出的时间；过了8小时，又发出同样SQL，这条记录仍会被查出，只不过end_time变成了8小时后
那个sysdate, 那么反复列出这条记录，意义何在？
我们先按原SQL查一下：
SQL>
SELECT *
FROM (SELECT a,
b,
c,
t.time start_time,
LEAD (t.time, 1, sysdate) OVER (PARTITION BY a, b ORDER BY time) end_time
FROM tab t)
WHERE end_time > = SYSDATE - 1/3 ORDER BY 1, 2;
A B C START_TIME END_TIME
---------- ---------- ---------- ------------------- -------------------
南京 玄武区 LI 2023-03-22 08:15:48 2023-03-22 14:15:48
南京 玄武区 ZHAO 2023-03-22 14:15:48 2023-03-22 15:13:24
南京 玄武区 SHEN 2023-03-22 15:13:24 2023-03-22 15:27:48
南京 玄武区 GU 2023-03-22 15:27:48 2023-03-22 20:15:58
武汉 江汉区 WANG 2023-03-22 08:44:36 2023-03-22 14:30:12
武汉 江汉区 GUAN 2023-03-22 14:30:12 2023-03-22 20:15:58
看到C='GU'和C='GUAN'的记录，就属于这种情况。本身它们孤独一枝，但因为所谓的LEAD (t.time, 1, sysdate)让它们出现在结果集里。
如果我们改写下，屏蔽掉这些记录，是否在业务上可行？改写后如下：
col a for a10;
col b for a10;
col c for a10;
SQL>
SELECT *
FROM (SELECT a,
b,
c,
t.time start_time,
LEAD (t.time, 1, null) OVER (PARTITION BY a, b ORDER BY time) end_time
FROM tab t)
WHERE end_time > = SYSDATE - 1/3 and end_time is not null ORDER BY 1, 2;
A B C START_TIME END_TIME
---------- ---------- ---------- ------------------- -------------------
南京 玄武区 LI 2023-03-22 08:15:48 2023-03-22 14:15:48
南京 玄武区 ZHAO 2023-03-22 14:15:48 2023-03-22 15:13:24
南京 玄武区 SHEN 2023-03-22 15:13:24 2023-03-22 15:27:48
武汉 江汉区 WANG 2023-03-22 08:44:36 2023-03-22 14:30:12
如果确实可以这样改写，那么也可以改写成如下：
with s as
(SELECT a,b,
lag(t.c,1,null) OVER (PARTITION BY a, b ORDER BY time) as c,
lag(t.time,1,null) OVER (PARTITION BY a, b ORDER BY time) as start_time,
t.time as end_time from tab t WHERE t.time > = SYSDATE - 1/3
ORDER BY 1, 2),
u as (SELECT a,
b,
c,
tab.time start_time,
LEAD (tab.time, 1, null) OVER (PARTITION BY a, b ORDER BY time) end_time
FROM tab where (a,b) in (select a,b from s where c is null))
select * from u where end_time>=sysdate -1/3
union
select * from s where c is not null order by 1,2;
A B C START_TIME END_TIME
---------- ---------- ---------- ------------------- -------------------
南京 玄武区 LI 2023-03-22 08:15:48 2023-03-22 14:15:48
南京 玄武区 SHEN 2023-03-22 15:13:24 2023-03-22 15:27:48
南京 玄武区 ZHAO 2023-03-22 14:15:48 2023-03-22 15:13:24
武汉 江汉区 WANG 2023-03-22 08:44:36 2023-03-22 14:30:12
这样写与原SQL比，避免了查询全表，应该效率还行。
如果
评论 有用 0感觉本身表设计会不会有问题。同一个a,b的记录，应该至多在表里存在两条，一条表示开始时间，一条表示结束时间。既然如此，为何不涉及成一条记录有start_time和end_time两个字段？如果因为设计不合理而只能在SQL上下功夫，也很累。
评论 有用 1
实际场景如同各位老师猜测，表设计有问题。
具体场景是一个产品生产每个阶段只记录了开始时间，没有记录结束时间，因此将指定条件操作的下一次开始时间作为结束时间来记录，如果没有下一次开始时间取sysdate。
业务是统计8h之内结束的相关数据，因此需要取值没有下一次记录的信息
评论 有用 0是统计8h之内结束的相关数据，如果没有下一次开始时间取sysdate。既然是统计8h内结束的操作，那么没有下一次的取sysdate，不合适啊。没有下一次的就是没结束。为何也要输出？
评论 有用 0
墨值悬赏