запрос такой
INSERT INTO SESSION_PROTO_CLASS (SESSION_ID,
CLASTER_TYPE_TYPE_ID,
CLASS_ID,
SESSION_RADIUS,
STAMP)
SELECT S.SESSION_ID,
P.CTYPE_TYPE,
C.CLASS_ID,
P.R,
S.STAMP
FROM SESSIONS S,
SHARED_PROTO_CLASS P,
CLASS C,
V_LAST_CLASTER_HEADER L,
(SELECT DISTINCT SN.SENSOR_ID
FROM EXT_SENSOR E,
SENSOR SN
WHERE E.SENSOR IS NOT NULL
AND RTRIM(SN.SENSOR_NAME) = RTRIM(RTRIM(E.SENSOR), CHR(13))) N
WHERE S.SESSIONKEY = P.SESSIONKEY
AND S.STAMP BETWEEN ( SELECT MIN(MIN_STAMP)
FROM CHAM_STAMP)
AND (SELECT MAX(MAX_STAMP)
FROM CHAM_STAMP)
AND S.SENSOR_ID = N.SENSOR_ID
AND C.CLASS_NUMBER = P.CLASS_NUMBER
AND P.CLASTER_TYPE = L.CLASTER_NUMBER
AND L.TYPE_REVISION_ID = C.TYPE_REVISION_ID
AND P.CTYPE_TYPE = L.CLASTER_TYPE_TYPE_ID
план с рабочей БД
INSERT STATEMENT ALL_ROWSCost: 179 394 Bytes: 829 310 Cardinality: 6 530
25 HASH JOIN "S"."SENSOR_ID"="N"."SENSOR_ID"Cost: 179 388 Bytes: 829 310 Cardinality: 6 530
5 VIEW SCANNER1. Cost: 29 Bytes: 13 Cardinality: 1
4 SORT UNIQUE Cost: 29 Bytes: 274 Cardinality: 1
3 HASH JOIN RTRIM("SN"."SENSOR_NAME")=RTRIM(RTRIM("E"."SENSOR"),'')Cost: 28 Bytes: 1 096 Cardinality: 4
1 TABLE ACCESS FULL SCANNER1.SENSOR Cost: 3 Bytes: 17 Cardinality: 1
2 EXTERNAL TABLE ACCESS FULL SCANNER1.EXT_SENSOR "E"."SENSOR" IS NOT NULLCost: 24 Bytes: 104 856 Cardinality: 408
24 HASH JOIN "S"."SESSIONKEY"="P"."SESSIONKEY"Cost: 179 358 Bytes: 744 420 Cardinality: 6 530
16 HASH JOIN "M"."REVISION"="R"."REVISION_NUMBER" AND "M"."CLASTER_TYPE_ID"="R"."CLASTER_TYPE_ID"Cost: 1 965 Bytes: 528 930 Cardinality: 6 530
8 VIEW SCANNER1. Cost: 1 Bytes: 416 Cardinality: 16
7 SORT GROUP BY Cost: 1 Bytes: 96 Cardinality: 16
6 INDEX FULL SCAN SCANNER1.UQ_REVISION Cost: 1 Bytes: 228 Cardinality: 38
15 HASH JOIN "C"."TYPE_REVISION_ID"="R"."TYPE_REVISION_ID" AND "R"."CLASTER_TYPE_ID"="C"."CLASTER_TYPE_ID"Cost: 1 964 Bytes: 852 995 Cardinality: 15 509
9 TABLE ACCESS FULL SCANNER1.TYPE_REVISION Cost: 3 Bytes: 342 Cardinality: 38
14 HASH JOIN "C"."CLASS_NUMBER"="P"."CLASS_NUMBER"Cost: 1 959 Bytes: 11 414 670 Cardinality: 248 145
12 HASH JOIN "P"."CLASTER_TYPE"="C"."CLASTER_NUMBER" AND "P"."CTYPE_TYPE"="C"."CLASTER_TYPE_TYPE_ID"Cost: 423 Bytes: 5 989 137 Cardinality: 181 489
10 TABLE ACCESS FULL SCANNER1.CLASTER_TYPE Cost: 3 Bytes: 144 Cardinality: 16
11 TABLE ACCESS FULL SCANNER1.SHARED_PROTO_CLASS Cost: 418 Bytes: 6 533 616 Cardinality: 272 234
13 TABLE ACCESS FULL SCANNER1.CLASS Cost: 449 Bytes: 7 573 176 Cardinality: 582 552
23 PARTITION RANGE ITERATOR Cost: 177 392 Bytes: 3 404 280 Cardinality: 103 160 Partition #: 19
22 TABLE ACCESS BY LOCAL INDEX ROWID SCANNER1.SESSIONS Cost: 177 392 Bytes: 3 404 280 Cardinality: 103 160 Partition #: 19
21 INDEX RANGE SCAN SCANNER1.PK1 "S"."STAMP">= (SELECT MIN("MIN_STAMP") FROM "CHAM_STAMP" "CHAM_STAMP") AND "S"."STAMP"<= (SELECT MAX("MAX_STAMP") FROM "CHAM_STAMP" "CHAM_STAMP")Cost: 961 Cardinality: 185 689 Partition #: 19
18 SORT AGGREGATE Bytes: 11 Cardinality: 1
17 TABLE ACCESS FULL SCANNER1.CHAM_STAMP Cost: 3 Bytes: 11 Cardinality: 1
20 SORT AGGREGATE Bytes: 11 Cardinality: 1
19 TABLE ACCESS FULL SCANNER1.CHAM_STAMP Cost: 3 Bytes: 11 Cardinality: 1
план с тестовой бд, на которой всё жутко тормозит
INSERT STATEMENT ALL_ROWSCost: 1 174 Bytes: 185 Cardinality: 1
28 NESTED LOOPS Cost: 1 168 Bytes: 185 Cardinality: 1
25 HASH JOIN "M"."REVISION"="R"."REVISION_NUMBER" AND "M"."CLASTER_TYPE_ID"="R"."CLASTER_TYPE_ID"Cost: 1 167 Bytes: 174 Cardinality: 1
21 TABLE ACCESS BY INDEX ROWID SCANNER.TYPE_REVISION Cost: 3 Bytes: 18 Cardinality: 2
20 NESTED LOOPS Cost: 1 166 Bytes: 148 Cardinality: 1
18 HASH JOIN "S"."SENSOR_ID"="N"."SENSOR_ID"Cost: 1 163 Bytes: 139 Cardinality: 1
12 TABLE ACCESS BY LOCAL INDEX ROWID SCANNER.SESSIONS "S"."SESSIONKEY"="P"."SESSIONKEY"Cost: 1 Bytes: 52 Cardinality: 1
11 NESTED LOOPS Cost: 1 133 Bytes: 126 Cardinality: 1
4 NESTED LOOPS Cost: 1 132 Bytes: 74 Cardinality: 1
1 TABLE ACCESS FULL SCANNER.SHARED_PROTO_CLASS Cost: 1 132 Bytes: 65 Cardinality: 1
3 TABLE ACCESS BY INDEX ROWID SCANNER.CLASTER_TYPE Cost: 0 Bytes: 9 Cardinality: 1
2 INDEX RANGE SCAN SCANNER.IDX$$_00560001 "P"."CLASTER_TYPE"="C"."CLASTER_NUMBER" AND "P"."CTYPE_TYPE"="C"."CLASTER_TYPE_TYPE_ID"Cost: 0 Cardinality: 1
10 PARTITION RANGE ITERATOR Cost: 1 Cardinality: 1 Partition #: 12
9 INDEX RANGE SCAN SCANNER.PK1 "S"."STAMP">= (SELECT MIN("MIN_STAMP") FROM "CHAM_STAMP" "CHAM_STAMP") AND "S"."STAMP"<= (SELECT MAX("MAX_STAMP") FROM "CHAM_STAMP" "CHAM_STAMP")Cost: 1 Cardinality: 1 Partition #: 12
6 SORT AGGREGATE Bytes: 11 Cardinality: 1
5 TABLE ACCESS FULL SCANNER.CHAM_STAMP Cost: 3 Bytes: 11 Cardinality: 1
8 SORT AGGREGATE Bytes: 11 Cardinality: 1
7 TABLE ACCESS FULL SCANNER.CHAM_STAMP Cost: 3 Bytes: 11 Cardinality: 1
17 VIEW SCANNER. Cost: 29 Bytes: 13 Cardinality: 1
16 SORT UNIQUE Cost: 29 Bytes: 274 Cardinality: 1
15 HASH JOIN RTRIM("SN"."SENSOR_NAME")=RTRIM(RTRIM("E"."SENSOR"),'')Cost: 28 Bytes: 1 096 Cardinality: 4
13 TABLE ACCESS FULL SCANNER.SENSOR Cost: 3 Bytes: 17 Cardinality: 1
14 EXTERNAL TABLE ACCESS FULL SCANNER.EXT_SENSOR "E"."SENSOR" IS NOT NULLCost: 24 Bytes: 104 856 Cardinality: 408
19 INDEX RANGE SCAN SCANNER.UQ_REVISION "R"."CLASTER_TYPE_ID"="C"."CLASTER_TYPE_ID"Cost: 1 Cardinality: 2
24 VIEW SCANNER. Cost: 1 Bytes: 416 Cardinality: 16
23 SORT GROUP BY Cost: 1 Bytes: 96 Cardinality: 16
22 INDEX FULL SCAN SCANNER.UQ_REVISION Cost: 1 Bytes: 228 Cardinality: 38
27 TABLE ACCESS BY INDEX ROWID SCANNER.CLASS Cost: 1 Bytes: 11 Cardinality: 1
26 INDEX UNIQUE SCAN SCANNER.UQ_CLASS "C"."TYPE_REVISION_ID"="R"."TYPE_REVISION_ID" AND "C"."CLASS_NUMBER"="P"."CLASS_NUMBER"Cost: 0 Cardinality: 1