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

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

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