SY, извиняюсь, все правильно
опробовал оба варианта, с рекурсивным запросом и аналитическими ф-циями на таблице с 300000 записей. И так, создаю таблицу AUFTRAG и заполняю ее записями до 300000:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
create table AUFTRAG(id number primary key, ORDER_ID_UNIQ number unique);
/
insert into AUFTRAG
(
select rownum, l
from
(
select l from
(
select rownum + 300000 l
from dual
connect by level < (300000-0)
)
order by dbms_random.random
)
where rownum <= 300000
);
commit;
тестирую варианит с аналитическими ф-ми:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
explain plan for
with t as (
select id,
lead(id) over(order by order_id_uniq) found_id_next
from auftrag
)
select found_id_next
from t
where t.id = :id;
/
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1735091813
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 299K| 7617K| | 1371 (1)| 00:00:01 |
|* 1 | VIEW | | 299K| 7617K| | 1371 (1)| 00:00:01 |
| 2 | WINDOW SORT | | 299K| 2929K| 5896K| 1371 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| AUFTRAG | 299K| 2929K| | 171 (1)| 00:00:01 |
---------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."ID"=TO_NUMBER(:ID))
15 rows selected
тестирую вариант с рекурсией
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
WITH ForId(ORDER_ID_UNIQ) AS
(
SELECT ORDER_ID_UNIQ FROM AUFTRAG WHERE id = :id
UNION ALL
SELECT ORDER_ID_UNIQ + 1 FROM ForId
WHERE NOT EXISTS
(
SELECT *
FROM AUFTRAG tmp
WHERE tmp.ORDER_ID_UNIQ = ForId.ORDER_ID_UNIQ + 1
)
AND ORDER_ID_UNIQ < 10000000000000000000000
),
Next_ORDER_ID_UNIQ(ORDER_ID_UNIQ) AS (SELECT cnt + (SELECT ORDER_ID_UNIQ FROM AUFTRAG WHERE id = :id) AS ORDER_ID_UNIQ FROM (SELECT COUNT(ORDER_ID_UNIQ) AS cnt FROM ForId))
SELECT * FROM AUFTRAG WHERE AUFTRAG.ORDER_ID_UNIQ = (SELECT ORDER_ID_UNIQ FROM Next_ORDER_ID_UNIQ);
/
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 232072449
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | AUFTRAG | 1 | 10 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0011306 | 1 | | 2 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 13 | 6 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 13 | | |
| 5 | VIEW | | 2 | 26 | 6 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 6 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | AUFTRAG | 1 | 10 | 2 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | SYS_C0011305 | 1 | | 1 (0)| 00:00:01 |
|* 9 | FILTER | | | | | |
|* 10 | RECURSIVE WITH PUMP | | | | | |
|* 11 | INDEX UNIQUE SCAN | SYS_C0011306 | 1 | 5 | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | AUFTRAG | 1 | 10 | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | SYS_C0011305 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("AUFTRAG"."ORDER_ID_UNIQ"= (SELECT "CNT"+ (SELECT "ORDER_ID_UNIQ" FROM "AUFTRAG"
"AUFTRAG" WHERE "ID"=TO_NUMBER(:ID)) FROM (SELECT COUNT("ORDER_ID_UNIQ") "CNT" FROM "FORID" "FORID")
"from$_subquery$_005"))
8 - access("ID"=TO_NUMBER(:ID))
9 - filter( NOT EXISTS (SELECT 0 FROM "AUFTRAG" "TMP" WHERE "TMP"."ORDER_ID_UNIQ"=:B1+1))
10 - filter("ORDER_ID_UNIQ"<10000000000000000000000)
11 - access("TMP"."ORDER_ID_UNIQ"=:B1+1)
13 - access("ID"=TO_NUMBER(:ID))
32 rows selected
еще раз напомню, что вариант с рекурсией имеет результат хороший, если ORDER_ID_UNIQ не имеет больших пустот.
