利用分析函数改写范围判断自关联查询

SELECT BEGIN, END, ROWID, LENGTH(BEGIN) FROM T_NUM A WHERE NOT EXISTS ( SELECT 1 FROM T_NUM B WHERE B.BEGIN <= A.BEGIN AND B.END >= A.END AND B.ROWID != A.ROWID AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN));

SQL> select length(begin), count(*) from t_num group by length(begin) order by 2 desc; LENGTH(BEGIN) COUNT(*) ————- ———- 12 22096 11 9011 13 8999 14 8186 16 49 9 45 8 41 7 27

SQL改写结果如下：

SELECT BEGIN, OLDEND END, LENGTH(BEGIN) FROM ( SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN, ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN FROM ( SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END FROM T_NUM ) ) WHERE RN = 1 AND CN = 1;

SQL> SELECT BEGIN, END, ROWID, LENGTH(BEGIN) 2 FROM T_NUM A 3 WHERE NOT EXISTS ( 4 SELECT 1 5 FROM T_NUM B 6 WHERE B.BEGIN <= A.BEGIN 7 AND B.END >= A.END 8 AND B.ROWID != A.ROWID 9 AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN)) 10 ; 48344 rows selected. Elapsed: 00:00:57.68 Execution Plan ———————————————————- Plan hash value: 2540751655 ———————————————————————————— | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ———————————————————————————— | 0 | SELECT STATEMENT | | 48454 | 1703K| | 275 (1)| 00:00:04 | |* 1 | HASH JOIN ANTI | | 48454 | 1703K| 1424K| 275 (1)| 00:00:04 | | 2 | TABLE ACCESS FULL| T_NUM | 48454 | 851K| | 68 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| T_NUM | 48454 | 851K| | 68 (0)| 00:00:01 | ———————————————————————————— Predicate Information (identified by operation id): ————————————————— 1 – access(LENGTH(TO_CHAR(“B”.”BEGIN”))=LENGTH(TO_CHAR(“A”.”BEGIN”))) filter(“B”.”BEGIN”<=”A”.”BEGIN” AND “B”.”END”>=”A”.”END” AND “B”.ROWID<>”A”.ROWID) Statistics ———————————————————- 0 recursive calls 0 db block gets 404 consistent gets 0 physical reads 0 redo size 2315794 bytes sent via SQL*Net to client 35966 bytes received via SQL*Net from client 3224 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 48344 rows processed SQL> SELECT BEGIN, OLDEND END, LENGTH(BEGIN) 2 FROM ( 3 SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN, 4 ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN 5 FROM 6 ( 7 SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END 8 FROM T_NUM 9 ) 10 ) 11 WHERE RN = 1 12 AND CN = 1; 48344 rows selected. Elapsed: 00:00:00.72 Execution Plan ———————————————————- Plan hash value: 1546715670 —————————————————————————————— | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | —————————————————————————————— | 0 | SELECT STATEMENT | | 48454 | 2460K| | 800 (1)| 00:00:10 | |* 1 | VIEW | | 48454 | 2460K| | 800 (1)| 00:00:10 | |* 2 | WINDOW SORT PUSHED RANK| | 48454 | 1845K| 2480K| 800 (1)| 00:00:10 | | 3 | WINDOW BUFFER | | 48454 | 1845K| | 800 (1)| 00:00:10 | | 4 | VIEW | | 48454 | 1845K| | 311 (1)| 00:00:04 | | 5 | WINDOW SORT | | 48454 | 662K| 1152K| 311 (1)| 00:00:04 | | 6 | TABLE ACCESS FULL | T_NUM | 48454 | 662K| | 68 (0)| 00:00:01 | —————————————————————————————— Predicate Information (identified by operation id): ————————————————— 1 – filter(“RN”=1 AND “CN”=1) 2 – filter(ROW_NUMBER() OVER ( PARTITION BY LENGTH(TO_CHAR(“BEGIN”)),”END” ORDER BY “BEGIN”)<=1) Statistics ———————————————————- 0 recursive calls 0 db block gets 202 consistent gets 0 physical reads 0 redo size 1493879 bytes sent via SQL*Net to client 35966 bytes received via SQL*Net from client 3224 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 48344 rows processed

0 条评论

相关文章

52230

28260

39140

使用exchange方式切换普通表到分区表

随着数据库数据量的不断增长，有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作，诸如导出表数据，然后创建分区表再导入数据到分区...

8310

39750

32840

13130

19820

10500