个人简介
about me:oracle ace pro,optimistic,passionate and harmonious. focus on oracle,mysql and other database programming,peformance tuning,db design, j2ee,linux/aix,architecture tech,etc
文章分类
全部博文（169）
- cbo（54）
- tuning&performan（32）
- concepts&archite（4）
- troubleshooting（5）
- new feature（7）
- sql（52）
- pl/sql（8）
- miscellaneous（3）
- mysql（4）
- 未分配的博文（0）
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2024-10-29 17:28:15
在oracle里如何查找重叠时间段的记录，先看建表语句：
overlap重叠查找算法：
一般用子查询实现，如果用join会导致结果集重复数量增多，因为不是1对1关系，而是1行对应多行。
使用子查询自关联，比如主表a,子查询的a取个别名b,对应的有start_date,end_date以及唯一标识id以及自关联的比如这里是staff_id
overlap思想：
分为交集和包含关系两种：
1）交集
start end
| | -- 记录 a
| | -- 第二条记录 b
可以用a.end_date between b.start_date and b.end_date
查询第二条可以用a.start_date between b.start_date and a.end_date
整体可以用or关系表示
a.end_date between b.start_date and b.end_date
or
a.start_date between b.start_date and a.end_date
2） 包含关系
start end
| | -- 记录 a
| | -- 第二条 b
用1）交集算法则a查询不到的，要查询a，也即按照第二条的start_date为基准即可
a.start_date < b.start_date and a.end_date > b.start_date
将1）和2）合并
如果将 交集和并集合并，简单的算法是: a的开始小于等于b的结束，a的结束大于等于b的开始
a.start_date <= b.end_date and a.end_date>= b.start_date
1： 子查询里有or，不能展开，走filter，可能效率不好
1) a的开始时间在b的时间范围内 或
2) a的结束时间在b的时间范围内 或
3) a的开始时间
2：没有or，按照条件关联比如staff_name一样的，可以走hash join，可能更好
如果将 交集和并集合并，简单的算法是: a的开始小于等于b的结束，a的结束大于等于b的开始
a.start_date <= b.end_date and a.end_date>= b.start_date
1实现： 这种子查询有or的复杂条件，不能展开，只能走filter，可能效率低：
第二种算法，没有or,可以走hash join ,可能更好：
|
drop table overlap_test; create table overlap_test ( id number primary key, staff_name varchar2(100), start_date date, end_date date ); -- id是主键 -- 查询相同staff有日期重叠的，从下面的看出，要查除了除了id=1,2,8的 insert into overlap_test values (1, 'a',to_date('01-jan-2024','dd-mon-yyyy'), to_date('05-jan-2024','dd-mon-yyyy')); insert into overlap_test values (2, 'a',to_date('05-mar-2024','dd-mon-yyyy'), to_date('08-mar-2024','dd-mon-yyyy')); insert into overlap_test values (3, 'b',to_date('04-mar-2024','dd-mon-yyyy'), to_date('07-mar-2024','dd-mon-yyyy')); insert into overlap_test values (4, 'b',to_date('06-mar-2024','dd-mon-yyyy'), to_date('09-mar-2024','dd-mon-yyyy')); insert into overlap_test values (5, 'b',to_date('06-mar-2024','dd-mon-yyyy'), to_date('07-mar-2024','dd-mon-yyyy')); insert into overlap_test values (6, 'c',to_date('04-mar-2024','dd-mon-yyyy'), to_date('09-mar-2024','dd-mon-yyyy')); insert into overlap_test values (7, 'c',to_date('05-mar-2024','dd-mon-yyyy'), to_date('10-mar-2024','dd-mon-yyyy')); insert into overlap_test values (8, 'c',to_date('11-mar-2024','dd-mon-yyyy'), to_date('12-mar-2024','dd-mon-yyyy')); insert into overlap_test values (9, 'd',to_date('01-mar-2024','dd-mon-yyyy'), to_date('12-mar-2024','dd-mon-yyyy')); insert into overlap_test values (10,'d',to_date('02-mar-2024','dd-mon-yyyy'), to_date('03-mar-2024','dd-mon-yyyy')); commit; -- 建个索引，分析不同写法的执行计划 create index idx_overlap_test on overlap_test(staff_name,start_date,end_date); |
overlap重叠查找算法：
一般用子查询实现，如果用join会导致结果集重复数量增多，因为不是1对1关系，而是1行对应多行。
使用子查询自关联，比如主表a,子查询的a取个别名b,对应的有start_date,end_date以及唯一标识id以及自关联的比如这里是staff_id
overlap思想：
分为交集和包含关系两种：
1）交集
start end
| | -- 记录 a
| | -- 第二条记录 b
可以用a.end_date between b.start_date and b.end_date
查询第二条可以用a.start_date between b.start_date and a.end_date
整体可以用or关系表示
a.end_date between b.start_date and b.end_date
or
a.start_date between b.start_date and a.end_date
2） 包含关系
start end
| | -- 记录 a
| | -- 第二条 b
用1）交集算法则a查询不到的，要查询a，也即按照第二条的start_date为基准即可
a.start_date < b.start_date and a.end_date > b.start_date
如果将 交集和并集合并，简单的算法是: a的开始小于等于b的结束，a的结束大于等于b的开始
a.start_date <= b.end_date and a.end_date>= b.start_date
1： 子查询里有or，不能展开，走filter，可能效率不好
1) a的开始时间在b的时间范围内 或
2) a的结束时间在b的时间范围内 或
3) a的开始时间
2：没有or，按照条件关联比如staff_name一样的，可以走hash join，可能更好
如果将 交集和并集合并，简单的算法是: a的开始小于等于b的结束，a的结束大于等于b的开始
a.start_date <= b.end_date and a.end_date>= b.start_date
1实现： 这种子查询有or的复杂条件，不能展开，只能走filter，可能效率低：
|
select a.* from overlap_test a where exists (select 1 from overlap_test b where ( a.start_date between b.start_date and b.end_date or a.end_date between b.start_date and b.end_date or (a.start_date < b.start_date and a.end_date > b.start_date) ) and a.staff_name = b.staff_name and a.id <> b.id ); id staff_name start_date end_date ---------- -------------------- ----------------- ----------------- 3 b 20240304 00:00:00 20240307 00:00:00 4 b 20240306 00:00:00 20240309 00:00:00 5 b 20240306 00:00:00 20240307 00:00:00 6 c 20240304 00:00:00 20240309 00:00:00 7 c 20240305 00:00:00 20240310 00:00:00 9 d 20240301 00:00:00 20240312 00:00:00 10 d 20240302 00:00:00 20240303 00:00:00 7 rows selected. elapsed: 00:00:00.02 execution plan ---------------------------------------------------------- plan hash value: 3561317091 --------------------------------------------------------------------------------------------------------- | id | operation | name | rows | bytes | cost (%cpu)| time | --------------------------------------------------------------------------------------------------------- | 0 | select statement | | 1 | 21 | 13 (0)| 00:00:01 | |* 1 | filter | | | | | | | 2 | table access full | overlap_test | 10 | 210 | 3 (0)| 00:00:01 | |* 3 | table access by index rowid batched| overlap_test | 1 | 21 | 2 (0)| 00:00:01 | |* 4 | index range scan | idx_overlap_test | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------- predicate information (identified by operation id): --------------------------------------------------- 1 - filter( exists (select 0 from "overlap_test" "b" where "b"."staff_name"=:b1 and "b"."id"<>:b2 and ("b"."start_date"<=:b3 and "b"."end_date">=:b4 or "b"."start_date"<=:b5 and "b"."end_date">=:b6 or "b"."start_date">:b7 and "b"."start_date"<:b8))) 3 - filter("b"."id"<>:b1) 4 - access("b"."staff_name"=:b1) filter("b"."start_date"<=:b1 and "b"."end_date">=:b2 or "b"."start_date"<=:b3 and "b"."end_date">=:b4 or "b"."start_date">:b5 and "b"."start_date"<:b6) statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 28 consistent gets 0 physical reads 0 redo size 988 bytes sent via sql*net to client 811 bytes received via sql*net from client 2 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 7 rows processed --缺少包含关系，少个id=9 select a.* from overlap_test a where exists (select 1 from overlap_test b where (a.start_date between b.start_date and b.end_date or a.end_date between b.start_date and b.end_date) and b.staff_name = a.staff_name and b.id <> a.id); id staff_name start_date end_date ---------- -------------------- ----------------- ----------------- 3 b 20240304 00:00:00 20240307 00:00:00 4 b 20240306 00:00:00 20240309 00:00:00 5 b 20240306 00:00:00 20240307 00:00:00 6 c 20240304 00:00:00 20240309 00:00:00 7 c 20240305 00:00:00 20240310 00:00:00 10 d 20240302 00:00:00 20240303 00:00:00 6 rows selected. |
|
select a.* from overlap_test a where exists (select 1 from overlap_test b where ( a.start_date <= b.end_date and a.end_date >= b.start_date ) and b.staff_name = a.staff_name and b.id <> a.id); id staff_name start_date end_date ---------- -------------------- ----------------- ----------------- 4 b 20240306 00:00:00 20240309 00:00:00 5 b 20240306 00:00:00 20240307 00:00:00 3 b 20240304 00:00:00 20240307 00:00:00 7 c 20240305 00:00:00 20240310 00:00:00 6 c 20240304 00:00:00 20240309 00:00:00 10 d 20240302 00:00:00 20240303 00:00:00 9 d 20240301 00:00:00 20240312 00:00:00 7 rows selected. elapsed: 00:00:00.00 execution plan ---------------------------------------------------------- plan hash value: 1291046832 -------------------------------------------------------------------------------------------- | id | operation | name | rows | bytes | cost (%cpu)| time | -------------------------------------------------------------------------------------------- | 0 | select statement | | 10 | 420 | 4 (0)| 00:00:01 | |* 1 | hash join semi | | 10 | 420 | 4 (0)| 00:00:01 | | 2 | view | index$_join$_001 | 10 | 210 | 2 (0)| 00:00:01 | |* 3 | hash join | | | | | | | 4 | index fast full scan| idx_overlap_test | 10 | 210 | 1 (0)| 00:00:01 | | 5 | index fast full scan| sys_c009213 | 10 | 210 | 1 (0)| 00:00:01 | | 6 | view | index$_join$_002 | 10 | 210 | 2 (0)| 00:00:01 | |* 7 | hash join | | | | | | | 8 | index fast full scan| idx_overlap_test | 10 | 210 | 1 (0)| 00:00:01 | | 9 | index fast full scan| sys_c009213 | 10 | 210 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- predicate information (identified by operation id): --------------------------------------------------- 1 - access("b"."staff_name"="a"."staff_name") filter("a"."start_date"<="b"."end_date" and "a"."end_date">="b"."start_date" and "b"."id"<>"a"."id") 3 - access(rowid=rowid) 7 - access(rowid=rowid) statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 17 consistent gets 0 physical reads 0 redo size 988 bytes sent via sql*net to client 682 bytes received via sql*net from client 2 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 7 rows processed --不能使用join,有重复数据 select a.* from overlap_test a ,overlap_test b where (a.start_date between b.start_date and b.end_date or a.end_date between b.start_date and b.end_date) and b.staff_name = a.staff_name and b.id <> a.id; id staff_name start_date end_date ---------- -------------------- ----------------- ----------------- 4 b 20240306 00:00:00 20240309 00:00:00 5 b 20240306 00:00:00 20240307 00:00:00 3 b 20240304 00:00:00 20240307 00:00:00 5 b 20240306 00:00:00 20240307 00:00:00 3 b 20240304 00:00:00 20240307 00:00:00 4 b 20240306 00:00:00 20240309 00:00:00 7 c 20240305 00:00:00 20240310 00:00:00 6 c 20240304 00:00:00 20240309 00:00:00 10 d 20240302 00:00:00 20240303 00:00:00 9 rows selected. elapsed: 00:00:00.00 |
给主人留下些什么吧！~~